Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead 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:

EquityAdj Close
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,

     [Adj Close]

FROM[https://community.qlik.com/thread/213200]

(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
sunny_talwar

Try this may be:

Table:

LOAD AutoNumber(RowNo(), Equity) as Key,

  Equity,

    [Adj Close]

FROM

[https://community.qlik.com/thread/213273]

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

Left Join (Table)

LOAD Key,

  Equity as _Equity,

  [Adj Close] as [_Adj Close]

Resident Table;


Capture.PNG

Dimension: Equity and _Equity

Expression: =Correl([Adj Close], [_Adj Close])

View solution in original post

4 Replies
sunny_talwar

Try this may be:

Table:

LOAD AutoNumber(RowNo(), Equity) as Key,

  Equity,

    [Adj Close]

FROM

[https://community.qlik.com/thread/213273]

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

Left Join (Table)

LOAD Key,

  Equity as _Equity,

  [Adj Close] as [_Adj Close]

Resident Table;


Capture.PNG

Dimension: Equity and _Equity

Expression: =Correl([Adj Close], [_Adj Close])

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:

pple.png

sunny_talwar

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!