52 Week Range Google Sheets Formula

What Is The 52 Week Range

There are 52 weeks in a year, the highest price reached in a given stock, and the lowest, during this period, constitute the 52-week range. It is the price difference between the High and Low of stock in a year.

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

This figure provides a good idea of the general volatility of the stock in a given 52-week period, with the High and Low providing bounds that are likely to have a significant effect on the current price.
As individual traders, it is important to take note of these levels as everyone else is also paying attention to those same bounds. On both the individual and the institutional level.

52 Week Range Calculation

To utilize the 52-week range as a performance metric, the figure first needs to be normalized.
We do this by asking the question:

“Where is the current price within its 52-week range and how do other stock’s current prices compare within their ranges? “.

This gives us the Current Price Relative to 52-Week Range formula expressed below.

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 15’144 and a low 12’177 and is currently trading at 137’03 ZARc.

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

52 Week Range For Stock Analysis

The application of this tool will depend on your trading methodology.
For example, you may want to use it to gain perspective on your position relative to the charts for your current year of trading, you may wish to compare multiple stocks against one another, filter best and worst performers,



Calculation Of Current Price Relative To 52-Week Range In Google Sheets

We can of course go onto the charts and do this manually, but the utility of always recording these figures by hand is limited and will place a great restriction on the applications possible with the information.

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

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

Calculation Tutorial

Step 1
Request 52-Week High, 52-Week Low and Today’s Price.

Cell B1 = JSE:VOD, in the below example.

Request 52 Week high

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

Request 52 Week Low

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

Request Todays Price

GOOGLEFINANCE(B1)
Todays Price Formula
Todays Price

Step 2
Calculate price difference (range) between the Current Price and 52 Week low, then the price difference between 52 week high and low.

Step 2.1
Calculate the price difference between Todays Price and 52 Week Low.

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 price difference between 52 Week High and 52 Week Low.

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
Divide Price Difference(1) by Price Difference(2).

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

“Format as percent” or multiply by 100 to convert 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.