ID-NR4 Google Sheets Formula

The ID-NR4 pattern, short for Inside Day Narrow Range 4 is a combination of two volatility contraction patterns into one, designed to isolate incidents of increasingly narrowing market periods which may then be further analyzed as triangles or channels for example.

It is composed of two distinct patterns:

  1. Inside Days

    We go into more depth on this pattern here, it is in essence a common chart occurrence where the current day’s high and low are within the bounds of the previous day’s high and low peaks. This can be seen very easily from the Daily timeframe of any chart when it happens. Can only be called so after the current day’s trading period has ended.
Current day within the last = inside Day

2. Narrow Range 4

This is an occurrence also best seen on the daily time frame where the current candlestick has the narrowest of ranges from the past four consecutive days. Thus also helping to isolate possible ranges and patterns like triangles or similar.

However unlike the inside day, the Narrow-Range-4 can open and/or close higher or lower than the previous day. It is determined by range relative to previous days not it’s position relative to them.

Smallest range of last 4 = NR4

So these two patterns above are relatively commonly occurring and are by themselves not reliable trading signals. However once you combine the two, you create a pattern which is far more rare and increases the potential for tradability.



Request historical data from the previous 3 trading days.

In this example we will be using Massmart Holdings Limited with the Google Finance ticker of”JSE:MSM”.

=QUERY(GOOGLEFINANCE("JSE:MSM","all", today() - 10, today()),"order by Col1 desc limit 3")


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

=QUERY(GOOGLEFINANCE("JSE:MSM","all", today() - 10, today()),"Select (Col3-Col4) Order by Col1 desc limit 3")

2.2) Wrap the query from step 2.1 in a MIN() function to the return the smallest range from the previous 3 days.

=MIN(QUERY( GOOGLEFINANCE ("JSE:MSM","all", today() - 20, today()),"Select (Col3-Col4) order by Col1 Desc limit 3"))

2.3) Request todays range using the code below.


The result will be displayed like so:


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 3 days, return : “NR4”



This is for combing the NR4 formula from STEP 3 with the Inside Day formula to create ID-NR4.

We will accomplish this by wrapping the NR4 and Inside Day formulas in a AND() function.
The AND() function is used to check if both formulas are true
In essence : Is there an “inside day ? and is there a NR4 ?.

Also to note; the formulas change a bit, in order for them to work inside the AND() function.
which means:

(….,”Inside Day, ” ” ) becomes (…..,1,0) & (…., “NR4”, “”) becomes (…., 1,0)
This is because the AND() function only accepts Boolean values.

The Final Result:

=IF(AND(IF((GOOGLEFINANCE ("JSE:MSM","HIGH")-GOOGLEFINANCE ("JSE:MSM","LOW"))<(MIN(QUERY(GOOGLEFINANCE("JSE:MSM","all", today() -10, today()),"SELECT (Col3-Col4) ORDER BY Col1 DESC LIMIT 3"))),1,0),IF(AND(IF((GOOGLEFINANCE("JSE:MSM","high")<QUERY(GOOGLEFINANCE("JSE:MSM","high",today()-6,today())," select Col2 order by Col1 desc limit 1 label Col2''",1)),1,0),IF((GOOGLEFINANCE("JSE:MSM","low"))>(QUERY(GOOGLEFINANCE ("JSE:MSM","low",today()-6,today())," select Col2 order by Col1 desc limit 1 label Col2''",1)),1,0),),1,0)),"ID-NR4","")
JSE:MSM ID-NR4 01/07/2021

The above, is the entirety of the process needed to calculate and display the ID-NR4 using GoogleSheets in combination with GoogIeFinance. Below is also a link to the workbook which contains the examples as they are and a working ID-NR4 scan for the JSE ( Johannesburg Stock Exchange).

One comment

Leave a Reply