Correlation, Rows, Columns, Index, Array?

[95helsby]

Hi,

I'll try and explain this as best as I can.

I have a data set with say 5 companies across the top and then data for the next 5days underneath the company headings.
On the next sheet I want to show the cross correlation of this data for each company combination. I know how to do this and so on this small scale I can simply change the columns that the correlation should relate to, but on a bigger scale say 100 companies I need a formula that I'm able to drag down and across easily.

My current formula looks like this: =CORREL('logs (5yr)'!$D$5:$D$265,'logs (5yr)'!E$5:E$265)

When I drag this accross it works fine as the column changes but when I drag it down I need the first part of the formula to change to column E, hence the first one would be 1.00 since it's correlating against itself.

I hope this makes sense.

Thanks in advance for any help.

Mark

 

[Andrew Fergus]

Hi Mark

I got this to work as follows. Here is a copy of the raw data (range A1:I18 with column B empty):

[TABLE="width: 596"]
<TBODY>[TR]
[TD="colspan: 2"]Raw Data:</SPAN>
[/TD]
[TD]Date</SPAN>
[/TD]
[TD]Co 1</SPAN>
[/TD]
[TD]Co 2</SPAN>
[/TD]
[TD]Co 3</SPAN>
[/TD]
[TD]Co 4</SPAN>
[/TD]
[TD]Co 5</SPAN>
[/TD]
[TD]Co 6</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1 Jan 12</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]50</SPAN>
[/TD]
[TD]98</SPAN>
[/TD]
[TD]82</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2 Jan 12</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]54</SPAN>
[/TD]
[TD]108</SPAN>
[/TD]
[TD]46</SPAN>
[/TD]
[TD]97</SPAN>
[/TD]
[TD]83</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3 Jan 12</SPAN>
[/TD]
[TD]13</SPAN>
[/TD]
[TD]54</SPAN>
[/TD]
[TD]108</SPAN>
[/TD]
[TD]46</SPAN>
[/TD]
[TD]96</SPAN>
[/TD]
[TD]84</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4 Jan 12</SPAN>
[/TD]
[TD]13</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]110</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]95</SPAN>
[/TD]
[TD]85</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]5 Jan 12</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]56</SPAN>
[/TD]
[TD]112</SPAN>
[/TD]
[TD]44</SPAN>
[/TD]
[TD]94</SPAN>
[/TD]
[TD]86</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]6 Jan 12</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]56</SPAN>
[/TD]
[TD]112</SPAN>
[/TD]
[TD]44</SPAN>
[/TD]
[TD]93</SPAN>
[/TD]
[TD]87</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]7 Jan 12</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]56</SPAN>
[/TD]
[TD]112</SPAN>
[/TD]
[TD]44</SPAN>
[/TD]
[TD]92</SPAN>
[/TD]
[TD]88</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]8 Jan 12</SPAN>
[/TD]
[TD]11</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]110</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]91</SPAN>
[/TD]
[TD]89</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9 Jan 12</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]54</SPAN>
[/TD]
[TD]108</SPAN>
[/TD]
[TD]46</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10 Jan 12</SPAN>
[/TD]
[TD]13</SPAN>
[/TD]
[TD]54</SPAN>
[/TD]
[TD]108</SPAN>
[/TD]
[TD]46</SPAN>
[/TD]
[TD]89</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11 Jan 12</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]110</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]88</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]12 Jan 12</SPAN>
[/TD]
[TD]16</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]110</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]87</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]13 Jan 12</SPAN>
[/TD]
[TD]17</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]110</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]86</SPAN>
[/TD]
[TD]89</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]14 Jan 12</SPAN>
[/TD]
[TD]18</SPAN>
[/TD]
[TD]56</SPAN>
[/TD]
[TD]112</SPAN>
[/TD]
[TD]44</SPAN>
[/TD]
[TD]85</SPAN>
[/TD]
[TD]88</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]15 Jan 12</SPAN>
[/TD]
[TD]20</SPAN>
[/TD]
[TD]56</SPAN>
[/TD]
[TD]112</SPAN>
[/TD]
[TD]44</SPAN>
[/TD]
[TD]84</SPAN>
[/TD]
[TD]87</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]16 Jan 12</SPAN>
[/TD]
[TD]20</SPAN>
[/TD]
[TD]57</SPAN>
[/TD]
[TD]114</SPAN>
[/TD]
[TD]43</SPAN>
[/TD]
[TD]83</SPAN>
[/TD]
[TD]86</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]17 Jan 12</SPAN>
[/TD]
[TD]20</SPAN>
[/TD]
[TD]56</SPAN>
[/TD]
[TD]112</SPAN>
[/TD]
[TD]44</SPAN>
[/TD]
[TD]82</SPAN>
[/TD]
[TD]85</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

