Correlation Matrix

[katie]

I have 30 securities with 5 years on monthly returns
Securities across row A and returns down the columns

A B C D
1 IBM TGT HD
2 1 3 2
3 -.5 2 -1
4 2 6 -3


I want to create a matrix that correlates the return of every security
against every securit

IBM TGT HD
IBM
TGT
HD

Is there a way to put a look up function into a correlation furnction when
you want it to supply you with an array?

=Correl(lookup (IBM,other work sheet row A, give array set below IBM in
other work sheet), lookup TGT, other work sheet row a, give array set below
TGT in other worksheet)

This type of formula is not working for me so any suggestions would be great.

[Domenic]

Assumptions:

Source table...

A1:C1 contains IBM, TGT, and HD

A2:C4 contains your data

Results table...

F1:H1 contains IBM, TGT, and HD

E2:E4 contains IBM, TGT, and HD

Formula:

F2, copied down and across:

=CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:$C$4,0,MATCH
(F$1,$A$1:$C$1,0)))

If you want the formula to leave the cell empty when correlating the
same security, try the following formula instead...

=IF($E2<>F$1,CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:
$C$4,0,MATCH(F$1,$A$1:$C$1,0))),"")

Adjust the ranges accordingly.

Hope this helps!

In article <[email protected]>,
"katie" <[email protected]> wrote:

> I have 30 securities with 5 years on monthly returns
> Securities across row A and returns down the columns
>
> A B C D
> 1 IBM TGT HD
> 2 1 3 2
> 3 -.5 2 -1
> 4 2 6 -3
>
>
> I want to create a matrix that correlates the return of every security
> against every securit
>
> IBM TGT HD
> IBM
> TGT
> HD
>
> Is there a way to put a look up function into a correlation furnction when
> you want it to supply you with an array?
>
> =Correl(lookup (IBM,other work sheet row A, give array set below IBM in
> other work sheet), lookup TGT, other work sheet row a, give array set below
> TGT in other worksheet)
>
> This type of formula is not working for me so any suggestions would be great.

[bpeltzer]

You can use the OFFSET function to dynamically create the array references.
Something like:
=IF(B$1=$A2,"",CORREL(OFFSET(Sheet1!$A$2,0,MATCH($A2,Sheet1!$1:$1,FALSE)-1,5,1),OFFSET(Sheet1!$A$2,0,MATCH(B$1,Sheet1!$1:$1,FALSE)-1,5,1)))
The first part of the IF just says not to bother correlating a security
against itself.
The MATCH functions figure out which columns of data to pull from sheet1.
The OFFSET functions use the match results to create the two arrays; the 5
in each OFFSET is to get five rows -- years -- of data.
--Bruce


"katie" wrote:

> I have 30 securities with 5 years on monthly returns
> Securities across row A and returns down the columns
>
> A B C D
> 1 IBM TGT HD
> 2 1 3 2
> 3 -.5 2 -1
> 4 2 6 -3
>
>
> I want to create a matrix that correlates the return of every security
> against every securit
>
> IBM TGT HD
> IBM
> TGT
> HD
>
> Is there a way to put a look up function into a correlation furnction when
> you want it to supply you with an array?
>
> =Correl(lookup (IBM,other work sheet row A, give array set below IBM in
> other work sheet), lookup TGT, other work sheet row a, give array set below
> TGT in other worksheet)
>
> This type of formula is not working for me so any suggestions would be great.

[katie]

you rock!!! thanks that worked

"Domenic" wrote:

> Assumptions:
>
> Source table...
>
> A1:C1 contains IBM, TGT, and HD
>
> A2:C4 contains your data
>
> Results table...
>
> F1:H1 contains IBM, TGT, and HD
>
> E2:E4 contains IBM, TGT, and HD
>
> Formula:
>
> F2, copied down and across:
>
> =CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:$C$4,0,MATCH
> (F$1,$A$1:$C$1,0)))
>
> If you want the formula to leave the cell empty when correlating the
> same security, try the following formula instead...
>
> =IF($E2<>F$1,CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:
> $C$4,0,MATCH(F$1,$A$1:$C$1,0))),"")
>
> Adjust the ranges accordingly.
>
> Hope this helps!
>
> In article <[email protected]>,
> "katie" <[email protected]> wrote:
>
> > I have 30 securities with 5 years on monthly returns
> > Securities across row A and returns down the columns
> >
> > A B C D
> > 1 IBM TGT HD
> > 2 1 3 2
> > 3 -.5 2 -1
> > 4 2 6 -3
> >
> >
> > I want to create a matrix that correlates the return of every security
> > against every securit
> >
> > IBM TGT HD
> > IBM
> > TGT
> > HD
> >
> > Is there a way to put a look up function into a correlation furnction when
> > you want it to supply you with an array?
> >
> > =Correl(lookup (IBM,other work sheet row A, give array set below IBM in
> > other work sheet), lookup TGT, other work sheet row a, give array set below
> > TGT in other worksheet)
> >
> > This type of formula is not working for me so any suggestions would be great.
>

