Last Updated on 29/08/2024 by Ndanileka

## What is a Current Streak

The concept of a “streak” is a well-known and celebrated metric in sports, where it often reflects an athlete’s or team’s current form and momentum. But what if we apply this idea to the financial markets, specifically to stock prices? What insights can we uncover?

When we talk about a stock price streak, we’re referring to consecutive days where a stock’s price has moved in the same direction, either consistently rising or falling.

But what can these streaks really tell us?

Do they signal a potential trend reversal, continuation, or simply reflect random market noise?

How long do these streaks typically last, and what should we consider “normal” in the context of different market environments?

Moreover, can these streaks offer us practical insights that could enhance our trading strategies?

In this two-part series, we’ll take a systematic approach to answering these questions. In the first part, we’ll define a method to calculate stock price streaks and automate this process with Google Apps Script to monitor a watchlist.

The second part will focus on analyzing historical data to identify key statistics and trends, providing insights that could help refine our trading approach.

### Calculation Method

Suppose we have stock with the following prices:

We can calculate historical streaks by applying these formulas in the adjacent columns.

**Column C (Binary Sequence) – **

=IF(B2 < B3, 1, -1)

This formula compares the closing price of the previous day (B2) with the current day (B3). If yesterday’s closing price is less than today’s, it returns 1 (indicating a gain). If yesterday’s closing price is greater, it returns -1 (indicating a loss).

**Column D (Streak Count) **

=IF(C3 = C2, D2 + 1, 1)

This formula checks whether today’s binary value (C3) is the same as yesterday’s (C2). If it is, it adds 1 to the previous streak count (extending the streak); otherwise, it resets the streak to 1. This counts the consecutive days of gains (1s) or losses (-1s).

**Column E (Streak Direction)**

=IF(C3 = 1, D3* 1, D3 * -1)

This formula applies a sign to the streak based on whether the streak is positive or negative. If the current day’s binary value is -1 (negative streak), it multiplies the streak by -1, indicating a negative streak. If the current day’s binary value is 1 (positive streak), it leaves the streak count as positive by multiplying D4 by 1.

### Current Steak Custom Function

=CURRENTSTREAK_GOOGLEFINANCE()

#### Data Preparation

The function requires the date and close price columns without the header row.

Detailed steps on removing the header row can be found in the EMA function tutorial. I will only provide the formula here.

Keep in mind that the length of the historical data request will impact the available streak count. For example, in my case, a 17-day data request resulted in 14 days of usable data for calculating the historical streaks.

`=QUERY(QUERY(GOOGLEFINANCE("V","CLOSE",TODAY()-17,TODAY()),"SELECT *",1),"SELECT* OFFSET 1",0)`

#### Function Parameters

The function takes a single parameter which is an array of Date and Close Prices with the header row removed

#### Apps Scripts Code

```
/**
* Returns the historical streaks of consecutive positive or negative close prices from a given range.
* The range should contain date and close price data, without a header row.
*
* Googlefinance range example:
* QUERY(QUERY(GOOGLEFINANCE(B1, "close", TODAY()-200, TODAY()), "SELECT *", 1), "SELECT * OFFSET 1", 0)
*
* @param {array} range An array containing date and close price data, where the first column represents dates and the second column represents close prices.
* @return {array} An array with date-streak values with the sign corresponding to consecutive positive or negative streaks.
*
* @customfunction
*/
function CURRENTSTREAK_GOOGLEFINANCE(range) {
const dates = range.map(row => new Date(row[0]));
const closePrices = range.map(row => parseFloat(row[1]));
// Initialize arrays for streak count and current streak
let streakCount = [];
let currentStreak = [];
// Calculate the binary sequence with 1 and -1
let binarySeq = [];
for (let i = 1; i < closePrices.length; i++) {
binarySeq.push(closePrices[i] > closePrices[i-1] ? 1 : -1);
}
// Count the streak and preserve the sign of the streak
streakCount.push(1 * binarySeq[0]);
currentStreak.push([dates[1], 1 * binarySeq[0]]); // Add first date-streak pair
for (var i = 1; i < binarySeq.length; i++) {
if (binarySeq[i] === binarySeq[i-1]) {
streakCount.push(streakCount[i-1] + (binarySeq[i] > 0 ? 1 : -1));
} else {
streakCount.push(binarySeq[i]);
}
currentStreak.push([dates[i+1], streakCount[i]]); // Add date-streak pair
}
// Return the array of date-streak pairs
return currentStreak;
}
```

## Current Streak Watchlist

Our default watchlist includes stocks listed on the S&P 100, with the Streak column (Column E) indicating the number of consecutive days a stock has either risen or fallen.

Happy Trading

### Discover more from DEUS HARVEST

Subscribe to get the latest posts sent to your email.