With the stocks markets tanking earlier this month, I thought it’d be interesting to track their historical performance and put into perspective just how badly things have been going lately. For those that don’t know, one of my side jobs is writing articles for the Motley Fool Canada and so naturally this example attracted my interest.

However, there’s not an easy way to calculate this in Excel, and so I decided to go the route of a custom function.

What I’m going to be looking to accomplish is a way to to track how many consecutive trading days that a stock has been up or down, and then also calculate the cumulative value of those gains and losses.

If you’d like to follow along with my example, you can download the file I used here (you’ll have to save the file, open it in Excel and enable content, otherwise you’ll see NAME? errors)

**Setting Up the Variables**

I want the calculation to start from the bottom (the current cell) and work its way back up, since the latest results will be at the bottom. To do this I create a ‘bottom’ variable that looks like this:

————————————————————————————————————–

bottom = selection.Count + selection.Row – 1

————————————————————————————————————–

I want the user to be able to select what range they want the calculation to apply to, rather than selecting everything.

I also setup a variable for the column, which I named as offsetnum:

————————————————————————————————————–

offsetnum = selection.Column

————————————————————————————————————–

These two variables allow me to set my starting point for my calculation.

**Determining if I’m Counting Negatives or Positives**

The value of the starting cell will determine if I am going to be looking for positive numbers (gains) or negatives (losses), and so I setup an if statement to determine whether the first value is a gain or loss:

————————————————————————————————————–

If Cells(bottom, offsetnum) < 0 Then

posneg = “negative”

Else

posneg = “positive”

End If

————————————————————————————————————–

**Start counting**

The final step involves counting the values depending on whether I’m looking for positives or negatives:

————————————————————————————————————–

For counter = bottom To 1 Step -1

If posneg = “negative” Then

If Cells(counter, offsetnum) < 0 Then

streak = streak – 1

Else

Exit For

End If

Else

If Cells(counter, offsetnum) >= 0 Then

streak = streak + 1

Else

Exit For

End If

End If

Next counter

————————————————————————————————————–

My complete function looks as follows:

————————————————————————————————————–

Function streak(selection As Range)

Application.Volatile

Application.Calculate

Dim bottom, offsetnum As Integer

Dim posneg As String

bottom = selection.Count + selection.Row – 1

offsetnum = selection.Column

‘Determine first value

If Cells(bottom, offsetnum) < 0 Then

posneg = “negative”

Else

posneg = “positive”

End If

For counter = bottom To 1 Step -1

If posneg = “negative” Then

If Cells(counter, offsetnum) < 0 Then

streak = streak – 1

Else

Exit For

End If

Else

If Cells(counter, offsetnum) >= 0 Then

streak = streak + 1

Else

Exit For

End If

End If

Next counter

End Function

————————————————————————————————————–

**Calculating consecutive points gains and losses**

Now that I have a function to tell me the current winning or losing streak, I can calculate the cumulative gains and losses.

To do this, I am going to sum as far as the streak goes. And so far starters, I’m going to start with the

**SUM** function. I am also going to use the

**OFFSET **function because I need to determine how many rows up I need to add. The OFFSET will start from the current position and determine how far back I need to go to add up the totals in the current streak.

However, because some streaks are negative, I’ll need to also use the **ABS **function to just grab the number, regardless of if it is positive or negative. My formula looks like this so far:

=SUM(OFFSET(H2,1-ABS(I2), 0

Column H is where my gain or loss value is, while column I is the streak value. Since I want to sum the cumulative gains, I need to reference column H as my starting point.

I added the 1- before the ABS function because that will ensure the number is a negative, meaning that my formula will calculate upward, rather than downward if the number were positive. I also have to decrease the number of cells to offset because I don’t want to include the current cell, otherwise the formula will go too far.

Since I’m not offsetting any columns I set the next argument to 0.

The last argument I need to enter is the height of the offset function, otherwise the formula will just offset by the number specified in the second argument and pull that value, rather than pulling all the values that fall within the range.

This actually involves just copying the same argument again, but this time for the height. My completed formula looks as follows:

=SUM(OFFSET(H2,1-ABS(I2),0,ABS(I2)))

**Note**

Sometimes with custom functions you might notice that your calculations hang or stop computing correctly. What that means is you just need to recalculate using either F9 or you can edit in the cell and click enter, which will normally trigger a recalculation as well.

Alternatively what you could do is after running the formulas for the dataset, copy them over as values to ensure that they don’t change, since in this case you likely wouldn’t need to recalculate the streak value again.