Follow stock market trends – Moving Average

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 the present time.

 

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

1. Calculate moving average

Formula in cell K23:

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

Back to top

2. How to add a moving average to an Excel stock chart

1. Press with right mouse button on on chart
2. Press with mouse on "Select Data..."
3. Press with left mouse button on "Add" button
4. Select cell range $K$23:$K$272
   
5. Press with left mouse button on OK button
6. Go to tab "Layout" on the ribbon.
7. Select "Series 4"
   
8. Press with left mouse button on "Format Selection" button, see picture above.
9. Select "Secondary Axis"
   
10. Go to "Line Color"
11. Select "Solid Line"
12. Pick a color
    

The chart now looks like this:

The data on the secondary axis has to be reversed.

1. Select line on chart
2. Go to tab "Layout" on the ribbon
3. Press with left mouse button on "Axis" button and then "Secondary horizontal axis" and finally press with left mouse button on "Show right to left axis"
   
4. Select and delete the horizontal axis above the chart
5. Go to tab "Layout" on the ribbon
6. Press with left mouse button on "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.

Back to top

3. 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 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:

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.

1. Press with right mouse button on on chart
2. Press with left mouse button on "Select Data..."
3. Press with left mouse button on "Add" button
4. Select cell range $L$23:$L$273
5. Press with left mouse button on OK button
6. Press with left mouse button on "Add" button again
7. Select cell range $M$23:$M$273
8. Press with left mouse button on OK button
9. Press with left mouse button on OK button

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

1. Go to tab "Layout"
2. Select Series 5
3. Press with left mouse button on "Format Selection"
4. Select "Marker Options"
5. Select "Built-in"
6. Pick a type
7. Go to "Line Color"
8. Select "No Line"
9. Select "Series 6" on tab "Layout" on the ribbon
10. Select "Marker Options"
11. Select "Built-in"
12. Pick a type
13. Go to "Line Color"
14. Select "No Line"

Back to top

4. How to extract the moving average turning dates using Excel array formulas

Array formula (Buy) in cell P23:

Formula (Buy Date) in cell O23:

Array formula (Sell) in cell S23:

Formula (Sell Date) in cell R23:

Back to top

4.1 How to enter an array formula

1. Select cell P23
2. Copy array formula
3. Paste array formula in the formula bar
4. Press and hold CTRL + SHIFT simultaneously
5. Press Enter once
6. 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.

Back to top

4.2 Explaining formula in cell P23

Step 1 - Check if each value in $K$23:$K$262 is larger than the next cell value below

The less than sign checks if the numerical value in a cell is less than the number in the next cell below.

$K$23:$K$262<$K$24:$K$263

The less than sign is a logical operator and the result is a boolean value, TRUE or FALSE.

Step 2 - Check if each value in $K$24:$K$263 is larger than the next cell value below

The larger than sign checks if the numerical value in a cell is larger than the number in the next cell below.

$K$24:$K$263>$K$25:$K$264

The larger than sign is also a logical operator and the result is a boolean value, TRUE or FALSE.

Step 3 - Multiply arrays

When we multiply arrays using the asterisk sign we apply AND-logic meaning TRUE is returned only if TRUE is present in the same position in both arrays.

TRUE * TRUE = TRUE

TRUE * FALSE = FALSE

FALSE * TRUE = FALSE

FALSE * FALSE = FALSE

($K$23:$K$262>$K$24:$K$263)*($K$24:$K$263<$K$25:$K$264)

The numerical equivalent is returned when two boolean values are multiplied.

TRUE = 1

FALSE = 0 (zero)

Step 4 - Create sequence from 1 to n

The ROW function returns a number representing the row based on a cell reference, for example, ROW(A1) returns 1.

This works also if we use a cell range as a cell reference, however, the formula is now an array formula. It calculates an array of values.

MATCH(ROW($G$23:$G$262), ROW($G$23:$G$262))

The MATCH function returns the relative position of a given value.

MATCH({23; 24; 25; ... ; 262}, {23; 24; 25; ... ; 262})

and returns {1; 2; 3; ... }.

Step 5 - Returns row number if logical expression is TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

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)), "")

Step 6 - Extract the k-th smallest row number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(array, k)

