Solved: counting number of observations under condition

[Anomandaris]

I'm trying to get 3 formulae for calculating days in drawdown…
Column A has daily dates and Column B has drawdowns which are either a negative number or 0(which means there is no drawdown)…

Every drawdown period starts with a negative number, and as long as the numbers continue negative the streak continues until it is stopped with a zero.

1. Need - Longest period in drawdown….this should basically be the most number of consecutive negative values in Column B (before the streak being interupted with a 0)

2. Days in Current drawdown - this is the most recent streak of negative numbers, in our case the answer is 10 as shown in column F. (hopefully formula detects the date and figures this out)

3, Days in Maximum drawdown - this is the negative streak which had the lowest value…….in our case I believe its about 292.

I counted this manually which can be a headache when you have a long list of numbers…….would greatly appreciate it if someone can help with this

thanks

I dont think i need a macro for this just excel formula

[tpoynton]

I know you prefer formula, but below I'll put some functions that seem to work. There probably is a formula, and I'd be interested in seeing it too! I used functions here because they act like a formula, but a sub would probably be easier if you need to go the VBA route.

FYI, the maximum drawdown for the sample data you provided is 341.

[vba]
Public Function MaxDD(ddRange As Range)
Dim rCell As Range
Dim ddCount As Long
Dim ddMax As Long
For Each rCell In ddRange
If rCell < 0 Then
ddCount = ddCount + 1
Else
If ddCount > ddMax Then
ddMax = ddCount
End If
ddCount = 0
End If
Next rCell
MaxDD = ddMax
End Function
---
Public Function CurrentDD(CurDDRange As Range)
Dim rCell As Range
Dim ddCount As Long
For Each rCell In CurDDRange
If rCell.Value < 0 Then
ddCount = ddCount + 1
Else
CurrentDD = ddCount
Exit For
End If
Next rCell
End Function
[/vba]

[Anomandaris]

thanks tp, thats brilliant, 341 is not the Max DD though, MAx DD is 291 (this is the negative streak which had the lowest recorded value of 35.69%), Longest DD period is 341 which is the most consecutive negative numbers.

there is a formula though, here it is if you're interested in taking a look



First define the following...

Name: Range

Refers to:

=INDEX($B:$B,2):$B$1356

Click Ok

Then, try...

Longest period in DD =MAX(FREQUENCY(IF(Range<>0,ROW(Range)),IF(Range=0,ROW(Range))))

Curr DD =COUNTIF(INDEX($B:$B,2):INDEX(Range,MATCH(0,Range,0)),"<0")

Max DD =LOOKUP(BigNum,CHOOSE({1,2},MAX(ROW(Range))+1,SMALL(IF(Range=0,IF(ROW(Range )-MIN(ROW(Range))+1>MATCH(F2,Range,0),ROW(Range))),1)))-LOOKUP(BigNum,CHOOSE({1,2},MIN(ROW(Range))-1,LARGE(IF(Range=0,IF(ROW(Range)-MIN(ROW(Range))+1<MATCH(F2,Range,0),ROW(Range))),1)))-1

2 of them are array formulas so ctrl+shift+enter them






thanks for the VBA, do you know what it would look like for Max DD?
I'll mark this solved!

[p45cal]

In the absence of other replies, (edit:and there were none when I looked 25 mins. ago!) while you probably can do this without macros I wouldn't relish creating or amending 2 or 3 of them.
The max drawdown, is easy, you've used
=SMALL($B$2:B1560,1)
but this seems to work too:
=MIN(B2:B1356)

For days in current DD you could try:

=IF(B2<0,MIN(IF(B2:B1356>=0,ROW(B2:B1356)))-2,"Not currently in drawdown")

but this is ARRAY ENTERED, and the -2 in the formula means it expects the first value to be in row 2.

However I have done a macro which is a function you can use on a worksheet. I hesitated to suggest it because I was just exploring different ways of doing things for myself.
This is how the formulae would look on a worksheet:

=mystats($B$2:$B$1356,"maxdd")
=mystats($B$2:$B$1356,"daysinmaxdd")
=mystats($B$2:$B$1356,"daysincurrentdd")
=mystats($B$2:$B$1356,"longestddperiod")
=mystats(B13:B1367,"xxxx")

(I've included the last one to show what happens when you enter the 2nd argument wrongly - it shows the valid possibilities with a message:

Incorrect 2nd argument? Should be one of: MAXDD, DAYSINMAXDD, DAYSINCURRENTDD, LONGESTDDPERIOD

In fact you can include spaces and/or vary the case. eg:
=mystats($B$2:$B$1356,"Days in Current DD")
will work.

Finally, here's the function
[vba]Function MyStats(therange, whichStat)
MAXDD = Application.Min(therange.Value)
LongestDDLength = 0
MaxDDLength = 0
LatestDDLength = 0
CurrentDDPeriodIsMaxDDPeriod = False
IsLatestDDPeriod = True
For Each cll In therange
If cll.Value < 0 Then
If cll.Value = MAXDD Then CurrentDDPeriodIsMaxDDPeriod = True
ThisPeriod = ThisPeriod + 1
If LongestDDLength < ThisPeriod Then LongestDDLength = ThisPeriod
If IsLatestDDPeriod Then LatestDDLength = ThisPeriod
Else
If CurrentDDPeriodIsMaxDDPeriod Then MaxDDLength = ThisPeriod
ThisPeriod = 0
CurrentDDPeriodIsMaxDDPeriod = False
IsLatestDDPeriod = False
End If
Next cll
Select Case UCase(Replace(whichStat, " ", ""))
Case "MAXDD": MyStats = MAXDD
Case "DAYSINMAXDD": MyStats = MaxDDLength
Case "DAYSINCURRENTDD": If therange.Cells(1).Value < 0 Then MyStats = LatestDDLength Else MyStats = "Not currently in drawdown"
Case "LONGESTDDPERIOD": MyStats = LongestDDLength
Case Else: MyStats = "Incorrect 2nd argument? Should be one of: MAXDD, DAYSINMAXDD, DAYSINCURRENTDD, LONGESTDDPERIOD"
End Select
End Function[/vba]

[tpoynton]

Ah, I misunderstood the question and did the longest period in drawdown. p45cal has provided the VBA to do it all in a single, succinct function!

Thanks for sharing the formula - I get the gist of it; longest period in drawdown works, but the max days in DD formula results in a name error - but I get the general idea...and have to say that it's just easier for me to wrap my head around the VBA!

[Anomandaris]

p45cal that was quite cool! All in one...
thanks both of you tpoynton and p45cal, keep up the great work