calculating covariance matrix from array in vba

[centauri]

I am trying to calculate a covariance matrix in VBA. I have managed to get my data into an array "datarng" which has 10 columns and 100 rows. I want to calculate the covariance matrix of my dataset so that I will have a new array 10x10 by calculating the covariance between each column in the original array. can anyone help?

 

[pgc01]

Hi centauri

You mean the autocovariance matrix?

So, for ex., if your values are in A1:J100, the element (2,5) in the autocovariance matrix would be

=COVAR(B1:B100,E1:E100)

Is this what you want to do in vba?

 

[centauri]

Yes, this is what I'm trying to do, but I have an array of data that also varies in size so I need to set up a new array which will be a square matrix of size equal to the number of columns in the first array. How do I define the size of the array and the elements in the new array?

 

[pgc01]

Hi

This is an example that you can adjust to your needs.

In this example I used an array 100x10 that I filled with random data.
I added some writing to the worksheet just so that you can easily check the result.

Select an empty worksheet and try:

Sub Test()
Dim dArrData(1 To 100, 1 To 10) As Double
Dim dAutoCoVar() As Double
Dim j As Long, k As Long
 
' fill the array with some bogus data
Randomize
For j = 1 To 100
    For k = 1 To 10
        dArrData(j, k) = Rnd
    Next k
Next j
 
' write to the worksheet, for debug
Range("A1:J100").Value = dArrData
 
' calculate the autocovariance matrix
dAutoCoVar = Autocovar(dArrData)
 
' write to the worksheet, for debug
Range("L1:U10").Value = dAutoCoVar
End Sub
 
Function Autocovar(dArrData() As Double) As Double()
Dim dArrResult() As Double
Dim j As Long, k As Long
 
' redim the result array as a square array.
ReDim dArrResult(1 To UBound(dArrData, 2), 1 To UBound(dArrData, 2))
 
' calculate the autocovariance matrix
For j = 1 To UBound(dArrData, 2)
    For k = 1 To UBound(dArrData, 2)
        With Application.WorksheetFunction
            dArrResult(j, k) = .Covar(.Index(dArrData, 0, j), .Index(dArrData, 0, k))
        End With
    Next k
Next j
Autocovar = dArrResult
End Function