problem with creating a serie of matrixes

[Johannes]

I would like to create a number of matrixes from a list (covariance between
assets over time). I put =MMULT(TRANSPOSE(O2:S25);O2:S25)/24 in excel and get
a 5x5 covariance matrix calculated on the dates 9301-9412. I would like new
matrix for every new months (moving one month at a time). The next would
refer to 9302-9501 or =MMULT(TRANSPOSE(O3:S26);O3:S26)/24 and so on. The
problem now is that I cannot simply copy the matrix below the previous one
because that one is five rows down and refer to the date 9306-9505.

I would be very thankful if some one knows how to do, maybe with a macro or
in some other way.

 

[RadarEye]

I would like to create a number of matrixes from a list (covariance between
assets over time). I put =MMULT(TRANSPOSE(O2:S25);O2:S25)/24 in excel and get
a 5x5 covariance matrix calculated on the dates 9301-9412. I would like new
matrix for every new months (moving one month at a time). The next would
refer to 9302-9501 or =MMULT(TRANSPOSE(O3:S26);O3:S26)/24 and so on. The
problem now is that I cannot simply copy the matrix below the previous one
because that one is five rows down and refer to the date 9306-9505.

I would be very thankful if some one knows how to do, maybe with a macro or
in some other way.

Hi Johannes,

In Excel 2003 I have created this macro:

Sub Johannes()
Dim lngRow As Long

' Position first result matrix
Range("B2").Select

lngRow = 2

Do
ActiveCell.Resize(5, 5).Select
Selection.FormulaArray = "=MMULT(TRANSPOSE(O" & _
lngRow & ":S" & (lngRow + 23) & "),O" & _
lngRow & ":S" & lngRow + 23 & ")/24"
lngRow = lngRow + 1
' Keep 2 rows between result matrixes
ActiveCell.Offset(7, 0).Select
Loop Until IsEmpty(Cells(lngRow + 23, 15))

End Sub

HTH,

Wouter