Excel Based Stock Market Analysis Using @Risk
Budget: ₹400 – ₹750 INR
I am looking for an expert who an use the @Risk software using Excel and perform some analysis on stock market data.
1. The Excel workbook provides returns on the Dow Jones Index futures contract from 3/14/2024
to 2/28/2025. (In practice the market value of the Mini Dow is $5 per tick, but we will just use
the index as provided)
a. Provide an excel graph of index values over time.
b. Using distribution fitting, what is the Best-Fit distribution? Provide a graphic of the Best
Fit distribution .
c. Explain in words what the (statistical or modeling) costs would be between using the
best-fit distribution and the theoretical log-normal distribution.
d. As a modeler and financial economist what is the best rationale for using the lognormal
distribution?
e. Compute the log (natural log) change of DOW index values over its entire range. Fit
these data to a distribution. What distribution would you expect to find, and is it
consistent with theory?
f. From e, using excel functions for average and standard deviation calculate the
annualized mean drift (multiply average by 250 days) and volatility (multiply standard
deviation by sqrt(250)) (Recall that because you use daily data the average is the daily
average return and standard deviation is standard deviation for a daily change).
g. What does the term ‘drift rate mean in economic terms?
h. What does the term ‘volatility’ mean in economic terms?
2. Go to Q2 DOW tab. In finance a random walk on prices is typically described by the following
process ( ) ( ) 2 .5 0,1
1
tN t P Pe t t
µσ σ − +
= −
Where µ is the drift or periodic growth of the process in years, σ is the annualized volatility
(standard deviation) of the percentage change, N ( ) 0,1 is a standard normal deviate or
RiskNormal(0,1) , and t is percent of year (e.g for 1 day t=1/250, 1 week t=1/52, 1 month t=1/12 and
so on). This is slightly different than the example used in class which was always for 1/250.
a) Using the two parameters computed in 1(f) generate a 250 day random walk assuming
geometric Brownian motion starting with 39,548 (which is the first futures value in DOW data)
and generating the walk for each day in the provided series. Provide a graph with the original
prices for 1st 250 days (in Column B), as well as 3 randomly generated price series from Column
C (e.g. hit F9 (recalculation) 3 times, copy and paste the randomly generated numbers as values;
create graph). Provide some commentary about what you observe in this graph and the use of
Monte Carlo simulation.
b) Build three simulations similar to part (a) but for 52 periods (i.e. weekly) using t = 1/52th of a
year (in cell B4) , monthly using t=1/12th of a year, and yearly t=1.0. For day 250, week 52,
month 12, and 1 year, provide the overlay graph of the probability distributions. Report the
mean, standard deviation and skewness. What do you observe? What does this say about time
scaling in random walk models? (Hint: in spreadsheet the last period in time series should
populate as output in cells F2:F5.)
c) Using the weekly model Provide a Summary Graph. Does this graph show the classical linear-invariance property? Explain your response. Include an illustration of the variance ratio (Hint:
compute the log change in index relative to week 1, add to output, take ratio of variance of
week k to week 1.)
3. The current price of the May 2025 corn futures contract traded on the CME was $4.69/bushel
Assume that the futures contract expires in 54 calendar days or 40 trading days. For futures
contracts the drift rate is assumed to be zero (0) when used to price put and call options (this is
because of certain arbitrage arguments). The annualized implied volatility of corn futures is
21.5% or 0.215. The current interest rate for discounting is 4.20%. The actual prices for Put and
Call options on March 2, 2025 are reported in the worksheet. (These are actually American
options which have an early exercise feature, but we will assume they are European style
options exercisable on day 40 only. Generally speaking American call options are closer to
European prices while put prices are typically higher because early exercise feature is more valuable)
And a few more tasks...
Task is quite urgent. Need immediate analysis.
1. The Excel workbook provides returns on the Dow Jones Index futures contract from 3/14/2024
to 2/28/2025. (In practice the market value of the Mini Dow is $5 per tick, but we will just use
the index as provided)
a. Provide an excel graph of index values over time.
b. Using distribution fitting, what is the Best-Fit distribution? Provide a graphic of the Best
Fit distribution .
c. Explain in words what the (statistical or modeling) costs would be between using the
best-fit distribution and the theoretical log-normal distribution.
d. As a modeler and financial economist what is the best rationale for using the lognormal
distribution?
e. Compute the log (natural log) change of DOW index values over its entire range. Fit
these data to a distribution. What distribution would you expect to find, and is it
consistent with theory?
f. From e, using excel functions for average and standard deviation calculate the
annualized mean drift (multiply average by 250 days) and volatility (multiply standard
deviation by sqrt(250)) (Recall that because you use daily data the average is the daily
average return and standard deviation is standard deviation for a daily change).
g. What does the term ‘drift rate mean in economic terms?
h. What does the term ‘volatility’ mean in economic terms?
2. Go to Q2 DOW tab. In finance a random walk on prices is typically described by the following
process ( ) ( ) 2 .5 0,1
1
tN t P Pe t t
µσ σ − +
= −
Where µ is the drift or periodic growth of the process in years, σ is the annualized volatility
(standard deviation) of the percentage change, N ( ) 0,1 is a standard normal deviate or
RiskNormal(0,1) , and t is percent of year (e.g for 1 day t=1/250, 1 week t=1/52, 1 month t=1/12 and
so on). This is slightly different than the example used in class which was always for 1/250.
a) Using the two parameters computed in 1(f) generate a 250 day random walk assuming
geometric Brownian motion starting with 39,548 (which is the first futures value in DOW data)
and generating the walk for each day in the provided series. Provide a graph with the original
prices for 1st 250 days (in Column B), as well as 3 randomly generated price series from Column
C (e.g. hit F9 (recalculation) 3 times, copy and paste the randomly generated numbers as values;
create graph). Provide some commentary about what you observe in this graph and the use of
Monte Carlo simulation.
b) Build three simulations similar to part (a) but for 52 periods (i.e. weekly) using t = 1/52th of a
year (in cell B4) , monthly using t=1/12th of a year, and yearly t=1.0. For day 250, week 52,
month 12, and 1 year, provide the overlay graph of the probability distributions. Report the
mean, standard deviation and skewness. What do you observe? What does this say about time
scaling in random walk models? (Hint: in spreadsheet the last period in time series should
populate as output in cells F2:F5.)
c) Using the weekly model Provide a Summary Graph. Does this graph show the classical linear-invariance property? Explain your response. Include an illustration of the variance ratio (Hint:
compute the log change in index relative to week 1, add to output, take ratio of variance of
week k to week 1.)
3. The current price of the May 2025 corn futures contract traded on the CME was $4.69/bushel
Assume that the futures contract expires in 54 calendar days or 40 trading days. For futures
contracts the drift rate is assumed to be zero (0) when used to price put and call options (this is
because of certain arbitrage arguments). The annualized implied volatility of corn futures is
21.5% or 0.215. The current interest rate for discounting is 4.20%. The actual prices for Put and
Call options on March 2, 2025 are reported in the worksheet. (These are actually American
options which have an early exercise feature, but we will assume they are European style
options exercisable on day 40 only. Generally speaking American call options are closer to
European prices while put prices are typically higher because early exercise feature is more valuable)
And a few more tasks...
Task is quite urgent. Need immediate analysis.