All traders can benefit from testing their trading strategies. It can highlight strengths and weaknesses and show how to improve as a trader. However, it is difficult to find an accurate way to test your trading strategies.
Excel is one of the most popular pieces of software in the world. Most people already have some skills at using Excel. In this article and accompanying video, I show how Excel can be used to test a wide variety of trading strategies on any market and timeframe.
Many people learn better by watching. I have recorded a YouTube video of me demonstrating how easy it can be to test your own strategies using Excel. In this video, I add historical data. I program 3 technical indicators. Finally, I enter the trade entry and exit criteria.
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 test 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.
It is vital to get good 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. MT4 is offered by many brokers and 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 quickly enter the data into your backtest. Do not use Cut and Paste because it might affect the formulas in the backtest spreadsheet.
Entry Signals – Technical Indicators and Chart Patterns
The next step to testing your strategy is to enter your trading criteria. Many people trade using technical indicators and chart patterns. These are based on mathematical formulas and can be calculated using Excel. In the video, 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 simply copy and paste it into your backtest spreadsheet.
Programming Your Entry and Exit Criteria
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 Stochsatic has crossed above the 20 line (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.
Exit criteria is programmed in 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.
Stop-Losses and Profit Targets
In this Tradinformed Backtest Model, I 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.
We can use Excel to calculate any results metrics that we 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.