Build an ETF Trend System in Excel

Build an ETF Trend System in Excel

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.

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 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: GSPC ETF Trend Example

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.


LinReg Macro
Macro recorded 3/8/2007 by Thomas Ott

Clear Data
Calc ETF Trends
ActiveCell.FormulaR1C1 = 8 Week”
ActiveCell.FormulaR1C1 = 13 Week”
ActiveCell.FormulaR1C1 = 26 Week”
ActiveCell.FormulaR1C1 = =SLOPE(R[-7]C[-2]:RC[-2],R[-7]C[-6]:RC[-6])”
Selection.AutoFill Destination:=Range(“G9:G54”), Type:=xlFillDefault
ActiveCell.FormulaR1C1 = =SLOPE(R[-12]C[-3]:RC[-3],R[-12]C[-7]:RC[-7])”
Selection.AutoFill Destination:=Range(“H14:H54”), Type:=xlFillDefault
ActiveCell.FormulaR1C1 = =SLOPE(R[-25]C[-4]:RC[-4],R[-25]C[-8]:RC[-8])”
Selection.AutoFill Destination:=Range(“I27:I54”), Type:=xlFillDefault
Format Columns
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,

Selection.FormatConditions(2).Font.ColorIndex = 50
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
ActiveCell.FormulaR1C1 = % Change”
ActiveWindow.SmallScroll Down:=18
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
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: GSPC ETF Trend Example 2

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 ETFs (or index’s) trend. See our last XLS example: GSPC ETF Trend Example 3

There you have it! A very simple and fun way for you to build a basic ETF trend system. Please feel free to modify the macro, or add to it as you see fit. If you have any questions or comments, please feel free to contact me.

Up next Building an AI financial market model - Lesson IV Calculating Historical Volatility Hi there!   This is one my most popular posts and I would love it if you became an RSS Reader! The inspiration for my S&P500 Volatility
Latest posts Revisiting GOOG, GE, NE, IYR from 2007 The Ye Old Blog List Motorola: Then and Now EWM Redux Testing for mean reversion with Python & developing simple VIX system - Talaikis unsorted - Tadas Talaikis Blog Steps to calculate centroids in cluster using K-means clustering algorithm - Data Science Central Basics of Statistical Mean Reversion Testing - QuantStart Algorithmic trading in less than 100 lines of Python code - O’Reilly Media Interpreting Machine Learning Models Microsoft the AI Powerhouse Investing in the S&P500 still beats AI Trading Microsoft makes a push to simplify machine learning | TechCrunch 10 Great Articles On Python Development — Hacker Noon Introduction to Keras Democratising Machine learning with H2O — Towards Data Science Getting started with Python datatable | Kaggle Phone Addiction Machine Learning Making Pesto Tastier 5 Dangerous Things You Should Let Your Kids Do The Pyschology of Writing Investing in 2019 and beyond TensorFlow and High Level APIs Driving Marketing Performance with H2O Driverless AI Machine Learning and Data Munging in H2O Driverless AI with datatable Making AI Happen Without Getting Fired Latest Musings from a Traveling Sales Engineer The Night before H2O World 2019 Why Forex Trading is Frustrating Functional Programming in Python Automatic Feature Engineering with Driverless AI Ray Dalio's Pure Alpha Fund