The macro is not flexible enough to accommodate the date input

**BNCOXUK** · 03-24-2013, 06:31 PM

Dear all,

i just found a nice macro to calculate the maximum drawdown of a stock:

http://investexcel.net/2450/maximum-drawdown-vba/

But the macro failed to accommodate the date input to make it more flexible. For example, if I want to use the macro to calculate the maximum drawdown between 01-Dec-2008 and 31-OCT-2009, then the formula failed with #VALUE:

=MaxDrawDown(IF((A6:A100>=DATEVALUE("01-DEC-2008"))*(A6:A100<=DATEVALUE("31-OCT-2009")),B6:B100,""))

Can I ask how to adapt the macro to allow for flexible date input?

Original macro code:

Function MaxDrawDown(returns As Variant) As Variant
Dim TS As Variant
Dim n As Integer
Dim Min As Double
TS = returns
n = UBound(TS)
Min = 0
For i = 1 To n
For j = i To n
temp = TS(j, 1) / TS(i, 1) - 1
If temp < Min Then
Min = temp
End If
Next
Next
MaxDrawDown = Min
End Function

**Osvaldo Palmeiro** · 03-24-2013, 10:25 PM

Hello.
You could try using a named range.

ex.

H4 = start date
H5 = end date

names manager > new > name: MyPeriod

into the 'refers to' box put
=OFFSET($A$6,MATCH($H$4,$A$6:$A$761,0)-1,1,MATCH($H$5,$A$6:$A$761,0)+1-MATCH($H$4,$A$6:$A$761,0),1)

then use
=MaxDrawDown(MyPeriod)

**BNCOXUK** · 03-25-2013, 12:12 AM

Thanks, Osvaldo. This worked nicely.

**Richard Buttrey** · 03-25-2013, 06:20 AM

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.

This is somewhat academic since it has now been answered, but you should not have posted the same question twice. With 173 posts you really should know that. Furthermore you have broken a second forum rule by not wrapping code in CODE tags. Again you've been here long enough to know that too.

Please note for the future. Had you been a newcomer I would have let it go at this but in view of your posting record there is an infraction on the way.

The macro is not flexible enough to accommodate the date input

LinkBack

Thread Tools

Display

The macro is not flexible enough to accommodate the date input

Re: The macro is not flexible enough to accommodate the date input

Re: The macro is not flexible enough to accommodate the date input

Re: The macro is not flexible enough to accommodate the date input

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions