correlation matrix

[#1]

I am trying to build the following correlation matrix:

nasdaq dow jones sp500

sp 500 .9 .7 1

nasdaq 1 .9 .8

dow jones .8 1 .7

Furthermore, if we add a new index to this matrix it need to automatically expand into a 4x4 table. So this is a dynamic correation grid. I tried to build this matrix with pivot table and straight but it s not working. Does anyone have any advice or example that can help me build such a matrix?

[tseebach]

The data your loading should something like this:

From To Value

sp 500 nasdaq .9

sp 500 dow jones .7

nasdaq dow jones .9

nasdaq sp500 .8

Then you can put From and To into a Dimension and value into your Expression. Then drag the To column to the top of the Expression colunm to make it a vertical one.

[#3]

Here is where diffculty comes in. The table which stores the data is of the following format:

Index	dtemonth	gross_profit
sp500	200901	539,754.75
sp500	200902	4,910,985.95
sp500	200903	(6,364,076.03)
sp500	200904	(181,889.80)
sp500	200905	(2,440,912.43)
sp500	200906	(666,537.74)
sp500	200907	583,795.79
sp500	200908	536,873.27
sp500	200909	(850,797.58)
sp500	200910	2,318,123.49
sp500	200911	2,686,174.42
sp500	200912	2,403,489.61
sp500	201001	(4,061,927.86)
sp500	201002	(1,045,574.43)
sp500	201003	3,720,635.60
sp500	201004	(1,723,617.73)
sp500	201005	(5,725,351.98)
nasdaq	200901	2,348,165.83
nasdaq	200902	1,757,471.49
nasdaq	200903	(3,712,985.59)
nasdaq	200904	(1,642,114.95)
nasdaq	200905	(618,843.31)
nasdaq	200906	(246,316.86)
nasdaq	200907	633,128.89
nasdaq	200908	3,639,483.78
nasdaq	200909	4,211,887.41
nasdaq	200910	(2,890,561.41)
nasdaq	200911	1,551,894.78
nasdaq	200912	4,439,487.28
nasdaq	201001	(7,853,511.23)
nasdaq	201002	194,673.15
nasdaq	201003	2,899,889.13
nasdaq	201004	(1,921,281.96)
nasdaq	201005	(5,333,947.39)
Dow Jones	200901	3001033.86
Dow Jones	200902	(889,982.86)
Dow Jones	200903	(5,159,477.59)
Dow Jones	200904	(333,957.88)
Dow Jones	200905	3,258,313.13
Dow Jones	200906	809,931.96
Dow Jones	200907	1,811,683.13
Dow Jones	200908	1,892,011.97
Dow Jones	200909	7,913,481.36
Dow Jones	200910	3,833,387.92
Dow Jones	200911	1,950,252.27
Dow Jones	200912	(4,193,824.94)
Dow Jones	201001	(3,292,703.77)
Dow Jones	201002	(321,177.78)
Dow Jones	201003	3,511,359.65
Dow Jones	201004	2,505,688.59
Dow Jones	201005	(8,533,409.84)
Russel	200901	(876,413.27)
Russel	200902	(2,930,873.40)
Russel	200903	10,159.69
Russel	200904	(785,864.11)
Russel	200905	(222,896.64)
Russel	200906	311,802.19
Russel	200907	(1,197,548.26)
Russel	200908	(1,509,318.96)
Russel	200909	(251,153.85)
Russel	200910	(732,472.95)
Russel	200911	(569,263.16)
Russel	200912	(1,055,649.36)
Russel	201001	109,633.41
Russel	201002	(76,332.84)
Russel	201003	(63,395.09)
Russel	201004	(952,521.79)
Russel	201005	(2,865,012.61)

I need to build a dynamic correlation matrix between sp500 vs nasdaq, sp500 vs russel, nasdq vs russel etc (see grid above). The data is in a form that doesnt facilate correlation caluclation (each index doesnt have it's own column). The matrix needs to be dynamic. If a new fund is added to this list the matrix will automatically expand. The matrix will become a 5x5 matrix. Does anyone have a solution to this problem or example that I can look at?

[johnw]

Well, I don't guarantee that this is it, but the numbers look... plausible? I simply added a duplicate table connected to the original by the month, then correlated the gross_profit in both tables using both indexes as dimensions.

[pgdavis2]

Great solution! This is extremely useful

Thanks!