Possible to speed up a Monte Carlo Simulation?

**jerseyguy1996** · 11-10-2009, 11:14 AM

I wrote a program to run the simulation 6 times. Each simulation runs through 10,000 possible scenarios with each scenario stretching out yearly for 30 years. I timed it and it takes just over an hour to run through it all. Is this typical for vba or have I just managed to write the most inefficient code of all time? I am a complete amateur so it is possible and don't be afraid to tell me because I would love to know a better way.

**shg** · 11-10-2009, 11:21 AM

See if this is faster:

Sub FastMonteCarlo()
    Dim i           As Long
    Dim j           As Long
    Dim fSum        As Single

    For i = 1 To 6
        fSum = 0
        For j = 1 To 10000
            fSum = fSum + Rnd
        Next j
        Cells(Rows.Count, "A").End(xlUp).Offset(1) = fSum
    Next i
End Sub

Runs in a fraction of a second...

Do you expect an answer based on the information provided?

**shg** · 11-10-2009, 11:46 AM

One thing you're doing (from your other thread) is using WorksheetFunction.NormInv to generate random numbers with normal distribution. That's very slow; this runs about 100 times faster:

Function RandNorm(Optional Mean As Single = 0!, _
                  Optional Dev As Single = 1!, _
                  Optional fCorrel As Single = 0!, _
                  Optional bVolatile As Boolean = False) As Single()
    ' shg 1999-1103
    ' Returns a pair of random deviates (Singles) with the specified
    ' mean, deviation, and correlation. Orders of magnitude faster than
    '     =NORMINV(RAND(), Mean, Dev)

    ' Box-Muller Polar Method
    ' Donald Knuth, The Art of Computer Programming,
    ' Vol 2, Seminumerical Algorithms, p. 117

    Dim af(1 To 2) As Single
    Dim x       As Single
    Dim y       As Single
    Dim w       As Single

    If bVolatile Then Application.Volatile

    Do
        x = 2! * Rnd - 1!
        y = 2! * Rnd - 1!
        w = x ^ 2 + y ^ 2
    Loop Until w < 1!

    w = Sqr((-2! * CSng(Log(w))) / w)
    af(1) = Dev * x * w + Mean
    af(2) = Dev * y * w + Mean

    If fCorrel <> 0! Then af(2) = fCorrel * af(1) + Sqr(1! - fCorrel * fCorrel) * af(2)
    RandNorm = af
End Function

**jerseyguy1996** · 11-11-2009, 12:49 PM

Ahhh...I had a breakthrough. A combination of Shg (thank you very much), and a quick read on how to use arrays. What was slowing me down was that I was writing the value to each cell as I was calculating it. This time I run all of the calculations and then write them all at once to the cells. 10 minutes is now down to about 5 seconds.

This stuff is so much fun!

Public Function GMonteCarlo() As Double
Dim i As Integer
Dim j As Integer
Dim Numcols As Integer
Dim Numrows As Integer
Dim Matrix() As Double
Dim BMV As Double
Dim Output As Worksheet
Set Output = Worksheets("Sheet4")

Numcols = 30
Numrows = 10000
ReDim Matrix(Numrows, Numcols)

For i = 1 To Numrows
BMV = 13334000
For j = 1 To Numcols
BMV = BMV * (1 + Application.WorksheetFunction.NormInv(Rnd(), 0.085, 0.13))
Matrix(i - 1, j - 1) = BMV
Next j
Next i

Output.range(Output.Cells(1, 1), Output.Cells(Numrows, Numcols)) = Matrix





End Function

**shg** · 11-11-2009, 12:57 PM

That's very good.

If you replace

Application.WorksheetFunction.NormInv(Rnd(), 0.085, 0.13)

with

RandNorm(0.085, 0.13)

... you should get significant additional speed.

Also

Output.range(Output.Cells(1, 1), Output.Cells(Numrows, Numcols)) = Matrix

should be just

Output.Resize(Numrows, Numcols).Value = Matrix

Possible to speed up a Monte Carlo Simulation?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Possible to speed up a Monte Carlo Simulation?

Re: Possible to speed up a Monte Carlo Simulation?

Re: Possible to speed up a Monte Carlo Simulation?

Re: Possible to speed up a Monte Carlo Simulation?

Re: Possible to speed up a Monte Carlo Simulation?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions