Excel help needed (again..)

April 21st, 2006 by eyal | Filed under Techie, Trading Plan. | Print This Post Print This Post

Hey Excel gurus.. I’m looking at adding a calculation of drawdown to my spreadsheet. I have columns of both individual trade results as well as a column for cumulative P&L but I have no idea how to build a formula which looks at the largest drawdown drop. I may also get the Excel Bible book and Excel Advanced Report Development for that extra edge in applying those concepts to my trading journal. Any help would also be greatly appreciated.. thank you.

Other possibly related posts:

« SWIR
My Drawdown »

12 Responses to “Excel help needed (again..)”

  1. Scott | 21/04/06

    This might help:

    http://www.andreassteiner.net/performanceanalysis/?download=MaxDrawdown.xls

  2. eyal | 21/04/06

    Thanks Scott. Looks pretty complicated, I’ll see if I can decipher how he’s calculating it.

  3. Babak | 21/04/06

    Eyal, ask in this forum.

  4. richard | 21/04/06

    Not exactly sure what you want, but are you looking for something like this?

    http://homepage.mac.com/rwtodd/Drawdown.xls

    I take the current drawdown to be:

    MAX( cum p/l to date) - current cum p/l

    … in terms of money, but you could convert it to % fairly easy, and then the next column identifies the largest drawdown from the start of the downturn to the deepest point.

    ..just thrown together, but seems to work on the sample data I typed in for cum p/l.

  5. richard | 21/04/06

    Here’s the % version, if that helps… seems like a better way to gauge it.

    http://homepage.mac.com/rwtodd/DrawdownPct.xls

  6. Scott | 22/04/06

    Eyal, take a look at column D in the spreadsheet at the link I listed above. It’s basically doing what Richard describes above — calculating drawdown (of % return). Then, cell E3 is calculating the max drawdown of the entire data set.
    The way they have it layed out, with the charts covering the top part of the data set, is confusing. Just move the charts out of the way.

  7. Andrew | 22/04/06

    Gday Eyal,

    This is what I use for $ DD in my spreadsheets.

    Column A = Individual trade results
    Column B = Equity Curve of results
    Columb C = Dollar Drawdown

    a1 = $5 (first trade is a $5 win)
    b1 = a1 (equity curve will be $5)
    c1 = 0 (DD is zero)

    a2 = -$10 (second trade is a $10 loss)
    b2 = b1 + a2 (Equity Curve is now -$5)
    c2 = =MIN(0,B2-B1+C1) (this will show a DD of -$5 now)

    Now just Ctr D (fill) the formula for column C down the column and presto Excel does the rest.

    Hope that helps.

    Regards,
    Andrew.

  8. Andrew | 22/04/06

    Bit sleepy when I wrote this.

    c2 will show a $10 draw down of course and not $5.

    And you will need to ctr D for column B and column C, after trade results have been entered. Anyway feel free to email me if that isn’t clear.

    Andrew.

  9. eyal | 22/04/06

    Super, guys! Got it working well. Thanks a lot for your help.

  10. Trader Eyal » Blog Archive » My Drawdown | 22/04/06

    [...] I’m always looking to better understand my system and the results it produces. I have taken a total of 94 trades this year (every single one is recorded on this blog btw) and with the kind help of a number of smart readers I now have details of my drawdown todate. If you’re interested, here are the numbers, in R risk value: [...]

  11. Abraham Mathews | 28/02/08

    Hi
    Could you share the xls book that worked for you on teh max drawdown

  12. eyal | 28/02/08

    Abrahami, I’ll need to look it up. I’m now using Stocktickr instead for my trading journal.

Share Your Thoughts