标题: Monte Carlo simulation in excel [打印本页] 作者: MythosHF 时间: 2011-9-22 21:16 标题: Monte Carlo simulation in excel
Hi guys,
I have a practical problem for you.
There are 255 daily qoutations of a stock, and the distribution of the qoutes changes is normal.
I want to construct a Monte Carlo simulation of the stock qoutes changes using rand() function in excel generating 5000 records using the exact function min+rand()*(max-min).
The random numbers are in the range 0-1, so my expectation is that the generated 5000 otcomes are normally distributed as the input (the initial qoutes) but in fact it is not.
Am I wrong in steps taken or my expectation is corrupted?
An on the real topic:
Level I - too many pages, nothing difficult
Level II - started "THE BIG BOOKS" yesterday. Ethics the same as level I by now.
Thanks for answers in advance.作者: willsucceed 时间: 2011-9-22 21:26
What are "min" and "max"?作者: Houjichasan 时间: 2011-9-22 21:37
ohai Wrote:
-------------------------------------------------------
> What are "min" and "max"?
You're generating uniform random variables between -3 and 5%. If you want normal random variables, you won't be able to use that exact function.
What is the variance of this normal curve (which will be necessary to generate normal rvs)? You could relate it to the min/max values by saying, perhaps, the minimum observed value (here -3%) is the 5th percentile, and the maximum observed movement (5%) is the 95% percentile, or whatever percentiles you want. The mean is 1%. Let me know more and I can help you generate normal rvs, unless I have completely misunderstood what you're asking for.作者: AndyNZ 时间: 2011-9-22 21:57
chaddy is right, you're generating uniform random numbers this way.
This is what I would do:
-Define the mean (probably 1% here)
-Define the standard deviation
-Generate normally distributed random numbers using the RAND and NORMINV function作者: therecruit 时间: 2011-9-22 22:08
Just to help you out a little more. I would pick a standard dev of around 2%, with a mean of 1.2%. Recall that 67% of results are within 1 std dev of mean, 96-ish% are within 2. From your limited results above, I think you want 3% and -1% within the first deviation band, and probably <5% outside of 4% from mean.
Your excel formula is then: =NORMINV(rand(),0.012,0.02) for your simulation.作者: WarrenB1 时间: 2011-9-22 22:18
What are you actually trying to achieve Bravo? Are you trying to generate a random walk or trying to simulate random changes to the bid-ask spread?
If you're trying to generate a random walk, then there are lots of examples of how to generate a simple random walk in excel or matlab on the web.
