How to Back Test a Trading Strategy Using Excel
A good trading strategy is the key to successful trading, whether it is in forex or any other financial product. But, different strategies work in different timeframes and market conditions. Not all strategies fit in with the trading style and the time availability of all traders, therefore need to be chosen carefully and after thorough evaluation. So how does one develop a good strategy? The answer is trial and error, wherein traders choose several strategies that fit with their style, try them and then zero in on one or two of them. Is that all? Unfortunately, no – what is required now is the back testing of the selected trading strategy to ensure its efficacy.
Back testing of a trading strategy is done by using past performance figures. It is highly useful for weeding out non-profitable strategies, while finding out the weaknesses of the others. The whole process of back testing of a strategy is a learning experience that enables traders to establish certain trading principles, besides modifying the strategy to remove any lacunae. Now, back testing can be done by using different platforms but the simplest of them is Microsoft Excel. Let’s find out how.
Back Testing in Excel
Microsoft Excel is easily accessible and known to most people. Its user-friendly features and the vast amount of online information about how to use it aid in the whole process of back testing. So, how and where does one start? The first step is to get a data set that can be tested. This means that one needs to get a series of dates or times and the prices of a selected currency or stock. So, what needs to be done is to acquire the open, high, low and close prices of a chosen currency or stock for some dates. In case of intraday trading strategies, one needs only the time series, but if the testing is for inter-day strategies, data for several days is required.
This data can be acquired from Yahoo Finance or Google Finance, or any other Finance site providing prices of various stocks and currencies by entering the symbol of the stock or the currency pair. One can download the historical data and load it onto Excel, although this is a cumbersome process. Yahoo Finance offers an option to directly download the selected information to an Excel spreadsheet. Some third-party tools can also be used for copying the data into an Excel sheet. Do this, name the file and save it.
The next step is to remove the columns and data that are not required. Some traders may require only the open and close values and can therefore delete the high, low and volume levels related to the chosen timeframes. Also, ensure that the data has been displayed from the earliest date to the latest date. This can be done by using the Data>Sort menu option in the Excel.
Once the data for back testing is ready, you need to select the indicator or the formula to calculate the indicator and specify the trading rules as per the strategy being tested. Several add-ons for Excel, containing a large number of predefined indicators for back testing, are available in the market.
Trading rules like going long when an indicator reaches a specific level or going short when it reaches another level are used. Other rules related to remaining neutral or taking variable positions are also specified at this time. The next step is to assume a starting cash value of say $10,000 or 15,000 and then adding or subtracting from it on the basis of the short or long positions taken during the testing period. The returns for each day get calculated automatically by using the formula specified. The results can then be depicted on a graph.
Things to Remember While Back Testing
One point to remember while using this back testing method is that we are not taking into account any commissions or spreads for the trades carried out. But in reality, commissions can have a significant impact on the actual outcome of a strategy and its viability. This is particularly important for high swing trades. We can use the chosen data to find out various ratios, like the CAGR and Sharpe ratio, to compare the particular strategy with the results of any other back tested strategy.
Do remember, while back testing a trading strategy, that what was profitable historically may not be so now because of several factors and changing conditions. Also, a good back test does not guarantee profits because of several assumptions made during the process. Having said that, it is still better to back test a strategy to find out its flaws and its performance in comparison to other strategies before using it in real time.
Disclaimer
If you liked this educational article please consult our Risk Disclosure Notice before starting to trade. Trading leveraged products involves a high level of risk. You may lose more than your invested capital.
