Click here to Skip to main content
15,867,568 members
Articles / Artificial Intelligence / Big Data

Market Recorder for Interactive Brokers

Rate me:
Please Sign up or sign in to vote.
4.76/5 (10 votes)
15 Mar 2016MIT8 min read 40.1K   1.2K   22   15
A tool that captures live market data for a set of symbols and saves them to 1-second snapshots in a SQL database.

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.

Image 1

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.

Image 2

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:

C#
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:

C#
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):

C#
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.

C#
// 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:

Image 3

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.

Image 4

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

Format Description Range
Byte Current sub-second (size: 1/256 second) (~4 ms resolution) 0 - 255
Byte Ticker Type - holds the tick data type: high, last, vol, bid, etc. 0 - 57
Byte Ticker ID - the symbolID in the Symbols Table (Ex: AMD, INTC, ^DJI) 0 - 255
Float Value - this is the actual data. (Ex: volume, price, etc.) (float)

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.

Image 5

  • 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

Image 6

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

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

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Help desk / Support
United States United States
Ryan White is an IT Coordinator, currently living in Pleasanton, California.

He earned his B.S. in Computer Science at California State University East Bay in 2012. Ryan has been writing lines of code since the age of 7 and continues to enjoy programming in his free time.

You can contact Ryan at s u n s e t q u e s t -A-T- h o t m a i l DOT com if you have any questions he can help out with.

Comments and Discussions

 
QuestionAnother table image is missing Pin
Patrick Scott6-Jan-20 8:41
Patrick Scott6-Jan-20 8:41 
QuestionAnother table image is missing Pin
Patrick Scott6-Jan-20 8:40
Patrick Scott6-Jan-20 8:40 
QuestionMissing another table image Pin
Patrick Scott6-Jan-20 8:38
Patrick Scott6-Jan-20 8:38 
Table image missing after the line
"The next 7 bytes is the first event, the next 7 bytes is the second event, and so on."
QuestionIt appears that your stream moments table is also missing Pin
Patrick Scott6-Jan-20 8:34
Patrick Scott6-Jan-20 8:34 
QuestionMissing Diagram? Chrome does not show the Diagram Pin
Patrick Scott6-Jan-20 8:23
Patrick Scott6-Jan-20 8:23 
GeneralMy vote of 5 Pin
Degryse Kris22-Mar-16 3:00
Degryse Kris22-Mar-16 3:00 
GeneralRe: My vote of 5 Pin
Ryan Scott White22-Mar-16 18:34
professionalRyan Scott White22-Mar-16 18:34 
Questionlook forward to your next article Pin
Southmountain16-Mar-16 15:31
Southmountain16-Mar-16 15:31 
AnswerRe: look forward to your next article Pin
Ryan Scott White16-Mar-16 18:31
professionalRyan Scott White16-Mar-16 18:31 
QuestionAbout high performance Pin
Alexey KK16-Mar-16 9:51
professionalAlexey KK16-Mar-16 9:51 
AnswerRe: About high performance Pin
Ryan Scott White16-Mar-16 18:25
professionalRyan Scott White16-Mar-16 18:25 
GeneralRe: About high performance Pin
Alexey KK24-Mar-16 8:49
professionalAlexey KK24-Mar-16 8:49 
QuestionLink broken Pin
Duncan Edwards Jones14-Mar-16 21:39
professionalDuncan Edwards Jones14-Mar-16 21:39 
AnswerRe: Link broken Pin
Ryan Scott White15-Mar-16 5:02
professionalRyan Scott White15-Mar-16 5:02 
GeneralMy vote of 4 Pin
Duncan Edwards Jones14-Mar-16 21:38
professionalDuncan Edwards Jones14-Mar-16 21:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.