SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Stock Price Correlation Matrix


A Stock Price Correlation Matrix

Author
Message
eephus101
eephus101
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 348
Comments posted to this topic are about the item A Stock Price Correlation Matrix

John R
irozenberg
irozenberg
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 145
Please note that matrix is symmetrical - therefore it could be calculated at least twice quicker. :-P
William Melcher
William Melcher
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 239
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
sherifffruitfly
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 427
Is this process numerically stable?
eephus101
eephus101
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 348
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
mark_donskoy
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 47
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
Misha_SQL
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 1023
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
Jon Cooney
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 320
Thanks!

I was looking for exactly this!
ze.emobile
ze.emobile
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 51
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
Wavesailor
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 13
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search