We help IT Professionals succeed at work.

Monte Carlo simulation in Excel VBA

Medium Priority
3,822 Views
Last Modified: 2014-07-09
Hello,
I need help creating a Monte Carlo simulation model in Excel. It's probably not as much of the Monte Carlo specific question per se but a question of approach to looking up corresponding values in probability table, suming and storing the results (in a temporary worksheet? or array? not sure what's best and how to achieve it), and moving on to the next simulation. Number of simulations is a user controlled input.  I have created a skeleton of the model with some very high level VBA coding that for now that asks for user input for number of simulations,  starts a blank "test" loop, and shows a progress bar. I need help/direction/guidance on the rest. My VBA knowledge would be best described as advanced beginner.

Thanks,
Wlad
MonteCarlo.xlsm
Comment
Watch Question

Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:

Commented:
>a question of approach to looking up corresponding values in probability table, suming and storing the results

this sounds like a not-so-simple formula

Are you familiar with VLOOKUP?  It takes an input (typically from a cell) and searches a column for a specific value and returns a corresponding value in an adjacent cell.

e.g. =VLOOKUP(A1, B:C, 2)
this says to take what's in cell A1 and search down column B and if found, return what's in the 2nd column of the group called B:C (i.e. column C)
if not found, you have a 4th option to either generate an error or return the closest value, which would assume that the values are sorted.

