Databases
StackOverflow2013
Postsdbo
POcalculating "Max Draw Down" in SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcalculating "Max Draw Down" in SQL
    primarykey
    data
    text
    <p>edit: it's worth reviewing the comments section of the first answer to get a clearer idea of the problem.</p> <p>edit: I'm using SQLServer 2005</p> <p>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: <a href="http://www.stat.columbia.edu/~vecer/maxdrawdown3.pdf">http://www.stat.columbia.edu/~vecer/maxdrawdown3.pdf</a></p> <p>effectively, you have a few terms defined mathematically:</p> <p>Running Maximum, M<sub>t</sub></p> <blockquote> <p>M<sub>t</sub> = max<sub>u in [0,t]</sub> (S<sub>u</sub>)<br> where S<sub>t</sub> is the price of a Stock, S, at time, t.</p> </blockquote> <p><p>Drawdown, D<sub>t</sub><p></p> <blockquote> <p>D<sub>t</sub> = M<sub>t</sub> - S<sub>t</sub></p> </blockquote> <p><p>Max Draw Down, MDD<sub>t</sub></p> <blockquote> <p>MDD<sub>t</sub> = max<sub>u in [0,t]</sub> (D<sub>u</sub>)</p> </blockquote> <p>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: </p> <blockquote> <pre><code>stockid int day date hi int --this is in pennies low int --also in pennies </code></pre> <p>so for a given date range, you'll see the same stockid every day for that date range.</p> </blockquote> <p><strong>EDIT:</strong><br> hi and low are high for the day and low for each day.</p> <p>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".</p> <p>The hard part though, is finding those max's and min's.</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRamy
    1. USRamy
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POcalculating "Max Draw Down" in SQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POcalculating "Max Draw Down" in SQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POcalculating "Max Draw Down" in SQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt would help to know what database you're using (Oracle, SQL Server, PostgreSQL, mySQL, etc).
      singulars
      1. POcalculating "Max Draw Down" in SQL
      1. USBob Jarvis
    2. CO@Bob, some of Ramy's older questions refer to SQLServer.
      singulars
      1. POcalculating "Max Draw Down" in SQL
      1. USJosef K
    3. COsorry, just edited the post to show sqlserver.
      singulars
      1. POcalculating "Max Draw Down" in SQL
      1. USRamy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload