Working with changing data? Spot and track trends with moving averages in Excel

 

by Kara Hiltz

Application: Microsoft Excel 2000/2002/2003/2007/2010/2013

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201812/stocktracker.zip

Let’s be realistic — data that changes daily, such as stock prices, is difficult to analyze due to its volatile nature. Fortunately, Excel has a data analysis tool that creates a moving average so you can smooth over the rough edges in your data analysis.

To find and plot moving averages, we’ll:

  • Set up a sample portfolio with closing stock prices that span a month.
  • Install the Analysis ToolPak, if necessary, which contains the Moving Average feature.
  • Find the five-day moving average of our stocks so we can get the gist of its trends.
  • Use one of Excel’s data analysis options to chart the moving average against the actual prices automatically.

Moving averages are a frequently used data analysis method, especially for finding trends over long periods. For example, you can find the 50-day trend of a stock to get a good picture of where it’s been and where it’s headed. A moving average helps you decide whether the stock is a good long-term investment. By focusing on the stock’s big picture, moving averages smooth over the data that can mislead you when you don’t look at the big picture.

Set up a sample portfolio

You can follow along with our example, as shown in Figure A, by downloading and extracting the file movingaverages.xls from the URL given at the beginning of this article. You can also create it from scratch. For simplicity, we’ll keep our moving average over a shorter time span, but keep in mind that many data analysts find it most effective to track stock prices over 20, 50, or 100 days.

IME18c02a

A: We’ll use the hypothetical data in this sample worksheet to demonstrate moving averages.

To set up the sample worksheet:

  1. Launch Excel and open a new workbook.
  2. Enter the data, as shown in Figure A.
  3. Apply currency number formatting to the stock prices in cells B4:C34 by selecting them and clicking the Currency Style button currency style on the Formatting toolbar.
  4. Add any other formatting you desire, such as bold font, larger font size, italics, etc.

Install Excel’s Data Analysis tools

Excel’s Data Analysis tools are part of the Analysis ToolPak that comes with the application. If you haven’t installed it already, you’ll need to do so in order to use the Moving Averages feature.

To install the Analysis ToolPak:

  1. Choose Tools | Add-ins from the menu bar to access the Add-Ins dialog box.
  2. Select the Analysis ToolPak check box in the Add-ins Available list box, as shown in Figure B.
  3. Click OK to load the add-in and dismiss the dialog box.

IME18c02b

B: You must select the check box next to the add-in for it to load correctly.

Adapt for Excel 2007, 2010 & 2013: To install the Analysis Toolpak in 2007, click the Office button and click the Excel Options button. Choose Add-Ins from the left pane. In 2010 and 2013, select File | Options | Add-Ins. If the Analysis ToolPak displays in your list of inactive application add-ins, select Excel Add-Ins from the Manage dropdown list and click Go. Excel displays the Add-Ins dialog box, just like you’d see in earlier versions.

Determine the moving average of your data

Now, a new menu item appears in the Tools menu. You can use this to access the Data Analysis features that install with the Analysis ToolPak.

 

To access the Moving Average feature:

  1. Choose Tools | Data Analysis from the menu bar to access the Data Analysis dialog box.
  2. Select Moving Average from the list box and click OK to access the Moving Average dialog box.

Adapt for Excel 2007, 2010 & 2013: In Excel 2007, 2010, and 2013, you’ll find the Data Analysis button in the Analysis group on the Data tab. Then, you can select Moving Average in the Data Analysis dialog box just as you would in earlier versions.

To determine the moving average of your stock prices:

  1. Click in the Input panel’s Input Range text box to activate it.
  2. Select the cells that include your stock prices, or enter the range manually. We’ll select cells B3:B34. Note: You should always use absolute references in both the input and output text boxes, which means that there are dollar signs ($) in front of each row and column indicator.
  3. If the first row[...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage