Stock Price Daily Range Google Sheets Formula

Stock Price Daily Range Google Sheets Formula

Last Updated on 03/09/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

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”.

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

Daily True Range Custom Function

=TR_GF()

/**
 * Returns the True Range for the given range of date, high, low, and close prices.
 * Example usage: QUERY(QUERY(GOOGLEFINANCE(B1,"all",TODAY()-200,TODAY()), "SELECT Col1, Col3, Col4, Col5", 1), "SELECT * OFFSET 1", 0)
 * In the inner query: Col1 is Date, Col3 is High, Col4 is Low, Col5 is Close.
 * 
 * @param {array} range A range containing date, high, low, and close price data from Google Finance.
 * @return {array} An array of arrays with date and true range values.
 * @customfunction
 */

function TR_GF(range) {
  // Extract columns from the range
  const dates = range.map(row => new Date(row[0]));
  const highs = range.map(row => parseFloat(row[1]));
  const lows = range.map(row => parseFloat(row[2]));
  const closes = range.map(row => parseFloat(row[3]));

  // Calculate True Range values
  let trueRanges = [];
  for (let i = 1; i < highs.length; i++) {
    let tr = Math.max(highs[i], closes[i - 1]) - Math.min(lows[i], closes[i - 1]);
    trueRanges.push([dates[i], tr]);
  }

  return trueRanges;
}

Function Parameters

True Range function parameters
Function Parameters

range:

The function takes a single parameter, an array consisting of the Date and corresponding values for the High, Low & Close Values.

Ensure that the array does not include the header row.

Example: To prepare your data, you can use a Google Sheets query function to remove the header row from data requested from GOOGLEFINANCE().
For instance, in this example, Col1 is Date, Col3 is High, Col4 is Low, Col5 is Close.

QUERY(QUERY(GOOGLEFINANCE(B1,"all",TODAY()-200,TODAY()), "SELECT Col1,Col3,Col4,Col5", 1),"SELECT* OFFSET 1",0)
Historical Price Request with header rows removed
Historical Price Request ( Date, High, Low & Close) without the header row

Spreadsheet

True Range Spreadsheet
True Range Column Chart

Happy Trading!


Discover more from DEUS HARVEST

Subscribe to get the latest posts sent to your email.

Leave a Reply

Back To Top