problem when creating a series of matrix

[johannes.liss]

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 (January 93 until December 94 - 24
observations). 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.

 

[Joel]

Don't use a calendar month, instead use a 30 day month.

 

[Johannes]

Thanks for your replay! I do not have the daily data though...

 

[Joel]

At the top of the pmatrix for each month if you included the month number
then you can use the Address function to create a reference offset from O2.

 

[Johannes]

ok, seems great but i'm not sure i'm following. Could you explain that a
little more. Thanks!

 

[Joel]

Maybe offset is better like this

Row Month Number Start Date Day Data
A B C
20 1 Jan 93
21 1 123
22 2 124
23 3 125
24 4 126
....
....
....
45 25 127


Row Month Number Start Date Day Data
A B C
50 2 Feb 93
51 1 123
52 2 124
53 3 125
54 4 126
....
....
....
75 25 127



Then formula for 1st month is

=MMULT(OFFSET(O2,B20-1,0,24,5);OFFSET(O2,B20-1,0,24,5))/24

Then formula for 2nd month is (30 rows down the spreadsheet)

=MMULT(OFFSET(O2,B50-1,0,24,5);OFFSET(O2,50-1,0,24,5))/24
B

 

[Johannes]

Thanks! I will try this now and replay here later.

Johannes