Market Recorder for Interactive Brokers

• Download source - 736 KB
• Download executable - 401 KB
• Download MS-SQL database - 5.4 MB
• View latest code on GitHub

Note: Before downloading, please review the Requirements and Setup Instructions.

Introduction

MarketRecorder connects to Interactive Broker’s (IB) TWS API and captures live streaming events for a set of symbols, such as bid, ask, volume, highs, lows, recent trades, etc. This raw event data is stored directly to a new database record every second.

This tool can be used to download market data for different reasons. For myself, I use real-time data to predict stocks using AI but it could be used for just about anything. To use it though, either an Interactive Broker’s account is required or the free demo TWS. The demo has fake information but can be useful for testing.

This article is a write up for MarketRecorder, one of several applications that are loosely connected. This app mainly saves raw real-time tick data directly to a database. Saving this dirty mess of almost random events is important because it closely represents real world trading. There is a second project named BriefMaker that takes this data to the next level by converting this event data into a more familiar and practical time-interval summary such has a high/low/close/vol every 6-seconds. This type of data can be dropped into a spreadsheet with a fixed time interval on one axis. BriefMaker will be covered in another article soon.

Market Recorder just records events. The advantage to saving these streaming events is that events what makes up the market. Markets are a chaotic mess of different events happing almost randomly. This chaotic form of data is what the market is. As soon as this data is converted, say to 6-second summary snapshots by BriefMaker for example, a little bit of the market’s fidelity is lost and cannot be restored. Saving the raw event data allows apps, like BriefMaker, to be re-run with different parameters or code.

Collecting Data with the API

Below is a dataflow chart for MarketRecorder.

Market data first comes in via the internet or dedicated circuit directly to the Interactive Brokers(IB) TWS application. TWS has a built-in API that allows users to do almost anything from accessing market data to checking order status to placing trades.

To retrieve data, we must first establish a TWS API connection. For clarity, I have removed some non-essential lines:

logger.Info("Connecting to Interactive Brokers API.");
IBClient client = new IBClient();
Thread.Sleep(1000);

int retryCt = 5;
while (!client.Connected)
{
    Thread.Sleep(2000); 
    if ((retryCt--) <= 0)
    {
        logger.Info("Tried to reconnect 5 times but failed - aborting.");
        return; // abort the function.
    }

    try
    {
        logger.Info("Connecting to TWS..." + "(Try " + (4 - retryCt) + " of 5)");
        client.Connect(settings.IBHostToConnectTo, settings.IBPortToConnectTo, 1);
        
        // Set the client ID.  Here we just use 1.
        client.RequestIds(1);
    }
    catch (Exception ex)
    {
        logger.Info("IB Connecting Exception: " + ex.Message);
    }
}
logger.Info("TWS Client is now Connected.");

Next, we register some tiker symbols that we are interested in receiving events for:

foreach (var symbol in symbols)
{
    Contract item = new Contract(symbol.Symbol.Trim(), symbol.Market, symbol.securityType, "USD");

    // Lets now register for event updates... 
    client.RequestMarketData(symbol.SymbolID, item, null, false, false);

    // More available options not used in Market Recorder... 
    // client.RequestMarketDepth(symbol.SymbolID, item, 10);
    // client.RequestContractDetails(symbol.SymbolID, item);
    // client.RequestFundamentalData(symbol.SymbolID, item, "Estimates");
    // client.RequestFundamentalData(symbol.SymbolID, item, "Financial Statements");
    // client.RequestFundamentalData(symbol.SymbolID, item, "Summary");
}

Now the application waits for updates. Whenever there is an event from TWS, say a new best bid price, the API fires an event method in the MarketRecorder. This method then captures the data into a BinaryWriter (capturingWriter):

void client_TickPrice(object sender, TickPriceEventArgs e)
{
 int secondFraction = (int)(DateTime.Now.Ticks % TimeSpan.TicksPerSecond / (TimeSpan.TicksPerSecond/256));
 lock (capturingWriterLock)
 {
   capturingWriter.Write((byte)secondFraction ); // record sub-second time (1/256th resolution)
   capturingWriter.Write((byte)e.TickType);      // kind of data (like bid, ask, last...)
   capturingWriter.Write((byte)e.TickerId);      // The Symbol ID (like AMD, INTC, INDU..)
   capturingWriter.Write((float)e.Price);        // The data - in this case price.
 }
     ...
}

Writing Data to the Database

On each second, the capturingWriter is uploaded to the StreamMoments database table.

// Round down to nearest second
DateTime time = new DateTime((DateTime.Now.Ticks / TimeSpan.TicksPerSecond) * TimeSpan.TicksPerSecond);

// Set capturing stream to processing stream 
BinaryWriter ProcWriter = capturingWriter;
lock (capturingWriterLock) { capturingWriter = manufacturedWriterForFuture; }

// Finish up processing stream
ProcWriter.BaseStream.Position = 0;
ProcWriter.Write(time.Ticks);
ProcWriter.Flush(); 

// Notify any WCF Clients of a new TstsRow 
OpenAndSendWithWCF(ProcWriter);

// Save to the database 
byte[] data = ((MemoryStream)ProcWriter.BaseStream).ToArray();
StreamMoment briefToSave = new StreamMoment() { SnapshotTime = time, Data = data };
try
{
    dc.StreamMoments.InsertOnSubmit(briefToSave);
    dc.SubmitChanges();
}
catch (Exception ex)
{
    logger.Error("Exception with SubmitChanges(): {0}", ex.Message);
}

// Display pushed results
logger.Debug("Snapshot complete ({0} bytes at {1})", data.Length, time.ToString("HH:mm:ss.fff"));

// Clean-up
ProcWriter.BaseStream.Dispose();
ProcWriter.Dispose();

