2

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?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Coding_Newbie
  • 365
  • 1
  • 3
  • 11
  • Tag your question with the database you are using. – Gordon Linoff Feb 10 '18 at 21:35
  • There are a million answers for this sort of thing using tables with a single "Ticker", this is what these tables actually look like; too bad you didn't accept the answer that best solves the problem – Dan White Nov 10 '20 at 21:57

2 Answers2

4

You want partition by:

SELECT Ticker, Date, price,
       ((price / lag(price, 1) OVER (PARTITION BY Ticker ORDER BY [Date])) - 1)* 100 AS daily_return
FROM [dbo].[Temp]
Order by Ticker, Date;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • How would I calculate a running drawdown each day? Drawdown is measured by (max price up to and including today - today's price ) - 1. For ABC, drawdown would be 0% on 1/1, 0% and 1/2, because it was making new highs on both days, 1.7% on 1/3 (99.8/101.5), 5.9% on 1/4 and 23.2% on 1/5. Do I need to store the max price in a temporary variable? – Coding_Newbie Feb 11 '18 at 06:08
  • correction: Drawdown = (current price / max price ) - 1 – Coding_Newbie Feb 11 '18 at 06:09
  • @Coding_Newbie . . . I am unclear on your comment. Your question appears to be: "I don't know how to re-set the value to NULL when it processes a new ticker." If you have another question, ask it as a question, not as a comment. – Gordon Linoff Feb 11 '18 at 13:08
0

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......)
armin.sharma
  • 16