Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Std Deviation of daily returns for stock prices

Posted on 2006-05-11
Medium Priority
2,313 Views
Hi.

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.

table

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.

Thanks,

Mark
0
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'
0

Author Comment

ID: 16659467
ivanovn,

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???
0

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'
from securityPrices
where ticker = 'CSCO'
ORDER BY 1 DESC LIMIT 1 offset 1)
0

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'
0

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.

Mark

0

Author Comment

ID: 16662073

I get this far BUT:

from securityPrices a INNER JOIN securityPrices b
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.

Mark
0

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.

gheist
EE Cleanup Volunteer
0

## Featured Post

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…