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.