If you apply that to a collection of cells (e.g. copy from A1 to  A2: A99) then you can get a collection of values that you can then SUM (which are automatically stored in the cells where you have the formulas.

Does this help?

Author

Commented:
rspahitz,
I ended up using sumproduct lookup vs vlookup. The code works well so far, where I am stuck is storing the results of each simulation in an array, since I am not well versed in arrays. I am trying to create a two dimensional array where 1st variable is the number of simulation, and second is the sum of the results from respective simulation:
Sub Simulation()


   
    Dim i As Long 'Number of simulations
    Dim ii As Long 'Number of random var in each simulation for Option 1
    Dim k As Long
    Dim sum1 As Long
    Dim MySims As Long 'Actual number of simulation the model will run
    Dim PercentDone As Single
    Dim SimsEntered As Variant 'User entered number of simulations
    Dim array1() As Long ' Array for storing simulation results for Option 1 Whats the best way to declare it ?????
       
   
   
           
        SimsEntered = InputBox("Enter number of Simulations")
        If SimsEntered = "" Or SimsEntered <= 0 Or IsNumeric(SimsEntered) = False Then
            MsgBox "The number of repetitions must be an integer greater than zero."
       
        Exit Sub
        End If
     
    MySims = CInt(SimsEntered)
       
    k = Worksheets("Simulation").Range("C5").Value 'getting number of random variables from user input cell
    'need error check if <= 0
       

        For i = 1 To MySims 'Loop for number of simulation
       
                For ii = 1 To k 'Loop for number of random variable to be generated per each simulation
                         
           
                    Worksheets("Results").Cells(ii, 1) = "=rand()" 'generate random variables
                    Worksheets("Results").Cells(ii, 1).Value = Worksheets("Results").Cells(ii, 1).Value 'Leave value only in the cell
           
                    'Look up average amount
                    Worksheets("Results").Cells(ii, 2).FormulaR1C1 = "=sumproduct((rngCPL <= RC[-1]) * (rngCPH >= RC[-1]) * rngAvAmt)" 'same here
                       
           
                Next ii
       
            sum1 = Application.Sum(Range(Cells(1, 2), Cells(k, 2))) 'sum all results in Column 2
               
            MsgBox sum1 'show total for testing purposes
           
           
            'Here need to store each sum1 in the two dimensional array1( , ) where first value is the number of simulation,
            'and second value is the value of sum1
            'This array will need to populate a table summarizing the simulations on a different sheet (output sheet)
           
            'Need to clear columns 1, 2 on "Results" sheet before next simulation runs
               
           
            'Track progress
            PercentDone = i / MySims

            'Update status bar
            Application.StatusBar = "Progress: " & Format(PercentDone, "0%") & " Complete"

        Next i
       

End Sub

Commented:
>
Dim array1() As Long ' Array for storing simulation results for Option 1 Whats the best way to declare it ?????
<

This is fine; this creates a dynamic array that can change dimensions later. I usually "initialize it right away like this:
ReDim array1(1,0)' this creates a 2-dimensional array of array1(0,0) and array1(1,0) since arrays are zero-based.

>
 'Here need to store each sum1 in the two dimensional array1( , ) where first value is the number of simulation,
            'and second value is the value of sum1
<

Since you'll only have paired values, one of the dimensions will be 2 and the other will grow (e.g. array1(0,0) array1(1,0) array1(0,1) array1(1,1) array1(0,2) array1(1,2) array1(0,3) array1(1,3)...)

' add a current-item counter somewhere; can probably use the "i" variable in this case

array1(0, i) = i'  <= not sure if this is what you mean (*)
array1(1, i) = sum1
ReDim Preserve array1(1, ubound(array1(2))+1)

(*) if this is what you want, you don't need a multidimension array because the index of the array entry is already known.  For example, for the 5th sum1, you already know it's item #5 (index is one less since it's zero-based)

--
>
'This array will need to populate a table summarizing the simulations on a different sheet (output sheet)
           
            'Need to clear columns 1, 2 on "Results" sheet before next simulation runs
<

Not quite sure about what you mean here.  We can address that next if the array parts work as you'd like.

Author

Commented:
My though behind 2 dimensional array was that i wanted to "paste" first dimension which is a simulation counter into the first column of a table with the simulation results, and the second dimension which are the actual results (sum1s) into a second column of the table. The reason behind using the table is that I could easily link other formulas in the spreadsheet via structured reference.

So array1 should be something like array1 ( number of simulations = MySims , number of results (same as number of simulations))
then store 1 through MySims in first dimension and all corresponding generated sum1s in second dimension. After loop is over "paste" array into the existing table.
MonteCarlo.xlsm

Commented:
Maybe an example would help.

So if you have 3 simulations (with results of say 7,2,6,4 in the first simulation), what do you want in the array? (Or give me a better set of numbers that make sense.)

array(0,0)=?
array(0,1)=?
array(0,2)=?
array(1,0)=?
etc.?

Author

Commented:
rspahitz,
So what model does in each simulation is summing (sum1 variable) the looked up $ amounts per probability table based on generated random variables. So simulation results would be something like this:

Simulation 1 = array(1, $35,234,345)
Simulation 2 = array(2, $34,345,678) through
Simulation MySim = array(MySim, $xxx,xxx,xxx)

etc how many simulations user defined (for practical reason I will limit it to 1,000)

My idea was then to just use that array to populte a table with results on "Simulation" sheet.

Commented:
Since you can't have $ amounts as index values in arrays, I assume you mean this:

SimluationArray(1) = $35,234,345
SimulationArray(2) = $34,345,678
SimlulationArray(n) = $xxx,xxx,xxx

This is a 1-dimensional array.  Think of is as a sheet in Excel.  All values are in column 1; row 1 has $35K, row 2 has $34K, row 3 has $???K, ... row n has $xxxK

Please confirm or correct.

Author

Commented:
yes, that's accurate!

Commented:
I finally had a chance to download your file.  Here's some code that I think may give you what you want.

First, add the ReDim line after getting the number of iterations to run:
...
    MySims = CInt(SimsEntered)
    ReDim array1(MySims)
...

Open in new window


Then update the value after your message box:
...            'MsgBox sum1 'show total for testing purposes
            
            array1(i) = sum1
...

Open in new window


then at the very end add this, which will add the result values onto the Simulation sheet's list:
...
    Sheets("Simulation").Activate
    For i = 1 To MySims
        Cells(i + 14, 2).Value = i
        Cells(i + 14, 3).Value = array1(i)
    Next
End Sub

Open in new window

Commented:
The complete sub that seems to work for me is here (cleaned up a bit):
Sub Simulation()
    Dim i As Long 'Number of simulations
    Dim ii As Long 'Number of random var in each simulation for Option 1
    Dim k As Long
    Dim sum1 As Long
    Dim MySims As Long 'Actual number of simulation the model will run
    Dim PercentDone As Single
    Dim SimsEntered As Variant 'User entered number of simulations
    Dim array1() As Long 'Array for storing simulation results for Option 1  ?????
       
    'need to clear the results of prior run from the table first
    With Sheet2.ListObjects("Table4")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
    
    SimsEntered = InputBox("Enter number of Simulations")
    If SimsEntered = "" Or SimsEntered <= 0 Or IsNumeric(SimsEntered) = False Then
        MsgBox "The number of repetitions must be an integer greater than zero."
        Exit Sub
    End If
      
    MySims = CInt(SimsEntered)
    ReDim array1(MySims)
       
    
    k = Worksheets("Simulation").Range("C12").Value 'getting number of random variables from user input cell
    'need error check if <= 0
       
    Worksheets("Results").Activate
    
    For i = 1 To MySims 'Loop for number of simulation
    
        For ii = 1 To k 'Loop for number of random variable to be generated per each simulation
            Worksheets("Results").Cells(ii, 1) = "=rand()" 'generate random variables
            Worksheets("Results").Cells(ii, 1).Value = Worksheets("Results").Cells(ii, 1).Value 'Leave value only in the cell
    
            'Look up average amount
            Worksheets("Results").Cells(ii, 2).FormulaR1C1 = "=sumproduct((rngCPL <= RC[-1]) * (rngCPH >= RC[-1]) * rngAvAmt)" 'same here
        Next ii
    
        sum1 = Application.Sum(Range(Cells(1, 2), Cells(k, 2))) 'sum all results in Column 2
           
        'MsgBox sum1 'show total for testing purposes
        array1(i) = sum1
        
        Worksheets("Results").Range("A:B").Clear 'clear the results page to get ready for next simulation
        
        'Track progress
        PercentDone = i / MySims

        'Update status bar
        Application.StatusBar = "Progress: " & Format(PercentDone, "0%") & " Complete"

    Next i
    
    Sheets("Simulation").Activate
    For i = 1 To MySims
        Cells(i + 14, 2).Value = i
        Cells(i + 14, 3).Value = array1(i)
    Next
End Sub

Open in new window

Author

Commented:
rspahitz,
thank you! I don't know why arrays are so intimidating, it was much simpler than I imagined. Last pair of questions
 - are there any tricks to speed up such type of a code? Only thing i could think of is turning off SreenUpdating in the beginning and turning it back on at the end.
- Is there any point to emptying the array at the end with Erase array?

Thanks!!!

Commented:
Glad to help.
I had noticed that the process was a bit slow.  Looking at the code, there's nothing obvious that's causing it to be slow other than the formulas displaying.

Since I think the cell assignments already reference the Results tab, you could try removing this line and see if it speeds things up since it won't have to show the calculations:

    Worksheets("Results").Activate