TradeLogIB: Importing Interactive Brokers trades into ONE

[Stanislav 302]

I publicly released TradeLogIB.py script on a github.com:

https://github.com/stenri/TradeLogIB

So I moved all documentation and script itself there. Here I only leave Motivation section from TradeLobIB.py documentation, so occasional visitor may read it and get interested.

 

Motivation:

As you may know, OptionNET Explorer lacks proper broker integration: it can only send orders to the broker but after the order is sent, it lives its own life. ONE does not know when and if the order is filled, at what price, how much commissions are paid and so on. It is assumes user manually downloads trades from the broker and uses Import Wizard to import trades, or just enters trades and commissions manually into Trade Log.

 

In case of Interactive Brokers user is only able to export broker activity statements after the session is over. So after you place an order and receive a fill you have to wait till the end of the session for activity statements or save the trade into Trade Log and manually enter commissions (this process is very error-prone).

 

All this makes trading process much less convenient than it should be. And we need a solution to import trades from a broker Trade Log into ONE without much effort. Here it is: TradeLogIB.py.

 

Edited December 18, 2017 by Stanislav

[CPO 37]

Incredible. Thank you @Stanislav

[kdunaway 13]

Big Thank you!

[Stanislav 302]

I fixed TradeLogIB.py script to work with the latest ONE 1.28.6 BETA which has been released several days ago.

 

It seems they fixed locale specific bugs in ONE when importing OptionXpress data. And now ONE always expect date and time to be in US locale format (however, ONE still needs AM/PM symbols to be set in Regional and Language settings in Control Panel.

 

See TradeLogIB_1.02_for_ONE_1.28.6BETA.zip attachment in the original thread post.

Edited October 11, 2017 by Stanislav

[Christof+ 274]

@Stanislav, thanks for sharing your work freely, highly appreciated!! I like your code layout.

Just for the unlikely case you haven't seen and will do the transgression to Python3 at some point: IB meanwhile offers their own python API implementation, no need for 3rd party solutions like IbPy anymore (no deal here, but for trading applications it might be more reliable and is supported).

[Stanislav 302]

1 minute ago, Christof+ said:

@Stanislav, Just for the unlikely case you haven't seen and will do the transgression to Python3 at some point: IB meanwhile offers their own python API implementation, no need for 3rd party solutions like IbPy anymore (no deal here, but for trading applications it might be more reliable and is supported).

Thanks! Yes I use IbPy as it supports Python 2.7 which I currently use for all my Python scripts development. I've seen new IB API with Python support but for now decided to stay with IbPy solution, as there are more examples available in the internet and IB Python API only support Python 3+.

I also have plans to adapt my own TradeLogIB.py script to be Python 3+ compatible (this mainly means changing print statement to print() function calls). Maybe I'll do this next time I update the script.

[Stanislav 302]

I publicly released TradeLogIB.py on a github.com:

https://github.com/stenri/TradeLogIB

Next step will be to rewrite the script to support Python 3.6+ and official IB Python API as suggested by @Christof+.

[Stanislav 302]

Here it is, the latest version of the TradeLogIB written for Python 3.6+. It uses official IB Python API via an amazing ib_insync package:

https://github.com/stenri/TradeLogIB

Script is now more than two times shorter as ib_insync allows to use synchronous programming. So working with IB API becomes quite pleasurable.

NOTE: Output of this version differs slightly from previous version output because of differences in number rounding in the last field "Total Cost". If you account for this, the rest of the output is the same.

[Christof+ 274]

Nice and clean, thank you for that!

[Stanislav 302]

Credits mostly go to ib_insync package, as it allowed me to greatly simplify the script.

ib_insync is a good example of how well designed API should look like (compared to the garbage IB ships ).

[Stanislav 302]

For those who are using TradeLogIB.py with Python 3.6 and ib_insync package I recommend to update and install latest ib_insync from the latest master code branch:

https://github.com/erdewit/ib_insync

ib_insync author merged in my Pull Request with a patch to solve the issue with fills for partially executed multi-legged option orders. I noticed TradeLogIB.py script based on ib_insync did not dump trades for a partially executed orders, I have to wait till order is completely filled or cancel an order and then run TradeLogIB.py to get my fills for that order in .csv file. More details here:

https://github.com/erdewit/ib_insync/issues/36

That was quite annoying, and previous version of the script based on IbPy and Python 2.7 worked well with partially executed orders, dumping fills immediately. So decided to make some research and investigate the issue. As a result I developed a patch to ib_insync package which solves this issue, and author merged in this patch today.

So, it may be a good idea to update ib_insync package.

[Christof+ 274]

 

@Stanislav very nice work! And I want to thank you for drawing my attention to ib_insync. I really like it. I am convinced that it's a good idea to keep code as simple as possible when it comes to trading applications - hence to avoid any extra layers or packages - but this one does indeed facilitate API communication in a very nice way (never thought it could even be fun to work with IB's API - apart from their frigging manual).  

 