[vandenberg p]

An alternative is to use the Correlation option from the Analysis Toolpak.
It will return the correlation matrix, as values, not formulas.

Pieter Vandenberg

Domenic <[email protected]> wrote:
: Assumptions:

: Source table...

: A1:C1 contains IBM, TGT, and HD

: A2:C4 contains your data

: Results table...

: F1:H1 contains IBM, TGT, and HD

: E2:E4 contains IBM, TGT, and HD

: Formula:

: F2, copied down and across:

: =CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:$C$4,0,MATCH
: (F$1,$A$1:$C$1,0)))

: If you want the formula to leave the cell empty when correlating the
: same security, try the following formula instead...

: =IF($E2<>F$1,CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:
: $C$4,0,MATCH(F$1,$A$1:$C$1,0))),"")

: Adjust the ranges accordingly.

: Hope this helps!

: In article <[email protected]>,
: "katie" <[email protected]> wrote:

:> I have 30 securities with 5 years on monthly returns
:> Securities across row A and returns down the columns
:>
:> A B C D
:> 1 IBM TGT HD
:> 2 1 3 2
:> 3 -.5 2 -1
:> 4 2 6 -3
:>
:>
:> I want to create a matrix that correlates the return of every security
:> against every securit
:>
:> IBM TGT HD
:> IBM
:> TGT
:> HD
:>
:> Is there a way to put a look up function into a correlation furnction when
:> you want it to supply you with an array?
:>
:> =Correl(lookup (IBM,other work sheet row A, give array set below IBM in
:> other work sheet), lookup TGT, other work sheet row a, give array set below
:> TGT in other worksheet)
:>
:> This type of formula is not working for me so any suggestions would be great.

[Rothman]

What if you have a results table which has labels that are the same? The
Match function simply finds the first instead of moving down to the proper
label. For example, my data essentially looks like this:

A B C...

1st# 2nd# 3rd# 4th# 5th# Sum Avg Med 1st# 2nd# 3rd# 4th#


If only there was some way to have =Correl($A$2:$A$94,A$2:A$94) increase the
column value as you drag the auto fill handle down...

I need the matrix to be dynamic, which is why the analysis toolkit matrix
won't work for me.

Thanks in advance (as always)!


"Domenic" wrote:

> Assumptions:
>
> Source table...
>
> A1:C1 contains IBM, TGT, and HD
>
> A2:C4 contains your data
>
> Results table...
>
> F1:H1 contains IBM, TGT, and HD
>
> E2:E4 contains IBM, TGT, and HD
>
> Formula:
>
> F2, copied down and across:
>
> =CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:$C$4,0,MATCH
> (F$1,$A$1:$C$1,0)))
>
> If you want the formula to leave the cell empty when correlating the
> same security, try the following formula instead...
>
> =IF($E2<>F$1,CORREL(INDEX($A$2:$C$4,0,MATCH($E2,$A$1:$C$1,0)),INDEX($A$2:
> $C$4,0,MATCH(F$1,$A$1:$C$1,0))),"")
>
> Adjust the ranges accordingly.
>
> Hope this helps!
>
> In article <[email protected]>,
> "katie" <[email protected]> wrote:
>
> > I have 30 securities with 5 years on monthly returns
> > Securities across row A and returns down the columns
> >
> > A B C D
> > 1 IBM TGT HD
> > 2 1 3 2
> > 3 -.5 2 -1
> > 4 2 6 -3
> >
> >
> > I want to create a matrix that correlates the return of every security
> > against every securit
> >
> > IBM TGT HD
> > IBM
> > TGT
> > HD
> >
> > Is there a way to put a look up function into a correlation furnction when
> > you want it to supply you with an array?
> >
> > =Correl(lookup (IBM,other work sheet row A, give array set below IBM in
> > other work sheet), lookup TGT, other work sheet row a, give array set below
> > TGT in other worksheet)
> >
> > This type of formula is not working for me so any suggestions would be great.
>