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.

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