| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem when creating a series of matrix
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
Don't use a calendar month, instead use a 30 day month.
" wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
Thanks for your replay! I do not have the daily data though...
"Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
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" wrote: Thanks for your replay! I do not have the daily data though... "Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
ok, seems great but i'm not sure i'm following. Could you explain that a
little more. Thanks! "Joel" wrote: 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" wrote: Thanks for your replay! I do not have the daily data though... "Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
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" wrote: ok, seems great but i'm not sure i'm following. Could you explain that a little more. Thanks! "Joel" wrote: 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" wrote: Thanks for your replay! I do not have the daily data though... "Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
Thanks! I will try this now and replay here later.
Johannes "Joel" wrote: 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" wrote: ok, seems great but i'm not sure i'm following. Could you explain that a little more. Thanks! "Joel" wrote: 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" wrote: Thanks for your replay! I do not have the daily data though... "Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
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! "Johannes" wrote: Thanks! I will try this now and replay here later. Johannes "Joel" wrote: 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" wrote: ok, seems great but i'm not sure i'm following. Could you explain that a little more. Thanks! "Joel" wrote: 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" wrote: Thanks for your replay! I do not have the daily data though... "Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
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" wrote: 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! "Johannes" wrote: Thanks! I will try this now and replay here later. Johannes "Joel" wrote: 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" wrote: ok, seems great but i'm not sure i'm following. Could you explain that a little more. Thanks! "Joel" wrote: 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" wrote: Thanks for your replay! I do not have the daily data though... "Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
problem when creating a series of matrix
Ah, ok, thanks, would it be possibly to send the sheet to you? My e-mail is
at the top of this thread. "Joel" wrote: 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" wrote: 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! "Johannes" wrote: Thanks! I will try this now and replay here later. Johannes "Joel" wrote: 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" wrote: ok, seems great but i'm not sure i'm following. Could you explain that a little more. Thanks! "Joel" wrote: 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" wrote: Thanks for your replay! I do not have the daily data though... "Joel" wrote: Don't use a calendar month, instead use a 30 day month. " wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time Series and Creating data points within a series | Excel Discussion (Misc queries) | |||
| from a series to a matrix using formula | Excel Discussion (Misc queries) | |||
| Creating a Cross Reference table/Matrix | Excel Discussion (Misc queries) | |||
| creating an hourly matrix (scheduling) | Excel Worksheet Functions | |||
| Creating a matrix from columns | Excel Discussion (Misc queries) | |||
Linear Mode