// Get next BinaryWriter ready ahead of time.
manufacturedWriterForFuture = new BinaryWriter(new MemoryStream(MemoryStreamReserveSpace));

Output Format of StreamMoments in Database

Every second MarketRecorder starts a new row in the StreamMoments table. After each second completes a new row is added to the StreamMoments table. Each row has a SnapshotTime(DateTime2) and Data(image) column:

SnapshotTime: This is the time at the end of the data collection. It is rounded to the end of the second. To get the beginning of the snapshot time, just subtract 1 second.

Data: The data field is what holds the streaming data. The size of it depends on how much data came across the wire and how busy the market was. The first 8 bytes of this field is the DateTimeNow.Ticks when the stream moment ended. The next 7 bytes is the first event, the next 7 bytes is the second event, and so on.

Each tick is saved in the following format: (7 bytes total):

To get the sub-second time for each event, use something like the following:

EventDateTime = SnapshotTime - 1 + (subSecond/256);

The symbols that are downloads are located in the Symbols Table. It is preloaded with examples.

Bytes were used for the sub-Second and TickerID in order to save space. This does provide some limitations tough. The sub-second accuracy is limited to 1/256 of a second, or 3.9ms. Because of network connections jitter though, this precision may be good enough. The TickerID is also a byte so the system, as-is, is limited to 256 tickers – but that is a lot to be downloading at one time. Also, IB has a 100 symbol limit for standard accounts so 256 is well beyond that amount.

Features

NLog logging – This is a popular logging library. Debugging real-time data is difficult because the market data does not stop and wait while we debug our code. Having a nice logger, such as NLog, lets us go back and track down issues.

High Performance – One of the main goals was to minimize latency. When working with trading algorithms, low latency is usually important. Where possible, data is made available as soon as possible for the database or a WCF transfer. To speed things up, a direct link between MarketRecorder and BriefMaker was setup using Window Communication Foundation(WCF). This bypassed a round trip hit to the database.

Built-in auto-reconnect – Sometimes different issues happen, such as a network outage or a problem with TWS, and the API needs to be re-initialized. MarketRecorder checks to make sure data is coming in and if there is no data it will restart the connection. Note: Sometimes when small amounts of data coming in, like afterhours, it will trip the auto-reconnect feature.

Command Line Shutdown – The program can be closed via the command line using “MarketMaker.exe /q”. For myself, I used to have a scheduled task run each day to close MarketMaker. I now use TWSStart (http://twsstart.free.fr).

The Symbols Table

Below is what the Symbols table looks like. The columns to the right of ‘Name’ are not used in MarketRecorder.

• SymbolID: Contains the ID for each symbol. (must be 0-255)
• Type: Used by MarketRecorder when registering symbols.
• Market: Used by MarketRecorder when registering symbols.
• Name: The symbol for the data to retrieve.
• FullName, TradeEnabled, TradeQuanity, Value, AvgVol, MarketCap, TickTypesToCapture fields are not used in MarketRecorder.

Direct WCF Connection

MarketRecorder has built-in functionality to directly connect to other applications, such as BriefMaker. The connection uses a WCF (Windows Communication Foundation) network connection and is used to minimize latency. When each 1-second snapshot is completed it can be shipped directly to another application, bypassing the database. The database record is still written however for historical reasons.

User Interface and Its Functions

Delete All Button - This will erase all the contents in the StreamMoments table.

Launch Web Demo Button – This opens up the website where users can download and connect to IB’s demo. This is useful for testing.

Future Wish List

MarketRecorder uses the krs.Ats.IBNet library. This has been an excellent library but not been updated since 2012. In the future, I would like to move it to the Interactive Brokers C# API.

A second improvement I would like to add, but am not sure if I’ll ever get around to it, is capturing additional events like level II data and news events. For news, it would be nice to even capture the meaning or mood in numerical values.

Setup Instructions

1. Download and mount the starter MS-SQL database. To start out, set the name to “Focus”. The DB has several SQL tables but only two are used: Symbols and StreamMoments.
2. Update the .config file:
   • If the database name is not focus, then you will need to update this in the connection string in the .config file.
   • Next, you will see IBHostToConnectTo and IBPortToConnectTo... adjust only if needed. The port number can be found in TWS -> File -> Global Configuration -> API -> Settings.
   • If needed, configure the ShutDownTime. This is the time the program will automatically close. I am in the PST time zone so that is why it is defaulted to 14:15(2:15PM).
3. To use the API in TWS, you will first need to enable it. In TWS, go to File -> Global Configuration -> API -> Settings and check the box "Enable ActiveX and socket clients". You might also need to add 127.0.0.1 in the Trusted IP Addresses. Also note the port number. This port number should match the value in the .config file mentioned above.
4. Start the MarketRecorder. It will connect to TWS and the DB automatically. If you are going to get an error, it will be within the first few seconds. If it does not connect, review the log details or debug it in Visual Studio.
5. After getting everything setup and the test data seems to be working, then feel free to update the list of symbols. If you are going to use BriefMaker, then I would keep a total of 32 stock items (0-31) and avoid editing the Market Indexes (32-38).

Requirements

• .NET 4.5
• Interactive Brokers Trader Workstation (TWS)
• Java for TWS – This will install automatically when installing TWS.
• An Interactive Brokers account is required for real data however IB does provide a demo account testing. This demo account has fake data. The login is “edemo” with the password “demouser”.
• MS SQL Server or MS-SQL Express(free)

History

• 2011-11-17 Initially created
• 2012-08-31 Added FixedStepDispatcherTimer
• 2013-02-11 Add single instance limitation, exit command line option, started WCF
• 2014-01-28 Finished WCF, improved stability
• 2016-03-12 Cleaned up for online posting / published code