And a summary analysis that looks like this (range K1:T8 on the same sheet):
[TABLE="width: 732"]
<TBODY>[TR]
[TD]Max Date :</SPAN>
[/TD]
[TD]17/01/2012</SPAN>
[/TD]
[TD][/TD]
[TD="colspan: 2"]Cross-Correlations</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Row :</SPAN>
[/TD]
[TD]18</SPAN>
[/TD]
[TD]Key</SPAN>
[/TD]
[TD][/TD]
[TD]Co 1</SPAN>
[/TD]
[TD]Co 2</SPAN>
[/TD]
[TD]Co 3</SPAN>
[/TD]
[TD]Co 4</SPAN>
[/TD]
[TD]Co 5</SPAN>
[/TD]
[TD]Co 6</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1</SPAN>
[/TD]
[TD]Co 1</SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[TD]0.53 </SPAN>
[/TD]
[TD]0.53 </SPAN>
[/TD]
[TD]-0.53 </SPAN>
[/TD]
[TD]-0.88 </SPAN>
[/TD]
[TD]0.13 </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2</SPAN>
[/TD]
[TD]Co 2</SPAN>
[/TD]
[TD]0.53 </SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[TD]-1.00 </SPAN>
[/TD]
[TD]-0.60 </SPAN>
[/TD]
[TD]0.36 </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3</SPAN>
[/TD]
[TD]Co 3</SPAN>
[/TD]
[TD]0.53 </SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[TD]-1.00 </SPAN>
[/TD]
[TD]-0.60 </SPAN>
[/TD]
[TD]0.36 </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4</SPAN>
[/TD]
[TD]Co 4</SPAN>
[/TD]
[TD]-0.53 </SPAN>
[/TD]
[TD]-1.00 </SPAN>
[/TD]
[TD]-1.00 </SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[TD]0.60 </SPAN>
[/TD]
[TD]-0.36 </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]5</SPAN>
[/TD]
[TD]Co 5</SPAN>
[/TD]
[TD]-0.88 </SPAN>
[/TD]
[TD]-0.60 </SPAN>
[/TD]
[TD]-0.60 </SPAN>
[/TD]
[TD]0.60 </SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[TD]-0.50 </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]6</SPAN>
[/TD]
[TD]Co 6</SPAN>
[/TD]
[TD]0.13 </SPAN>
[/TD]
[TD]0.36 </SPAN>
[/TD]
[TD]0.36 </SPAN>
[/TD]
[TD]-0.36 </SPAN>
[/TD]
[TD]-0.50 </SPAN>
[/TD]
[TD]1.00 </SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

The formulas used are:
L1: =MAX(C:C)
this returns the last date entered into column C (change to suit your data) for the purposes of finding the last row in the next formula:
L2: =MATCH(L1,C:C)
this returns the last row number where there is a date on column C, and assumes a) you are using a date/time field somewhere, b) each date/time field is unique. and c) the dates/times are in ascending order.

Enter the company values in cells O2:T2 and N3:N8 per my example above. These could be linked to the raw data.

In cell M3 enter the formula:
=MATCH($N3,$D$1:$I$1)
and copy down as I have (see the column titled "Key"). This returns the column number (within the data range of D:I) of the company name in cell N3.

Then in cell O3 enter the following formula:
=CORREL(INDEX($D$2:$I$2,1,$M3):INDEX($D:$I,$L$2,$M3),INDEX(D:D,ROW($C$2)):INDEX(D:D,$L$2))
and copy down and across. As you can see from my example above there are the values 1 in the top-left to bottom-right diagonal which shows each company has a correlation of 1 with itself. Further proof the formula works is the perfect negative correlation between "Co 2" and "Co 4" where the two values sum to 100, and the perfect correlation between "Co 2" and "Co 3" where the value for "Co 3" is double that of "Co 2", and so on.

Within the CORREL formula in cell O3, this part:
INDEX($D$2:$I$2,1,$M3):INDEX($D:$I,$L$2,$M3)
returns the range D2:D18. As this is copied down it references E2:E18 and so on, and doesn't change as you copy the formula across.

And this part:
INDEX(D:D,ROW($C$2)):INDEX(D:D,$L$2))
returns the range D2:D18. As this is copied across it references E2:E18 and so on, and doesn't change as you copy the formula down.

I trust this helps.
Andrew