Strategy Backtesting in Excel Strategy Backtesting Expert Overview The Backtesting Expert is a spreadsheet model that allows you to create trading strategies using the technical indicators and running the strategies through historical data. The performance of the strategies can then be measured and analyzed quickly and easily. During the backtesting process, the Backtesting Expert runs through the historical data in a row by row manner trading top to bottom. Each strategy specified will be evaluated to determine whether the entry conditions are met. If the conditions are satisfied, a trade will be entered. On the other hand, if the exit conditions are met, a position that was entered previously will be exited. Different variations of technical indicators can be generated and combined to form a trading strategy. This makes the Backtesting Expert an extremely powerful and flexible tool. Backtesting Expert The Backtesting Expert is a spreadsheet model that allows you to create trading strategies using the technical indicators and running the strategies through historical data. The model can be setup to enter excel Long or Short positions when certain conditions occur and exit the positions when another set of conditions are met. By trading automatically on historical data, the excel can determine the profitability of a trading strategy. Backtesting Expert Step by Step Tutorial 1. This launches a spreadsheet model with multiple worksheets for you to generate technical analysis indicators and run back tests on the different strategies. You will notice the Backtesting Expert includes many familiar worksheets like "DownloadedData", "AnalysisInput", "AnalysisOutput", forex and "ChartOutput" from the Technical Analysis Expert model. This allows you to run all your back tests quickly and easily from a familiar spreadsheet environment. First, select the "DownloadedData" worksheet. You can copy data from any spreadsheets or comma-separated values csv files to this worksheet for technical analysis. The format of the data is as shown in the diagram. Alternatively, you can refer to the "Download Forex Trading Data" document to download data from well-known data sources such as Yahoo Finance, Google Finance or Forex. Trading you have copied the data, go to the "AnalysisInput" worksheet and click on the "Analyze and BackTest" button. This will generate the different technical indicators into the "AnalysisOutput" worksheet and perform backtesting on the strategies specified in the "StrategyBackTestingInput" worksheet. Click on the "StrategyBackTestingInput" worksheet. In this tutorial you will only need to know that we have specified both a long and short strategies using moving average crossovers. We will be going into details of specifying strategies in the next section of this document. The diagram below shows the two strategies. Once the back tests are completed, the output will be placed in the "AnalysisOutput", "TradeLogOutput" and "TradeSummaryOutput" worksheets. The "AnalysisOutput" worksheet contains the full historical prices and the technical indicators of the stock. This information is useful if you like to trace through the strategies to see how the stock positions are entered and exited. The "TradeLogOutput" worksheet contains a summary of the trades carried out by the Backtesting Expert. The data can be easily filtered to only show data for a specific strategy. This worksheet is useful for determining the overall profit or loss of a strategy at different time frames. The most important output of the back tests is placed in the "TradeSummaryOutput" worksheet. This worksheet contains the total profit of the strategies carried out. Of these trades, 5 are Long positions and 5 are Short positions. Explanation of the different Worksheets This section contains the detailed explanation of the different worksheets in the Backtesting Expert model. The "DownloadedData", "AnalysisInput", "AnalysOutput", "ChartInput" and "ChartOutput" worksheets are the same as in the Technical Analysis Expert model. Thus they will not be described in this section. For a complete description of these worksheets, please refer to the Technical Analysis Expert section. StrategyBackTestingInput worksheet All the inputs for backtesting including the strategies are entered using this worksheet. A strategy is basically a set of conditions or rules which you will buy in a stock or sell a stock. For example, you may want to execute a strategy to go Long purchase stocks if the 12 days moving average of the price crosses above the 24 days moving average. This worksheet works together with the technical indicators and price data in the "AnalysisOutput" worksheet. Hence the moving average technical indicators have to be generated in order to have a trading strategy based on moving average. Imagine the scenario where conditions for purchasing a stock has occurred and the Backtesting Expert entered a Long or Short trade. However the time frame is too short and has ended before the trade can meet the exit conditions, resulting in some trades not exited when the backtesting session ends. Else, the trades template be left opened when backtesting session ends. Strategies A maximum of for strategies can be supported in one single back test. The diagram below shows the inputs required for specifying a strategy. The Strategy Initials is used in the "AnalysisOutput" and "TradeLog" worksheets for identifying the strategies. 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. Functions crossabove X,Y - Returns True if column X cross above column Y. 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. YY - Column YY ZZ - Column ZZ This is the most interesting and flexible part of the Entry Conditions. It allows columns from the "AnalysisOutput" worksheet to be specified. When the back tests are for out, each row from the column will be used for evaluation. On top of that it can also forex use of Variables as template below. Variables for Exit Conditions profit This is for 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 - Trading average value profit or loss of a single trade of the simulated back test. Largest winning Trade - The profit of the largest winning trade. Largest losing Trade - The loss of the largest losing trade. A ratio of greater than 1 indicates a profitable strategy. TradeLogOutput worksheet This worksheet contains all the trades simulated by the Backtesting Expert sorted by the date. It allows you to zoom in to any specific trade or time frame to determine the profitability of a strategy quickly and easily. Date - The date where excel Long or Short position is entered or exited. Strategy - The strategy that is used for executing this trade. Position - The position of the trade, whether Long or Template. Trade - Indicates whether this trade is buying or selling stocks. Shares - Number of shares traded. Price - The price in which the stocks are purchased or sold. Both the commission on entering the position and exiting the position are accounted for on position close. Trademark Acknowledgements Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. Avery is the trademark of Avery Dennison Corporation. All other product names are trademarks, registered trademarks, or service marks of their respective owners. Subscribe to our newsletter. Back to TraderCode Technical Analysis Software and Technical Indicators. Copyright cConnectCode Pte Ltd.
Adopting straight away the scientific view that humans are nothing more than physico-chemical mechanisms, he declared that the task for philosophy is to work out an account of the mind which is compatible with this view.
And if within the realm of historical investigation, a more seductive charm lies for him in the analysis of great personalities than in ascertaining the course of historical development, then verily may he look about in vain for such personalities in the antiquity and middle ages of India.
The first (literal) iconic image is in some way imprinted on the second (iconic) image.
Adopting straight away the scientific view that humans are nothing more than physico-chemical mechanisms, he declared that the task for philosophy is to work out an account of the mind which is compatible with this view.
Therefore, primary and secondary neural lineages co-existed as isolated model systems.
And if within the realm of historical investigation, a more seductive charm lies for him in the analysis of great personalities than in ascertaining the course of historical development, then verily may he look about in vain for such personalities in the antiquity and middle ages of India.
The Living I Ching: Using Ancient Chinese Wisdom to Shape Your Life.