The ROWS function returns the number of rows based on a cell reference.

ROWS(array)

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)))

Step 7 - Get corresponding value from column G

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(array, [row_num], [column_num])

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)))

Step 8 - Handle errors

The IFERROR function lets you catch most errors in Excel formulas.

IFERROR(value, value_if_error)

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))), "")

Back to top

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.

Tip! - Add data labels

Back to top

6. 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 workbook lets you change how these moving averages are calculated. There is a get link below.

Above is an animated gif demonstrating a stock chart of S&P 500 with monthly prices. Two moving averages #1, #2, buy and sell points are plotted in this chart.

When the moving averages intersects a buy or sell point is created. If you use the value 7 in cell C1, moving average #1 uses the average of 7 months.

As you can see using a 15 month average (#1) returns better and fewer buy and sell points.

Extracting buy and sell points

Not only is the chart refreshed as you type new values in cell C1 and C2, the data below the chart is also instantly refreshed.

Array formula in cell B25:

Array formula in cell C25:

Array formula in cell D25:

How to create an array formula

1. Copy array formula above
2. Select cell B25
3. Paste formula to cell
4. Make sure you see the prompt in the cell, double press with left mouse button on the cell if you don't.
5. Press and hold Ctrl+ Shift simultaneously
6. Press Enter once
7. Release all keys

How to copy array formula

1. Select cell B25
2. Copy cell (not formula in formula bar)
3. Select cell range B26:B50
4. Paste

Explaining formula in cell B25

Step 1 - Remove errors

The IFERROR function removes errors and returns an array containing values or nothing, errors are filtered out.

IFERROR(Calculation!$M$2:$M$247, "")

Step 2 - Check if cell is empty

The less than and greater than sign combined means "not equal to", in this case the logical operators make sure that the value is not equal to nothing. It returns boolean values TRUE or FALSE.

IFERROR(Calculation!$M$2:$M$247, "")<>""

Step 3 - Check column N as well

Actions described in step 1 and 2 are also applied to column N.

(IFERROR(Calculation!$N$2:$N$247, "")<>"")

Step 4 - If cells are not empty return the corresponding relative row number

The + sign between the logical expressions performs an OR calculation meaning if the cell in column M is not equal to nothing OR if the cell in column N is not equal to nothing then return TRUE, both logical expressions must return FALSE in order to return a FALSE.

(IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>"")

The IF function then returns the second argument if the logical expression returns TRUE.

IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), "")

The second argument is the corresponding row number and the third argument is nothing.

MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)

Step 5 - Extract the k-th largest row number

The LARGE function extracts the k-th largest value in the array. LARGE( array, k)

LARGE(IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), ""), ROW(A1))

Argument k is a relative cell reference that changes when the cell is copied, this makes it possible to extract different values.

Step 6 - Return value from column D based on row number

The INDEX function returns a value from a cell range based on a row and column number.

INDEX(Calculation!$D$2:$D$247, LARGE(IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), ""), ROW(A1)))

Calculating buy and sell points

Sheet: Calculation

Array formula in cell K2:

Array formula in cell L2:

Formula in cell M2:

Formula in cell N2:

Copy cells and paste down as far as needed.

The calculation sheet imports historical prices using this user defined function:
Excel udf: Import historical stock prices from yahoo – added features

Further reading

If you want to know how to plot buy and sell points in an excel stock chart, read this post:
Add buy and sell points to a stock chart

7. Follow stock market trends - trailing stop

Now I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post showed you how to identify the trend using moving averages.

The picture above shows you a chart of S&P 500 with two lines, one is green and one is red. If the stock price moves above the green line a buy signal is returned, if the price moves below the red line a sell signal is generated.

7.1. How trailing stops work

In a bull market, the price moves above the green line again and again, and in a bear market the price moves repeatedly below the red line. The trailing stop method makes it easier to identify when a bull market becomes a bear market and vice versa. Let me explain how these colored lines are constructed.

The red line shows the smallest value out of the last eight months' price bars, see blue arrows on the picture above. In October 2013 the smallest value is found from the first price quote (February 2013). In November 2013 the smallest value is found in the price quote March 2013.

