Trend Trading in Excel

Today I wanted to share with you a part of the algorithmic back end to my ETF Trend System. Note, that 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 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 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.

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.

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.

Trend Following

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?

The answer is YES!

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.

So how do use his spreadsheet to build your own trend-following system? I made this part easy for 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 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 inputed 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.

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

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

Trend Following