A Stock Price Correlation Matrix

  • eephus101

    SSC Enthusiast

    Points: 138

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

    John R

  • irozenberg

    SSC-Addicted

    Points: 478

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

  • William Melcher

    SSC-Addicted

    Points: 425

    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

    Is this process numerically stable?

  • eephus101

    SSC Enthusiast

    Points: 138

    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

    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: 5385

    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

    Thanks!

    I was looking for exactly this!

  • ze.emobile

    SSC Enthusiast

    Points: 139

    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

    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

    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:

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

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