Stock Price Daily Range Google Sheets Formula

What Is A Range ?
Trading range illustrated with candle stick chart

Trading ranges are characterized by the tendency of stocks to keep their movement bound within specific price points of a high (resistance) and a low (support) at varying periods.
Ranges are often a good indicator of market volatility as volatility is an outcome of price action.

The one that is directly proportional to volatility is range.
Range can be defined as the distance the price moves per increment of time.

J. Welles Wilder Jr. (June 11, 1935 – April 18, 2021)
New Concepts In Technical Trading Systems, 1978

The Daily Range
Daily Range Calculation In Google Sheets

STEP 1
Request 2 price points: Today’s high and Today’s Low

=GOOGLEFINANCE(B1,"high")
The formula fetches the Today’s High [Captured on cell B7]
=GOOGLEFINANCE(B1,"low")
The formula fetches the Today’s Low [Captured on cell C7]

STEP 2
Calculate the Daily Range

=GOOGLEFINANCE(B1,"high")-GOOGLEFINANCE(B1,"low")
The formula calculates the Daily Range [Captured on cell B10]

Complete.



Daily True Range
Distance between Today's High  & Today's Low
Distance between Today’s High & Today’s Low
Distance between Yesterday's Close  & Today's  High
Distance between Yesterday’s Close & Today’s High
Distance between Yesterday's Close & Today's Low
Distance between Yesterday’s Close & Today’s Low

The True Range is defined as the maximum range that the price moved , either during the day or from yesterdays close to the extreme point reached during the day.
Suppose we have a stock with the following price points:
Yesterday’s Close = 70, Today’s High = 85, Todays Low = 75

Method 1 – Manual Calculation

Since we measuring the magnitude of the range and not the direction, we use the ABS() function to return positive values.

(D1) = Absolute Value of (Today’s High – Today’s Low)

= ABS (85 – 75)
= 10

(D2) = Absolute Value of (Today’s High – Yesterday’s Close )
= ABS (85 – 70)
= 15

(D3) = Absolute Value of (Yesterday’s Close – Today’s Low)
= ABS (70 -75)
= 5

True Range = MAX(D1, D2, D3)
= MAX(10, 15, 5)
= 15

Method 2 – Manual Calculation

Find the price difference between the extreme (Max & Min) values from the 3 price points.

True Range = MAX( Today’s High, Yesterday’s Close ) – MIN( Today’s Low, Yesterday’s Close )
= Max(85, 70) – Min(75, 70)
= 85 – 70
= 15

Note how for the MAX value, we only input “Today’s High” and “Yesterdays Close” and for the MIN value, we only input ” Today’s Low” and ” Yesterday’s Close”.
This is done to minimize the number of inputs required to calculate the True Range;
by using simple logic, we can conclude that “Today’s Low” cannot be higher than “Today’s High” therefore, there isn’t a need to include the price point when calculating for MAX value. The opposite will apply when calculating the MIN value; there isn’t need to include “Todays High”.

Daily True Range Calculation In Google Sheet
=MAX(GOOGLEFINANCE(B1,"HIGH"),GOOGLEFINANCE(B1,"CLOSEYEST"))-MIN(GOOGLEFINANCE(B1,"LOW"),GOOGLEFINANCE(B1,"CLOSEYEST"))

STEP 1
Request 3 price points: Today’s high, Today’s Low and Yesterdays Close
In our example, Cell B1 = “AMD”

STEP 1.1
Request Today’s High

=GOOGLEFINANCE(B1,"high")
The formula fetches the Today's High [Captured on cell B9]
The formula fetches the Today’s High [Captured on cell B9]

STEP 1.2
Request Today’s Low

=GOOGLEFINANCE(B1,"low")
The formula fetches the Today's Low [Captured on cell C9]

The formula fetches the Today’s Low [Captured on cell C9]

STEP 1.3
Request Yesterday’s Close

=GOOGLEFINANCE(B1,"closeyest")
The formula fetches the Yesterday's Close [Captured on cell D9]
The formula fetches the Yesterday’s Close [Captured on cell D9]

STEP 2
Find the max and min values from the 3 price points collected in step 1

STEP 2.1
Find the max value between Today’s High and Yesterday’s close.

=MAX(GOOGLEFINANCE(B1,"HIGH"),GOOGLEFINANCE(B1,"CLOSEYEST"))
The formula returns the MAX value between Today's High & Yesterday's Close
The formula returns the MAX value between Today’s High & Yesterday’s Close

STEP 2.2
Find the min value between Today’s Low and Yesterday’s Close.

=MIN(GOOGLEFINANCE(B1,"LOW"),GOOGLEFINANCE(B1,"CLOSEYEST"))
The formula returns the MIN value between Today's Low & Yesterday's Close
The formula returns the MIN value between Today’s Low & Yesterday’s Close

STEP 3
Calculate the True Range

=MAX(GOOGLEFINANCE(B1,"HIGH"),GOOGLEFINANCE(B1,"CLOSEYEST"))-MIN(GOOGLEFINANCE(B1,"LOW"),GOOGLEFINANCE(B1,"CLOSEYEST"))
The formula calculates the Daily True Range
The formula calculates the Daily True Range

Complete.


2 comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.