# Monte Carlo simulation in VBA

#### diogene

##### New Member
Hi everyone,

I am running a brief simulation with VBA where one variable (call it x) affects the output (call it y). The variables are two time series (x1,x2,...,x10 and y1,y2,...,y10).
I just implemeted the code to run 200 simulations at once and get the average values for x and y for each year.
What I would like to achieve in another tab is basically copying the worst case among these 200 simulations and just paste it there.
I am not sure how to proceed since the code would need to compare the results for each simulation and establish the lowest, then when finding a new lowest substituing with that, and so on.
Would anyone be able to help me?

Many thanks!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You could make another variable, lets say Worstxy, then set it equal to the first simulation. Then during each simulation, simply compare the results of that simulation to what is currently stored in Worstxy. If the current simulation is worse that what is stored in Worstxy, then set Worstxy to the current simulation and continue on to the next simulation. After all of the simulations are done, Worstxy will hold the worst case for all of the simulations run. Something like this:

Code:
``````Dim Worstxy As Double 'dim Worstxy as whatever your regular output would be instead of double
Worstxy = 0 ' or some other value that is unreachable as the output of the simulation

'run your simulation as normal

If Worstxy = 0 Then ' so Worstxy will be set to the output of the first simulation
Worstxy = OutputOfCurrentSimulation
Else
If OutputOfCurrentSimulation < Worstxy Then
Worstxy = OutputOfCurrentSimulation
End If
End If

' Continue on to the next simulation as normal``````

You could make another variable, lets say Worstxy, then set it equal to the first simulation. Then during each simulation, simply compare the results of that simulation to what is currently stored in Worstxy. If the current simulation is worse that what is stored in Worstxy, then set Worstxy to the current simulation and continue on to the next simulation. After all of the simulations are done, Worstxy will hold the worst case for all of the simulations run. Something like this:

That makes sense, thank you. I also used a similar code to find the second worst observation, since I am trying to find the 99% VaR out of 100 observation (with 100 simulations, it is then the 99th worst result). How can I improve this so that when I change the number of simulations (let's say 200 instead of 100) it yields the proper 99% VaR (which would then be the 198th observation)?

Many thanks!

Replies
3
Views
185
Replies
2
Views
196
Replies
2
Views
1K
Replies
4
Views
2K
Replies
5
Views
171

Threads
1,196,047
Messages
6,013,085
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back