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 following picture shows you a stock chart 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.

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

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:

The green line formula in cell M23:

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

Add lines to stock chart

1. Right click on chart
2. Click on "Select Data..."
3. Click on "Add" button
4. Select values in column K ($K$23:$K$271)
5. Click OK
6. Click OK
7. Select chart
8. Go to tab "Layout" on the ribbon
9. Select Series 4 for on the top left corner
10. Clik "Format Selection" button
11. Make sure values are plotted on "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. Click "Axes" button and then "Secondary horizontal axis" and finally "Show axis left to right"
4. Right click on the horizontal secondary axis (above the plot area)
5. Click "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. Click "Axes" button and then "Secondary vertical axis" and finally click "Show none"

Add signal markers to 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 column L and N)
2. Plot series on secondary axis
3. Use markers instead of solid lines.

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

Download excel *.xlsm 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.

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

Follow stock market trends - trailing stop.xlsm