As time moves on the red line advances higher and higher until August 2015. The price moves below the red line and the first sell signal is generated.

This chart shows with blue markers each time a price bar moves above the green line and with red markers each time a price bar moves below the red line. As you can see this method is not perfect, sometimes it generates false signals, see markers in year 1998, 2010 and 2011. Who knows if sell signal in August 2015 is correct? Only time can tell.

Back to top

7.2. How to build this stock chart

The following formula allows you to change the number of calculated months, you can do that by changing the number in cell L20.

The red line formula in cell K23:

7.2.1 Explaining formula in cell K23

Step 1 - Calculate cell based on value in cell L20

The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference.

OFFSET(reference, rows, columns, [height], [width])

OFFSET(F24,$L$20-1,0)

becomes

OFFSET(F24,8-1,0)

becomes

OFFSET(F24, 7, 0)

and returns a reference to cell F31.

Step 2 - Create a reference to a cell range

The colon allows you to create a cell reference to a cell range.

F24:OFFSET(F24,$L$20-1,0)

returns F24:F31.

Step 3 -

MIN(F24:OFFSET(F24,$L$20-1,0))

The green line formula in cell M23:

Copy cell K23 and paste to cells below as far as needed. Repeat with cell M23.

Back to top

7.3. Add lines to the stock chart

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select Data...".
3. Press with mouse on the "Add" button.
4. Select values in column K ($K$23:$K$271).
5. Press with left mouse button on OK.
6. Press with left mouse button on OK.
7. Select chart.
8. Go to tab "Layout" on the ribbon.
9. Select Series 4 for on the top left corner.
10. Press with left mouse button on the "Format Selection" button.
11. Make sure values are plotted on the "Secondary axis" (enabled).
12. Go to "Line Color".
13. Select "Solid Line" and pick a color.

