calculating "Max Draw Down" in SQL

Question

edit: it's worth reviewing the comments section of the first answer to get a clearer idea of the problem.

edit: I'm using SQLServer 2005

something similar to this was posted before but I don't think enough information was given by the poster to truly explain what max draw down is. All my definitions of max draw down come from (the first two pages of) this paper: http://www.stat.columbia.edu/~vecer/maxdrawdown3.pdf

effectively, you have a few terms defined mathematically:

Running Maximum, M_t

M_t = max_{u in [0,t]} (S_u)
where S_t is the price of a Stock, S, at time, t.

Drawdown, D_t

D_t = M_t - S_t

Max Draw Down, MDD_t

MDD_t = max_{u in [0,t]} (D_u)

so, effectively what needs to be determined is the local maximums and minimums from a set of hi and low prices for a given stock over a period of time. I have a historical quote table with the following (relevant) columns:

stockid int  
day date  
hi  int --this is in pennies  
low int --also in pennies  

so for a given date range, you'll see the same stockid every day for that date range.

EDIT:
hi and low are high for the day and low for each day.

once the local max's and min's are determined, you can pair every max with every min that comes after it and calculate the difference. From that set, the maximum difference would be the "Max Draw Down".

The hard part though, is finding those max's and min's.

edit: it should be noted: max drawdown is defined as the value of the hypothetical loss if the stock is bought at it's highest buy point and sold at it's lows sell point. A stock can't be sold at a minval that came before a maxval. so, if the global minval comes before the global maxval, those two values do not provide enough information to determine the max-drawdown.

Answer 1

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.