Help With Array!! (1 Viewer)

[Excel_Kid1081]

Hello-

I'm building a procedure that takes various time series returns by certain sectors and calculates a Covariance Matrix of the returns....As you can see from my comments in the code I am having trouble getting these values into an array. I don't have a ton of experience with them so any thoughts are much appreciated. Once I get the array completed I think it will be pretty easy to create the covariance matrix.

Here is my code......

Sub CalcCovariance()
Dim db As DAO.Database
Dim strSQL As String
Dim myArrVar As Variant
Dim IndexName As String
Dim IndexDate As String
Dim rs As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim Covariance As Double
Dim AvgDiff As Double
Dim IndexAvg As Double
Dim totalRows As Integer
Dim MnthReturn As Double
Dim intI As Integer
Dim intJ As Integer
Dim intK As Integer

Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT Index FROM tblSectors")

'Grabs each unique sector index name from the Sectors table
Do Until rs.EOF
IndexName = rs!Index
'Calculates the average and total record count per index
strSQL = "SELECT SectorReturns.Index, Avg(SectorReturns.tot_return) AS AvgReturn, " & _
"Count(SectorReturns.tot_return) AS totalRows FROM SectorReturns " & _
"GROUP BY SectorReturns.Index HAVING (((SectorReturns.Index)= " & quote & IndexName & quote & "));"

Set rs2 = CurrentDb.OpenRecordset(strSQL)
IndexAvg = rs2!AvgReturn
'totalRows = rs!totalRows

rs.Close
'Grabs each monthly return for given index
strSQL = "SELECT SectorReturns.Index, SectorReturns.IndexDate, SectorReturns.tot_return AS MonthlyReturn FROM SectorReturns " & _
"WHERE (((SectorReturns.Index)= " & quote & IndexName & quote & ")); "

Set rs3 = CurrentDb.OpenRecordset(strSQL)
'Loops through and calculates the difference from the Monthly Return compared to the average and places into an Array
intI = 1
intJ = 1
intK = 1
ReDim myArrVar(0, 0, 0)
Do While Not rs3.EOF
ReDim Preserve myArrVar(1 To intI, 1 To intJ, 1 To intK)
IndexDate = rs3!IndexDate
MnthReturn = rs3!MonthlyReturn
AvgDiff = (MnthReturn - IndexAvg)
'Sticking Point!!!

myArrVar(intI) = IndexDate
myArrVar(intJ) = IndexName
myArrVar(jintK) = AvgDiff
i = i + 1
j = j + 1
rs.MoveNext
Loop
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub

Thanks!!

EK

 

[DALeffler]

Your code is declaring a 3 dimensional array:

ReDim myArrVar(0, 0, 0)

but then only references the first dimension in the assign statements:

myArrVar(intI) = IndexDate
myArrVar(intJ) = IndexName
myArrVar(jintK) = AvgDiff

when you need to always reference all three dimensions whenever your code tries to assign a value to any 3-d array element:

myArrVar(1,intI,1) = IndexDate
myArrVar(1,intj,1) = IndexName
myArrVar(1,jintK,1) = AvgDiff

I was basing my example around something like: myArrVar(intRow, intCol, intSheetnum)

hth,