Stock markets can go into sharp declines over a short period of time. For example, President Trump’s directive to implement tariffs in early April 2025 sent the U.S. stock market into a sharp decline after major stock indexes and some individual stocks had reached record highs just a few weeks before.
Tracking how much stocks have fallen from their 52-week highs can be useful in your trading strategy in concert with technical indicators that can help determine whether stocks are oversold. An index losing 10% or more from its highs is said to be in a correction, while a loss of 20% or more puts it in a bear market.
💵💰Don’t miss the move: Subscribe to TheStreet’s free daily newsletter 💰💵
We’ve created a spreadsheet on Google Sheets using live Google Finance data that can track a stock or index’s price change from its 52-week high. The spreadsheet also shows how much a stock from its latest trade would need to rise to reach those 52-week highs.
The spreadsheet version of the following tutorial can be downloaded here. Make a copy of the worksheet by selecting “Make a copy” from the drop-down in the File menu.
Related: How to create a stock tracker with live data using Google Finance
How to create a table of stocks tracking 52-week highs
To create a spreadsheet that will allow you to use Google Finance, you will need to set up a Google account that will let you access Google Sheets. The first step to create a new spreadsheet on Google Sheets is by typing “sheets.new” in the URL bar of your browser.
From the top row, going from left to right by column, attributes will be listed to define the requested data. In this case, we will use stocks that make up the Nasdaq-100, an index of 100 of the largest non-financial companies on the Nasdaq stock exchange. The index is skewed toward the largest technology companies in the U.S., such as Apple (AAPL) , Nvidia (NVDA) , and Microsoft (MSFT) .
For the spreadsheet that’ll be titled “52-week highs,” we will use the following attributes:
Name: This lists the name of the company.High52: This shows the stock’s highest price in the past 52 weeks.Price: This provides the latest available trading price of the stock. In Google Finance, there may be a slight delay, behind real-time, from some exchanges on prices for particular securities. Tradetime: This provides the last time and day of trading for the stock.Marketcap: This shows the market capitalization of the company at the time of trade.
Screen grab of a spreadsheet via Google Sheets on price changes for Nasdaq-100 stocks.
To understand how to create Google Finance’s formula with attributes, refer to the section “Understanding Google Finance’s formula” in our “How to create a stock tracker with live data using Google Finance” tutorial.
For this spreadsheet, we’ll create eight columns that will be a combination of attributes and customized calculations. The cell in the first column and first row will be labeled “ticker,” and we will place as static data the stock ticker symbols of the Nasdaq-100 below that in Column A.
The attribute “name” will be in Row 1 for Column B, “high52” in Column C, “price” in Column D, “tradetime” in Column G, and “marketcap” in Column H. Data for price and market cap are in U.S. dollars.
The header for Column E will be labeled “% change from 52-week high” to show how much a stock has fallen from its 52-week high. The cell is highlighted in red to show a decline. (At the same time, a stock closing at its 52-week high would highlight the cell in white and its change as zero from the last price.)
Column F is labeled “% appreciation needed from last price to reach 52-week high” to show how much the stock from its last time of trading will need to reach its 52-week high.
Column H for market capitalization has a color gradient from light yellow to dark yellow to highlight companies with the smallest and largest market capitalizations.
More on tools:
How to create macros in Google Sheets: Automating functions on commandHow to use conditional formatting in Google Sheets: Ranges, formulas & moreHow to create an in-cell dropdown list on Google Sheets
How to sort percent changes from the 52-week high
A macro can sort the stocks listed in the spreadsheet from top to bottom based on Column E, “% change from the 52-week high,” which shows the largest declines from their 52-week highs.
In the top menu under “Extensions,” the dropdown shows Macros, and under Macros, there’s a macro function labeled “Largest Declines from 52-Week Highs.” Clicking on that macro will sort the stocks, which in this spreadsheet will be stocks in the Nasdaq-100, from the largest decline to the smallest.
How to customize the 52-week high spreadsheet to track your own stocks
At the bottom of the spreadsheet, there’s a tab titled “Indexes” that shows main stock indexes including the S&P 500, the Dow Jones Industrial Average, and the Nasdaq-100. The layout is similar to the spreadsheet tab “Nasdaq-100,” and you can add whatever indexes you want.
Alternatively, you can copy the “Nasdaq-100” tab and create your own list of stocks to track their changes from their 52-week highs.
Related: Veteran fund manager unveils eye-popping S&P 500 forecast
