backtesting tool V2

When I had just started trading on eToro I realized that I needed a possibility to test my trading strategies and approaches. After a bit of googling I found ideas and information how to set up my own backtesting tool in a spreadsheet. So I did it, I set up my first backtesting tool (click here for more information). I quickly realized, that possibilities are limited with a spreadsheet based tool. Therefore I started developing a macro-based tool, which I will further describe in this post.

Drawbacks of a spreadsheet based tool

First, let's have a look at the major disadvantages of my spreadsheet based tool that appeared to me when I tried to optimized my strategy:

• One market day (my time base for now) is represented in one row of the spreadsheet. This includes both, market data and trades. Thus it is not possible to have more than one trading action on a day. For example closing a long trade and open a short trade on the same day is not possible.
• It was rather difficult to keep an overview of the trades, especially if a longer period of time is considered for calculation. 
• It was difficult to switch between different signal systems, since the underlying formulas had to be replaced and adjusted.

I was looking for a more flexible approach and finally decided to move on with a VBA macro based tool. Thus I could eliminate all the above mentioned drawbacks of my first verion.

Macro based backtesting tool

My backtesting tool V2 is made up of four major modules, that are shown in the picture and described below. Every module has a differentiated functionality and the interfaces between the modules are defined. With such an approach it is possible to work on one of the module without compromising the others. Furthermore market days and trades are programmed as objects, which makes the source code clear and the programming itself easier. 

database and indicators

So far I used the market data of DAX on a daily basis, including opening and closing rates as well as daily lows and highs. This data has to be feeded into a spreadsheet and is later automatically imported by the tool. The calculation of the market indicators is performed directly in the spreadsheet for two simple reasons: First, the indicators I am using so far can easily be calculated in a spreadsheet thus leaving no real advantage for a macro implementation. Second, the indicators don't have to be calculated on every strategy trial run, which saves computation time.

signal system module

Once the market data and the indicators are imported to the macro, they can be analyzed for buying and selling signals. It is also possible to perform further calculations or combine different indicators freely. The output of the modules are signals for opening and closing buy and sell trades. As it turned out, finding the right signal system is the hardest part of all. So there is still much potential for improvement.

trading module

This module does all the trading. It checks running trades for stop loss and take profit limits and processes the signals given by the signal system module. This module also includes the risk and money management as well as the accounting. That means it calculates the fees on the trades, profits and losses. Every action is documented in the trade object itself, so that it is easiyl accessible for the repoting module.

reporting module

The reporting module writes the major results of the macro back into the spreadsheet. In the picture below is an example for such a  report. It shows a list of all trades performed in the considered period (not all are shown in the screenshot) including detailed information e.g. about opening rate, closing rate and profit. In addition it compares the performance of the strategy to a simple hold and buy strategy of the underlying asset. In the case shown below the strategy would generate a gain of 37% in 5 years, while the asset itself gained 58% - time to work on the actual strategy!

***

Related posts:

Strategy backtest pt.10 - copper trading strategy

Strategy backtest pt.9 - "past performance is not an indication of future results"

strategy backtest pt.2

Evaluating the strength of a trading strategy
First trading strategy backtest

***

Important remark: The results presented above and throughout my blog are no recommendation for your trading! I only share my personal findings and opinions to give ideas and let my followers and copiers know what I am currently working on. I can not guarantee the correctness of my calculations and my presented results. Furthermore past performance is not an indication for future results. Only trade with money you are prepared to lose!