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.
J. Welles Wilder Jr. (June 11, 1935 – April 18, 2021)
Range can be defined as the distance the price moves per increment of time.
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
=GOOGLEFINANCE(B1,"high")
=GOOGLEFINANCE(B1,"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.
(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")
Step 1.2
Request Today’s Low
=GOOGLEFINANCE(B1,"low")
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!
Discover more from DEUS HARVEST
Subscribe to get the latest posts sent to your email.