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

 

[Johannes]

Sorry, I do not understand that. whta does the colunn 1, 2, 3... and the
colunn123, 124, 125... refer to? If the stock returns are below, does that
work then? In my first message I had counted the excess returns and the
MMULT(...) funktion was on the excess return but if you use the offset
funktion you can count on the returns directly right?

A B C D F
G
1 MÃ¥nad Stock 1 Stock 3 Stock 3 Stock 4 Stock 5
2 9301 -0,031 -0,090 -0,088 -0,054 -0,062
3 9302 0,114 0,033 0,046 0,346 0,162
4 9303 -0,012 -0,010 -0,004 0,135 -0,015
5 9304 0,040 0,032 0,004 -0,011 0,058
6 9305 0,070 -0,031 -0,022 0,218 -0,069
7 9306 -0,011 -0,023 -0,045 0,052 -0,043
8 9307 0,107 0,176 0,198 0,094 0,106
9 9308 0,070 0,085 0,035 0,014 0,065
10 9309 0,008 -0,052 -0,080 0,151 0,042
11 9310 0,104 0,110 0,182 0,044 0,100
12 9311 -0,088 -0,039 0,014 -0,262 -0,021
13 9312 0,083 0,064 -0,021 0,018 0,097
14 9401 0,121 0,056 0,278 0,062 0,057
15 9402 -0,045 0,076 0,096 -0,036 -0,004
16 9403 -0,091 0,011 -0,095 -0,034 0,044
17 9404 0,058 0,116 0,128 0,018 -0,024
18 9405 0,004 -0,102 -0,007 0,114 -0,046
19 9406 -0,071 -0,055 -0,124 0,005 -0,097
20 9407 0,073 0,044 0,071 0,073 0,017
21 9408 -0,004 -0,011 0,029 0,010 -0,016
22 9409 -0,032 0,011 -0,090 -0,043 -0,058
23 9410 0,056 0,043 0,054 0,099 0,035
24 9411 0,017 -0,005 0,032 -0,051 0,043
25 9412 -0,032 -0,026 -0,021 -0,010 -0,024
26 9501 0,013 0,005 -0,003 -0,017 0,079


Thank you very much!

 

[Joel]

I was showing 1...25 for 25 data entries and the 123,124,125 stock data.
what you included below is the sheet where the formulas will go. I would
need to see the sheet where the data is located to write the formula.

 

[Johannes]

Ah, ok, thanks, would it be possibly to send the sheet to you? My e-mail is
at the top of this thread.