PDA

View Full Version : Need help to create a series of covariance matrices in my worksheet



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

Bob Phillips
04-08-2012, 10:53 AM
Can you post an example workbook with expected results.

L1meta
04-08-2012, 12:38 PM
Thank you for your reply! :)

Because of size constraints this does not contain the whole thing, but should give you a good picture. Basically I want to have a function that will write a vertical series of covar-matrices such as the one this file, at the beginning of each month using x number of past returns to calculate it.

Bob Phillips
04-08-2012, 04:04 PM
Public Sub CreateMatrices()
Const FORMULA_COUNT As String = _
"SUMPRODUCT(--(TEXT(C3:C<lastrow>,""yyyymmm"")=""<testdate>""))"
Dim LastRow As Long
Dim LastCol As Long
Dim NextDate As Date
Dim NextRow As Long
Dim vecResult As Variant
Dim sh As Worksheet

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
LastCol = .Range("D2").End(xlToRight).Column
NextRow = 3
Do

NextDate = .Cells(NextRow, "C").Value
numrows = .Evaluate(Replace(Replace(FORMULA_COUNT, _
"<lastrow>", LastRow), _
"<testdate>", Format(.Cells(NextRow, "C").Value, "yyyymmm")))
vecResult = VarCovar(.Cells(NextRow, "D").Resize(LastRow - NextRow + 1, LastCol - 3))
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
sh.Name = Format(.Cells(NextRow, "C").Value, "yyyymmm")
sh.Range("A1").Resize(numrows, LastCol - 3) = vecResult

NextRow = NextRow + numrows
Loop Until NextRow > LastRow
End With

Application.ScreenUpdating = True
End Sub

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

L1meta
04-09-2012, 09:06 AM
Thank you very much for this! :)