How to Backtest a MACD Trading Strategy using Excel

Last Updated on November 25, 2019 by Mark Ursell

Learn How to Backtest Your Trading Strategies Using Excel

Do you want to improve your trading skills and profitability?  If so, one of the best ways is to learn how to test your own trading strategies. Tradinformed Backtest Models are built in an Excel spreadsheet. They are a way for anyone to test their strategies with different indicators, different markets and different timeframes.

If you want to see, them being used see the page: How to Use a Tradinformed Backtest Model. To see the latest model go to the Tradinformed Shop.

Constructing the Backtest Model

To help explain the process I have recorded two videos which you can see below. Building a spreadsheet to backtest a trading strategy can take a bit of time depending on the complexity.  However, once the spreadsheet has been constructed it can easily be altered to test different types of trading strategy. In these videos, I have used a number of nested IF functions to reduce the number of columns.  When you are building a spreadsheet model for the first time it can be a good idea to use separate columns for each IF statement.  By separating the IF statements, it is easier to spot mistakes and work through the logic of the trading strategy.

Trading with the MACD Indicator

I like the MACD indicator on the larger timeframes, particularly on the daily and weekly charts.  The MACD does not try to pick market tops or bottoms but it does often find a decent trade entry point in the direction of a trend.  It is easy to adjust the parameters of the indicator using the spreadsheet in the videos. In this video, I am using the MACD on its own, but it can be combined with other indicators such as moving averages.

Part 1

Part 2

Formulas Used in the Videos

Entry Price =IF(trade entry =”trade”,close,prev cell)
Take Profit =IF(trade entry =”trade”,close+(atrTP factor),prev cell) Stop Loss =IF(trade entry =”trade”,close-(atrSL factor),prev cell)
Profitable Trade Profit =PT%capital prev period Losing Trade Loss =SL%capital prev period
Trade Running =IF(OR(trade entry column=”trade”,prev cell =1),if(high<take profit,if(low>stop loss,1,0),0),0)
Capital =IF(trade running prev period=1,IF(high>take profit,prev cell+profitable trade profit,IF(low<stop loss,prev cell-losing trade loss,prev cell)),prev cell)+rollover
Rollover =IF(trade running prev period=1,((entry price-entry price prev period)/(profit target-entry price))*profitable trade profit,0)
Trades =capital-capital prev period
Drawdown =1-(capital/MAX(capital prev period:capital first period)