52-Week Range Google Sheets Formula

Last Updated on 17/04/2023 by Ndanileka

WHAT IS THE 52-WEEK RANGE

The 52-week range is a measure of a stock’s price range over a one-year period. It represents the highest and lowest prices that a stock has traded at over the previous 52 weeks (or one year). The range is useful for investors and traders as it provides an idea of the volatility and performance of the stock over a longer time frame. It is also used as a reference point for determining whether a stock is currently trading at a relatively high or low price.


52-WEEK RANGE CALCULATION

The 52-week range of a stock is determined by calculating the difference between its highest and lowest prices within a given 52-week period.

52-Week Range = (52-Week High) – (52-Week Low)

This figure provides a useful indicator of the stock’s general volatility, with the highest and lowest prices serving as upper and lower bounds that are likely to have a significant impact on its current price.


CURRENT PRICE RELATIVE TO 52-WEEK RANGE

To use the 52-week range as a performance metric, we must first normalize it.
This means we need to consider where the current price of the stock falls within its 52-week range and compare it to other stocks’ current prices within their respective ranges.

To achieve this, we can use the Current Price Relative to 52-Week Range formula,
Which involves subtracting the 52-week low from the current price and dividing it by the difference between the 52-week high and the 52-week low.
We then multiply the result by 100 to obtain a percentage.

Current Price Relative to 52-Week Range =
((Todays Price – 52 Week Low ) / ( 52 Week High – 52 Week Low) ) x 100

Example

Suppose over the last year a stock reached a high of 151.44 and a low of 121.77 and is currently trading at 137.03.
The Current Price Relative to 52-Week Range as;
((137.03 – 121.77) / (151.44 – 137.03)) * 100 = 51.43%

52 Week Range
52 Week Range Calculation

This means the current price is 51.43% above it’s 52 Week Low or 48.57% ( 100% – 51.43%) below its 52 Week High.



CURRENT PRICE RELATIVE TO 52-WEEK RANGE GOOGLE SHEETS FORMULA

Formula

=(GOOGLEFINANCE(“Ticker” ) – GOOGLEFINANCE(“Ticker”,”low52″)) / (GOOGLEFINANCE(“Ticker”, “High52”) – GOOGLEFINANCE(B1, “Low52”))

Tutorial

Cell B1 = “JSE: VOD” (Vodacom Group Ltd)

Step 1. Request 3 Price Points (52-Week High, 52-Week Low, Today’s Price)

Step 1.1. Request 52-Week High

=GOOGLEFINANCE(B1, "high52")
52 Week High Formula
52-Week High, Google Sheets Formula

Step 1.2. Request 52-Week Low

=GOOGLEFINANCE(B1, "low52")
52 Week Low Formula
52-Week Low, Google Sheets Formula

Step 1.3. Request Todays Price

GOOGLEFINANCE(B1)
Todays Price Formula
Todays Price, Google Sheets Formula

Step 2. Determining Price Differences between High, Low, and the Current Price.

Step 2.1. Subtract the 52-Week Low from the Current price.

Todays Price – 52 Week Low = Price Difference (1)

Todays Price - 52 Week Low
Todays Price – 52 Week Low = Price Difference(1)

Step 2.2. Calculate the 52-Week Range.

52 Week High – 52 Week Low = Price Difference (2)

52 Week High - 52 Week High
52 Week High – 52 Week Low = Price Difference (2)

Step 3. Complete the Current Price Relative to 52-Week Range Formula.

Price Difference (1) / Price Difference (2) = Current Price Relative to 52-Week Range Formula

“Format as percent” or multiply by 100 to convert the return from decimal to percentage.

=(GOOGLEFINANCE(B1)-GOOGLEFINANCE(B1,"low52"))/(GOOGLEFINANCE(B1, "high52") - GOOGLEFINANCE(B1, "low52"))
Current Price Relative to 52-Week Range Formula
Price Difference (1) / Price Difference (2) = Current Price Relative to 52-Week Range Formula

Complete

View and copy the spreadsheet on the link below and tailor the data to your needs.

Google Sheets, 52 Week Range Watchlist

Leave a Reply