In this project, you will conduct a basic back-test of a purely technical trading strategy, based on the simple moving average (SMA). The strategy is that you will buy the stock if it moves above its 50 day simple moving average by at least a certain threshold percentage, and sell it if it shoots below the SMA50 by the same percentage.
The included spreadsheet is already set up to test the strategy, given a time series of daily closing prices (using the “adjusted close” data, which is adjusted for stock splits and dividends). The spreadsheet is loaded with daily adjusted closing prices for SPY (an S&P 500 index ETF). The default settings are a threshold of 5%, and a trading fee of $10 per trade (a realistic fee you might pay per trade on an actual brokerage account).
Your first task is to examine the structure of the spreadsheet, and understand what it is doing.
Then, use the spreadsheet to answer the following questions:
- With the default settings, what is the date on which youM first buy the the ETF?
- With the default settings, what is the date on which you first sell the ETF?
- With the default settings, what is your total return (as a percentage) over the period from the start of the data series (day 50, where SMA50 is first available) to the end, using your trading strategy?
- What is the total return (as a percentage) of the buy-and-hold strategy of buying the ETF on day 50 (first day that SMA50 is available), and selling at the end?
- Looks like the trading strategy with the 5% threshold isn’t working too well compared to buy-and-hold, eh? Let’s try to tweak the threshold percentage, and see if we can come up with a better result! Tweak the percentage in the range of 0 to 20 (stick to integers), and see which threshold produces the best result. What is the best result you can achieve? What threshold percentage gets you there? Does it beat the buy-and-hold strategy?
- Bonus question (+5%): adjust the spreadsheet to use the 200-day SMA, then tweak the threshold between 0 and 20, as before. Can you beat the buy-and-hold strategy? What is the best result you can achieve?
- Let’s try a different asset. Go back to the default settings, and then import the daily adjusted closing prices for Microsoft (MSFT), for the same time range – 1993-01-29 to 2014-10-28. (On finance.yahoo.com, go to historical prices for MSFT, then download the CSV file by clicking the ‘download to spreadsheet’ button at the bottom.) Don’t forget to re-sort the data series to go from oldest to newest. What is the total return of your portfolio when trading MSFT with a threshold of 5%, using SMA50?
- What is the total return using buy-and-hold strategy on MSFT?
- Tweak the threshold between 0 and 20, and see if you can beat the buy and hold strategy. Can you? What is the best result you can achieve, and at which threshold does it occur?
