Navigation: Using an Imported Trade List >

Importing IB Flex Query Output

 

 

 

Users of Interactive Brokers have the ability to create a special report called a "Flex Query" to generate a list of all trade transactions from any time period.

Alternatively, the Trade Log window in TWS can also produce a shorter-term transaction list in a different format.

Because the output of these queries is a transaction list, it can be difficult to consolidate all of the individual transactions in to round-trip trades.

RealTest handles this task automatically, and supports either IB format or any transaction list in similar format.

You do not need to tell RealTest that you are using an IB transaction list of either format vs. a standard RealTest trade list -- it will automatically detect the format of the input file.

Here is an example of the type of IB flex query output that can be used as an imported trade list in RealTest:

Here is an example of TWS log output:

RealTest simply requires that six basic columns be present: Symbol, Action, Quantity, Price, Date, Time.

Optionally, Date/Time can be combined in one column, as in the first list, or be separate columns, as in the second list.

If a commission column is present, it will also be used.

If an "Order Ref." or "Order Reference" (any column with "ref" in its name) is present, that will be interpreted as a strategy name filter if at least one value from this column matches the current

strategy's name.

Note that the time values, whether in their own column or as part of Date/Time, MUST include SECONDS if your transaction list includes any groups of transactions where buying and selling of the same symbol occurred within the same minute. Beware that if you edit a CSV file in Excel, it will DELETE the seconds from all date/time values, which is very annoying.

The rows and columns can be in any order -- RealTest will sort them before using them.

In the above example, Quantity is positive for buys and negative for sells. This is not a requirement. The Buy/Sell or Action column is used to determine the direction of the transaction, and the absolute value of quantity is always used.

Similarly, the Commission values do not have to be negative -- their absolute value is used as well.

Finally, in some of the above examples, dates use mm/dd/yy format. If you have changed RealTest's Program Options to use dd/mm/yy format, then you must also use this format in all imported trade lists. RealTest uses this setting to decide how to parse dates in CSV files, so this is an important consideration.


To use a transaction list like this in RealTest, simply add a Strategy with the any name, and use TradeList to provide the path to the CSV file:

If your IB orders happened to include an order reference field containing your strategy names, you could use those same names here, to filter the trade list by only including trades for that specific strategy. A script can define multiple strategies using the same tradelist input file as needed.

Next you will have to import a data file that covers the date range of your trade list and includes all of the symbols it contains.

A frequent issue that grows worse the farther back you go is that symbols may have been changed or delisted since the trades in the report were made.

When you run the test, the trade list is parsed, the transactions are consolidated, and each symbols is looked up in the currently active data file.

When symbols are not found, they are written to the log window as follows:

To remedy this situation, you can optionally create a special file in your RealTest folder (c:\RealTest by default) called symchanges.csv.

This file, if present, will be used as a kind of map, to look up each symbol in the trade list and translate it during the parsing of the list.

The format of symchanges.csv is simply TradeSymbol,DataSymbol, as in this example:

Just add one row for every symbol that needs to be changed. The order of the rows does not matter.

Note that if RealTest is still unable to match a trade symbol to a symbol in the current data file, then that trade can not be included in the subsequent "backtest" of the trade list.

The log shown above may also include some warnings about trade prices, which will look like this:

In this case, as indicated, these trades will still be included in the backtest, as presumably they did actually occur if you got them from IB!

Every now and then, a trade fill will actually happen outside the official "high" or "low" of the day. Most often this is due to execution on a dark pool. Dark pool executions do not become part of the official daily bar range.

More likely, these warnings are caused by past price adjustments due to mergers or spinoffs. (If your price data comes from Norgate or Yahoo, regular stock splits or dividends will not cause these warnings, since RealTest always uses "as-traded" prices.)

Even if your IB transactions did not include strategy names in their order reference fields, you can still at least divide your transaction backtest into separate sides (long vs. short) if you want to. To accomplish this, just define two strategies for the same tradelist, one for each side:


Now that you know all about how to set up and run tests using your IB Flex Query transaction list, all you need to do is to produce one!

Here is how this is done (as of this writing in December 2020):

1. Log in to your IB Account Management web page and select "Reports / Tax Docs" from the menu:

 

2. Select "Flex Queries" from the menu of the page that comes up:

3. Add a new trade confirmation flex query:

4. Make settings look like this:

 

5. This part is important. You MUST use these three settings exactly (or use dd/MM/yy if that option has been specified in RealTest):

6. Click on ‘Trade Confirmation’ to edit the fields:

7. This will take some work with selecting and moving choices around from their long list, but you have to end up with the first 6 items below present. They can be in any order, and any number of other columns can be present, but these 6 must be included:

 

8. Finish setting that up and save the query, then run it by clicking the arrow:

9. Select your desired period and make sure it’s CSV, then click Run:

 

10. Once it creates the CSV, save it to c:\realtest or any folder of your choice.

Now you have the IBTrades transaction list file and can use it to study your actual trades in RealTest!

 

 

 

 

Copyright © 2020-2021 Systematic Solutions, LLC