PDA

View Full Version : code_montecarlo



blanka
04-06-2013, 01:25 PM
Could someone please help me with this code:

Option Explicit
Sub ABC()
Dim myrange As Range
Dim i As Double
Dim j As Double
Dim k As Double
Dim h As Integer
For i = 8746.38571 To 14198.47389 Step 1
For j = 0.038574 To 0.083659 Step 0.01
For k = -425.48572 To -189.58392 Step 0.001
For h = 1 To 10000
myrange.Cells(h, 1) = i * j * k
myrange.Cells(h, 2) = rand()
myrange.Cells(h, 3) = myrange.Cells(h, 1) * myrange.Cells(h, 2)
Next i
Next j
Next k
Next h
End Sub


The idea is:
1. I need a product of three numbers, going from 8746.38571 to 14198.47389, from 0.038574 to 0.083659, and from -425.48572 to -189.58392 (all possible product combinations). The step of increase doesn't have to be the fifth decimal (0.00001); something like the step of 0.001 or 0.01 or 0.01 would suffice. Altogether, about 5.000 or 10.000 or 20.000 products would suffice (but the more, the better).
2. Each of these products has to be multiplied with a random number that should be automatically generated with function rand() ... although I'm not sure this function works in VBA.
3. Finally, a graph should be drawn (the products on one axis and the probabilites on the other). Something like normal distribution or a graph with confidence intervals should be derived.
Suggestions about better programs for Monte Carlo simulation are more than welcome :) Any help will be appreciated.

Aussiebear
04-06-2013, 02:40 PM
You need to change the order of the closing tags for each loop

SamT
04-06-2013, 06:13 PM
Too many zeros for my brain, Sorry.