Repeat above steps (add a new series with values from column M ($M$23:$M$271).

As you can see the two lines are in reverse order compared to the stock price bars.

1. Select one of the lines, it does not matter which one.
2. Go to tab "Layout" on the ribbon.
3. Press with left mouse button on the "Axes" button and then "Secondary horizontal axis" and finally "Show axis left to right".
4. Press with right mouse button on on the horizontal secondary axis (above the plot area).
5. Press with left mouse button on "Format Axis...".
6. Enable "Categories in reverse order".
7. Delete the horizontal secondary axis (above the plot area).
8. Select one of the lines.
9. Press with left mouse button on the "Axes" button and then "Secondary vertical axis" and finally press with left mouse button on "Show none".

Back to top

7.4. Add breakout signs to the chart

Sell formula in L23:

Buy formula in N23:

Copy cell L23 and paste to cells below as far as needed. Repeat with cell N23.

Now add two more markers series, see instructions above. Here is a small recap:

1. Add two series (values in columns L and N).
2. Plot series on the secondary axis.
3. Use markers instead of solid lines.

This post shows you in greater detail how to add markers.

Back to top

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.

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

Back to top

8. Add buy and sell markers 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 day average.

It is easy to create a stock chart in Excel. In this article, I am going to describe how to insert buy and sell points to an Excel chart.

To simplify things I will use a 50 day moving average as an indicator. If the 50day average changes from negative to positive a buy signal is generated and a sell signal when the average moves from positive to a negative trend.

I have copied stock data from the Yahoo Finance website, you can easily pick a stock or index and get historical data.

Calculating moving average

The AVERAGE function calculates the moving average, remember to use the last 50 days as an argument. I am using a relative cell reference that changes accordingly when the cell is copied to cells below.

In cell K2:

Copy cell K2 and paste down as far as needed.

Find buy and sell dates

The following formulas check if the 50-day average is higher or lower than the previous day and the day before that, this will return the close value only if there is a change in trend.

In other words multiple buy or sell signals won't be displayed, only the first one. The first IF function has two logical expressions (K2-K3>0) and (K3-K4<0), both these conditions must be true to return the value in K2.

In cell L2:

In cell M2:

Copy cell L2 and M2 and paste down as far as needed.

Extract year and month from date

Let's extract year and month from dates to avoid a mess on the x axis in our stock chart. The YEAR function returns the year based on an Excel date.

Chandoo has a great post: Show Months & Years in Charts without Cluttering

In cell B2:

In cell B3:

The TEXT function formats an Excel date in this particular example, and displays the month formatted with only first three visible characters.

The IF function makes sure that the formatted month name is shown only when the month changes, this to avoid repeated months.

Cell C2:

Copy cell B3 and C2 and paste down as far as needed.

Create stock chart

It is important that the data is arranged in this order: High, Low and Close.

1. Select cell range F1:H300
2. Go to tab "Insert"
3. Press with left mouse button on "Other charts"
4. Press with left mouse button on "High Low Close stock chart"

Adjust vertical axis min and max values

If your chart is zoomed out too far you can simply adjust the visible axis range by following these steps.

1. Select vertical axis
2. Press with right mouse button on on vertical axis
3. Press with left mouse button on "Format axis..."
4. Press with left mouse button on "Axis Options"
5. Press with left mouse button on Minimum fixed and type 1050.
6. Press with left mouse button on Maximum fixed and type 1450.

Change horizontal axis labels

These steps change the x-axis numbers to years and months based on the values in column B and C.

1. Press with right mouse button on on chart
2. Press with left mouse button on "Select data"
   
3. Press with left mouse button on "Edit" button
4. Select cell range B2:C300
5. Press with left mouse button on OK

Reverse horizontal (category) axis

If the dates are backward then these steps tell you how to change the order.

1. Select horizontal axis
2. Press with right mouse button on on horizontal axis
3. Press with left mouse button on "Format axis.."
4. Press with left mouse button on "Axis Options"
5. Press with left mouse button on "Categories in reverse order"
6. Select "Major tick mark type:" None
7. Press with left mouse button on OK

Add moving average

These steps explains how to display the moving average on an Excel chart.

1. Select cell range K1:K300
2. Copy
3. Select chart
4. Got to tab "Home"
5. Press with left mouse button on "Paste"
6. Press with left mouse button on "Paste Special.."
   
7. Press with left mouse button on "Ok"
8. Press with mouse on chart
9. Go to tab "Format"
10. Select "50 day m-avg"
    
11. Press with left mouse button on "Format selection"
12. Press with left mouse button on "Series Options"
13. Press with left mouse button on "Secondary axis"
14. Press with left mouse button on "Line color" and select: Solid Line
15. Press with left mouse button on Close button

 How to display buy and sell signals on an Excel chart?

1. Select cell range L2:L300
2. Copy
3. Select chart
4. Go to tab "Home"
5. Press with left mouse button on "Paste"
6. Press with left mouse button on "Paste special"
7. Press with left mouse button on Ok

Select cell range M2:M300 and repeat above steps.

1. Press with left mouse button on on chart to select it
2. Go to tab "Format"
3. Select "Buy"
4. Press with left mouse button on "Format Selection"
5. Press with left mouse button on "Marker Options"
6. Press with left mouse button on "Bulit-in"
7. Select a marker type and size
8. Select "Marker Fill"
9. Press with left mouse button on "Solid Fill"
10. Select "Line Color": No line
11. Press with left mouse button on Close

Repeat above steps with "Sell"

Change secondary vertical axis

1. Select and press with right mouse button on on the vertical axis to the left
2. Change minimum and maximum values. They must match the primary axis.
3. Select and press with right mouse button on on the vertical axis to the right
4. Press with left mouse button on "Delete"

Add Data Labels

Buy series

1. Press with left mouse button on on chart to select it
2. Go to tab "Layout"
3. Select "Buy" series
4. Press with left mouse button on "Data Labels"
5. Press with left mouse button on "More Data Label Options"
6. Press with left mouse button on "Label options"
7. Deselect "Values"
8. Select "Series Name"
9. Label position: Below
10. Press with left mouse button on Close

Sell series

1. Press with left mouse button on on chart to select it
2. Go to tab "Layout"
3. Select "Sell" series
4. Press with left mouse button on "Data Labels"
5. Press with left mouse button on "More Data Label Options"
6. Press with left mouse button on "Label options"
7. Deselect "Values"
8. Select "Series Name"
9. Label position: Above
10. Press with left mouse button on Close

Stock market trend category

More than 1300 Excel formulas

Excel categories

Comments (5)