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!
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?
@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.
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.
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
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!
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
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.
+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.
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.
+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!
This woman is able to demonstrate complex calculations using simple methods, which is stunning.
absolutely brilliant, can't believe so much was stuffed in in just 3 minutes
Thank you for taking the time out to explain this in such simple terms.
You're welcome!
Where did the equation come from at 1:13, the cumulative?
Just the exact tutorial I have been looking for. She is the best.
really love this video. finally understand the meaning of Monte Carlo
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? 🤔
Very helpful, thanks!
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!
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.
Hey! Could anyone please explain to me what she did for cell "Tuesday" x "Cumulative"? Thanks y'all!
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.
You are a wizard, Sara.
Thanks Sarah. Valuable content!
very much to the point explanation. thank you!
Clever girl. Liked the direct approach. Thanks.
There is a saying in Russian "коротко и ясно", meaning "clear and concise". This is about this 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.
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
Thankyou for helping out with the school project
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.
Does anyone know of a way to do this same type of thing (simulate 10,000 times at once) in google sheets?
My 3 hrs of lecture in just 3 minutes.. and much better understood..
@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.
Great tutorial. Thanks for sharing
KILLER video.
Great video!
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.
GRacias!!!!!, please more videos on power BI, excel, R,Phyton.
Thanks Sarah.
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
amazing explanation. Thank you!
You're welcome!
thanks. nice short and sweet explanation.
you're welcome!
Fantastic!
How are you getting % sign on the days numbers
Thank you very much! Still very helpful!
is 10k the max amount of simulations you can run?
Thanks Sara!
Thanks for the solution
Bravo! Subsribed!
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!
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.
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
Brilliant!
Great ... just great job...
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
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!
Hi mam plz explain how to use this tricks in stock market plz
Great thanks
Omg. Love you queen
Amazing
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
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
Excellent
Subscribed
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.
What if we only want the positive return to price a european call? Thank you very much! :)
Try this: ruclips.net/video/r67_YRtYcR8/видео.html
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.
The BEST
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.
I love it
great!!!
Thnkssss...
Hit me up, i will show an easy way around for this one 1:25
I love you.
what a badass
Nothing is interesting about the Monta Carlo simulation of a single distribution/ It is only useful for analyzing the combinations of distributions.
Show us how it's done then.
Brill video Sara
Have a good day
Hey, woman! Come back to youtube!!!
Do these a week before covid 19 shut us down... Place your bets and get wipe out :) . Cool excel tips.
The cumulative calculation is questionable. Who says the Video needs to be hurried upin 3 mts, leaving doubts to those who watch it.
how woud I even graph this?
With a histogram. It would show you the distribution of possible outcomes.
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
Very fast, difficult for beginners
slow it down to 0.5 the normal play speed!
good tutorial but this method isn't accurate...
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
OHHHHHH HOTTIE ARRIVED AT THE END OF THE VIDEO...!!!!
Jı
in israel sarah and silverstein are common names