May 16, 2007
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.
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: 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 ETF’s (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.
May 16th, 2007 at 9:59 am
Good stuff.
May 17th, 2007 at 5:00 am
gzone: I’m glad you like it. Check back often as I post more Excel stuff.
May 17th, 2007 at 12:26 pm
Tom – this is a nice building block. Takes advantage of a simple statistical model, and couples the trading intuition of multiple time periods.
A question: what is the importance of having the open, high, and low if the slope coefficient is calculated off of the close?
May 17th, 2007 at 12:27 pm
On an aside, when I copied and pasted the macro from the post text, it gave me an error (I think unidentified sub). The work around was to simply open your example and replace the data with the desired ETF. No problems then.
May 17th, 2007 at 12:38 pm
CPP: I might just post a txt file of the macro, it looks like when I pasted it into the blog it didn’t format it right.
I used the open, high, low quotes for other statistical analysis in my algorithmic backend. I might post those calculations another time. :)
June 27th, 2007 at 9:20 am
Tom, the error might due to the slope function,
it calculates difference between 2 points for the gradient using
column A, if data not by date, it might return error
June 27th, 2007 at 9:44 am
W. Chee, column A is used as the “X” values in calculating the slope function.
You’re right, if you don’t use the Date for “X” values, you could get an error in the function.
August 4th, 2007 at 12:51 pm
So is the basic idea to buy when all three slope calculations are positive? I’m not clear on buy/sell logic that would be applied.
Also, I did a search on Maoxian’s site and couldn’t find anything about Mr. Fuzzy – where did you find the basic information the basic approach?
thanks!
August 5th, 2007 at 5:53 am
Damian: I used to trade using the system this way, if the 26 week trend was bullish then I would look to go long. I would use the 13 week to find “dips” for to buy, and the 8 week as a confirmation indicator to make sure there was some momentum behind my buy.
August 5th, 2007 at 5:57 am
RE: Mr. Fuzzy, do a search on his site about Fuzzy. He uses some proprietary system that he won’t share with us. I developed this system based on his ETF tables he used to post. My goal was to use trial and error to mimic his signals. After a while I began to get the same signals he did on a weekly basis, although I suspected he used a 20 week signal instead of a 26 week.
August 5th, 2007 at 8:03 am
Thanks for the reply Tom – interesting stuff. I take it you don’t trade the system any longer?
I’ve also been evaluating a system using 1-3-6-12 month (and any variation on that) returns. I’m thinking of combining the two to get confirmations on signals.
August 5th, 2007 at 8:24 am
Damian, I still use it as part of my Excel ATS but had to stop development on it because of time limitations. I don’t actively trade anymore, rather I try timing the market and move cash into funds when the market panics.
December 29th, 2007 at 9:33 am
[...] Historical VolatilityBuilding an AI financial market model – Lesson IBuild an ETF Trend System in ExcelBuilding an AI financial market model – Lesson IIIBuilding an AI financial market model – Lesson [...]
January 1st, 2008 at 9:33 pm
[...] Historical VolatilityBuilding an AI financial market model – Lesson IBuild an ETF Trend System in ExcelBuilding an AI financial market model – Lesson IIIBuilding an AI financial market model – Lesson [...]
February 19th, 2008 at 8:42 am
On an aside, when I copied and pasted the macro from the post text, it gave me an error (I think unidentified sub).
February 21st, 2008 at 6:00 am
excel chic: Try downloading the example spreadsheet #2 or #3, the macro is already loaded into it.
November 13th, 2008 at 4:44 pm
One thing to note is the fitness of the linear regression, if one or more of the linear regressions are not particularly in terms of the R squared statistic you might be able to infer that the movement is choppy, whereas good R squared will not be proof but it will be an indicator that your signals are telling you the right thing.
Better to have no signal if it is going sideways than a false positive.
November 16th, 2008 at 5:34 pm
Jason: agreed. I talk about the coefficient of determination (R^2) in one of my blog posts somewhere.
April 12th, 2009 at 9:11 am
I always am greatly amused when people who have just discovered “automated arithmetic” methods try to apply them in predicting the markets. I have developed, invented, and written NN codes and models for MUCH more than a decade. I have even imbedded genetic algorithms in the back prop sections of consensus nets. I have used specialized wavelet bases and independent component analysis etc. etc. In short, I have used more advanced techniques than you have even (yet) dreamed of. What is my point? Financial markets and their instruments CAN NOT BE PREDICTED with any regularity that will produce a consistent profit. Eventually you will discover that the ONLY way to consistently make money using computational “stuff” is to sell the “stuff” to the wide-eyed. But, have fun!
April 12th, 2009 at 3:19 pm
Jim: Thanks so much for commenting. Do you have a background in programing, research, or both? I’m curious to find out how you ended up in this industry and applied your coding to the financial markets.
January 12th, 2010 at 11:45 am
buenos dias ¡
disculpe thommas Ott, le escribo ya que me gustaria ver todos los videos de rapid miner 5.0 pero no le entiendo muy bien a la velocida que usted habla ingles para mi es demasiado rapido so me gustaria que me diera un link donde este los videeo tutoriales ya que son muy utiles para mi
esperando respuesta favorable me despido de usted
GRACIAS
January 14th, 2010 at 5:40 am
Graciela,
I don't understand your question. The links to all my videos are in the Tutorial section.