Check out my sequel video where I walk thru a Python based approach medium.com/analytics-vidhya/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-with-python-mcerp-7d57e63112fa
Hey Zhijing, firstly, this is amazing and very helpful. Thanks for the content. It made life easy. One thing I can't get my head around is how the correlation matrix works in understanding those values and attributing them to the elements. I have gone through your links posted for more examples and answers in the comments yet I can't seem to wrap my head around it. How does the risked value lets say in accommodation, understand that there is a correlation of 0.85 between its value and the value of meals. I do not see any link in those tabs. The function reads numbers from a table that do not have the elements linked. I might be super confused, but I've been scratching my head around this a lot. Hope you can help...
Thank you so much! Really appreciate! 💗 but I have problem, anyone here using Macbook? The RiskPert formula in Excel is not working with the formula shown on the video. Appreciate your comments.
Hello Zhijing Eu, Thank you for this great video. Only it is not clear to me how you wired the covariance calculations in the example in the spreadsheet (the table is clear, but where and how is it connected in the model?) Can you please specify what formula's you used and in what lines/cells in the spreadsheet and in the results pagina we can find this? That would be really very helpful. Thank you!
In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 as a ",RiskCorrmat" suffix to the whatever risk distribution is being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample
Hi Zhi, Thank you for this detailed elaboration. If we have one of the cost component, for which we are sure that that has only one cost estimate, can we assume its lowest, highest and most likely value to be same and include it in the sum?
@@Mzalikhan1983 if the cost element really has zero variability just add it to the sum total but as a flat input (I.e Just a static fixed number instead of an XlRisk Formula) The only thing is that the cost element won't show up in the risk tornado chart since there is no expected risk of change in the estimate.
@@Mzalikhan1983 have you checked if the low, most likely high values are valid? There is a limit to how much you can "stretch" or "squish" the pert shape ... You might want to post a question on the official XlRisk github page too github.com/pyscripter/XLRisk/issues
You must be an advanced user I only have Office 365 not 366 🤣. On a more serious note I think XLRisk only works with the excel desktop version (Excel 2010 onwards) but not excel online. github.com/pyscripter/XLRisk
You can run Monte Carlo Sims using just Numpy and Scipy. However this can be a a bit tedious to set up so there are a few Python Libraries that simplify this process in terms of providing more choices for sampling methods, managing sub-simulations and summarising the results PyMCSL pymcsl.readthedocs.io/en/latest Monaco monaco.readthedocs.io/en/latest MCerp github.com/tisimst/mcerp There are lots of examples online if you do a general web search. However most tutorials skip a critical element - how to induce rank correlation between the random input variables. Without this, the simulation will assume that all input variables are independent and uncorrelated which is usually not reflective of most modelled phenomenon. Therefore simulation outcomes will end up underestimating the variability due to the Central Limit Theorem statisticsbyjim.com/basics/central-limit-theorem/ However I can recommend this one example that does cover the use of correlated variables using MCerp : towardsdatascience.com/journey-to-monte-carlo-mc-simulations-with-correlated-variables-in-python-1aef84d5742d
@@shiwaninaik3935 glad I could help. I'm planning to redo this tutorial video using Python instead of XL Risk when I get a bit of time later this year so stay tuned.
Hello Zhijing Eu, thanks a lot for your video. i try to use monte carlo for QMRA. Is it true if i say graph cummulative distribution describe about variability? I found out about how to make uncertainty with monte carlo, and it said that i need to second-order. But i confused about parameter input that i use in second-order. And i cant find Tornado diagram in my XLRisk. i use 2 parameter input for my output. does it affect the output of the diagram?
I don't quite understand your question. This tool does not assess the likelihood or impact but assumes you know enough about the phenomenon to select an appropriate probability distribution to simulate it's behavior...you can create two variables- one being a binomial on-off representing likelihood and another variable to reflect the impact range and multiply both distributions to get the overall effect. Is that what you meant ? If you have actual observed data you can even build your own custom distributions. Unfortunately you can't do it in XL Risk but commercial packages like Palisade @Risk or Oracle Crystal Ball have this feature
Suppose I am calculating the uncertainty in a quality H, where as H= a x b x c x d and in here, b,c and d, all three have low, most likely and high values available, should I calculate low, most likely and high values of H first and then calculate uncertainty using three values of H? OR i should first calculate risk vales for b,c and and then some how use them to calculate the uncertainty in value of H?
If you have the underlying data / records to build an estimate at the finer granularity (i.e a,b,c ,d) you should use that and let the simulation results determine the range of the coarser variable H. The trick will be whether you have the cross correlation data between a,b,c,d . Unless you apply some form of correlation , the central limit theorem will make the variability "cancel out" and you will likely underestimate the true amount of variability in H. You should also consider the potential problem of mismatched granularity in model input variables i.e if you model a,b,c,d as inputs but the other model inputs are more at the level of H as this can make interpreting the input drivers a bit harder.
@@ZhijingEu I believe a.b.c. and d are coming from independent sources and have no correlation in between. So I would calculate the low value of H, using low values of b,c, and d, (similarly most like and high values of H) and then apply pertrisk on three values of H to calculate uncertainty. Thank you Zhi, XLR is extremely user friendly and your guidance was really helpful. Best Regards
@@Mzalikhan1983 glad you found the content helpful. Good luck and on a final note, do watch out about your assumption that the variables are independent and uncorrelated. Case in point : www.barrons.com/articles/when-markets-crash-everythings-correlated-even-factors-51548324000
When I open the excel and enable the content all of the formulas become invalid for 'Risked Value' cells. Do I need to link it to the XLrisk file? This is whats in the cell "=@'C:\Users\Lenovo\Desktop\Programming\Excel\XLRisk\XLRisk.xlam'!RiskPert(C4,D4,E4)"
In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 as a ",RiskCorrmat" suffix to the whatever risk distribution is being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample
hi, looks like a great tool, I'm doing a university course however I am a complete novice in excel and have not a clue how you make this run have you left info out of your video. Also where do I find your written tutorial.
Hello Zhijing Eu, thanks for your kind effort. Im trying to practice the simulation by watching your video. I've used the same variables and the same values like yours. I've installed the XLRisk correctly. However, my resluts shows no outputs, no graphs but inputs only. Could you please guide me to solve the issue? Thanks in advance.
Not exactly MC simulations but similar concept that uses Geometric Brownian Motion to model share price behavior th-cam.com/video/irMSmRUlIkc/w-d-xo.html (Spoiler Alert : It doesn't work as well as you'd think)
Hey Zhijing, can you show it again how you linked the correlation matrix with results in I4:I9. I am getting following message when I try to run simulation. "The risk functions have not been properly linked to the correlation matrix".
In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 via the ",RiskCorrmat" suffix to the risk distribution being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample
Tx Zhijing, but you must have done something in addition to RiskCormax in order to enhance the correlation. I see that cells in I4:I9 are named i.e., I5 is "AccomCost" etc... Have you connected it somehow to Corr matrix?
You need to ensure you name the range for the Corr matrix but also specify which variable you are referring to i.e I5 Accom Cost is ", RiskCorrmat($G$14:$I$16,1)" , I6 Meals is ", RiskCorrmat($G$14:$I$16,2)" and I8 Holiday Tours is ", RiskCorrmat($G$14:$I$16,3)"
Can you help me with something? I'm getting the following error with corr matrix: "The table (referenced) doesn't have all off-diagonal values between -1 and 1". The thing is that it is, and there is nothing on githup about how to deal with it.
Under the tab Uncertain Range+Events+Corr, there is a table in cells G14:I16 that represents the correlation matrix. Maybe you accidentally changed the values in the non diagonal cells? (H14, I14, G15, I15, G16, H16)
@@ZhijingEu Your is perfect, I'm using another one, the values for the correlation differ from -0,27 to 1 with 19 variables (diagonal all 1), I've tried to change the numbers and reduce the 19 but receive the same message.
Hmmmm... This is tricky. First try to set the non diagonals as zeroes just to see if it still runs and it's not an XLRisk problem. If not then it could be that the correlation matrix is inconsistent. kb.palisade.com/index.php?pg=kb.page&id=75 Unfortunately unlike commercial software like @Risk or Frontline Solver which have Auto Corr matrix adjustment tools, you might have to find a way to use Python to calc the eigen values... if you know a bit of coding you might be able to apply the method described here to adjust your coefficients community.wolfram.com/groups/-/m/t/1078904
Try these stackoverflow.com/questions/10939213/how-can-i-calculate-the-nearest-positive-semi-definite-matrix or stackoverflow.com/questions/43238173/python-convert-matrix-to-positive-semi-definite
The Best Explanation I have seen in years, thank you
Came for monte carlo , stayed for an open source tool, XL Risk !
Check out my sequel video where I walk thru a Python based approach medium.com/analytics-vidhya/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-with-python-mcerp-7d57e63112fa
Great guide, thanks a lot!
Thank you Zhijing!! Great explanation
mvl fuuuuul doing risk without montecarlo simulations, he is a big fuuullllllll of the world
Very well explained, clear, useful..thank you!
Hey Zhijing, firstly, this is amazing and very helpful. Thanks for the content. It made life easy. One thing I can't get my head around is how the correlation matrix works in understanding those values and attributing them to the elements. I have gone through your links posted for more examples and answers in the comments yet I can't seem to wrap my head around it. How does the risked value lets say in accommodation, understand that there is a correlation of 0.85 between its value and the value of meals. I do not see any link in those tabs. The function reads numbers from a table that do not have the elements linked. I might be super confused, but I've been scratching my head around this a lot. Hope you can help...
great Explanation
Zhijing, amazing explanation. Very didatic. Many thanks. If you allow me please inform in the details of the video the link to download your file.
github.com/ZhijingEu/MonteCarloSimulation
Thank you so much! Really appreciate! 💗 but I have problem, anyone here using Macbook? The RiskPert formula in Excel is not working with the formula shown on the video. Appreciate your comments.
It's working! I am using this Add-ins!😍
Hello Zhijing Eu, Thank you for this great video. Only it is not clear to me how you wired the covariance calculations in the example in the spreadsheet (the table is clear, but where and how is it connected in the model?) Can you please specify what formula's you used and in what lines/cells in the spreadsheet and in the results pagina we can find this? That would be really very helpful. Thank you!
In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 as a ",RiskCorrmat" suffix to the whatever risk distribution is being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample
Hi Zhi, Thank you for this detailed elaboration. If we have one of the cost component, for which we are sure that that has only one cost estimate, can we assume its lowest, highest and most likely value to be same and include it in the sum?
@@Mzalikhan1983 if the cost element really has zero variability just add it to the sum total but as a flat input (I.e Just a static fixed number instead of an XlRisk Formula) The only thing is that the cost element won't show up in the risk tornado chart since there is no expected risk of change in the estimate.
@@Mzalikhan1983 have you checked if the low, most likely high values are valid? There is a limit to how much you can "stretch" or "squish" the pert shape ... You might want to post a question on the official XlRisk github page too github.com/pyscripter/XLRisk/issues
My bad Zhi, it works perfectly fine when i wrote =RiskPert(D2,E2,F2), It gave me error previously when I was using =RiskPert(D2:F2).
Great explanation thank you.
I’m an office for Mac user, can I use this addin with my office Excel 366? Thanks
You must be an advanced user I only have Office 365 not 366 🤣. On a more serious note I think XLRisk only works with the excel desktop version (Excel 2010 onwards) but not excel online. github.com/pyscripter/XLRisk
@@ZhijingEu Thank you for sharing the excelRisk ❤️
Do you have link or information for cash flow forecasting using Monte Carlo simulation?
Excelent, and the Open Source tool also
That was nice explanation. Could we be able to do it using python ?
Thanks
You can run Monte Carlo Sims using just Numpy and Scipy.
However this can be a a bit tedious to set up so there are a few Python Libraries that simplify this process in terms of providing more choices for sampling methods, managing sub-simulations and summarising the results
PyMCSL pymcsl.readthedocs.io/en/latest
Monaco monaco.readthedocs.io/en/latest
MCerp github.com/tisimst/mcerp
There are lots of examples online if you do a general web search. However most tutorials skip a critical element - how to induce rank correlation between the random input variables.
Without this, the simulation will assume that all input variables are independent and uncorrelated which is usually not reflective of most modelled phenomenon. Therefore simulation outcomes will end up underestimating the variability due to the Central Limit Theorem statisticsbyjim.com/basics/central-limit-theorem/
However I can recommend this one example that does cover the use of correlated variables using MCerp :
towardsdatascience.com/journey-to-monte-carlo-mc-simulations-with-correlated-variables-in-python-1aef84d5742d
@@ZhijingEuThanks 👍
will definitely go through it
@@shiwaninaik3935 glad I could help. I'm planning to redo this tutorial video using Python instead of XL Risk when I get a bit of time later this year so stay tuned.
@shiwaninaik3935 zhijingeu.medium.com/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-with-python-mcerp-7d57e63112fa#bb3f
Hello Zhijing Eu, thanks a lot for your video. i try to use monte carlo for QMRA.
Is it true if i say graph cummulative distribution describe about variability?
I found out about how to make uncertainty with monte carlo, and it said that i need to second-order. But i confused about parameter input that i use in second-order.
And i cant find Tornado diagram in my XLRisk. i use 2 parameter input for my output. does it affect the output of the diagram?
Can you make monte carlo for assessing impact and likelihood of risk? Example, they have assed using a heat map; I then simulate how likely that is?
I don't quite understand your question. This tool does not assess the likelihood or impact but assumes you know enough about the phenomenon to select an appropriate probability distribution to simulate it's behavior...you can create two variables- one being a binomial on-off representing likelihood and another variable to reflect the impact range and multiply both distributions to get the overall effect. Is that what you meant ? If you have actual observed data you can even build your own custom distributions. Unfortunately you can't do it in XL Risk but commercial packages like Palisade @Risk or Oracle Crystal Ball have this feature
great job - thx
Hey, great video, would you mind releasing this document as a template - i cant seem to get mine to work
github.com/ZhijingEu/MonteCarloSimulation
Suppose I am calculating the uncertainty in a quality H, where as H= a x b x c x d and in here, b,c and d, all three have low, most likely and high values available, should I calculate low, most likely and high values of H first and then calculate uncertainty using three values of H? OR i should first calculate risk vales for b,c and and then some how use them to calculate the uncertainty in value of H?
If you have the underlying data / records to build an estimate at the finer granularity (i.e a,b,c ,d) you should use that and let the simulation results determine the range of the coarser variable H.
The trick will be whether you have the cross correlation data between a,b,c,d . Unless you apply some form of correlation , the central limit theorem will make the variability "cancel out" and you will likely underestimate the true amount of variability in H.
You should also consider the potential problem of mismatched granularity in model input variables i.e if you model a,b,c,d as inputs but the other model inputs are more at the level of H as this can make interpreting the input drivers a bit harder.
@@ZhijingEu I believe a.b.c. and d are coming from independent sources and have no correlation in between. So I would calculate the low value of H, using low values of b,c, and d, (similarly most like and high values of H) and then apply pertrisk on three values of H to calculate uncertainty.
Thank you Zhi, XLR is extremely user friendly and your guidance was really helpful. Best Regards
@@Mzalikhan1983 glad you found the content helpful. Good luck and on a final note, do watch out about your assumption that the variables are independent and uncorrelated. Case in point : www.barrons.com/articles/when-markets-crash-everythings-correlated-even-factors-51548324000
is it possible to download the spreadsheet to see the data there? recognising it would need the addin to operate. thanks
github.com/ZhijingEu/MonteCarloSimulation/
When I open the excel and enable the content all of the formulas become invalid for 'Risked Value' cells. Do I need to link it to the XLrisk file? This is whats in the cell "=@'C:\Users\Lenovo\Desktop\Programming\Excel\XLRisk\XLRisk.xlam'!RiskPert(C4,D4,E4)"
it is not clear how you connect the correlation matrix to the original ranges
In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 as a ",RiskCorrmat" suffix to the whatever risk distribution is being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample
hi, looks like a great tool, I'm doing a university course however I am a complete novice in excel and have not a clue how you make this run have you left info out of your video. Also where do I find your written tutorial.
github.com/ZhijingEu/MonteCarloSimulation/
medium.com/analytics-vidhya/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-cf904b1ab503
@@ZhijingEu Thankyou
Hello Zhijing Eu, thanks for your kind effort. Im trying to practice the simulation by watching your video. I've used the same variables and the same values like yours. I've installed the XLRisk correctly. However, my resluts shows no outputs, no graphs but inputs only. Could you please guide me to solve the issue? Thanks in advance.
Can you make monte carlo simulations for trading stocks
Not exactly MC simulations but similar concept that uses Geometric Brownian Motion to model share price behavior th-cam.com/video/irMSmRUlIkc/w-d-xo.html (Spoiler Alert : It doesn't work as well as you'd think)
Hey Zhijing, can you show it again how you linked the correlation matrix with results in I4:I9. I am getting following message when I try to run simulation. "The risk functions have not been properly linked to the correlation matrix".
In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 via the ",RiskCorrmat" suffix to the risk distribution being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample
Tx Zhijing, but you must have done something in addition to RiskCormax in order to enhance the correlation. I see that cells in I4:I9 are named i.e., I5 is "AccomCost" etc... Have you connected it somehow to Corr matrix?
You need to ensure you name the range for the Corr matrix but also specify which variable you are referring to i.e I5 Accom Cost is ", RiskCorrmat($G$14:$I$16,1)" , I6 Meals is ", RiskCorrmat($G$14:$I$16,2)" and I8 Holiday Tours is ", RiskCorrmat($G$14:$I$16,3)"
Tx, sorted out.
where I will get "RiskPert" functions ? How to calculate Risk Value?
XLRisk supports both RiskPert and RiskTriang functions :- github.com/pyscripter/XLRisk/wiki/RiskFunctions
Is it possible to obtain this excel to further analyse how you did it ?
github.com/ZhijingEu/MonteCarloSimulation/
medium.com/analytics-vidhya/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-cf904b1ab503
Can you share the python code related to XLRisk Monte Carlo simulation ?
Did you mean the XLXS file? It's here github.com/ZhijingEu/MonteCarloSimulation
I also have a Python version of the same worked example here : th-cam.com/video/UISccjS1Gv4/w-d-xo.htmlsi=5j9qtmt7P6X17B_E
@@ZhijingEu Amazing!! Thank you.
@@ZhijingEu Thank you.
Can you help me with something? I'm getting the following error with corr matrix: "The table (referenced) doesn't have all off-diagonal values between -1 and 1". The thing is that it is, and there is nothing on githup about how to deal with it.
Under the tab Uncertain Range+Events+Corr, there is a table in cells G14:I16 that represents the correlation matrix. Maybe you accidentally changed the values in the non diagonal cells? (H14, I14, G15, I15, G16, H16)
@@ZhijingEu Your is perfect, I'm using another one, the values for the correlation differ from -0,27 to 1 with 19 variables (diagonal all 1), I've tried to change the numbers and reduce the 19 but receive the same message.
Hmmmm... This is tricky. First try to set the non diagonals as zeroes just to see if it still runs and it's not an XLRisk problem. If not then it could be that the correlation matrix is inconsistent. kb.palisade.com/index.php?pg=kb.page&id=75
Unfortunately unlike commercial software like @Risk or Frontline Solver which have Auto Corr matrix adjustment tools, you might have to find a way to use Python to calc the eigen values... if you know a bit of coding you might be able to apply the method described here to adjust your coefficients community.wolfram.com/groups/-/m/t/1078904
Try these stackoverflow.com/questions/10939213/how-can-i-calculate-the-nearest-positive-semi-definite-matrix or stackoverflow.com/questions/43238173/python-convert-matrix-to-positive-semi-definite