Arrays

[Kaz]

Hi, just wondering if I could get some help with my macro. I am trying to get the array to return 1000 different numbers in a range of cells but it just keeps repeating the same value 1000 times.


Option Explicit
Option Base 1



Sub ValueAtRisk()


Dim AnnualReturn As Double
Dim AnnualVolatility As Double
Dim VarPeriod As Integer
Dim CurrentPrice As Variant
Dim ExpectedReturn As Double
Dim Volatility As Double
Dim Drift As Double
Dim i, j As Integer
Dim MyArray(1 To 1000) As Variant



AnnualReturn = ActiveSheet.Range("O1").Value
AnnualVolatility = ActiveSheet.Range("O2").Value
VarPeriod = ActiveSheet.Range("O11").Value


ExpectedReturn = AnnualReturn / 252
Volatility = AnnualVolatility / Sqr(252)
Drift = ExpectedReturn - 0.5 * Volatility * Volatility


For j = 1 To 1000


For i = 1 To VarPeriod
If i = 1 Then
CurrentPrice = ActiveSheet.Range("O8").Value
Else
CurrentPrice = CurrentPrice * Exp((Application.NormInv(Rnd(), 0, 1) * Volatility) + Drift)
End If

Next i


MyArray(j) = CurrentPrice


Next j


Range("o15:o1015") = MyArray


End Sub

[SamT]

CurrentPrice = ActiveSheet.Range("O8").Value 'Don't refer to Sheet and Range 1000 times in loop

For j = 1 To 1000
NewPrice = CurrentPrice

For i = 2 To VarPeriod
Randomize
NewPrice = NewPrice * Exp((Application.NormInv(Rnd(), 0, 1) * Volatility) + Drift)
Next i

MyArray(j) = NewPrice

[Kaz]

Thanks for your help but I am still getting the result repeated 1000 times rather than 1000 different results. Any ideas?

[snb]

Please do not Quote !


sub M_snb()
[o15:o1015] = [O8*exp(norm.inv(rand(),0,1)* O2/sqrt(252)+O1/252-.5*(O2/sqrt(252))^2)*row(1:1000)]
end sub

[Paul_Hossler]

Biggest thing is the .Transpose below since your array goes in as a 1000 rows, but you only captured the first column 1000 times

Few other suggestions also




Option Explicit

Sub ValueAtRisk()
Dim AnnualReturn As Double
Dim AnnualVolatility As Double
Dim VarPeriod As Integer
Dim CurrentPrice As Variant, NewPrice As Variant
Dim ExpectedReturn As Double
Dim Volatility As Double
Dim Drift As Double
Dim i As Integer, j As Integer '<<<<<<<<<<
Dim MyArray(1 To 1000) As Variant

' AnnualReturn = ActiveSheet.Range("O1").Value
' AnnualVolatility = ActiveSheet.Range("O2").Value
' VarPeriod = ActiveSheet.Range("O11").Value

AnnualReturn = 0.06
AnnualVolatility = 0.1
VarPeriod = 12
CurrentPrice = 1000

ExpectedReturn = AnnualReturn / 252
Volatility = AnnualVolatility / Sqr(252)
Drift = ExpectedReturn - 0.5 * Volatility * Volatility


For j = 1 To 1000

NewPrice = CurrentPrice

For i = 2 To VarPeriod
NewPrice = NewPrice * Exp((Application.NormInv(Rnd(), 0, 1) * Volatility) + Drift)
Next i

MyArray(j) = NewPrice
Next j

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Range("o15:o1015") = Application.WorksheetFunction.Transpose(MyArray)
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

End Sub

[Paul_Hossler]

I forgot to put the Option Base 1 back in the sample above

[Paul_Hossler]

And maybe a better way to say


Biggest thing is the .Transpose below since your array goes in as a 1000 rows, but you only captured the first column 1000 times

is


Biggest thing is the .Transpose below since your array goes in as one row with a 1000 columns but you only captured the first column 1000 times

[Kaz]

Thanks Paul! It was the transpose function I was missing....

[Paul_Hossler]

1. SamT's idea about using 'CurrentPrice' is a good one. I only 'borrowed' it

2. FYI in

Dim I, j As Integer

the I is defaulted to a Variant type since in VBA you have to repeat the 'As Integer' for all variables in the Dim

[Kaz]

1. SamT's idea about using 'CurrentPrice' is a good one. I only 'borrowed' it

2. FYI in

Dim I, j As Integer

the I is defaulted to a Variant type since in VBA you have to repeat the 'As Integer' for all variables in the Dim

ok thanks