L1meta
04-08-2012, 10:36 AM
Hi, I'm new here. :) I tried searching first, but couldn't find what I need. So here goes.
I have a set consisting of a column of dates and T daily returns for N assets in my excel worksheet. What I want to do is to create a N*N covariance matrix for the beginning of each month based on the returns of all the assets for the past x days.
I have this code here for creating a covariance matrix, but I don't know how to make excel/vba create several of them automatically for the beginning of each month. Awesome if any of you can help me. :) And let me know if you need more information in order to do so.
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 a set consisting of a column of dates and T daily returns for N assets in my excel worksheet. What I want to do is to create a N*N covariance matrix for the beginning of each month based on the returns of all the assets for the past x days.
I have this code here for creating a covariance matrix, but I don't know how to make excel/vba create several of them automatically for the beginning of each month. Awesome if any of you can help me. :) And let me know if you need more information in order to do so.
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