Reconciliation Tool: RT vs IB

[Gareth]

Hi all,

Just wanted to ask if anyone has built a reconciliation tool to speed up the checking of Real Test trades to live trading in IB for a daily system that does a lot of trades. I’m busy building one from scratch in excel and its not as straightforward as I’d hoped. RT trades are just a list, but the IB trades export may either miss a trade, get a different price fill, or have the trade completed in multiple legs on both the buy or the sell side. So there’s a bit of data sanitisation required and I just wanted to ask how others have approached this to speed this up.

I see excel has 2 new functions.(Groupby and Pivotby) which might become part of my solution.

Thanks

Gareth

 

[BenOsborn]

Hi Gareth,

I have a pretty simple spreadsheet I use for a quick check.  I have automated some of it with a python script and now the whole process takes a couple of minutes.  Funnily enough I just started fully automating the process yesterday (yet to finish) and have now seen your post.

The semi-automated version is that I download the daily report from IB and then run a python script.  The script does all of the work using pandas, grouping together transactions that took place in multiple transactions, shorts the longs and shorts, assigns them to the right strategy if more than one strategy traded the same symbol, calculates the P&L and then inserts them into the appropriate spreadsheet columns of the spread sheet and saves it.  It then converts them into the right format to go into a separate Trade List spreadsheet for realtest to run went I want to replay trades and saves that.  The script takes 2 seconds to run, the longest part is logging into TWS and hitting download today’s trades.

For the backtest side I was doing that manually and copying across but yesterday created a script to run it automatically each morning at 6:20am (i’m in Perth).  If Norgate hasn’t published the data it will check back every 30 minutes and then automatically run the backtest when the status says published.  In the next couple of days I will finish automating the whole thing so I just have to open the spreadsheet and run my eye over the missed trades (which doesn’t happen that often these days).  I have just been lazy since automating the first part took the process from up to 30 mins or more depending on the number of trades down to 5 mins max.

Happy to share the scripts if you want to take a look (don’t expect anything too professional though!)

[Gareth]

Hey Ben,

This sounds like a great solution. Yes please do share, I’d love to have a look. I don’t really know how to code in Python just yet but I guess I can learn. Chatgpt will help a lot as well explaining some of the logic and sounds like a skill I just need to have. I think Python can be used with the IB API which is something else Ive been thinking I need to take a look at further down the line. Fast-tracking / automating this backtest vs IB reconciliation step is critical for me as its currently taking way too long to do it manually. Semi-manually is progress! 🙂  My email is [email protected] if you want to email it. No worries I wouldn’t even know what professional looks like haha.

Thanks for this. Will save me hours.

Gareth

[BenOsborn]

Hey Gareth,

Just sent.  If you want me to run you through anything just let me know, more than happy to help out.

ChatGPT is definitely my friend when it comes to these things!

Cheers,
Ben

[Gareth]

Hey Ben,

Awesome thanks! Email received. Will get stuck into over the weekend. Will reach out if I find myself arguing with Chat GPT!

Cheers

Gareth

[Author]

Posts