There are several ways to backtest an Excel trading model.
You can do it visually by recording the buy, sell, and out signals given by your model in an Excel spreadsheet, including the date, time, and theoretical trade prices. This is very slow and cumbersome. Further, backtesting manually only gives you a rudimentary idea of your model’s performance.
Alternatively, you can code your model with dedicated software. This requires some up front investment. It also requires you to translate your Excel model’s logic into the logic required of the software. We will skip this option, since this article is dedicated to backtesting in Excel.
The best method for backtesting your Excel trading model is to use Excel itself to implement a backtesting system. Excel is ideal for this task, as it is a virtual “Swiss Army Knife.”
What you want to do is replicate the buy/sell logic of your trading model and enhance it to operate over a long historical period, generate a series of buy/sell/out signals, calculate per-trade and cumulative profits and losses, and output a variety of return-risk statistics. You may want to further enhance the backtesting model with Monte Carlo simulation. You may add the capability to backtest an entire portfolio or watch list of securities at one time.
