IBKR options trade CSV checker

What the checker reads

The checker reads the Trades section of an Interactive Brokers (IBKR) Flex Query in CSV format. A Flex Query is a report that you configure in your IBKR account. You select its sections, its fields, and its output format.

The file must have one header row with the field names. The checker finds each column by its name. It ignores capital letters, spaces, and punctuation in the name. So Trade Date and TradeDate are the same column. The order of the columns has no effect.

IBKR's field list shows names with spaces, such as Trade Date. Public Flex Query CSV files write the names without spaces, such as TradeDate. Two fields have a different name in the CSV file. The table shows those names.

The checker ignores other columns. It also reads text output with a tab or pipe delimiter. It skips the BOF, EOF, BOS, and EOS rows that IBKR adds when the query includes header and trailer records.

A Flex Query can contain stock, cash, and option trades. When the file has an Asset Class column, the checker reads only the rows with the value OPT. It counts the other rows as skipped. When the query has more than one section, each section starts with its own header row. The checker reads only the first trade section.

Settings in the Flex Query

• Select CSV as the output format.
• Turn on column headers. Without them, the checker cannot find the fields.
• Add the required fields to the Trades section.
• Add IB Execution ID. The CSV file calls it IBExecID. With it, the checker can find exact duplicate fills.
• Note the date format in the general settings. IBKR uses yyyyMMdd if you do not change it. Select the same format in the checker.

IBKR states that queries created after June 2019 have a Date/Time field and no Trade Time field. The checker accepts either form.

Worked example Synthetic example

The example file has 14 trade lines for an invented underlying, SYNTH. It has no real account data. Its header row uses the field names of a public Flex Query CSV file. It also has one stock trade. Most lines contain one deliberate error. You can load it in the checker or download it above.

The checker reads 12 rows and reports 11 errors and 1 warning. 2 lines cannot be read.

A missing column

Remove the Trade Price column from the first two trade rows of the example. The checker then reports 2 errors. The first one is for the whole file:

The file has no "Trade Price" column. Add this field to the Trades section of the Flex Query.

The other errors are in the rows:

• Line 3 has the same IB Execution ID as line 2. The same fill appears twice.

What each problem means

Missing field

A required column is not in the header row. Add the field to the query and export again.

Missing value

A required column has an empty cell in a trade row.

Invalid value

A value has the wrong form. Examples are a date that does not exist, a price with a decimal comma, a quantity that is not a whole number, and a Put/Call value other than C or P. An OCC symbol that does not agree with Strike, Expiry, or Put/Call is also invalid.

Duplicate fill

Two rows with the same IB Execution ID are the same fill. That is an error. Two rows without an ID can have the same symbol, time, quantity, and price. That is a warning, because separate fills can have the same details.

Unreadable row

The row has a different number of values than the header, or a quotation mark that does not close. The checker cannot match its values to columns.

Skipped row

The row is not an option trade. Examples are a repeated header, a trailer record, a stock trade, and a row in another section of the query.

Unsupported layout

The file is an IBKR Activity Statement. The checker cannot read that layout.

An OCC symbol uses the option symbol format of the Options Clearing Corporation. It contains the underlying, the expiry date, C or P, and the strike. The checker compares it with the other columns only when the symbol has that format.

Common questions

Does TradeRecount store my file?

No. The page reads the file in your browser. It does not send the file or its rows to a server. When you close the page, the data is gone.

Can I check an Activity Statement CSV?

Not yet. The checker finds this layout and stops with an error. Each Activity Statement row starts with the section name, as in Trades,Data,Order. The columns have other names, such as T. Price and Comm/Fee. Export a Flex Query with the Trades section instead.

Why does every date show an error?

The date format in the checker is probably different from the format in your query. If one other format reads every trade date, the result tells you which one.

Why is a partial fill marked as a possible duplicate?

Without an execution ID, two fills at the same second, quantity, and price look the same. Add IB Execution ID to the query to remove this doubt.

Does the checker convert time zones?

No. It checks that each time is a real time. The IBKR pages that we read do not state the time zone of the trade time.

Does it work with other brokers?

No. It reads the IBKR field names only.

Limits of this check

• It checks the form of each value. It does not compare the trades with your IBKR account.
• It assumes that Expiry uses the same date format as Trade Date.
• It does not check that the quantity sign matches a buy or a sale.
• It does not read the Activity Statement layout or XML output.
• We compared the field names with public test files, not with an export from every account type. A header with a different word will not match.
• It reads at most 20,000 trade rows and files up to 5 MB.

See all options tools