NoSQL and the IB API

So, I must admit that designing database schema is not my favourite activity. I'd much rather be working on modelling and trade monitoring than constant auditing and maintenance.

I've switched from my previous SQL schema to using NoSQL as provided by MongoDB. Some may argue that this is a dangerous approach (Edit: the rebuttal) considering the requirements, but I enjoy the quick and easy document management and the simple efficient indexing of entries.

As I believe everyone is aware, Interactive Brokers offers a great service for small individual traders, but for those wanting to use their API, it's a bit of a nightmare. I've spent quite a lot of time working on a proprietary wrapper which is resistant to their frequent API updates. So I thought I'd share some code snippets.

Following from the NoSQL introduction, an example of recording data from my IB feed, all I have to do is run code such as the following:

//Connect to IB through API
FuzzIBConnection.get().connect();

//ES December 2012 Futures Contract
Contract contract = new Contract();
contract.m_secType = "FUT";
contract.m_symbol = "ES";
contract.m_localSymbol = "ESZ1";
contract.m_expiry = "201112";
contract.m_currency = "USD";
contract.m_exchange = "GLOBEX";

//Instantiate a handler for the security feed to record to the database
SecurityFeedRecorder sfr = new SecurityFeedRecorder(contract);

//Get the data feed from the IB API, and add the SecurityFeedRecorder object
//as a handler.
SecurityFeed securityFeed = FeedManager.getSecurityFeed(contract);
securityFeed.addHandler(sfr);

This snippet of code will then collect all of the information from the IB API regarding the ES futures contract, and record it to a database which I defined in my MongoDB management class.

In a little more detail: tick data is received live from the IB API in the SecurityFeed object, which then publishes this data to all of the handlers added to its handler list. In this case, there is only one handler, a SecurityFeedRecorder object. This SecurityFeedRecorder encapsulates organisation of where to store the data in the database with information obtained from the Contract object passed to its constructor.

At the moment I chose the system to default to run the database locally with the code, but if I decide to migrate to a dedicated machine, it's a simple case of including the line of code:

FuzzMongo.initMongo("127.0.0.1")

obviously replacing the localhost address with the ip of the host machine.

I don't just record security data, but also information regarding my account, contract details from IB, audit trails, trades made, as much as I can.

It's then incredibly easy and efficient to retrieve this data for manipulation. For quick and dirty analysis I can spit out csv files and start working with them in Excel. Plotting equity curves, sector exposure, contract details reports, etc etc.

Pair Trade Finder

I've received a series of requests and inquiries over the past few months regarding whether the pairs trading code or binaries are available for purchase. Sadly I do not really have the time, nor willpower to launch it as a product by myself considering the legal requirements, the after sale support, and general documentation that would be required.

I would like to refer anyone who has expressed an interest in acquiring the full version of the program to check out Pair Trade Finder. The latest release as of the beginning of May 2011 includes the ability to calculate the confidence of cointegration across pairs' error terms, and I especially like the organisation of results into a database. The best part is, you can check out the program for free for 30 days.

Update to the Pairs Trading Platform

I decided to upload a new set of videos showcasing a few updates I've written for the pairs trading platform. Not much has aesthetically changed, a lot of the modifications are with the link to IB causing problems with portfolio calculations. Nevertheless it might provide some interesting viewing. The demo version of this software is still available from this blog.


Batch stock analysis.


Intra-day pair analysis.

Bit Hacks

I must admit that I'm fairly new to computer science. I followed a link on proggit to a series of bithacks hosted by Stanford.
http://graphics.stanford.edu/~seander/bithacks.html

I found some of the basic ones a good challenge to think about without looking at the answer right away. Otherwise I'm astonished at the power available from these techniques although I'm aware most compilers are very well optimised accomodating for these methods. Even though branching isn't really a concern for me, it's fascinating to read.

Using Open Office to Manage a Derby/SQL Database

Following on from a previous post regarding the archival of audit trails from IB. I designed and created a quick and dirty database using a local JDBC/derby within Netbeans. I did absolutely nothing fancy, just wrote instructions for the database to store any and all information that is received from the API, and any information that gets passed through.


This obviously isn't practical if I were going for low latency trades, just analysing performance in the NetBeans profiler shows my DatabaseHandler class eating up a shocking amount of CPU time (of the order of 10ms per call of a method with prepared statements writing to the database).


My reason for doing this however, is that I can link to the derby database via OpenOffice base. This is fantastic for keeping a rudimentary and lightweight account of all the data i've been sending to and receiving from IB which is human readable.


I use this to plot my net liquidation value, analyse specific performance of specific strategies, and generate reports of any kind.

Archiving Interactive Brokers Audit Trails

An irritant of the interactive brokers single account is that the audit trail can only be viewed for the past week. I did however search through the TWS file system and found a series of folders named with a seemingly random series of letters. Within these are archived xml format audit trails which could be parsed and their data put into some form of database.


There is a problem though, the tags of each of the values are given as integers and not to their corresponding description. I contacted IB regarding whether it's possible to get a list of the tag number/description matching pairs, but was informed that this information is not given out to clients. A pain in my opinion, but there's not much I can do.


However, I have written an html scraper of the condensed form of the audit trail which can be viewed from the TWS by the toolbar: View -> Audit Trail which converts the information to csv and excel formats. This works fine, however it's defeats the object of keeping an automated trading system if I have to manually save the html audit trail at the end of every day (or all 7 at the end of a week). Also given that some of IB's clients manage multiple accounts, the simple 5 minute act of saving an audit trail and parsing it each day becomes a much larger and costly task.


If I ever get the incentive, it would be possible to write a matching algorithm from these to find the corresponding description for each matching tag. For now though, I'm content with the 5 minutes each day on the single account.

Pair Trade Analyser

Around 3-4 months ago I wrote a platform to facilitate the style of pairs trading that I've adopted. Just yesterday I stripped it down to release a small sample of what it can do but even in this form it's still quite a useful little tool.


This program gets daily data from the Google finance. Credit to the libraries used is given in the About of the application.


This little demo only grabs a year of daily data from Google, whereas the full version has a flexible time horizon over which to perform the analysis. The full version also allows for batch analysis of each possible permutation of a group of stocks pairing them up, as well as automated trading with intra-day analysis using the interactive brokers api.


Download Link