Narrow Range 7 (NR7) Google Sheets Formula

Last Updated on 30/03/2023 by Ndanileka

INTRODUCTION

The idea of narrow range patterns can be traced back to Tony Crabel’s book, “Day Trading with Short Term Price Patterns & Opening Range Breakout” Despite being published in 1990, many of its strategies maintain their relevance and effectiveness in today’s trading landscape.

In a previous post, we covered the NR4 pattern, which is closely related to the NR7 pattern discussed here. It’s essential to review that post before diving into this one, as this article focuses solely on implementing the NR7. Also, keep in mind that the previous post includes an extra step on how to combine the NR4 or NR7 with the Inside Day pattern.

WHAT IS THE NR7?

The NR7, or Narrow Range 7, is a technical chart pattern used by traders to identify potential breakouts in the market. It occurs when the trading range (the difference between the high and low prices) of a stock is the narrowest it has been in the last seven days, including the current day. The NR7 pattern represents a period of consolidation and reduced volatility, which often precedes significant price movements as the market’s pent-up energy is eventually released. Traders can use the NR7 pattern as a signal to enter long or short positions, anticipating a breakout or trend reversal. However, it’s crucial to employ proper risk management techniques to protect against potential losses.

Bear in mind that for the NR7 pattern, the 7th candlestick doesn’t have to fall within the ranges of the preceding days unless you’re combining it with the Inside Day pattern, creating an “ID-NR7”

Here’s a step-by-step process to identify the NR7 pattern using historical price data in Google sheets.
In this example, Cell B1 = “AMD” (Advanced Micro Devices, Inc)

Step 1.1. Request historical prices from the previous 6 trading days.

``=QUERY(GOOGLEFINANCE (B1,"all",TODAY()-12,TODAY()),"ORDER BY Col1 DESC LIMIT 6")``

Step 1.2.  Calculate the trading range for each day by subtracting the low (Col4) from the high (Col3).

``=QUERY( GOOGLEFINANCE (B1,"all", TODAY()-12, TODAY()),"SELECT (Col3-Col4) ORDER BY Col1 DESC LIMIT 6")``

Step 1.3. Return the MIN range from step 2.1.
Wrap the query from step 2.1 with the MIN() function to return the Min Range.

``=MIN(QUERY(GOOGLEFINANCE(B1,"all",TODAY()-12,TODAY()),"SELECT (Col3-Col4) ORDER BY Col1 DESC LIMIT 6"))``

Step 1.4. Request today’s range by subtracting today’s low from today’s high.

``=GOOGLEFINANCE (B1,"HIGH")-GOOGLEFINANCE (B1,"LOW")``

Step 2. Complete the NR7 formula

Merge the formulas from steps 1.3 and 1.4 into a single cell using the IF() function to create a logical expression.
“If today’s range is smaller than the smallest range from the previous 6 days, return: “NR7”

``=IF((GOOGLEFINANCE (B1,"HIGH")-GOOGLEFINANCE (B1,"LOW"))<MIN(QUERY( GOOGLEFINANCE (B1,"all", today() - 12, today()),"SELECT (Col3-Col4) ORDER BY Col1 DESC LIMIT 6")),"NR7"," ")``