Stock Price Daily Range Google Sheets Formula

Stock Price Daily Range Google Sheets Formula

Last Updated on 24/05/2024 by Ndanileka

What is a Price Range?

Trading range illustrated with candle stick chart
Trading range illustrated with candle stick chart

When it comes to stock prices, trading ranges are a common occurrence. These ranges are defined by the tendency of stocks to remain within certain price points, typically a high (resistance) and a low (support), over a certain period. By observing these ranges, investors can gain insight into market volatility, as volatility is a result 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

Daily Range

The Daily Range (DR) refers to the difference between the highest and lowest price of an asset during a single trading day.

daily range illustrated with candle stick

Daily Range Google Sheets Formula (Tutorial)

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

=GOOGLEFINANCE(B1,"high")
Daily High Formual in google Sheets
Today’s High Request
=GOOGLEFINANCE(B1,"low")
Daily low formula in google sheets
Today’s Low Request

Step 2. Calculate the Daily Range

=GOOGLEFINANCE(B1,"high")-GOOGLEFINANCE(B1,"low")
Daily Range Formula in google Sheets
Daily Range Formula

Daily True Range

The Daily True Range (DTR) is a measure of the Daily Range that is calculated as the greatest of the following values:
The difference between the day’s high and low,
The difference between the previous day’s closing price and the current day’s high,
Or the difference between the previous day’s closing price and the current day’s low.

Distance between Today's High  & Today's Low
Difference between Today’s High & Today’s Low
Distance between Yesterday's Close  & Today's  High
Difference between Yesterday’s Close & Today’s High
Distance between Yesterday's Close & Today's Low
Difference between Yesterday’s Close & Today’s Low

Daily True Range Calculation


Suppose we have a stock with the following price points:
Yesterday’s Close = 70, Today’s High = 85, Todays Low = 75

Calculation Method 1

Since we measure 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

Calculation Method 2

Find the price difference between the extreme (Max & Min) values from the three 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 the MAX value. The opposite will apply when calculating the MIN value; there isn’t a need to include “Today’s High”.

Daily True Range Google Sheets Formula (Tutorial)

=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 Yesterday’s 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. Return the max and min values from the three price points obtained in Step 1.

Step 2.1. Return 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 Return 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

Spreadsheet

Happy Trading!

Leave a Reply

Back To Top

Discover more from DEUS HARVEST

Subscribe now to keep reading and get access to the full archive.

Continue reading