Learn how to a build a cloud-first strategyRegister Now


Std Deviation of daily returns for stock prices

Posted on 2006-05-11
Medium Priority
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
  • 4
  • 2
LVL 10

Expert Comment

ID: 16659097
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

ID: 16659467

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

ID: 16659517
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'
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 10

Accepted Solution

ivanovn earned 1600 total points
ID: 16660307
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

ID: 16660976
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

ID: 16662073

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 62

Expert Comment

ID: 17151909
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month20 days, 19 hours left to enroll

810 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