Building the backtesting model begins with designing the data handling layout. Will you be manually inputting the historical price-volume data? Will the data come from a DDE link directly into Excel, or will it come from imported files such as .csv or .txt.? What does the data format look like? Will the backtesting model use open-high-low-close-volume data or just one price point (for example, the close) per time period? Where will the data be stored? What target worksheet within the backtesting model will manage the data?
Once the data handling is designed, next comes the actual trading logic. This MUST be exactly the same as your actual trading model logic. The major differences are a) the backtesting model must repeat the same rules and conditions for every period in the historical data series, and b) the backtesting model must save the results of each trade for further analysis. This can be done by arraying Excel formulas in a calculation spreadsheet which references the historical data. It can also be done in VBA code which cycles through the historical data and outputs buy, sell, and out signals matched with dates and times into a spreadsheet. Or it can be done with a combination of formulas and VBA. our preference is to use VBA to move the data around and formulas where the calculations need to be visible and auditable. The approach is up to you.
Next, you must add the profit and loss (P&L or PnL or P/L) calculations. Depending on your model, this can be quite tricky. One key question is what execution price is assumed — if trading signals are calculated at price bar closes then the normal procedure is to assume trades are executed at the following bar’s open price. However, it is possible to use any other price, including a price several bars later, an average price (for example VWAP which is popular with asset managers), or a statistically estimated price such as one defined by linear regression or a density function.
Finally, a backtesting model must also have:
- Cumulative equity
- Transaction fees or commissions on a per-trade, per-contract or per-share basis
- Slippage amount or percent
- Statistics such as wins/losses, largest win/loss, expectancy, Sharpe Ratio, Treynor Ratio, etc.
- Ability to automatically save and categorize backtesting results for further analysis.
- Additional features such as position vs. trade calculations, variable or pyramiding position sizes, stop losses, graphical output, profit/loss distributions, “live real-time” interaction with the trading model, etc.
Coding and testing these calculations in Excel is a non-trivial exercise. It is critical to get the calculations correct, otherwise your trading model performance will be completely incorrect!
