Test these strategies out and see for yourself whether you can compose a solid trading system. This is something you can only do with sufficient experience. Even though I've only been demoing the trial version I'm glad I found it.
That is, it is often possible to find a strategy that would have worked well in the past, but will not work well in the future. Despite these limitations, backtesting provides valuable information not available when models and strategies are tested on synthetic data. Simba's backtesting spreadsheet [a Bogleheads community project]. The latest version and download instructions are in this post , which links to Google Drive.
Here is a brief overview of the individual worksheets:. The spreadsheet also includes statistics and charts for a portfolio that is rebalanced annually default and one that is not rebalanced un-rebalanced. The spreadsheet provides an extensive set of historical returns for various types of index and non-index funds.
Most funds are from Vanguard, and all corresponding historical returns have been validated with Vanguard. All returns are expressed as total returns, i. The perspective of a U. When actual fund returns are not available e. In general, fairly good quality data is available starting in for U. Most International returns are available starting in Most sector returns aren't available before The Entry Conditions can be expressed as a formula expression. The formula expression is case sensitive and it can make use of Functions, Operators and Columns as described below.
This function checks the previous periods to ensure that a crossover has actually occurred. Returns True if all the logical expressions are True. Returns True if any of the logical expressions are True. It allows columns from the "AnalysisOutput" worksheet to be specified.
When the back tests are carried out, each row from the column will be used for evaluation. On top of that it can also make use of Variables as shown below. Variables for Exit Conditions profit This is defined as the selling price minus the purchase price. The selling price must be greater than the purchase price for a profit to be made. Otherwise the profit will be zero. Otherwise profitpct will be zero. Otherwise losspct will be zero. The percentage commission and commission in dollars will be summed up to calculate the total commission.
TradeSummaryOutput worksheet This is a worksheet that contains a summary of all the trades carried out during the back tests. The results are categorised into Long and Short Trades. A description of all the fields can be found below. This value is calculated by summing all the profits and losses of all the trades simulated in the back test.
Total Commission - Total commission required for all the trades simulated during the back test. Total number of Trades - Total number of trades carried out during the simulated back test. Number of winning Trades - Number of trades that make a profit.
Number of losing Trades - Number of trades that make a loss. Percent winning Trades - Number of winning trades divided by Total number of trades. Percent losing Trades - Number of losing trades divided by Total number of trades. Average winning Trade - The average value of the profits of the winning trades.
Average losing Trade - The average value of the losses of the losing trades. Average Trade - The average value profit or loss of a single trade of the simulated back test. It is also goes through the results and carries out a step-through analysis. The cell references will depend on which data you are using in which columns.
However, once you understand the trading strategy that is being tested it should be easy to adapt the formulas to your own spreadsheet or backtesting system. The backtest was carried out over three periods of 20, 1-hour periods 3 years, 3 months.
If you are interested in using Excel to backtest trading strategies my new Ebook course: My name is Mark Ursell, and I am a full-time individual trader and investor.