Using arrays to calculate medians

[James Brown]

Hi, I'm fairly new to VBA and was looking to start using arrays to calculate medians of monte carlo simulations. I know how to set up an array as I have been reading through such websites as:

http://excelmontecarlo.com/24a_writing_arrays_to_worksheet.html

The problem is when I'm looking to input into an array the values of a share price it doesn't seem to work, for example my code is:

'----Setting Array Size----
ReDim Arr(0 To nSimulations)

'----Set GBM----
Shock = (Volatility * Sqr(TimePeriod))

'----Main Loop----
For i = 1 To nSimulations

'----Loop for the life of the option by days----
For J = 1 To mSimulations

'----Normal Random Number Generator----
U1 = genrand_real3()
Norm1 = Moro_NormSInv(U1)

'----Drift----
Drift = (RiskFreeRate - DividendYield - (0.5 * (Volatility * Volatility))) * TimePeriod
Shock = (Volatility * Sqr(TimePeriod))

'---Calculate NewPrice Using GBM----
NewPrice = NewPrice * Exp(Drift + (Shock * Norm1))

Next J

Arr(i) = NewPrice

Next i

The problem is that I don't know how to place the array so it remembers each output for the NewPrice, then how do I order the array numerically so I can calculate the median and thirdly how to output the median to an excel cell.

Thank you in advance for any help

James

[Rodney Roe]

It looks like you need to look at your nested for loops, the newprice is changing in your j loop but isn't getting input into your array. Also if you want your information in your array to be kept declare it in the declaration section i.e. before all your functions, and subs. As for ordering your array numerically see below,

Dim arr1(), arr2() As Integer 'Declaration
Public Sub calculate()
Dim i, r, j, k As Integer
 
[A1].Select   'For a test i put some random #'s in col A
ReDim arr1([A1].End(xlDown).Row)
 
For i = 0 To UBound(arr1) - 1 'Input data into array
    arr1(i) = ActiveCell.Offset(i, 0)
Next
 
ReDim arr2(UBound(arr1))  ' sort array numerically
For i = 0 To UBound(arr1) - 1
    arr2(i) = arr1(i)
    j = arr2(i)
    For r = k To UBound(arr1) - 1
        If arr1(r) < arr2(i) Then
            arr2(i) = arr1(r)
        End If
    Next
    For r = 0 To UBound(arr1) - 1
        If arr2(i) = arr1(r) Then arr1(r) = j
    Next
    k = k + 1
Next
 
[B1].Select  'export data into worksheet
For i = 0 To UBound(arr1) - 1
    ActiveCell.Offset(i, 0) = arr2(i)
Next
End Sub
 

you'll have to modify it to suit.
Hope this helps.

RRR