Excel calculate maximum drawdown code#

Set sheet = first_price_cell.Worksheet ' sheet for price Price_column = first_price_cell.Column ' price columnĭate_column = first_date_cell.Column ' date column

Excel calculate maximum drawdown code#

Although the O(N^2) solution is, I think, an easier concept to grapple with, when it’s translated into code it just doesn’t grok.Function DIRR(first_price_cell As Range, first_date_cell As Range) As Doubleĭim first_row As Long, last_row As Long, price_column As Long, date_column As Long, sheet As Worksheetįirst_row = first_price_cell.Row ' first row The purist in me likes that fact that the O(N) solution looks like easier code to understand than the O(N^2) solution. If the next value is less than the maximum then we see if this difference is larger than any previously encountered difference, if it is then this is our new peak-to-trough. However, the second part of the ‘if’ is where the magic happens. We move through the prices and the first part of the ‘if’ will find the highest part of the peak so far. This solution is a bit harder to explain. If (prices - prices) > (prices - prices): # You can only determine the largest drawdown on a downward price! The challenge then is to find an O(N) solution to the problem and to save-those-much-needed-cycles for something really important: For small n this doesn’t really matter, but for any decently sized data-series this baby will be slow-as-molasses. That is for any data-series of size N the best and worst case will result in N * N-1 iterations, in shorthand this is O(N^2). The problem with this solution is that it has quadratic complexity. This is a natural way to solve the problem because it looks at all possible start points and assesses what the worst outcome would be. At the end you just return the two points you found. If this difference is also the largest seen in the data-set so far then make it the largest positive difference of all points.

The deal is relatively straight-forward: compare the fist item against every item after it in the list and store the largest positive difference. I don’t know if that’s the proper term but it harks back to the days when I used to be a number-cruncher for some statisticians. It’s what I have come to know as a ‘between’ analysis. This led to the first solution (in Python): I believe what I really want is: the largest positive difference of high minus low where the low occurs after the high in time-order. Part of the problem is coming up with a consistent natural language of what you want your peak and trough to be. To do it reliably (and quickly) on a computer, is not that straight forward. Well, finding these two babys by eye is trivial.

The upper one is the ‘peak’ and the lower one is the ‘trough’.

Clearly past prices are not an indicator of future losses. This is the point of this analysis and is a way for investors in the asset to see how bad, ‘bad’ has really been in the past. You’ll see that there are two points on the graph (marked in red) where if you had invested at the first point and pulled out on the second point you would have the worst-case loss. Look at the following random graph of pretend asset prices: Sometimes investors want to be able to judge what the absolute worst case scenario would have been if they’d invested in something. Ok, so this is a little bit technical but it’s an intriguing puzzle that got me thinking quite hard.