In volatile markets, share prices can go up and down sharply — from one extreme to another. A large decline in a particular stock could wipe out a significant investment in a portfolio, leaving an investor to wonder at that moment whether to dump the stock or hold on.
Tracking stock price changes from their 52-week lows can help investors identify opportunities to snap up shares in companies that might be undervalued.
(You can also track stock price changes from their 52-week highs using this tutorial.)
💵💰Don’t miss the move: Subscribe to TheStreet’s free daily newsletter 💰💵
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.
A spreadsheet using data from Google Finance can allow you to track how much stocks have risen from their 52-week lows.
How to use Google Sheets to track stock prices relative to their 52-week lows
A first step to create a spreadsheet for using Google Finance will require you to set up a Google account that will allow you to access Google Sheets. To create a new spreadsheet on Google Sheets, type “sheets.new” in the URL bar of your browser.
At the top row — from left to right by column — you will need to list the attributes defined by Google Finance that will import the requested data into the spreadsheet.
In this example, we will use stocks that make up the Dow Jones Industrial Average, an index of 30 stocks that, according to the index’s compilers, represent the breadth of the U.S. economy. The index was formerly composed only of stocks listed on the New York Stock Exchange, but it now includes Nasdaq stocks such as Microsoft (MSFT) , Nvidia (NVDA) , and Apple (AAPL) .
Related: How to track stock price changes from 52-week highs on Google Finance
For the spreadsheet, titled “52-week lows,” we will use the following attributes:
Name: This lists the name of the company.Low52: This shows the stock’s lowest 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 on real-time 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.
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.
This spreadsheet will have seven columns, of which one will have a customized calculation. The cell in the first column and first row will be labeled “ticker.” Static data for stock ticker symbols of the Dow 30 will appear in Column A.
The attribute “name” will be in Row 1 for Column B, “low52” in Column C, “price” in Column D, “tradetime” in Column F, and “marketcap” in Column G.
The header for Column E will be labeled “% change from 52-week low” to show how much a stock has risen from its 52-week low. (Note: A stock’s 52-week low will never be above its current price.)
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 percentage changes from 52-week lows
You can calculate how much each Dow stock has risen from its 52-week low with the creation of a customized macro.
In the top menu under “Extensions,” the dropdown shows Macros, and under Macros, there’s a function labeled “Largest Gains from 52-Week Lows.” Clicking on that macro will sort the Dow stocks in this spreadsheet from the biggest gain to the smallest, based on Column E, “% change from 52-week low.”
How to customize the 52-week low spreadsheet to track your own stocks
The bottom of the spreadsheet shows a tab titled “Indexes,” and it lists major stock indexes: the S&P 500, the Dow Jones Industrial Average, the Nasdaq Composite, the Russell 2000 Index, and the Nasdaq-100. The layout is similar to the spreadsheet tab “DJIA,” and you can add whatever indexes you want by copying the entire row of one of the existing indexes and pasting it into an empty row.
Alternatively, you can copy the “DJIA” tab and create your own list of stocks to track their changes from their 52-week lows.
Related: Veteran fund manager unveils eye-popping S&P 500 forecast
