0

I have a table of stock prices: 

DECLARE @table TABLE (ClosingDate DATE, Ticker VarChar(6), Price Decimal (6,2))

INSERT INTO @Table
VALUES ('1/1/13' , 'ABC' , '100.00')
          ,('1/2/13' , 'ABC' , '101.50')  
          ,('1/3/13' , 'ABC' , '99.80')
          ,('1/4/13' , 'ABC' , '95.50')
          ,('1/5/13' , 'ABC' , '78.00')
          ,('1/1/13' , 'JKL' , '34.57')
          ,('1/2/13' , 'JKL' , '33.99')  
          ,('1/3/13' , 'JKL' , '31.85')
          ,('1/4/13' , 'JKL' , '30.11')
          ,('1/5/13' , 'JKL' , '45.00')
          ,('1/1/13' , 'XYZ' , '11.50')
          ,('1/2/13' , 'XYZ' , '12.10')  
          ,('1/3/13' , 'XYZ' , '17.15')
          ,('1/4/13' , 'XYZ' , '14.10')
          ,('1/5/13' , 'XYZ' , '15.55')

I calculate drawdowns (% from max peak or max price) for each ticker:

SELECT Ticker,
    t.ClosingDate,
    t.Price, 
    MAX(t.[Price]) OVER (PARTITION BY Ticker ORDER BY ClosingDate) AS max_price,
    (t.[Price] / MAX(t.[Price]) OVER (PARTITION BY Ticker ORDER BY ClosingDate)) - 1 AS Drawdown
FROM 
    @Table t;

Output:

  Ticker    ClosingDate Price   max_price   Drawdown
  -----------------------------------------------------
    ABC     2013-01-01  100.00  100.00     0.000000000
    ABC     2013-01-02  101.50  101.50     0.000000000
    ABC     2013-01-03  99.80   101.50    -0.016748769
    ABC     2013-01-04  95.50   101.50    -0.059113301
    ABC     2013-01-05  78.00   101.50    -0.231527094
    JKL     2013-01-01  34.57   34.57      0.000000000
    JKL     2013-01-02  33.99   34.57     -0.016777553
    JKL     2013-01-03  31.85   34.57     -0.078680938
    JKL     2013-01-04  30.11   34.57     -0.129013596
    JKL     2013-01-05  45.00   45.00      0.000000000
    XYZ     2013-01-01  11.50   11.50      0.000000000
    XYZ     2013-01-02  12.10   12.10      0.000000000
    XYZ     2013-01-03  17.15   17.15      0.000000000
    XYZ     2013-01-04  14.10   17.15     -0.177842566
    XYZ     2013-01-05  15.55   17.15     -0.093294461

A new high price is designated as a drawdown or 0.

How can I add days in drawdown?

Any date where drawdown = 0 resets the days counter to 0 and builds as each day remains in drawdown (price < max price)

Here is my expected output:

Ticker  ClosingDate  Price       max_price  Drawdown    Days in DD
--------------------------------------------------------------------
ABC     1/1/2013     100.00      100.00      0.0000     0
ABC     1/2/2013     101.50      101.50      0.0000     0
ABC     1/3/2013      99.80      101.50     -0.0167     1
ABC     1/4/2013      95.50      101.50     -0.0591     2
ABC     1/5/2013      78.00      101.50     -0.2315     3
JKL     1/1/2013      34.57       34.57      0.0000     0
JKL     1/2/2013      33.99       34.57     -0.0168     1
JKL     1/3/2013      31.85       34.57     -0.0787     2
JKL     1/4/2013      30.11       34.57     -0.1290     3
JKL     1/5/2013      45.00       45.00      0.0000     0
XYZ     1/1/2013      11.50       11.50      0.0000     0
XYZ     1/2/2013      12.10       12.10      0.0000     0
XYZ     1/3/2013      17.15       17.15      0.0000     0
XYZ     1/4/2013      14.10       17.15     -0.1778     1
XYZ     1/5/2013      15.55       17.15     -0.0933     2
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Coding_Newbie
  • 365
  • 1
  • 3
  • 11
  • Use row_number partitioned by max_price and subtract 1 – uzi Apr 15 '18 at 04:24
  • Uzi, would that approach work for multiple drawdowns? I'm interested in identifying the days in drawdown for any date specified. How would I identify max price up to a specific date? I can easily accomplish this in Excel, but am not sure how to do this in SQL. – Coding_Newbie Apr 15 '18 at 13:20
  • Uzi, it works great. Thanks! – Coding_Newbie Apr 15 '18 at 13:53
  • The only issue may occur if you have gaps in ClosingDates. Have you checked that case? – uzi Apr 15 '18 at 14:30
  • My data was cleaned already for no gaps, but you bring up an interesting point, since my dataset may not be as clean going forward. Will test it out. thanks – Coding_Newbie Apr 15 '18 at 15:22

0 Answers0