A Stock Price Correlation Matrix

Question

Post reply

A Stock Price Correlation Matrix

eephus101

SSC Enthusiast

Points: 138
More actions
May 13, 2012 at 9:27 pm

#256498

Comments posted to this topic are about the item A Stock Price Correlation Matrix
John R

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

irozenberg SSC-Addicted Points: 478 More actions · Answer 1

Please note that matrix is symmetrical - therefore it could be calculated at least twice quicker. 😛

William Melcher SSC-Addicted Points: 425 More actions · Answer 2

thanks for the article John! To ironzenberg's point, you could cut down the number of calculations by adding a where b.ticker > a.ticker to the join.

sherifffruitfly Ten Centuries Points: 1198 More actions · Answer 3

sherifffruitfly

Ten Centuries

Points: 1198

May 14, 2012 at 8:57 am

#1487162

Is this process numerically stable?

eephus101 SSC Enthusiast Points: 138 More actions · Answer 4

irozenberg , william,

thanks for the feedback!

You are correct. I did calc twice to displace both the upper left and ower right portions of the displayed matrix. The reason for this is that many financial pros like to see both sides of this "mirror".

For large matrices, where performance could be impaired, I would utilize the "b.ticker > a.ticker" clause.

again, thanks...

-John

John R

mark_donskoy SSC Rookie Points: 35 More actions · Answer 5

about 7 years ago i had a similar program - it calculated correlation between all Russel 1000 stocks plus few indices (all together about 1100 tickers) for data from 2 years back up to the date.

it took about 50GB of space on tempdb and about an hour to calculate

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 6

In addition to this being a very useful article, I liked how it brings together several other powerful concepts: TVP and dynamic pivot. Thank you!

Jon Cooney Old Hand Points: 329 More actions · Answer 7

Jon Cooney

Old Hand

Points: 329

May 15, 2012 at 1:38 pm

#1487985

Thanks!

I was looking for exactly this!

ze.emobile SSC Enthusiast Points: 139 More actions · Answer 8

Thanks for posting this. It's a blast from the past for me because I was tasked with doing the same about 20 years ago and all I had to work with was C-language. Relational databases at that time were not suitable for such work. As I recall my exercise also involved taking it a step further and attempting to optimize asset weighting so asto minimize portfolio variance for a required rate of return. In any event, SQL is way way more efficient approach to doing this stuff, at least from the programming standpoint, and you demonstrated that.

Wavesailor SSC Enthusiast Points: 101 More actions · Answer 9

Hi,

Thanks for article.

Does anyone know how to do this in mySQL? - I have some data in a mySQL DB and would love to view it.

I was able to convert the function but the rest exceeds my limited ability

DELIMITER $$

CREATE FUNCTION `fnGetCorrelation`(TickerA VARCHAR(10),

TickerB VARCHAR(10),

DateStart DATE,

DateEnd DATE

) RETURNS decimal(22,6)

BEGIN

DECLARE MeanA, MeanB, Corr, Return_Corr NUMERIC(22,6);

-- Get Mean Value for Ticker A

SELECT AVG(Price) INTO MeanA FROM Prices

WHERE ticker = TickerA

AND Date >= DateStart

AND Date <= IFNULL(DateEnd, CURDATE())

GROUP BY ticker;

-- Get Mean Value for Ticker B

SELECT AVG(Price) INTO MeanB FROM Prices

WHERE ticker = TickerB

AND Date >= DateStart

AND Date <= IFNULL(DateEnd, CURDATE())

GROUP BY ticker;

-- Calculate Correlation Coefficient

SELECT

(AVG((prcA.Price-MeanA)*(prcB.Price-MeanB)))/(STDDEV_POP(prcA.Price)*STDDEV_POP(prcB.Price)) into CORR

FROM Prices prcA JOIN Prices prcB

ON prcA.Date = prcB.Date

WHERE prcA.ticker = TickerA

AND prcB.TICKER = TickerB

AND prcA.Date >= DateStart

AND prcA.Date <= DateEnd;

-- SET Return_Corr = Corr;

RETURN(Corr);

END

Can anybody help?

Wavesailor SSC Enthusiast Points: 101 More actions · Answer 10

Wavesailor (5/18/2012)
...
Does anyone know how to do this in mySQL?
Can anybody help?

I figured it out. So here is the answer in case someone else is looking for it.

Your driving table of Tickers

DROP TABLE IF EXISTS TickerListType;

CREATE TABLE TickerListType (

`Ticker` varchar(30) NOT NULL,

PRIMARY KEY (`Ticker`)

);

INSERT INTO TickerListType

VALUES

('CAR1'),

('DRG1'),

('GLD1'),

('OIL1'),

('RTL1')

;

The code for the Stored Procedure

delimiter //

DROP PROCEDURE IF EXISTS uspGetCorrMatrix//

CREATE PROCEDURE uspGetCorrMatrix(IN DateStart date, IN DateEnd date)

BEGIN

SELECT concat(

"SELECT CorrMatrix,", "",

group_concat( DISTINCT concat("\t\tMAX(IF(c.Ticker2 = '", Ticker, "', c.Corr, NULL)) AS '", Ticker, "'" , "")),

"FROM ",

"(select a.TICKER as CorrMatrix, b.TICKER as Ticker2",

"\t , fnGetCorrelation(a.TICKER, b.TICKER, '", DateStart, "','", DateEnd, "') as Corr",

"\t from TickerListType a CROSS JOIN TickerListType b) AS c",

"GROUP BY CorrMatrix"

) statement

INTO @dynamic_correlation_matrix

FROM TickerListType;

PREPARE dynamic_correlation_matrix

FROM @dynamic_correlation_matrix;

EXECUTE dynamic_correlation_matrix;

DEALLOCATE PREPARE dynamic_correlation_matrix;

END;//

delimiter ;

And finally call the Stored Procedure

CALL uspGetCorrMatrix('2011-01-01','2011-03-31');

I learnt a lot about the differences between SQL Server and mySQL :w00t: