Array slicing, calculations on dynamic array, rolling fixed period lookback

[msaldut]

I am attempting to pass historical price data for 5 stocks into a 3D array and then do calculations there (and keep in dimension 3). My array is called "matrix" in the workbook attached, and the 3 dimensions are: 1-number of periods (in this case, days), 2-number of stocks and 3-price data and other calculated fields.

I have tried to implement an APPLICATION.INDEX approach that I have tried to learn about by googling and other forums, but the 3 statements which I commented out at the bottom are still generating errors (variables = high_price, low_price and stdev_price).
I am very familiar with worksheet functionality but am struggling to translate it to VBA. My goal is to do this for a much larger chunk of data which will include enough calculations to grind a worksheet to a halt.

This is what I have so far...


Sub fivestocks()


Dim price As Double
Dim num_periods As Long
Dim num_stocks As Integer
Dim num_fields As Integer
Dim i As Integer 'period counter
Dim s As Integer 'stocks counter
Dim tf As Integer 'fields counter
Dim lookback_period As Integer
Dim high_price As Double
Dim low_price As Double
Dim stdev_price As Double
'declare ARRAY
Dim matrix() As Variant


i = 1
s = 1
tf = 1
lookback_period = 5


num_periods = Range(Cells(2, 2), Cells(2, 2).End(xlDown)).Cells.Count
num_stocks = Range(Cells(2, 2), Cells(2, 2).End(xlToRight)).Cells.Count
num_fields = 2 '1-price, 2-%chg


ReDim matrix(1 To num_periods, 1 To num_stocks, 1 To num_fields)


'''''''''' populate array with price data - start in B2
For i = LBound(matrix, 1) To UBound(matrix, 1)
For s = LBound(matrix, 2) To UBound(matrix, 2)
matrix(i, s, 1) = Range("B2").Offset(i - 1, s - 1).Value
Next s
Next i

'''''''''' calculate %chg and populate array
For s = LBound(matrix, 2) To UBound(matrix, 2)
For i = LBound(matrix, 1) To UBound(matrix, 1)
If i < 2 Then
matrix(i, s, 2) = 0
Else
matrix(i, s, 2) = matrix(i, s, 1) / matrix(i - 1, s, 1) - 1
End If
Next i
Next s


For s = LBound(matrix, 2) To UBound(matrix, 2)
For i = lookback_period + 2 To UBound(matrix, 1)
'high_price = Application.Max(Application.Index(matrix, Evaluate("transpose(row(" & i - lookback_period & ":" & i - 1 & "))")))
'low_price = Application.Min(Application.Index(matrix, Evaluate("transpose(row(" & i - lookback_period & ":" & i - 1 & "))")))
'stdev_price = Application.Stdev(Application.Index(matrix, Evaluate("transpose(row(" & i - lookback_period & ":" & i - 1 & "))")))
matrix(i, s, 3) = high_price
matrix(i, s, 4) = low_price
matrix(i, s, 5) = stdev_price

Next i
Next s


End Sub

 

[Jerry Sullivan]

As far as I know, Application.Index can only be applied to a 2D array.

A couple observations on the code you posted...

1. You aren't providing a column_num argument to Application.Index. That's needed for Index to work on a 2D array (where column_num=0 indicates all columns). If Application.Index were to work on a 3D array, I presume it would require a column_num reference.

2. The last block of code is trying to store values in matrix(i, s, 3)....matrix(i, s, 5) but the 3rd dimension of the matrix array is declared with an upper bound of 2.

It's not apparent what benefit you would get from processing the calculations in a 3D array - especially since you aren't doing any calculations in the Z-axis. The 3rd dimension is providing storage for results of calculations applied to the first "plane". Even if the syntax you tried worked, it would probably be simpler to use multiple 2D arrays. That would allow you to more efficiently read your data into a single 2D array in like...

matrixPrices=Range("B2").Resize(num_periods, num_stocks).Value