I'm trying to back test strategies using Excel. I'm trying to use a column to determine my max draw-down from the high point of my account at any time. Is there a function that might be helpful in this regard? Trying to convert it into logic-speak if the max drawn-down I'm describing is not clear, it might say something like - "at any point along the column, from that point, look back to the lowest point that hadn't been prviously recovered by subsequent increases, calculate the difference, then tell me the highest of these calculated amounts." Something like that. Also, say I want to plot some numbers, and I have at least a point in the lower left of the graph, and one in the upper right. I want to draw a line between those two. But I don't want to be just a straight diagonal line. As it goes from the far bottom left up to the far top right, I want it to start of rising very slowly at first, almost horizontally. Then, the further right you go, it starts rising faster and faster. Such that when it is almost all the way to the right the line is almost vertical. Anyone know what such a line (and graph) would be called? And if I had a few points (probably the two described, and at least one midpoint to help define the shape of the curve) how I could use a function or other feature of Excel to draw a graph and extrapolate the various values among the drawn line? As always, thanks in advance fellow ETers!
Let's say that in column A you have your account value, which varies every day. In column B, cell B3, you can have the following formula: =A3/MAX($A$2:A3)- 1 Then copy that down. This formula shows you the max DD, day to day.
The line you describe is exponential. I don't use Excel much but if you draw the graph with a log scale and then add a linear fit to the graph that will give you the line. The problem then is converting it back into 'normal' space from log space to get meaningful information. The harder solution is to use the LN() function on all your values, then use a regression on the logged values, then get the coefficients and use them to work out the fitted y values for each of your x values. Then do EXP() on the fitted y values to convert them back into 'normal' space. I'm afraid I haven't time to find a copy of Excel and spell out how to do this in detail, but hopefully this will give you some pointers. BTW you're probably at the limit of what Excel can do and you might consider learning a stats package, or python/R. GAT
Here is a google docs sheet that does what I describe: The explicit link is here in case the sheet doesn't display: Code: https://docs.google.com/spreadsheets/d/17di3b7MJCBX2ZnKs3Rki409o33EQfS5Y67Z8XCkhDBc/edit?usp=sharing GAT
I recommend continuing with excel, you'd be surprised how powerful this program is. There's a reason a lot of brokers and platform providers now provide excel linking, RTD and APIs etc, its just so much easier to implement your strategy on. For designing and visualising your trading system, nothing competes with Excel so even if you evolve towards a programming language like Python later on (which can take many, many years to learn), you'll at least have the system design covered which will make things easier.