How to Build a Trend Trading System in Excel

Today I wanted to share with you a part of the algorithmic back end of my ETF Trend Trading System. Note, I said “part”, I’m not giving away all my secrets. It’s written completely in Excel, incredibly simple, and is a macro that you can import. The system works by using something called linear regression slope.

What’s that?

The easiest way to understand what linear regression slope is is to think back to your basic statistics class. Linear regression is the “best fit” line between a bunch of data points. A line is defined by the formula: y = mx+b, where y is your data point’s position on the y-axis, m is the slope, x is your data point’s position on the x-axis, and b is the slope intercept.

What this ETF Trend Trading system does is place a “best fit” line across several price data points (8, 13, and 26 weeks) and then calculate the slope of the line. If the slope is positive, you have an upward-trending ETF. Conversely, if the slope is negative then you have a downward-tending slope.

As the ETF trades in the markets, the price goes up, down, and sometimes consolidates inside a trend. When that happens the linear regression slope begins to “flatten” out, meaning the slope becomes more horizontal. When combined with two or more periods, like an 8, 13, and 26-week period, you can see the overall short-term, medium-term, and long-term trends in a particular ETF. This makes for a great indicator that warns you of either a change in trend or a dip in buying opportunity.

Ready to try it out for yourself? Just follow these easy steps and you’ll be ETF Trend following in no time. First, you have to make sure you have Excel 2003 or a later version installed and access to ETF data.

Step 1: Get two years of ETF data.

You’ll need your favorite ETF and two years of weekly closing data. Make sure you include the date, open, high, low, and closing prices. You can cheat, and follow along with my example by downloading this XLS.

In the example contained in this lesson, I use the S&P500 weekly data but you can substitute that with any ETF or index you’d like to follow.

Step 2: Copy the macro code below and paste it into your Excel Visual Basic Editor. You can find this editor by going to Tools > Macros > Visual Basic Editor.

```    Sub ETF_TREND()
‘
’ LinReg Macro
’ Macro recorded 3/8/2007 by Thomas Ott
’
’Clear Data
Columns(“G:Q”).Select
Selection.ClearContents
’Calc ETF Trends
Range(“G1”).Select
ActiveCell.FormulaR1C1 = “8 Week”
Range(“H1”).Select
ActiveCell.FormulaR1C1 = “13 Week”
Range(“I1”).Select
ActiveCell.FormulaR1C1 = “26 Week”
Range(“G9”).Select
ActiveCell.FormulaR1C1 = “=SLOPE(R[-7]C[-2]:RC[-2],R[-7]C[-6]:RC[-6])”
Selection.AutoFill Destination:=Range(“G9:G54”), Type:=xlFillDefault
Range(“G9:G54”).Select
Range(“H14”).Select
ActiveCell.FormulaR1C1 = “=SLOPE(R[-12]C[-3]:RC[-3],R[-12]C[-7]:RC[-7])”
Selection.AutoFill Destination:=Range(“H14:H54”), Type:=xlFillDefault
Range(“H14:H54”).Select
Range(“I27”).Select
ActiveCell.FormulaR1C1 = “=SLOPE(R[-25]C[-4]:RC[-4],R[-25]C[-8]:RC[-8])”
Selection.AutoFill Destination:=Range(“I27:I54”), Type:=xlFillDefault
Range(“I27:I54”).Select
’ Format Columns
Range(“G9”).Select
Selection.FormatConditions.Delete
Formula1:=“0”
Selection.FormatConditions(1).Font.ColorIndex = 3
Formula1:=“0”
Selection.FormatConditions(2).Font.ColorIndex = 50
Selection.Copy
Range(“G9:I54”).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = “0.000000”
Selection.NumberFormat = “0.00000”
Selection.NumberFormat = “0.0000”
Selection.NumberFormat = “0.000”
’ Percent Change Function
Range(“J1”).Select
ActiveCell.FormulaR1C1 = “% Change”
Range(“J2”).Select
ActiveWindow.SmallScroll Down:=18
Range(“J53”).Select
ActiveCell.FormulaR1C1 = “=(RC[-5]-R[-51]C[-5])/R[-51]C[-5]”
ActiveWindow.SmallScroll Down:=6
Selection.Style = “Percent”
Selection.NumberFormat = “0.0%”
Selection.NumberFormat = “0.00%”
Selection.AutoFill Destination:=Range(“J53:J54”), Type:=xlFillDefault
Range(“J53:J54”).Select
End Sub
```

Step 3: Save the file and then activate the macro by clicking Run.

You should see that the macro created four new columns and color-coded the slopes. It should look something like this XLS.

Step 4: This step is optional but I highly recommend you do this.

You should build a chart from the 8, 13, and 26-week slopes. This will help you identify the peaks and valleys in the ETF’s (or index’s) trend. See our last XLS example.

A Machine Learning Trend Trading System

Building an asset trend-following system is quite easy to do if you’ve read my tutorials. You gather your data, assign trend values (UP, DOWN), and then run it through a classification algorithm like YALE’s IBK operator. Doing this is what some people call Fuzzy trend analysis< and it’s quite easy to do if you use YALE, but what if you don’t have the time to learn YALE? Is there another way to do it, perhaps using Excel?

Before I direct you to a place where you can learn how to build a classification trend following model in Excel, we have to understand what the classification algorithm is and how it works. The classification algorithm I use is called “KNN.” KNN stands for “K nearest neighbor” and the best explanation I’ve found for what it is and how it works is from Kardi Teknomo’s website:

K-nearest neighbor is a supervised learning algorithm where the result of new instance query is classified based on majority of K-nearest neighbor category. The purpose of this algorithm is to classify a new object based on attributes and training samples. The classifiers do not use any model to fit and only based on memory. Given a query point, we find K number of objects or (training points) closest to the query point. The classification is using majority vote among the classification of the K objects. Any ties can be broken at random. K Nearest neighbor algorithm used neighborhood classification as the prediction value of the new query instance. [via Kardi Teknomo PhD]

If you spend some time on Doctor Teknomo’s site, you’ll find his fantastic tutorial, complete with his spreadsheet examples, explaining how to use KNN in Excel to make predictions.

Here’s what I did, first I modified his spreadsheet and populated it with 24 trading days of the iShares MSCI Japan Index EWJ and the iShares MSCI Singapore Index EWS ETFs. What I wanted to do is predict EWS’s trend (+ for up, – for down) using the data for both ETFs. Next, I changed the trend value to either + or -, in column D, and then changed the “K” cell value to 8.

Changing the number in the “K” cell tells the algorithm how many of your query cell’s neighbors it should look at to make its prediction. The spreadsheet then automatically calculated the correct trend value “+” for EWS after I inputted my preferred “K” value.

It’s as simple as that! Now you have, in a rudimentary way, the ability to create your own trend-following system in Excel using neural net algorithms. Do spend time learning how this algorithm works because it’s very powerful and you can easily incorporate it into an ATS or other quantitative analytic trading system.

Note: All information contained herein is for informational purposes only and does not constitute an offer to sell nor the solicitation of an offer to buy any security. “Neural Market Trends”, Sixth World Investments LLC, or anyone affiliated with the production of this site’s information is not responsible for any activities conducted by viewers. This material is informational only and does not recommend investment activities for corresponding viewers.

Subscribe to our newsletter and never miss a beat! Thank you so much!

Low Cost Cheat Sheets

Here are handy low cost cheat sheets to kickstart your learning in Excel and other products (affiliate links):

Posted

in

by

Tags: