Thanks for great video! One question, in your example, you simulate weekly return actually, is it equivalent to use weekly return mean and weekly return stdev to proceed? ( is it equivalent to scale daily parameters to weekly then proceed ?) Thank you!
Speed everything up with VBA code, VBA formulas, Excel formulas through VBA , freeze the screen (screen.updating=false) first then compute everything then unfreeze the screen. Your computer can generate several hundreds of millions of pseudorandom numbers in a second and this was 15 years ago when I built simulation for bootstrapping confidence intervals for forecasts.
If you have the skill and the time, yes. But in places with security concerns VBA and macros may be forbidden. I find that can be a problem when running courses. Also for small jobs writing and debugging a macro is slower and more error prone than the simple technique demonstrated here. For serious work I would prefer to use a monte carlo package - the real cost of writing macros and creating code for sampling from different distributions or correlated variables, and graphing the outcomes can be shockingly high.
I have formula that can produce both negative and positive values but my output can't be negative. I try to treat negative values as zero but it truncated my output to positive and provide higher mean value , this help to over estimate the mean. If there is any way we can re-run excel formula to get only positive outputs for any iteration.
Hello I would to ask. How can I make Monte Carlo simulating if i have original numbers like: -150, +200,-90, +15.12, -55, +114,+113+500...... And i want to this numbers place tho the simulation and become another sequence. How can I simulate this numbers?
double A++ on the Excell, but I have serious reservations about the math. Since ever percent comes from a different base, growing bases shrinks returns and shrinking bases increase returns thus cycling produces growth estimates which probably are not real
@BI EXCEL On the first attempt I got the random numbers to generate, but when I did the same thing it started generating the same number for the different trials. I don't know whats happening. Please help. Thanks :) I already tried redoing it on a new workbook.
Hey this is an awesome account and I can't wait to see more! Can you quickly explain how to take this example one step further? Could you expand to show how to make this specific model project the probability of the S&P 500 fluctuating 5% MONHLY? Thank you this will be a big help!
To analyze a month instead of a week you need to add a bunch of rows and change Mon-Fri to 1-21. (There are about 21 trading days in a month). And copy the formulas down for all 21 days. Then you will run your analysis on the cumulative return on the 21st day. Hope that helps!
@@SenorJoeBiden do your own research man, lol, there are plenty of sources online...you can also do the calculations and create a distribution chart with the % changes of the returns, you will see it follows a Gaussian distribution or more exactly a Cauchy distribution...
@@davidgutierrez8795 The burden lies on you to provide evidence. You said that it is Gaussian and then said it's more exactly Cauchy. Gaussian and Cauchy are two different distributions with very different structures. For one thing, Gaussian tails are too thin while Cauchy tails are too heavy. Both do not accurately describe S&P daily returns.
Hi. We would get a Very similar result by taking the weekly average returns with r=(1+0.0003)^5, and std deviation with std=0.0097*sqrt(5). Is this results only coincidence, or is usual that these approaches ends up in very close outcomes? Nice video. Regards from Brazil!
It's to reflect the fact that the growth rates are cumulative. Assume the stock index grows by 1% on Monday and 2% on Tuesday. Then by the close of Monday the index level is (original value)*(1+1%). On Tuesday, the growth applies to its original value plus the growth that occurred on Monday. So by the close of Tuesday the level of the index is (original value)*(1+1%)*(1+2%). If you want to express the total growth of the index as a percentage, the formula would be: [ (original value)*(1+1%)*(1+2%)-(original value) ] / (original value) This simplifies to (1+1%)*(1+2%)-1. This is what is being reflected in the formula.
You are absolutely right but that isn't what she used in her calculation. Using your example, she used ((1+1%)+1)(1+2%). Please double check and let me know.
That is because you want the cumulative change between days. Take day 1 the change is 1%, day 2 the change is -0.3%, for the interval the total change would be: Initial value *(1+0.01) this gives us the value at the end of day 1, we continue: Initial value * (1+0.01)*(1-0.03) this is the value at day 2. If are interested in the porcentual change for the two days period is simply (1+i) being i our change for the two consecutive days. (1+0.01)*(1-0.03)=(1+i), if we solve for i we get: (1+0.01)*(1-0.03)-1=i , the used formula is correct.
When you say the daily Standard deviation is 0.97%. it is the st.dev of the stock price or the returns?I think it has t be the st.dev of returns.can some one confirm
+fadi al naji She is assuming that the S&P 500 returns are normally distributed. To get a meaningful result out of the simulation she is generating random returns which follows the same distribution. For this step she is using norminv.
She samples a normal distribution with the daily mean and std of S&P500. Therefore, if you choose a random number between 0-1, and take that together with the other parameters, you get as output the value of the S&P500 being less or equal with the used random probability.
+Jek Jekkey There are some plug-ins that enables excel to generate random numbers, which follow particular distributions, e.g. log-normal distribution, triangular distribution,... One plug-in is SIP-math
Instead of a random normal number you could also do a randomly chosen bootstrap sample to generate the returns, and use the same "data table" to pick the sample 10000 times. For instance you could use random 3 month samples out of the historical weekly S&P historical returns 1871 to present. The sample size is key though since if you make it too small (e.g. one week) you'd lose the serial correlation (return to mean) that separate stocks from random walks.
This video raises more questions than it answers. You've taken S&P500 data and completely butchered it in order to make a 3 minute video of a MC simulation. I hope nobody is placing trades based information from this video.
Amazing what this woman taught in 3 minutes. Honestly, spectacular! Used what she taught successfully.
Thanks! Half the internet said you couldn't do this, but I knew there had to be an easy way. This was perfect!
absolutely brilliant, can't believe so much was stuffed in in just 3 minutes
This woman is able to demonstrate complex calculations using simple methods, which is stunning.
Just the exact tutorial I have been looking for. She is the best.
really love this video. finally understand the meaning of Monte Carlo
Thank you for taking the time out to explain this in such simple terms.
You're welcome!
This was VERY helpful…and all in less than 4 minutes! Now can I get back the 2+ hrs I spent watching the other MCS videos? 🤔
Thanks Sarah. Valuable content!
very much to the point explanation. thank you!
Thankyou for helping out with the school project
Clever girl. Liked the direct approach. Thanks.
There is a saying in Russian "коротко и ясно", meaning "clear and concise". This is about this video.
Very nice use of Excel's Data Table! Thanks for sharing Sara.
Kevin Lehrbass Thanks! I think we need a little more Data Table love in general. It is probably time for a Data Table video.
You are a wizard, Sara.
Where did the equation come from at 1:13, the cumulative?
Thanks for great video! One question, in your example, you simulate weekly return actually, is it equivalent to use weekly return mean and weekly return stdev to proceed? ( is it equivalent to scale daily parameters to weekly then proceed ?) Thank you!
KILLER video.
I have been waiting for this day.. hurray. I was looking for how to save all your videos on the BI website.. the opportunity is here.
Thanks Emmanuel! I'm really excited about building this channel.
Thanks Sarah.
Great video!
Nice video! Just why are u using (A+1)*(B+1)-1 instead of simply adding (A+B). Is an error to simply add the percentages in the cumulative?
I think no! It's easier A+B
I think she is coumpunding the returns which makes more sense than simply adding them
Yes, compound interest is multiplication.
Speed everything up with VBA code, VBA formulas, Excel formulas through VBA , freeze the screen (screen.updating=false) first then compute everything then unfreeze the screen. Your computer can generate several hundreds of millions of pseudorandom numbers in a second and this was 15 years ago when I built simulation for bootstrapping confidence intervals for forecasts.
If you have the skill and the time, yes. But in places with security concerns VBA and macros may be forbidden. I find that can be a problem when running courses.
Also for small jobs writing and debugging a macro is slower and more error prone than the simple technique demonstrated here.
For serious work I would prefer to use a monte carlo package - the real cost of writing macros and creating code for sampling from different distributions or correlated variables, and graphing the outcomes can be shockingly high.
Hey! Could anyone please explain to me what she did for cell "Tuesday" x "Cumulative"? Thanks y'all!
Great tutorial. Thanks for sharing
GRacias!!!!!, please more videos on power BI, excel, R,Phyton.
Fantastic!
amazing explanation. Thank you!
You're welcome!
My 3 hrs of lecture in just 3 minutes.. and much better understood..
Bravo! Subsribed!
thanks. nice short and sweet explanation.
you're welcome!
Thanks for the solution
I have formula that can produce both negative and positive values but my output can't be negative. I try to treat negative values as zero but it truncated my output to positive and provide higher mean value , this help to over estimate the mean. If there is any way we can re-run excel formula to get only positive outputs for any iteration.
6 years late but abs
Thanks Sara!
Hello I would to ask. How can I make Monte Carlo simulating if i have original numbers like: -150, +200,-90, +15.12, -55, +114,+113+500...... And i want to this numbers place tho the simulation and become another sequence. How can I simulate this numbers?
It should work in exactly the same way, just that your average and standard deviation will be numbers instead of percentages.
double A++ on the Excell, but I have serious reservations about the math. Since ever percent comes from a different base, growing bases shrinks returns and shrinking bases increase returns thus cycling produces growth estimates which probably are not real
Thank you very much! Still very helpful!
@BI EXCEL On the first attempt I got the random numbers to generate, but when I did the same thing it started generating the same number for the different trials. I don't know whats happening. Please help. Thanks :) I already tried redoing it on a new workbook.
Hey this is an awesome account and I can't wait to see more!
Can you quickly explain how to take this example one step further? Could you expand to show how to make this specific model project the probability of the S&P 500 fluctuating 5% MONHLY? Thank you this will be a big help!
To analyze a month instead of a week you need to add a bunch of rows and change Mon-Fri to 1-21. (There are about 21 trading days in a month). And copy the formulas down for all 21 days. Then you will run your analysis on the cumulative return on the 21st day.
Hope that helps!
Pretty big assumption that the S&P daily returns are normal. Cool video!
S van der Heijden it is in fact Gaussian
@@davidgutierrez8795 can you please provide a source, David?
@@SenorJoeBiden do your own research man, lol, there are plenty of sources online...you can also do the calculations and create a distribution chart with the % changes of the returns, you will see it follows a Gaussian distribution or more exactly a Cauchy distribution...
@@davidgutierrez8795 The burden lies on you to provide evidence. You said that it is Gaussian and then said it's more exactly Cauchy. Gaussian and Cauchy are two different distributions with very different structures. For one thing, Gaussian tails are too thin while Cauchy tails are too heavy. Both do not accurately describe S&P daily returns.
Central Limit Theorem
Subscribed
Brilliant!
Amazing
Great ... just great job...
Omg. Love you queen
Hi.
We would get a Very similar result by taking the weekly average returns with r=(1+0.0003)^5, and std deviation with std=0.0097*sqrt(5).
Is this results only coincidence, or is usual that these approaches ends up in very close outcomes?
Nice video. Regards from Brazil!
The BEST
Does anyone know of a way to do this same type of thing (simulate 10,000 times at once) in google sheets?
If you get a bunch of the same numbers, press f9 and that may refresh it so that you get the random numbers you hoped for.
Excellent
is 10k the max amount of simulations you can run?
What's the formula for NORMINV though? I can't see how this function works anywhere as I am trying to code this in PHP
How are you getting % sign on the days numbers
Great thanks
where did you get the data from? is there any website I can find the information, too?
Get the daily historical data from Yahoo finance and use excel to calculate the daily mean return and standard deviation
confused why you do (A+1)*(B+1) -1?
It's to reflect the fact that the growth rates are cumulative. Assume the stock index grows by 1% on Monday and 2% on Tuesday. Then by the close of Monday the index level is (original value)*(1+1%). On Tuesday, the growth applies to its original value plus the growth that occurred on Monday. So by the close of Tuesday the level of the index is (original value)*(1+1%)*(1+2%).
If you want to express the total growth of the index as a percentage, the formula would be:
[ (original value)*(1+1%)*(1+2%)-(original value) ] / (original value)
This simplifies to (1+1%)*(1+2%)-1. This is what is being reflected in the formula.
You are absolutely right but that isn't what she used in her calculation. Using your example, she used ((1+1%)+1)(1+2%). Please double check and let me know.
Thnksss...
That is because you want the cumulative change between days. Take day 1 the change is 1%, day 2 the change is -0.3%, for the interval the total change would be: Initial value *(1+0.01) this gives us the value at the end of day 1, we continue: Initial value * (1+0.01)*(1-0.03) this is the value at day 2. If are interested in the porcentual change for the two days period is simply (1+i) being i our change for the two consecutive days. (1+0.01)*(1-0.03)=(1+i), if we solve for i we get: (1+0.01)*(1-0.03)-1=i , the used formula is correct.
Nothing is interesting about the Monta Carlo simulation of a single distribution/ It is only useful for analyzing the combinations of distributions.
When you say the daily Standard deviation is 0.97%. it is the st.dev of the stock price or the returns?I think it has t be the st.dev of returns.can some one confirm
It's the standard deviation of the daily returns.
Hi mam plz explain how to use this tricks in stock market plz
I love it
What if we only want the positive return to price a european call? Thank you very much! :)
Try this: ua-cam.com/video/r67_YRtYcR8/v-deo.html
great!!!
Cool
Why are you using norminv?
+fadi al naji
She is assuming that the S&P 500 returns are normally distributed. To get a meaningful result out of the simulation she is generating random returns which follows the same distribution. For this step she is using norminv.
She samples a normal distribution with the daily mean and std of S&P500. Therefore, if you choose a random number between 0-1, and take that together with the other parameters, you get as output the value of the S&P500 being less or equal with the used random probability.
Central Limit Theorem
I love you.
Thnkssss...
what a badass
I have some questions
How can i do if data isn't not normal distribution?
+Jek Jekkey
There are some plug-ins that enables excel to generate random numbers, which follow particular distributions, e.g. log-normal distribution, triangular distribution,... One plug-in is SIP-math
Instead of a random normal number you could also do a randomly chosen bootstrap sample to generate the returns, and use the same "data table" to pick the sample 10000 times. For instance you could use random 3 month samples out of the historical weekly S&P historical returns 1871 to present. The sample size is key though since if you make it too small (e.g. one week) you'd lose the serial correlation (return to mean) that separate stocks from random walks.
That's a bunch of variability there.
Hey, woman! Come back to youtube!!!
Brill video Sara
Have a good day
That's why I stick with python. All those clicking around and adding formulas on a spreadsheet drives me crazy.
HI Ahmad , how u use Python to combat these formulas inexcel. I m learning python, but it will be great if u guide me a bit. @ ak7862000@gmail.com
how woud I even graph this?
With a histogram. It would show you the distribution of possible outcomes.
The cumulative calculation is questionable. Who says the Video needs to be hurried upin 3 mts, leaving doubts to those who watch it.
Hit me up, i will show an easy way around for this one 1:25
Do these a week before covid 19 shut us down... Place your bets and get wipe out :) . Cool excel tips.
Very fast, difficult for beginners
slow it down to 0.5 the normal play speed!
good tutorial but this method isn't accurate...
OHHHHHH HOTTIE ARRIVED AT THE END OF THE VIDEO...!!!!
Jı
in israel sarah and silverstein are common names
This video raises more questions than it answers. You've taken S&P500 data and completely butchered it in order to make a 3 minute video of a MC simulation. I hope nobody is placing trades based information from this video.
I think the point of this video is to demonstrate how to conduct a Monte Carlo simulation, and not how to invest your money based on S&P 500 data.
Jus a quick demonstration of the function. But yes she shouldve added a disclaimer