Building A Probabilistic Risk Estimate Using Monte Carlo Simulations
Вставка
- Опубліковано 16 тра 2024
- This tutorial covers the basic steps in using XL Risk (an open source Excel Add In) to run Monte Carlo Simulations to generate a probabilistic risk estimate.
This video is accompanied by a more detailed article on Medium / building-a-probabilist...
Also here is the Github repo that has the xlsx used in the video and instructions on how to install the open source tool XL Risk
github.com/ZhijingEu/MonteCar...
Thank you Zhijing!! Great explanation
Very well explained, clear, useful..thank you!
The Best Explanation I have seen in years, thank you
Thank you SO much for this video and your guidance !! - 🙏
Great guide, thanks a lot!
great Explanation
Excelent, and the Open Source tool also
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 job - thx
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...
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?
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
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
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
@@CH2CH2CH2 Thank you for sharing the excelRisk ❤️
Do you have link or information for cash flow forecasting using Monte Carlo simulation?
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)"
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/
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
@@CH2CH2CH2 Thankyou
Hey, great video, would you mind releasing this document as a template - i cant seem to get mine to work
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!😍
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
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
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)
@@CH2CH2CH2 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
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
@@CH2CH2CH2Thanks 👍
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
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 make monte carlo simulations for trading stocks
Not exactly MC simulations but similar concept that uses Geometric Brownian Motion to model share price behavior ua-cam.com/video/irMSmRUlIkc/v-deo.html (Spoiler Alert : It doesn't work as well as you'd think)
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
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 : ua-cam.com/video/UISccjS1Gv4/v-deo.htmlsi=5j9qtmt7P6X17B_E
@@CH2CH2CH2 Amazing!! Thank you.
@@CH2CH2CH2 Thank you.
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.