I have a table of stock prices, from which I'd like to calculate drawdowns.
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'm defining drawdown as the difference between the max price (up to and including that date) minus the current price divided by the max price.
Here's what the daily max price and daily drawdown should look like:
Ticker Date price max drawdown
ABC 01/01/13 100.00 100.00 0.0%
ABC 01/02/13 101.50 101.50 0.0%
ABC 01/03/13 99.80 101.50 1.7%
ABC 01/04/13 95.50 101.50 5.9%
ABC 01/05/13 78.00 101.50 23.2%
XYZ 01/01/13 11.50 11.50 0.0%
XYZ 01/02/13 12.10 12.10 0.0%
XYZ 01/03/13 13.15 17.15 0.0%
XYZ 01/04/13 14.10 14.10 17.8%
XYZ 01/05/13 15.55 15.55 9.3%
I know how to calculate one max drawdown for an entire dataset, but am struggling to refine that apply to individual stocks and be a daily drawdown.
;WITH x AS
( SELECT [drop] = ((s.price-e.price)/s.price) *100
FROM [temp] AS s
INNER JOIN [temp] AS e
ON s.[Date] < e.[Date]
AND s.price > e.price
)
SELECT [Largest Drawdown] = MAX([drop]) FROM x;
Largest Drawdown
87.900000000000000
This drawdown is calculating the max price for the entire dataset (ABC of 101.50) and the low from the dataset (XYZ = 11.50) to get its largest drawdown of 87.9%
How do I calculate daily drawdowns for each stock?
This sounds like a window function:
select t.*,
max(t.price) over (partition by ticker order by date) as max_price,
(t.price / max(t.price) over (partition by ticker order by date)) - 1 as ratio
from temp t;