All traders can benefit from testing their trading strategies. It can highlight strengths and weaknesses and show you how you can improve as a trader. However, it is difficult to find an accurate way to start backtesting a trading strategy
Excel is one of the most popular pieces of software in the world. Most people already have some skills
Many people learn better by watching. I have recorded a YouTube video of me demonstrating how easy it can be to test your strategies using Excel. In this video, I add historical data. I program three technical indicators. Finally, I enter the trade entry and exit criteria.
The Framework for Backtesting a Trading Strategy
Every time you test a trading strategy you are doing the same things over and over. You do not want to start with a blank template every time you need to analyze a strategy.
You should develop a framework for how to develop a trading strategy. I use a Tradinformed Backtest Model as a framework to test all my trading strategies. These models include many useful features including stop-losses, profit targets and trailing stops. They also include a variety of different metrics to analyze the performance of the trading strategy.
How to Backtest a Trading Strategy in Excel
- Enter Your Historical Data
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.
- Enter Your Technical Indicators
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.
- Program Your Entries and Exits
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”)
- Programming Entry Criteria
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.
- Programming Exit Criteria
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.
- Enter Your Stop-Loss and Profit Targets
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.
- Review Your Results
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.
What Difference Would This Make To You?
Learning how to backtest can make the difference between a confident trader who knows when to take profits and when to cut his losses, and an uncertain trader who changes strategy after a few losses.
With a reliable and well-tested strategy, you will be able to relax when you trade the markets. Alert for opportunities and enjoying lots more winning trades.
Taking the time to learn the basics of backtesting a trading strategy will pay off many times over by keeping you in winning trades longer and helping you avoid losing strategies, habits and difficult markets.