NR7 Google Sheets Formula


Narrow range patterns come from Tony Crabel’s book, Day Trading with Short Term Price Patterns & Opening Range Breakout. Even though the book, which was published in 1990, is currently out of print, many of its ideas are still effective.

We discussed the NR4 pattern which is a sister pattern to this in a previous post. Make sure to have a look before this one, as here I will only explain how to make use of the NR7. Keep in mind that in this previous post there is an additional step on how to fuse the NR4 or NR7 with the Inside Day pattern.


To refresh your memory, the Narrow Range 7 pattern is the identification of an occurrence whereby a candlestick has the narrowest range between high and low out of the previous seven candlesticks. I personally believe this pattern is best used on a daily timeframe.

Keep in mind it is not necessary for the 7th candlestick to be within the ranges of the previous days unless you are using it in conjunction with the Inside Day pattern thus making it an
“ID-NR7” similar to the previous post linked to in the first paragraph.



You can easily do this by measuring the high’s and lows of the previous days each time you analyze a stock chart by hand, and isolating the stocks where the pattern is occurring alongside other indicators you use. However doing this for a large chart-set will quickly become tedious and slow down your workflow.


Referencing the the Inside Day and ID-NR4 articles can and might give you some deeper insights not expressed overtly by us.


Request historical data from the previous 6 trading days and sort in descending order.

In this example we will use AECI Limited the Google Finance ticker for this share is “JSE:ARL”, referenced on cell A1.

=QUERY(GOOGLEFINANCE (A1,"all",today()-12,today()),"ORDER BY Col1 DESC LIMIT 6")
This should be the result after correctly entering the code.


2.1) Now we request price ranges from the previous 6 trading days.
( High – Low) = Price range

=QUERY(GOOGLEFINANCE (A1,"all", today()-12, today()),"SELECT (Col3-Col4) ORDER BY Col1 DESC LIMIT 6")
Again, here is the result you should have after writing the code.

2.2) Now you have to wrap the query from step 2.1 in a MIN() function to return the smallest range from the previous 6 days we just requested in step 2.1.

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

2.3) Then afterwards, request todays range using the formula below.


The result will be displayed like so:

Here’s your result


We then combine step 2.2 and 2.3‘s formula into a single cell with the below logic statement.
If todays range is smaller than the smallest range from the previous 6 days, return : “NR7”

=IF((GOOGLEFINANCE (A1,"HIGH")-GOOGLEFINANCE(A1,"LOW"))<MIN(QUERY(GOOGLEFINANCE (A1,"all", today()-12, today()),"SELECT (Col3-Col4) ORDER BY Col1 DESC LIMIT 6")),"NR7"," ")
The final result of your coding work.
JSE:ARL 13/08/2021 NR7


Now you should, like in the last image have a cell that tells you when you have a Narrowest range of the previous seven days on that given stock as soon as the 7th day of trading closes. Now you may await a sufficient trading opportunity for you to exploit.

You can now repeat this process for other charts you desire to have a look at.

A simpler way would be to copy the code you’ve already done and modify it only to change the stock tickers and replicate it all the way down the sheet. Alternatively you could do separate sheets or whichever format best appeals to how you’ve organized your setup.

Good Luck!

Leave a Reply