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 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
- Right click on chart
- Click on "Select Data..."
- Click on "Add" button
- Select values in column K ($K$23:$K$271)
- Click OK
- Click OK
- Select chart
- Go to tab "Layout" on the ribbon
- Select Series 4 for on the top left corner
- Clik "Format Selection" button
- Make sure values are plotted on "Secondary axis" (enabled)
- Go to "Line Color"
- 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.
- Select one of the lines, it does not matter which one.
- Go to tab "Layout" on the ribbon
- Click "Axes" button and then "Secondary horizontal axis" and finally "Show axis left to right"
- Right click on the horizontal secondary axis (above the plot area)
- Click "Format Axis..."
- Enable "Categories in reverse order"
- Delete the horizontal secondary axis (above the plot area)
- Select one of the lines
- 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:
- Add two series (values in column L and N)
- Plot series on secondary axis
- 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.
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 […]
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 […]
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart bar is […]
Leave a 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.