asked on

Std Deviation of daily returns for stock prices

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

ivanovn

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'

mearisk

ASKER

ivanovn,

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

mearisk

ASKER

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'

ASKER CERTIFIED SOLUTION

ivanovn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mearisk

ASKER

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

TICKER TRADEDATE CLOSEPRICE RETURN
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

mearisk

ASKER

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.

Mark

gheist

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