Backtesting a Trading Strategy – Example

Download and import historical data from a provider such as Yahoo Finance.

It is vital to get good quality historical price data before backtesting. It is easy to get daily and long-term price data often for free. Yahoo Finance has got a huge range of different markets.

To get intraday data is more difficult. I use MT4 for my forex trading. Many brokers offer MT4 and it has the advantage that it allows you to download data directly from the terminal. To download the data you need to select Tools – History Center and then choose the market to export.

Once you have the historical data in a spreadsheet, you can use Copy and Paste to enter it into your backtest quickly. Do not use Cut and Paste because it might affect the formulas in the backtest spreadsheet.

The next step in backtesting a trading strategy is to enter your trading criteria. Many people trade using technical indicators and chart patterns.

Indicators and chart patterns are based on mathematical formulas and can be calculated using Excel. In the video above, I demonstrate how to quickly calculate an Exponential Moving Average, a Stochastic Oscillator and the Average True Range. You can see from the video that it does not take very long to do this.

Most of the time you will not want to calculate the indicators from scratch. To make this quicker and easier I have written two eBooks that show how to calculate a range of technical indicators and chart patterns. To get more information check out: Improve Your Trading Results by Calculating Technical Indicators and Get Better Trading Results Using Technical Indicators. Both of these come with a spreadsheet containing all the indicator calculations.

Once you have the indicator in a spreadsheet, you can copy and paste it into your backtest spreadsheet.

This bit can be challenging for people who are not used to IF Statements in Excel. If Statements are the key building blocks of all trading logic.

We want to enter trades under specific conditions. This could be when the MACD has crossed the 0 line, a Doji Candle has formed or the price has reached a certain Fibonacci level.

The syntax for If Statements is: IF (Logic) – is True then do this – is False then do this.

In Excel, we might want to use an If Statement to check whether X is greater than Y. The formula would look like this:
=IF(X>Y, “X is Higher”, “X is Lower”)

In the video, I used a trade entry criteria of entering Long when the price is greater than the EMA and the Stochastic has crossed above the 20 level (oversold line). My Trade Entry criteria are in Column R.

The first cell contained: 
=IF(AND(F203>G203,K203>Results!$C$12,K202<Results!$C$12,AC203=$AC$3), “Long”,””)

We can make more sense of this if we translate it into pseudo-code. This means using normal language to explain each step. In pseudo-code, the statement reads:

IF (Close > the EMA AND Stochastic > Oversold Line AND Previous Stochastic < Oversold Line AND no Long trades are Open), Then Enter Long, Otherwise do Nothing.

You program Exit criteria exactly the same way as entry criteria. In this case, I might want to exit a Long Trade when the stochastic moves above 80 (overbought line).

In Excel I used the code: 
=IF(AND(K203>Results!$C$13,U203=0,T203=0,AC203=$AC$2),”Close”,)

In pseudo-code, this means: IF(Stochastic > Overbought Line AND Stop-Loss has not been hit AND Profit Target has not been hit AND Long Trades is Open, Then Close Long, Otherwise do Nothing.

In a Tradinformed Backtest Model, you have stop-losses and profit targets programmed already. They are calculated using a multiple of the ATR. This means that they are dynamic and adjust to market volatility.

You can use Excel to calculate any results metrics that you want. In this spreadsheet, I use a variety of methods to see how profitable the strategy is. The Profit Factor measures the absolute value of the winning trades divided by the losing trades. The win percentage tells us how many trades are profitable compared to how many are losing. I also compare the value of the average winning trade with the average losing trade.

I also use a Capital Graph to get a visual impression of the trading strategy over time. This will show whether the results have been consistent or they have happened during specific market conditions.