Hello!
I have this code for calculating the variance-covariance matrix for a portfolio consisting of 3 stocks. Somehow I can't seem to get it to work.
Could someone please help me?
Thank you
Function VarCovar(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numCols As Integer
numCols = rng.Columns.Count
Dim matrix() As Double
ReDim matrix(numCols - 1, numCols - 1)
For i = 1 To numCols
For j = 1 To numCols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i
VarCovar = matrix
End Function
I have this code for calculating the variance-covariance matrix for a portfolio consisting of 3 stocks. Somehow I can't seem to get it to work.
Could someone please help me?
Thank you
Function VarCovar(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numCols As Integer
numCols = rng.Columns.Count
Dim matrix() As Double
ReDim matrix(numCols - 1, numCols - 1)
For i = 1 To numCols
For j = 1 To numCols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i
VarCovar = matrix
End Function
