Today I wanted to share with you a part of the algorithmic back end to my ETF Trend 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.
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 following 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-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 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=“0” Selection.FormatConditions(1).Font.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, 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 that 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.
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 its 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?
The answer is YES!
Before I direct you to a place where you can learn how 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 us Knn in Excel to make predictions.
So how do use his spreadsheet to build your own trend following system? I made this part easy on you, to follow along just download my version of the good Doctor's spreadsheet. << This link is dead. I can't find the XLS.
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 ETF's. What I wanted to do is predict EWS's trend (+ for up, - for down) using the data for both ETF's. 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 input 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 the time learning how this algorithm works because its very powerful and you can easily incorporate it into an ATS or other quantitative analytic trading system.