I have a table of stock prices that I'm trying to calculate daily returns:
Ticker Date price
ABC 01/01/13 100.00
ABC 01/02/13 101.50
ABC 01/03/13 99.80
ABC 01/04/13 95.50
ABC 01/05/13 78.00
XYZ 01/01/13 11.50
XYZ 01/02/13 12.10
XYZ 01/03/13 13.15
XYZ 01/04/13 14.10
XYZ 01/05/13 15.55
I have a formula for calculating returns, using the Lag function, but I don't know how to re-set the value to NULL when it processes a new ticker.
Its using ABC's price from 01/05/13 to calculate XYZ's return for 01/01/03. XYZ's 01/01/03 return should be NULL.
Ticker Date price RETURN
ABC 01/01/13 100.00 NULL
ABC 01/02/13 101.50 1.50
ABC 01/03/13 99.80 -1.67
ABC 01/04/13 95.50 -4.31
ABC 01/05/13 78.00 -18.32
XYZ 01/01/13 11.50 -85.26
XYZ 01/02/13 12.10 5.22
XYZ 01/03/13 13.15 8.68
XYZ 01/04/13 14.10 7.22
XYZ 01/05/13 15.55 10.28
Here's my script:
SELECT Ticker,Date, price,((price / lag(price, 1) OVER (ORDER BY Ticker, [Date])) - 1)* 100 AS 'RETURN'
FROM [dbo].[Temp]
Order by Ticker, Date
Do I need to create a loop to calculate the correct return?
You can use an if statement on the date, If the date is not in the legit range, you return null, otherwise, return your formula.
(IF date_field<lower_date,null,...............what ever......)