Stock Price Daily Range Google Sheets Formula

Last Updated on 08/04/2024 by Ndanileka

WHAT IS A RANGE?

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 of time. 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

A daily range refers to the difference between the highest and lowest prices of a financial asset during a single trading day.

DAILY RANGE GOOGLE SHEETS FORMULA

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

DAILY TRUE RANGE

The daily true range (DTR) is a measure of volatility 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.

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.

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

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

=GOOGLEFINANCE(B1,"closeyest")

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

Step 2.2 Return 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"))

---