Std Deviation of daily returns for stock prices

Posted on 2006-05-11
Last Modified: 2012-06-27

I am trying to create a sql statement on a table that would calculate the standard deviation of the daily returns for each Ticker I send to the function.


Date             Ticker              closingPrice
1/2/2006       CSCO               35.45
1/3/2006       CSC0               35.25
1/4/2006       CSCO               35.55
1/5/2006       CSCO               36.00

I first am having a problem with the first calculation which is to calculate the return of two day which is (ClosingPrice / ClosingPrice[d-1])-1.  I know that I need to join the table to itself, but I am getting alot of records for each day. In the current table I should get three records returns.

The final algorythm is STDEV((ClosingPrice / ClosingPrice[d-1])-1) for the period of time selected for that ticker.


Question by:mearisk
    LVL 10

    Expert Comment

    Try something like:

    SELECT ((closingPrice/(SELECT closingPrice FROM tickertable WHERE tstamp='2006-01-05' AND ticker='CSCO'))-1) FROM tickertable WHERE tstamp='2006-01-04' AND ticker='CSCO'

    Author Comment


    thank you for your response.

    Question: In the sub statement the date would be the previous day OR the LAST Date for CSCO. Would some sort of MAX Date command work???

    Author Comment

    Here is what I started out with

    select newPrice.tradedate, (newPrice.closingPrice/oldPrice.closingPrice) - 1 as return
    from securityPrices newPrice, securityPrices oldPrice
    where  newPrice.exchangeCode = oldPrice.exchangeCode
    AND newPrice.ticker = oldPrice.ticker
    AND newPrice.ticker = 'CSCO'
    AND newPrice.tradedate = (select tradedate
                      from securityPrices
                      where ticker = 'CSCO'
                      AND tradedate <= '5/2/2006'
                      ORDER BY 1 DESC LIMIT 1 offset 1)
    AND newPrice.tradedate <= '5/2/2006'
    LVL 10

    Accepted Solution

    Yes, you can use MAX date in the substatement.

    So my example would turn into:

    SELECT ((closingPrice/(SELECT closingPrice FROM securityPrices WHERE tradedate=((SELECT MAX(tradedate) FROM securityPrices WHERE ticker='CSCO')-1) AND ticker='CSCO'))-1) FROM securityPrices WHERE tradedate=(SELECT MAX(tradedate) FROM securityPrices WHERE ticker='CSCO') AND ticker='CSCO'

    Author Comment

    This has been extremely helpful,  and gets me the return for the last two days of data. Which I can use as well.

    But because of my inability to be clear I have appended some information that might be helpful to get this over the finish line.

    I need to get the standard deviation of returns for all data upto the present.

    The Data example

    CSCO      4/28/2006      20.95      
    CSCO      5/1/2006      20.96                  0.04773%
    CSCO      5/2/2006      21.39                  2.05153%
    CSCO      5/3/2006      21.49                   0.46751%
    CSCO      5/4/2006      21.86                  1.72173%
    CSCO      5/5/2006      21.75                 -0.50320%
    CSCO      5/8/2006      21.76                  0.04598%
    CSCO      5/9/2006      21.68                 -0.36765%
    CSCO      5/10/2006      20.75                -4.28967%

    STD DEVIATION 1.93097% is what I am looking to get for this set of data.

    I am very sorry for any mix up.

    Again, thank you very much for your help.



    Author Comment


    I get this far BUT:

    SELECT a.tradedate, (a.closingprice/b.closingPrice)-1 as return
    from securityPrices a INNER JOIN securityPrices b
    ON a.tradedate = b.tradedate+1
    where a.ticker = b.ticker
    AND a.ticker = 'CSCO'

    I lose a day and a record every few days. I know it because of the b.tradedate + 1 and joining with a record in 'a'....but....

    This is it so far....getting there.

    LVL 61

    Expert Comment

    No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
    I will leave the following recommendation for this question in the Cleanup topic area:

    Accept ivanovn http:#16660307

    Any objections should be posted here in the next 4 days. After that time, the question will be closed.

    EE Cleanup Volunteer

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now