Follow stock market trends

Follow stock market trends – Moving Average

Author: Oscar Cronquist Article last updated on August 06, 2017

In my previous post I described how to build a dynamic stock chart that lets you easily adjust the date range and change index/company. Price data is quickly and automatically fetched from yahoo finance.

This post shows you how to add tools to the charts so you can quickly identify the trend for a stock or index. The stock market often trends for many months up or down and a moving average smooths out price data.

The examples shown in this post are based on S&P 500 index and larger trends. Charts show price data on a monthly scale and the date range is from 1995 to present time.

This excel stock chart has a 10 month moving average. It can be calculated using the AVERAGE function on an excel worksheet.

Formula in cell K23:

=AVERAGE(G24:G33)

This function calculates the average from 10 latest closing prices. Copy cell K23 and paste to cells below as far as needed.

How to add a moving average to an excel stock chart

Right click on chart
Click on "Select Data..."
Click "Add" button
Select cell range $K$23:$K$272
Click OK button
Go to tab "Layout" on the ribbon.
Select "Series 4"
Click "Format Selection" button, see picture above.
Select "Secondary Axis"
Go to "Line Color"
Select "Solid Line"
Pick a color

The chart now looks like this:

The data on the secondary axis has to be reversed.

Select line on chart
Go to tab "Layout" on the ribbon
Click "Axis" button and then "Secondary horizontal axis" and finally click "Show right to left axis"
Select and delete the horizontal axis above the chart
Go to tab "Layout" on the ribbon
Click "Axis" button and "Secondary Vertical Axis" and finally "None"

The chart looks like this:

A moving average indicates if a market is about to go up or down in the long term.

Plot moving average turning points

Let's start with calculating when the moving average changes from moving down to up.

Buy formula in cell L23:

=IF((K23>K24)*(K24<K25),G23,"")

If the value in cell K23 is larger than cell K24 AND cell K24 is smaller than K25 THEN return the closing price. If not return nothing.
Read more about IF function.

The next formula calculates when the moving average changes from going up to going down.

Sell formula in cell M23:

=IF((K23<K24)*(K24>K25),G23,"")

Copy cell range L23:M23 and paste to cells below as far as needed. It is now time to plot these moving average turning points.

Right click on chart
Click "Select Data..."
Click "Add" button
Select cell range $L$23:$L$273
Click OK button
Click "Add" button again
Select cell range $M$23:$M$273
Click OK button
Click OK button

Here is how to remove lines shown above and use markers instead.

Go to tab "Layout"
Select Series 5
Click "Format Selection"
Select "Marker Options"
Select "Built-in"
Pick a type
Go to "Line Color"
Select "No Line"
Select "Series 6" on tab "Layout" on the ribbon
Select "Marker Options"
Select "Built-in"
Pick a type
Go to "Line Color"
Select "No Line"

How to extract the moving average turning dates using excel array formulas

Array formula (Buy) in cell P23:

=IFERROR(INDEX($G$23:$G$262, SMALL(IF(($K$23:$K$262>$K$24:$K$263)*($K$24:$K$263<$K$25:$K$264), MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262)), ""), ROWS($A$1:A1))), "")

Formula (Buy Date) in cell O23:

=IFERROR(INDEX($C$23:$C$262,MATCH(P23,$G$23:$G$262,0)),"")

Array formula (Sell) in cell S23:

=IFERROR(INDEX($G$23:$G$262, SMALL(IF(($K$23:$K$262<$K$24:$K$263)*($K$24:$K$263>$K$25:$K$264), MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262)), ""), ROWS($A$1:A1))), "")

Formula (Sell Date) in cell R23:

=IFERROR(INDEX($C$23:$C$262,MATCH(S23,$G$23:$G$262,0)),"")

How to enter an array formula

Select cell P23
Copy array formula
Paste array formula in formula bar
Press and hold CTRL + SHIFT simultaneously
Press Enter once
Release all keys

If you did the above instructions correctly, the formula begins and ends with a curly bracket, like this {=formula}. Don´t enter these characters yourself.

Make sure you enter array formulas in cell P23 and S23. Then copy cell P23 and paste to cells below as far as needed. Repeat with cell S23, R23 and O23.

Download excel *.xlsx file

This template has dynamic named ranges for all chart series. This lets you change the date range and all chart data is adjusted automatically. I have not described how they work in this post, if you are curious

The template also contains a small custom function to fetch stock data from yahoo finance.

Follow stock market trends - moving avg.xlsm

Tip! - Add data labels

Plot buy and sell points in an Excel Chart based on two moving averages

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]

Add buy and sell points to a stock chart

The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]

Stock market trends – pivots

If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart bar is […]

Follow stock market trends – trailing stop

This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]

Comments (1)

One Response to “Follow stock market trends – Moving Average”

Follow stock market trends – trailing stop | Get Digital Help - Microsoft Excel resource says:

November 13, 2015 at 10:24 pm

[…] ← Previous post - […]

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form