Last Updated on 03/09/2024 by Ndanileka

What is a Price 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. 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 Google Sheets Formula (Tutorial)

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

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

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

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

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)

Spreadsheet

Happy Trading!