Find longest consecutive winning streaks

[trey1982]

My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%

 

[Gary''s Student]

In C1 enter 1
In C2 enter:
=IF(B2>0,C1+1,0) and copy down. We see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2

Look for the max in column C (12) to find the end of the longest streak,
then look for the next lower value followed by a zero to find the end of the
next longest streak, etc.

 

[trey1982]

How can I create a formula that looks for the next lower value followed by a
zero to find the end of the next longest streak?

thanks...trey

 

[Don Guillett]

From a post of mine about a month ago. Modify to suit

Try this where text in col a and numbers in col 2-11
Sub findlongestzeros()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
mc = 0
For j = 2 To 11
If Cells(i, j) = 0 And Cells(i, j + 1) = 0 Then mc = mc + 1
Next j
'MsgBox mc
If mc > mss Then
mss = mc
mr = i
End If
Next i
MsgBox "Max is Row " & mr
'MsgBox mss
End Sub

 

[Gary''s Student]

We know a streak has ended if we have a positive value followed by a zero.
So in D1 enter:
=IF(AND((C1>0),(C2=0)),C1,"") and copy down. We now see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2 2

So now the 12 is still the highest, 7 is the next highest, etc. This method
will also display ties.

 

[T. Valko]

In C1 enter 1

That works on the posted sample but if the first entry was negative...

Try this instead:

=--(B1>0)

 

[T. Valko]

Using a single array formula** entered in, say, E1:

=LARGE(FREQUENCY(IF(B$1:B$46>0,ROW(B$1:B$46)),IF(B$1:B$46<=0,ROW(B$1:B$46))),ROWS(E$1:E1))

Copy down until you get either 0s or #NUM! errors.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[trey1982]

to the final part of my question. how does one determine the return for each
of these "winning" periods. [(1+x)*(1+x2)*(1+x3) - 1] i understand. i can
even figure out a way on how to get the first "winning period" return, but am
stumped on how to figure out a way to the return for each of these "winning"
periods.

....trey