Stock Price Daily Range Google Sheets Formula

What Is A Range ?

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

STEP 2
Calculate the Daily Range

=GOOGLEFINANCE(B1,"high")-GOOGLEFINANCE(B1,"low")

Complete.

---

---

Daily True Range

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.

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.3
Request Yesterday’s Close

=GOOGLEFINANCE(B1,"closeyest")

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"))

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

=MIN(GOOGLEFINANCE(B1,"LOW"),GOOGLEFINANCE(B1,"CLOSEYEST"))

STEP 3
Calculate the True Range

=MAX(GOOGLEFINANCE(B1,"HIGH"),GOOGLEFINANCE(B1,"CLOSEYEST"))-MIN(GOOGLEFINANCE(B1,"LOW"),GOOGLEFINANCE(B1,"CLOSEYEST"))

Complete.

---