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