EOD Portfolio BackTesting Take II with the Full Monty

[ajcode]

Okay, thought I'd start again on Portfolio Backtesting and create a new thread. This is a tough thread to explain because of the Nightmare programming involved.

So lets get to the good stuff first. Assuming you have created your list of trades from any number of software packages (e.g. Metastock, Amibroker, Multicharts, Excel, Matlab, Weathlab, Methlab, etc.) and store these trades in a COMMA separated file (.csv) format. The format has to be the exact format as specified below:
________________________________________________________________________________
Trade Type, Direction, Ticker/Symbol, Quantity, Entry Price, Entry Date, Exit Price, Exit Date, Stop Price, Cost

Trade Type -> 0 = Normal Trade, 1 = Cancelled Trade, 2 = Exit at Open Trade, 3 = Open Trade
Direction -> 0 = Short, 1 = Long
Ticker/Symbol-> For Example BHP

And the rest is self explanatory. With the Trade Type, you will probably only ever use Normal trades for backtesting, so set it to 0.
________________________________________________________________________________

Using excel (and later on I might add python), I have written macros that call my backtesting DLL to perform a single backtest by first reading the csv file created in the format above. The macros then extract the resultant trade list from the dll to display the statistical results. This all happens at the click of a button. There are 3 buttons, Clear Results, Single Simulation and Monte Carlo. The Monte Carlo button simply runs the backtest a multiple number of times to create multiple trade lists that are unique. And as can be seen in the complicated VBA code, a UNIQUE trade list is one that results in a different total profit to all the other trade lists processed, otherwise we discard it. There are some settings in the spreadsheet that you can set such as Max Capital and Max Orders to help simulate how you would trade in real life. There are some charts that are displayed too (The good stuff).

So first I shall attach the 32 and 64 bit DLL's here, and in the following post attach the excel file used to call the DLL and sample trade list files along with some pretty pictures. The DLL files accept up to 400 trades, just in case this goes Platinum LOL

 

[ajcode]

Once you have downloaded all files, if you are using 32 bit excel, use 32 bit DLL, if using 64 bit excel, use 64 bit DLL. Extract all files into the same directory as shown below:



Open up PBT.xls (need to accept macros) and you should be able to click the buttons to perform a backtest on default sample trade list file Long.csv. Here is an example where we click on Single Simulation using Windows 7 64 bit with 64 bit Excel:



And here is an example where we click on Monte Carlo using Windows XP 32 bit with 32 bit Excel (Sample trade list file Short.csv):



There are some other sample files, but they are only small and show how other trade types are to be inputted. These are better explained in my testing post next (very boring stuff).

Attached is file PBT02_20150828.zip, which contains the following files:

PBT.xls
Long.csv
Short.csv
InsertCancelled.csv
InsertExitAtOpen.csv
InsertOpen.csv

Okay that's the fun stuff. The next post will explain how the dll works in greater detail, I do this by hoping to get the user to look at the code in another excel file and execute the macros by pressing ALT-F8. There are no buttons.

 

[ajcode]

Attached is excel file BTDLLTest.xls. Put this in the same directory as DLL. To view/run macros, you need to Press ALT-F8. This can be best explained in the following diagram:



Hopefully this explains most of the complicated workings of the DLL in greater detail. It is advisable to not just run the code, but look at how it works by clicking Edit button.