×

Authorised and Regulated: FCA UK / SCB
×

How to Back Test a Trading Strategy Using Excel

Computer with graphic cards for bitcoin mining

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.

Welcome to Blackwell Global

Blackwell Global operates across a range of jurisdictions, please see the options below.
Please see below the protections you'll waive by not continuing to the UK website.
switching to the global website.
By clicking Switch to Global website, you acknowledge the information below.

Best Execution

Protections you'll keep


Blackwell Global will ensure to always act in the best interest of our clients by offering a best excution policy across our wide range of trading products

Segregated Account

Clients funds will be stored in segregated client money accounts, we ensure security of funds With tier 1 banking parnters.

Balance Protection

Blackwell Global will uphold our commitment to clients by offering negative balance protection.

Leverage Restriction

You will not be protected by the leverage caps for retail clients under the FCA UK entity for CFD trading. 200:1 is the maximum leverage under our global entity. The protection of 1:30 is the maximum leverage under our UK entity

Restriction on Incentives
Restriction on Incentives

Clients and partners will not be protected by FCA restrictions on Incentives to retail clients and traders, Under our FCA entity no trading incentives may be offered.

Protections you'll waive

Start Trading Now

Tight Spreads from 0.1 pips

Trade Micro Lots

FCA Regulated

Segregated Funds

Personal Account Manager