Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Calculate correlation coefficient among all pairs of equities

I have some Equities (50+), for example:

Equity

AAPL

GOOG
IBM
APC
XOM
.............
.............

For each Equity I have its adj Close in some dates, for example:

AAPL104.78
AAPL102.15
AAPL100.15
AAPL95.92
GOOGL741.84
GOOGL742.58
GOOGL743.61
GOOGL726.39
IBM134.57
IBM134.47
IBM133.80
IBM131.80
APC49.07
APC48.46
APC43.72
APC40.06
XOM76.76
XOM77.41
XOM76.77
XOM75.54

My target is to calculate the correlation coefficient among all pairs. I am able to calculate the correlation coefficient between two specific equities as you can see in the answer of one of my previous post:

LOAD If(Equity <> previous(Equity),1,Rangesum(1,Peek(RowNo))) as RowNo,

Equity,

(html, codepage is 1252, embedded labels, table is @2);

Then:

=Correl( aggr(Only({<Equity = {AAPL}>}[Adj Close]), RowNo ),aggr(Only({<Equity = {IBM}>}[Adj Close]), RowNo) )

Correl function works perfectly with "AAPL" and "IMB"; but I have a lot of equities and I want to calculate the coefficient for all pairs; I 'can't' do this "by hand" because it is a really slow work.

How can I solve?
Thank you.

1 Solution

Accepted Solutions
MVP

Try this may be:

Table:

Equity,

FROM

(html, codepage is 1252, embedded labels, table is @2);

Left Join (Table)

Equity as _Equity,

Resident Table;

Dimension: Equity and _Equity

4 Replies
MVP

Try this may be:

Table:

Equity,

FROM

(html, codepage is 1252, embedded labels, table is @2);

Left Join (Table)

Equity as _Equity,

Resident Table;

Dimension: Equity and _Equity

Not applicable
Author

Great answer. It works. Just a question about rendering: i'm not able to open .qvw since I'm using a personal edition.
How can I get your table?

I use a pivot table chart and this is what I get:

MVP

Just drag the _Equity Dimension to the right top corner and it will be pivoted to go across

Not applicable
Author

Thank you for your patience. It works perfectly!

Community Browser