Btw, I meanwhile made the experience that for all fill or portfolio tracking tasks IB's flex query mechanism is a more robust approach than the event driven API calls (which at times can loose a fill or a position). Even ib_insync offers their FlexReport class to handle call and file processing in a nice one-liner (well, once you set up the Trade Confirm or Reporting Flex Query in IB's Account Management). I use this approach successfully f.e. for a trading log/journal in SQL for performance tracking and analysis.

 

 

Edited January 7, 2018 by Christof+

[Stanislav 302]

@Christof+, thanks! Yes, ib_insync author also commented that flex webservice or FlexReport might be of some interest for my project. But I think current TradeLogIB implementation is optimal for the particular task I solve.

 

Out of curiosity, does FlexReport's allow to receive fills in "realtime" - immediately after an order is filled? This is what I need to properly integrate OptionNET Explorer and IB. At least for the reports that can be generated via Account Management one have to wait till the end of the session to generate a report with today's fills. That is what I want to avoid.

[Christof+ 274]

Yes, the queries you set up under Reports/Activity/FlexQueries (website's old 'classic' GUI) can only return data from yesterday and before. But these are not the only ones you can setup. The ones under Reports/TradeConfirmations/FlexQueries return all trades of today if you specify it (as the name might already suggest). Not sure how long it actually takes until fills are available (the docs say nothing), think I never actually downloaded 'immediately' in the sense of like 'right away'. But I never missed anything (the only disadvantage is that trade confirmations cannot return fx rates to your base currency, while the first kind of reports can. But you will be able to live without this).

Just if you wanted to play with it: tweaking line 170 in your gist (from "for fill in ib.fills():" to possibly something like "for fill in ib_insync.flexreport.FlexReport(token=xxxxx, queryId=xxxx)" might already do most of the trick.

 

[greenspan76 332]

You guys are talking way over my head here (so hopefully I'm not making a really stupid comment without realizing it), but on many occasions, I have logged in to the website within 1-2 minutes of executing a trade and the trade has appeared in the Trade Confirmations under Account Management. I would think that if its available on the the Trade Confirmations on IB's website, it would also be available for what you guys are discussing and I would assume that seeing it 1-2 minutes later is indicative of instantaneous reporting (even though I've never actually looked a few seconds after the trade to verify)

[Stanislav 302]

@Christof+, @greenspan76, thanks! Looks like Flex Report can be used to get list of trade confirmations in real-time.

I am quite happy how my TradeLogIB script works currently. The only limitation is that IB API only return trades for the past 1-7 days, depending on how TWS is configured. But that is ok, since the idea is to import trades into ONE as soon as possible after we get a fill. And I never detected any issues with API "which at times can loose a fill or a position" as @Christof+ wrote.

Maybe I'll experiment with Flex Report sometime in the future - they can be useful for generating year-to-date reports for example. But that is slightly outside of scope of TradeLogIB functionality as I see it.

[Stanislav 302]

I fixed --daemon mode in TradeLogIB.py and made some tests. Daemon mode now really works, so you can run:

 

TradeLogIB.py --daemon

 

And scripts sits in memory and automatically updates TradeLog.csv file when new fills are received in TWS. All you need is to import trades into ONE. Even less work than before.

Edited February 16, 2018 by Stanislav

[Javier 99]

Stan

On 16/2/2018 at 10:06 PM, Stanislav said:

I fixed --daemon mode in TradeLogIB.py and made some tests. Daemon mode now really works, so you can run:

 

TradeLogIB.py --daemon

 

And scripts sits in memory and automatically updates TradeLog.csv file when new fills are received in TWS. All you need is to import trades into ONE. Even less work than before.

Thanks for you macro, I am trying to set it up but I have very little (to none) knowledge of Python environments, so I’m using your GubHub configurations as a cheet sheet, and have 3 main issues:

• Installed Anaconda env. successfully. Ok
• installed IB  API successfully. Ok
• unpacked TradeLogIB.py in my hard drive. Ok
• issue 1) install ib_insync using “pip3 install .........”???. I cannot figure out how to do this step (I also have ib_insync-master.zip unpacked in my HD). Should I launch this install as an ordinary MS command? (Doesn’t work .... cannot find pip3 cmd) or launch any app to make the installation?

 

Once this installed, rest config seams easy till. ....

 

Issue 2) How to launch this insync and your TradeLogIB.py for the day to day work?

Issue 3) I understand You must have, apart of TWS, or IBGateway, and obviously One on, insync an TradeLog also active to make the trades import from One import icon. True?

 Could you help, please? 

[Stanislav 302]

@Javier, ib_insync is an extension module for Python 3.x. You need to install it once, and then forget it (so you only run TWS, ONE and TradeLogIB.py on day-to-day basis).

To install ib_insync, you can either unpack ib_insync-master.zip into the folder with TradeLogIB.py script, and get the following directory structure:

ib_insync
  ib.py
  objects.py
  ...
TradeLogIB.py

This will install ib_insync for use for TradeLogIB.py exclusively. TradeLogIB.py as any python script looks for the extension modules in the current directory.

 

OR

 

You can use the command "pip install ib_insync" to install the module system-wide. The command "pip3 install ib_insync" is from ib_insync package documentation. Actually in Anaconda there is no pip3 command, but the usual pip.

NOTE: If you still can not find pip command, it is located in the <AnacondaRoot>\Scripts\pip.exe. So, check your PATH environment variable, or just go to this folder and run command from there.

[Javier 99]

On 10/4/2018 at 4:53 PM, Stanislav said:

@Javier, ib_insync is an extension module for Python 3.x. You need to install it once, and then forget it (so you only run TWS, ONE and TradeLogIB.py on day-to-day basis).

To install ib_insync, you can either unpack ib_insync-master.zip into the folder with TradeLogIB.py script, and get the following directory structure:

ib_insync
  ib.py
  objects.py
  ...
TradeLogIB.py

This will install ib_insync for use for TradeLogIB.py exclusively. TradeLogIB.py as any python script looks for the extension modules in the current directory.

 

OR

 

You can use the command "pip install ib_insync" to install the module system-wide. The command "pip3 install ib_insync" is from ib_insync package documentation. Actually in Anaconda there is no pip3 command, but the usual pip.

NOTE: If you still can not find pip command, it is located in the <AnacondaRoot>\Scripts\pip.exe. So, check your PATH environment variable, or just go to this folder and run command from there.

Stanislav, I had some time to go on with septup today morning. Everithing went on till launchig your script that gives me error "IB API from http://inter...............github.io is required". But I have the API installed running and working ok with ONE (see attachament). In fact I use it for Live feed data. Can You shed some light?

[Stanislav 302]

@Javier, you need to run the following command from %TWS_API_ROOT%\source\pythonclient\ folder to install IB API Python bindings:

> setup.py install

where %TWS_API_ROOT% is the root directory of you IB API installation (usually "C:\TWS API").

 

Looks like I forgot to mention this step in the TradeLogIB documentation. Going to fix it later.

[Javier 99]

Repeated

Edited April 14, 2018 by Javier

[Javier 99]

On 12/4/2018 at 4:19 PM, Stanislav said:

@Javier, you need to run the following command from %TWS_API_ROOT%\source\pythonclient\ folder to install IB API Python bindings:

> setup.py install

where %TWS_API_ROOT% is the root directory of you IB API installation (usually "C:\TWS API").

 

Looks like I forgot to mention this step in the TradeLogIB documentation. Going to fix it later.

I got it working yesterday, so that first of all thank You very much, I believe it is a key feature for working with ONE and IB, thousands of times better than the official trade transfer offered by One team through Activity Reports. I have tested it Today and seems to work nicely, eventhough I cannot have all three components (TWS, ONE and TradeLogIB daemon) working simultaneously, maybe a port 4001 conflict, cause the last one connection is refused. Ever dig into that?

[Stanislav 302]

@Javier, if TWS+ONE and TWS+TradeLogIB work by themselves, and only all three components - TWS+ONE+TradeLogIB - do not work together, I'd guess this is clientId conflict. So you need to change ClientId in ONE to something other than your current setting (TradeLogIB needs clientId to be the same as "master client id" configured in TWS as described in TradeLogIB documentation).

 

Also, in case of any troubles with TradeLogIB.py script, you can add --logLevel=20 to the command line:

python.exe TradeLogIB.py --daemon --logLevel=20

This creates TradeLogIB.log log file which you can inspect and get an idea what exactly goes wrong in TradeLogIB.

[BlackBat 66]

Hi @Stanislav, thanks a lot for your script, so far it works great for me, too, I was able to successfully import into ONE the csv exported by your script.
There is one exception, though: all commissions are being listed as "0".

I use:
- Python 3.6.6
- TWS API 973.07 for Linux
- the latest commit `2b04a67b270513077c6990197fecdf4933e34a91` of ib_insync
- the latest commit `77ab5c422849d5101caef12ace76fcd33b2709d9` of TradeLogIB
I'm wondering if it's just me or something has changed in some of the components which does affect other people, too.

This test in your code returns true for me:

            if hasattr(fill, 'commissionReport'):

so the `fill` object does have this `commissionReport` attribute and it is of type `ib_insync.objects.CommissionReport`.
I listed the properties and values of these `fill.commissionReport` objects for each of the `fill` objects returned by `ib.fills()`, they all look uninitialized:

fill.commissionReport.commission = 0.0
fill.commissionReport.currency = ''
fill.commissionReport.defaults = {'execId': '', 'commission': 0.0, 'currency': '', 'realizedPNL': 0.0, 'yield_': 0.0, 'yieldRedemptionDate': 0}
fill.commissionReport.execId = ''
fill.commissionReport.realizedPNL = 0.0
fill.commissionReport.yieldRedemptionDate = 0
fill.commissionReport.yield_ = 0.0

I also searched for the word `commission` in the log file generated with --logLevel=10 (DEBUG), but no match.

Are you able to get the commissions from IB at this moment? If affirmative, do you use different versions for some of the components?
Any hints where I should look next?

Thank you!

Edited June 24, 2018 by BlackBat

[Stanislav 302]

@BlackBat, I did not test TradeLogIB.py on Linux and I did not update ib_insync for some time. So I do not know how TradeLogIB works with recent ib_insync versions. I attached ib_insync library files that I use (I have ib_insync subfolder in the location where I store TradeLogIB.py, so Python uses ib_insync from this folder instead of system-wide version).

Also, make sure you configured "Mosaic / TradeLog and set "Show trades for: Last 7 days" in TWS" as described in TradeLogIB documenation. 

 

ib_insync.zip

[BlackBat 66]

 

Quote

Also, make sure you configured "Mosaic / TradeLog and set "Show trades for: Last 7 days" in TWS" as described in TradeLogIB documenation. 

Yup, I did that.

Later edit: Thanks  for the zip, but AFAICT this does not have anything to do with the version of the software components. The fill objects for more recent trades (approximately the  last 24 hours) have the commissionReport field properly initialized:

commissionReport=CommissionReport(execId='000126e2.5b30f0f5.03.01', commission=0.6678, currency='USD'),

whereas older trades have it empty:

commissionReport=CommissionReport()

I will keep monitoring the situation during the next days.

 

Another question: have you tried to use ib_insync for building other tools, like accessing historical data for automated backtesting, instead of manually simulating certain strategies on previous earnings cycles in ONE?

Edited June 26, 2018 by BlackBat

[Stanislav 302]

@BlackBat, yes I use ib_insync in some of my scripts, but not for automated backtesting or anything such complex.

[Stanislav 302]

UPD: The info below turned out to be incorrect. Please, ignore it.

 

 

@BlackBat, by the way, I can confirm TradeLogIB.py works correctly with TWS API 973.02 + older ib_insync build + TWS 968.

And I experience the same issues with commissions being zero on TWS API 973.05 + recent ib_insync from master branch + TWS 968.

 

So, either TWS API 973.05 or new ib_insync package is broken. TWS itself does not seem to be responsible. Unfortunately I do not have enough time to investigate the issue further myself right now. If you do find out the details, please, let me know.

Edited July 23, 2018 by Stanislav

[Stanislav 302]

@BlackBat, My previous post turned out to be incorrect. I retested everything with TWS API 973.02, 973.05, 973.07 and TWS 968, 972.1m (can not test on TWS 973 as I only use standalone builds which do not update automatically).

I found I've just forgot to set Master ClientId in TWS configuration and TradeLogIB.py to match, as outlined in TradeLogIB documentation. After I fixed Master ClientId on my test TWS installation, commissions started to show up in TradeLogIB.csv output file.

Here is the setup with most recent versions I tested to work ok on Windows:

TWS API 973.07
ib_insync v0.9.27 (commit 0924e7ebd62182640f3181b4d5d0f782251010dc)
TradeLogIB.py (commit a76e1df1bdcbe993114847e965a546a709b7870c)

 

NOTE: I've changed a one line in TradeLogIB.py to force it to work with latest ib_insync package. Please, get the latest copy.

Edited July 23, 2018 by Stanislav

[BlackBat 66]

19 hours ago, Stanislav said:

Please, get the latest copy.

Thanks, I will.

In the mean time I configured TWS to export the trade log automatically every day and I am working on a script that parses the exported csv and groups trades by the underlying. The target is to have a clear view of the PnL for more complex trades where some of the legs are already closed and therefore TWS doesn't give us an accurate indication of the trade's PnL, for example straddles with multiple strangle iterations.

Interesting experiment so far, but I still have more work to do in order to get it into an awesome shape.

Edited July 24, 2018 by BlackBat

[BlackBat 66]

On 7/24/2018 at 12:40 AM, Stanislav said:

Please, get the latest copy

Got the latest copy, the csv is now generated with non-zero commissions as it should be, good job!

However, if I include the transactions from July 19, I run into the following error (I also included some extra info to show the software versions):

The transaction causing the error seems to be this one:

Have you ever encountered similar issues for some of the entries?

Thanks again for the updated version.

[Stanislav 302]

@BlackBat, yes I see the similar issue with some trades older than a fews days. Looks like TWS reports commissions prior to reporting the trade itself (prior to reporting execution). And ib_insync does not like it.

 

Do you encounter this issue with "todays" trades? If not, we can ignore this issue for now, as usual workflow is to import the trades into ONE or some other software right after the trade is made.

Edited July 25, 2018 by Stanislav

[BlackBat 66]

1 hour ago, Stanislav said:

Do you encounter this issue with "todays" trades? If not, we can ignore this issue for now, as usual workflow is to import the trades into ONE or some other software right after the trade is made.

No, all transactions more recent than July 19 were processed flawlessly. So, if only older entries are impacted by this error, then having your script running as a daemon should ensure timely entry processing, so we should never run into this issue again. 

[Stanislav 302]

I updated TradeLogIB to support modern TWS / TWS API versions and added a support for automatic UTC to Local Time Zone conversion:

https://github.com/stenri/TradeLogIB

Check "TradeLogIB 4.0 notes" section documentation for a details on updating the script and TWS.

[Stanislav 302]

I've updated TradeLogIB script. It now reconnects to TWS every minute.

 

This should help to overcome annoying ib_insync bug with partially filled orders when script is in --daemon mode (ib_insync sometimes incorrectly handles partial fills which results in some fills appearing twice in the log after order fills completely, and that messes up things when TradeLogIB.csv is imported into ONE). 

 

But on a fresh connect ib_insync always get all fills correctly from TWS. So as a workaround I decided to let TradeLogIB.py reconnect automatically to TWS every minute:

 

https://github.com/stenri/TradeLogIB

Edited March 17, 2020 by Stanislav

[olufemig 0]

does this still work with the current version of ONE?

[Stanislav 302]

@olufemig, I do not longer use ONE, so can't verify if TradeLogIB.py script is still working with ONE. But I do not see any reasons why it should not work. You are free to try this out.

 

Also note that as a former SO member, I'm in the group on this forum which requires all my posts to be approved by a moderator before others can see them. That limits my ability to give timely answers and participate in the discussions.