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.
What's on this page
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.
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.
3. Add lines to the stock chart
- Press with right mouse button on on the chart.
- Press with mouse on "Select Data...".
- Press with mouse on the "Add" button.
- Select values in column K ($K$23:$K$271).
- Press with left mouse button on OK.
- Press with left mouse button on OK.
- Select chart.
- Go to tab "Layout" on the ribbon.
- Select Series 4 for on the top left corner.
- Press with left mouse button on the "Format Selection" button.
- Make sure values are plotted on the "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.
- Press with left mouse button on the "Axes" button and then "Secondary horizontal axis" and finally "Show axis left to right".
- Press with right mouse button on on the horizontal secondary axis (above the plot area).
- Press with left mouse button on "Format Axis...".
- Enable "Categories in reverse order".
- Delete the horizontal secondary axis (above the plot area).
- Select one of the lines.
- Press with left mouse button on the "Axes" button and then "Secondary vertical axis" and finally press with left mouse button on "Show none".
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:
- Add two series (values in columns L and N).
- Plot series on the secondary axis.
- Use markers instead of solid lines.
This post shows you in greater detail how to add markers.
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.
Stock market trend category
This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
Excel categories
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.
