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

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

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:

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

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

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.

Get the Excel file

Follow-stock-market-trends-trailing.xlsm

Back to top

Related articles

Dynamic stock chart
This stock chart built in Excel allows you to change the date range and the chart is instantly updated. What's […]