CODEWITHSUNDEEP
excelvbarandommontecarlo
Patrick Dosier
Patrick Dosier

Reputation: 45

Speed up a Monte Carlo in Excel

I am running a Monte Carlo in Excel. To represent the distribution of demand for each product SKU during each month, I am using the following formula over 500 times:

=IFERROR(BETA.INV(RAND(),(1+4*(D35-D31)/(D27-D31)),(1+4*(D27-D35)/(D27-D31)),D31,D27),0)

So, that's over 500 RAND() volatile functions at a time.

The output from each iteration of a formula is stored in a table. I have a VBA to handle that:

Sub QARLO_Products()
'runs on QARLO_P!, for forecasting product
    Range("Q7:OC100000").ClearContents
    For Iteration = 1 To Range("G1").Value
        Range("I1").Value = Range("G1").Value - Iteration
        'For QMIN Zinc-Bulk
        Cells(6 + Iteration, 17) = Iteration
        Cells(6 + Iteration, 18) = Cells(39, 4)
        Cells(6 + Iteration, 19) = Cells(39, 5)
        Cells(6 + Iteration, 20) = Cells(39, 6)
        Cells(6 + Iteration, 21) = Cells(39, 7)
        Cells(6 + Iteration, 22) = Cells(39, 8)
        Cells(6 + Iteration, 23) = Cells(39, 9)
        Cells(6 + Iteration, 24) = Cells(39, 10)
        Cells(6 + Iteration, 25) = Cells(39, 11)
        Cells(6 + Iteration, 26) = Cells(39, 12)
        Cells(6 + Iteration, 27) = Cells(39, 13)
        Cells(6 + Iteration, 28) = Cells(39, 14)
        Cells(6 + Iteration, 29) = Cells(39, 15)
        'For QMIN Zinc-Jugs
        Cells(6 + Iteration, 30) = Iteration
        Cells(6 + Iteration, 31) = Cells(40, 4)
.... blah, blah, blah and so on for all products....
        Cells(6 + Iteration, 444) = Cells(561, 14)
        Cells(6 + Iteration, 445) = Cells(561, 15)
   Next Iteration
End Sub

The left-hand side of these statements represent the table location for recording output data, the right-hand side are the outputs from the above mentioned formula.

I notice that this VBA runs through each and every line in series. Each line causes all 500 volatile RAND() functions to recalculate. One iteration of this VBA takes 30 seconds on all 8 cores of a Core i7/ 32GB RAM. I want to run 5,000 iterations on a regular basis.

Can you suggest methods to make this model run more efficiently?

I have done all the basic/general things to make Excel run more efficiently.

Upvotes: 4

Views: 1176

Answers (4)

T123
T123

Reputation: 149

In addition what others said you could rewrite your Application.Worksheetfunction.Norminv(..) as in https://www.autoitscript.com/forum/topic/147479-checking-for-undefined-result-from-some-math-functions/ and replace the rnd() part in it by a Halton-Sequence. It also offers the option to repeat your results and speeds up the whole process considerably..

Upvotes: 0

Govert
Govert

Reputation: 16907

One small change might make a large difference.

Read and write a large range with a single call into / from an array. I can't check the exact code now, but you can say something like

   Range(Cells(6 + Iteration, 18), Cells(6+Iteration, 455).Value = Range(Cells(39,4), Cells(561,15).Value

Or by splitting the read and write so you can check things in the Debugger

   Dim TempValues As Variant ' Really a 2D array of variants

   TempValues = Range(Cells(39,4), Cells(561,15)).Value
   Range(Cells(6 + Iteration, 18), Cells(6 + Iteration, 455)).Value = TempValues

By doing this you will read / write everything in one shot, and by writing only once you will once force the volatile calculation to happen once, instead of on every write.

This looks like a nice write-up about reading and writing ranges as arrays, and checking things out under the debugger: https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm

Some other ideas:

  • Replace the volatile function with your own rand function in VBA (which maybe just calls Rnd internally). Then if will only get now values (and recalculate) when you explictly recalculate all.
  • Switch the sheet to manual calculation during your 'recording' loop to prevent the volatile cells from calculating while you're writing to the sheet.

Upvotes: 2

pizzettix
pizzettix

Reputation: 421

If you want to make Excel more efficient you have to consider to run your Monte Carlo simulation entirely in VBA. Store the results in an array and plot these in the excel sheet at the end of all the simulations.

Upvotes: 1

pizzettix
pizzettix

Reputation: 421

What do you mean with "I have done all the basic/general things to make Excel run more efficiently"? Did you try to turn of screenupdating, and so on?

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

'Place your macro code here

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

Upvotes: 2

Related Questions