Backtesting in Excel
Readers know that I use Excel to produce a lot of the simple backtests and graphics on this blog.
I get a ton of email about how I do that, so rather than continuing to help readers piecemeal, I want to provide a simple Excel template that readers can then expand upon in their own research: click for newer versions of Excel (XLSX) or legacy 97-03 versions (XLS).
Note: I don’t have Excel 97-03 on my computer, so I have no idea what the legacy XLS version will look like when you open it.
. . . . .
This is a simple 50/200-day moving average crossover strategy buying at the close on the day of the cross.
I’ve stripped it down to the bare bones, so it doesn’t include things like dividends, return on cash, transaction costs, or much in the way of analysis, but I think savvy readers should be able to take this basic template and build off of it.
Spreadsheet walk-through:
Columns A and B (shaded in grey) hold dates and closing prices, the only static information the user provides. Everything else is calculated automatically.
Columns C and D are the short and long SMAs. Note how I’ve used the “indirect” and “address” functions of Excel to allow you to change the length of each SMA in row 1 and immediately see the impact to the backtest. Note: save the workbook after changing the SMA lengths to ensure that the workbook finishes recalculating.
Columns E and F are the equity curves, starting with $10,000, for both the strategy and buy & hold.
Columns G and H are the daily price changes used to calculate annualized return and volatility, and columns I and J is a running drawdown calculation.
Important: If you scroll down to rows 15511 through 15514 you’ll find some simple summary stats like annualized return, max drawdown, etc. And there is also a chart of the two equity curves on tab 1.
. . . . .
I hope that gets interested readers moving in the right direction. Again, this is very bare bones and there are all sorts of nifty things you could add to this basic template depending on the task at hand.
Happy Trading (and Backtesting),
ms
. . . . .
To stay up to date with what’s happening at the MarketSci Blog, we recommend subscribing to our RSS Feed or Email Feed.
Filed under: Random Stuff | 21 Comments
Thanks so much, was looking for something like this a long time!
Thanks a ton for making this available. Appreciate it.
Wow, what a great tool. If anyone knows where to get daily dividend-adjusted closing prices for the S&P 500, let me know!
– Scott
Hello Scott – two options (that I know of): (1) you can use an asset with the dividends already baked in like VFINX or SPY, but then you’re not going to have acccess to that much historical data, or (2) you can interpolate daily dividends from monthly data from Shiller’s data set here: http://www.econ.yale.edu/~shiller/data/ie_data.xls. The downside of that approach is that you’re not capturing the true dispersion of dividends throughout the month but for longer term strategies like this one that isn’t really an issue. Note that if you go with option 2, you can compare your dividend-adjusted results with something like VFINX to make sure you’ve done it correctly.
Also note that usually with something like a 50/200-day crossover strategy I’ll assume the moving averages were calculated based on the price-only data, but assume trades were placed on the dividend-adjusted data (as this matches how most investors would trade the strategy).
Hope that helps,
michael
very welcomme, tnx!
Michael
I have tried reproducing RSI in excel recently and I seem to hit a glitch that I can’t figure out. Perhaps you or one of your readers can help since I know you coveted it in the past. My apologies if the explanation is somewhere else on the blog. I searched and couldn’t find it.
When I come across a multiday streak of wins/losses I get a 100/0 value. This does not to seem to happen in any of the third party charting services I use so I assume I am doing something incorrectly. Any help you be appreciated.
Hello Al – there’s a link on this page from stockcharts that should help: http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:relative_strength_index_rsi. Scroll down to the link that reads “Here’s an Excel Spreadsheet…”
Note that at least one of the columns (I don’t recall exactly) uses a different value for the first cell than subsequent ones. This trips a lot of folks up. Hope that helps. michael
I backtest in Excel but never knew how to set parms for moving averages like this. Thanks. FYI, In Excel 2003 the spreadsheet seems fine.
Glad to see your posting again! Don’t underestimate your contribution to the systematic traders out there.
Why go through all that work to get a dynamic moving average, “indirect(“b”&(row()-c$1+1))” should work equally fine.
Hello Emil – I’m always looking for ways ot make my “code” more efficient, but I’m not following you. Can you tell me for example exactly what cell C500 would say on the worksheet? Thanks in advance, michael
If c1=200, then indirect(“b”&(row()-c$1+1)) = b301, the whole formula would go something like “=avg(indirect(“b”&(row()-c$1+1)):b500)”.
We’re only trying making the first cells row in the range dynamic, so why not have all the rest static and save some computing time.
Cheers!
Hello Emil – still not seeing it – tried changing that cell to your suggested formula (changing “avg” to “average” and “row()” to “row(a500)”) and it’s giving me an error. Mind sending me back the sheet with your suggested mod? Email addy is michael AT marketsci DOT com. Thanks.
I wanted to help out since I enjoy your blog so much. The formula to put in cell C500 is:
=AVERAGE(INDIRECT(“b”&(ROW()-C$1+1)):B500)
The row(), without any arguments returns the current row in which the function appears.
When you copy this down to all columns it works, but I cannot tell what it is you are saving other than not needing the address function.
Your original formula for this C500 could be changed as well to something a little simpler, though I have no idea if the time it takes to run is affected much. You can again just use row() and then for the column, just use the number 2 since you always want column B.
=AVERAGE(INDIRECT(ADDRESS(ROW()-C$1+1,2)):$B500)
Thanks Brian – Emil and I had worked it out offline. That’s a very good tip. I suspect it saves horsepower, but I’m not sure. I work with some ginormous spreadsheets sometimes, so I was going to put it to work on those and see (will be nice if it helps me increase the size of the data set Excel can handle). michael
Hi, thanks, but is posible other excel for stocks?
Hello Jose – it’s possible, but not really suitable for Excel. When I test a large basket of stocks I usually use off-the-shelf software. I use an old (pre-Fidelity) version of Wealth-Lab. There are a ton of other options, I’m just not qualified to say which is the best. michael
Michael,
I have taken your Excel Spreadsheet and converted it to a Google Docs Spreadsheet. If anyone wants to copy it is available here: http://goo.gl/5TghL. I also took that spreadsheet and updated it to compare the strategies of closing price crossing the 200d MA and a hybrid of the 50/200d MA crossover strategy with price crossing 20d MA. Just to give it a name I called it the Golden 200. Basically you Sell on Death Cross. While in Death Cross, buy and sell at closing above or below 200d MA. Here is a link to that spreadsheet. http://goo.gl/uqUD9
Thanks for the original.
Nizer,
I’m looking at your excellent Google spreadsheet you posted above and then apparently expanded on and posted in the comments at The Big Picture:
http://www.ritholtz.com/blog/2012/01/revisiting-quant-approach-to-tactical-asset-allocation/
Your “Golden 200″ strategy seems too good to be true! The 200-day SMA and Golden Cross strategies both tied buy-and-hold, but Golden 200 outperformed by a full percentage point (7.9%, vs. 6.9% for buy and hold).
One downside seems the number of whipsaws. How can I calculate the number of transactions per year for the Golden 200? I don’t quite understand the spreadsheet since it doesn’t have formulas (what does the column mean with the 0.99, 1.00, and 1.01 values in it?) Maybe you can contact me through Facebook…
Scott
Scott,
Actually one of the reasons that I posted the spreadsheet is to get some eyeballs on it, because if it is not accurate I would like to know. I think it is good, but I always question my results. Also if you use the Yahoo Dow Data back to the 30′s it doesn’t do as well.
I had to take out most of the formulas, since Google Spreadsheets allows a limited number, but if you go to the bottom you will see the formulas. It takes a little time to load all the rows. The PC column is the percentage change from day to day.
I am not sure of the best way to track the buy/sells per year, but I added a new column with the formula =IF(F15598F15599,IF(F15599=F15600,1,0),0). It seems to get some false positives if there was no change in the closing price 2 days in a row, but the Golden 200 strategy seems to get about 2.6 buys per year.
Nizer