calculating "Max Draw Down" in SQL

Answer 1

For SQL Server and for one stock at a time, try this:

Create Procedure 'MDDCalc'(
    @StartDate date,
    @EndDate date,
    @Stock int)

AS

DECLARE @MinVal Int
DECLARE @MaxVal Int
DECLARE @MaxDate date

SET @MaxVal = (
    SELECT MAX(hi)
    FROM Table
    WHERE Stockid = @Stock 
    AND Day BETWEEN (@Startdate-1) AND (@EndDate+1))

SET @MaxDate=(
    SELECT Min(Date)
    FROM Table
    WHERE Stockid = @Stock
    AND hi = @MaxVal)

SET @MinVal = (
    SELECT MIN(low)
    FROM Table
    WHERE Stockid = @Stock 
    AND Day BETWEEN (@MaxDate-1) AND (@EndDate+1))

SELECT  (@MaxVal-@MinVal) AS 'MDD'

Answer 2

Brutally inefficient, but very simple version using a view is below:

WITH DDView
AS (SELECT      pd_curr.StockID,
                pd_curr.Date,
                pd_curr.Low_Price  AS CurrPrice,
                pd_prev.High_Price AS PrevPrice,
                pd_curr.Low_Price / pd_prev.High_Price - 1.0 AS DD

    FROM        PriceData pd_curr
    INNER JOIN  PriceData pd_prev
            ON  pd_curr.StockID = pd_prev.StockID
            AND pd_curr.Date >= pd_prev.Date
            AND pd_curr.Low_Price <= pd_prev.High_Price
            AND pd_prev.Date >= '2001-12-31' -- @param: min_date of analyzed period
    WHERE       pd_curr.Date <= '2010-09-31' -- @param: max_date of analyzed period
)
SELECT      dd.StockID,
            MIN(COALESCE(dd.DD, 0)) AS MaxDrawDown
FROM        DDView dd
GROUP BY    dd.StockID

As usually you would perform the analysis on specific time period, it would make sense to wrap the query in a stored procedure with the parameters @StartDate, @EndDate and possibly @StockID. Again, this is quite inefficient by design - O(N^2), but if you have good indices and not huge amount of data, SQL Server will handle it pretty good.

Answer 3

Is this what you're after?

select StockID,max(drawdown) maxdrawdown
from (
select h.StockID,h.day highdate,l.day lowdate,h.hi - l.lo drawdown
from mdd h
  inner join mdd l on h.StockID = l.StockID
    and h.day<l.day) x
group by StockID;

It's a SQL based brute force approach. It compares every low price after today's hi price within the same stock and finds the greatest difference between the two prices. This will be the Maximum Draw Down.

It doesn't compare consider the same day as possible for maximum draw down as we don't have enough info in the table to determine if the Hi price happened before the Lo price on the day.

Answer 4

Some things we need to consider in the problem domain:

1. Stocks have a range of prices every day, often viewed in candlestick charts
2. lets call the highest price of a day HI
3. lets call the lowest price of a day LOW
4. the problem is constrained by time, even if the time constraints are the IPO date and Delisting Dates
5. the maximum drawdown is the most you could possibly lose on a stock over that timeframe
6. assuming a LONG strategy: logically if we are able to determine all local maxes (MAXES) and all local mins (MINS) we could define a set of where we pair each MAX with each subsequent MIN and calculate the difference DIFFS
7. Sometimes the difference will result in a negative number, however that is not a drawdown
8. therefore, we need to select append 0 in the set of diffs and select the max

The problem lies in defining the MAXES and the MINS, with the function of the curve we could apply calculus, bummer we can't. Obviously

1. the maxes need to come from the HI and
2. the MINS need to come from the LOW

One way to solve this is to define a cursor and brute force it. Functional languages have nice toolsets for solving this as well.

Answer 5

Here is a SQL Server 2005 user-defined function that should return the correct answer for a single stockid very efficiently

CREATE FUNCTION dbo.StockMaxDD(@StockID int, @day datetime) RETURNS int  AS
 BEGIN
    Declare @MaxVal int;    Set @MaxVal = 0;
    Declare @MaxDD int;     Set @MaxDD = 0;

    SELECT TOP(99999)
        @MaxDD  = CASE WHEN @MaxDD < (@MaxVal-low) THEN (@MaxVal-low)  ELSE @MaxDD END,
        @MaxVal = CASE WHEN hi  > @MaxVal THEN hi   ELSE @MaxVal END
    FROM    StockHiLo
    WHERE   stockid = @Stockid
      AND   [day]  <= @day
    ORDER BY [day] ASC

    RETURN @MaxDD;
 END

This would not, however, be very efficient for doing a number of stockids at the same time. If you need to do many/all of the stockids at once, then there is a similar, but substantially more difficult approach that can do that very efficiently.