How do I record Excel data from each Monte Carlo run?

L

LaStormPrep

I am running multiple Marte Carlo simulations involving 300 iterations per
run. I want to automatically record the data from each run so that I can
build a better data base.
 
V

vezerid

Hi,

the following macro assumes that your system will run a new simulation
using the F9 key, i.e. simple calculation. It writes to a destination
worksheet named Target Sheet. It stores 10 output cells, starting with
B4 and G2 and ends with X3.

It provides for up to 65535 simulations. You can start from row 1 if
you don't need headers. The code can be changed with a loop in its
core, if output data is a table. It can be modified to much more rows,
or you might find an already made variant for Access or some DBMS in
..programming.

Sub SimAndStore()
Dim dest As Worksheet
Dim i As Long
Dim destcell As Range

Set dest = Sheets("Target Sheet")
Application.Calculation = xlCalculationManual
For i = 2 To 65536
Application.Calculate
Set destcell = dest.Cells(i, 1)
destcell.Cells(1, 1) = Range("B4")
destcell.Cells(1, 2) = Range("G2")
'...
destcell.Cells(1, 10) = Range("X3")
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

HTH
Kostis Vezerides
 
M

Mike Middleton

LaStormPrep -
I am running multiple Marte Carlo simulations involving 300 iterations per
run. I want to automatically record the data from each run so that I can
build a better data base. <
Click to expand...

Assuming you have a model with random inputs and a single output, one method
is to use the Data Table command: a column of "trial" numbers, 1 to 300; a
formula at the top of an adjacent column on the right, usually just a
reference to the cell containing the output formula of your model; select
both columns, including the blank cell at the top of the column on the left,
and enter any unused cell as the "Column Input Cell" in the Data Table
dialog box.

- Mike
www.mikemiddleton.com
 
Top