has anybody developed an excel macro

smodato · Post by **smodato** » Wed Nov 17, 2004 9:05 am

Hi, I was mixing and remixing data un some excel files and I was starting to think about the development of a macro do calculate the drawdown of an equity.
So I suppose I have the equity sotred trade by trade in the cells of my spreadsheet, I can graph it and from the graph evalutae the maximum drawdonw, it would be nice anyway to get the info elsewhere and maybe with some more details.
Before starting my visualbasic adveture I thought it would be better to ask whether something similar is already available.
Thanks, bye
smodato

bazzacontango · Post by **bazzacontango** » Sun Jan 09, 2005 7:38 pm

Maybe a little late but I've wrote a macro/sheet that calcs drawdown%, sharpe, sortino, RRR and Ave%worstDD, and can post if you're still interested.

smodato · Post by **smodato** » Mon Jan 10, 2005 1:37 am

Thanks for your reply, I'm still very much interested in the matter so, if it is no problem, please post your solution.
Bye and thanks
Smodato

bazzacontango · Post by **bazzacontango** » Mon Jan 10, 2005 3:04 am

OK, here's the spreadsheet, I've included Mark Johnson's "Thirteen" equity curve for an example of use. Appreciate any feedback from you or any else who uses it.

Read the Notes then press the button "Calc Ratios" on the "Go" sheet.

Cheers.

Fixed an error in drawdown calc and added drawdown length.

smodato · Post by **smodato** » Mon Jan 10, 2005 4:35 am

Thanks, very much, I launche the macro on the file as downloaded and I got an error, run time 1004, the debugger showed this:
Selection.Sort Key1:=Range("M10"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal inMrk Johnson's average of 5 worst DD calculation.
I will look into the code to understand it, it is a great job anyway, I'm sure it will help my analyses very much.

Bye
Smodato

bazzacontango · Post by **bazzacontango** » Mon Jan 10, 2005 6:32 am

I don't know why you received that error.

Did it ask to open as read-only, say no if it does.

You could replace that section of code with a bubble-sort routine. I was lazy and just used the excel application sort.

smodato · Post by **smodato** » Mon Jan 10, 2005 8:16 am

Hi, I tried again with different time frames as input but again and again the same mistake, I said both Yes and No to read only open but no change even.
In any case I will look into the macro and try to get out what I need, if I can find out why the error occurs I will come back to you.
Bye
Smodato

bazzacontango · Post by **bazzacontango** » Wed Jan 19, 2005 8:18 am

An updated version of the spreadsheet has been uploaded.

It fixes an issue with the drawdown initialization and added drawdown length calculation.

Note: the drawdown lengths and dates are sorted by longest drawdown, and do not correspond to the deepest drawdowns. So ddindex 1 row, has the deepest% and the longest in calendar days.