How should I store tick data?

Question

How should I store tick data? For example, if I have an IB trading account, how should I download and store the tick data directly to my computer? Which software should I use?

Answer 1

Using IBrokers from R is going to be the easiest route. A quick example of capturing data to disk would be:

library(IBrokers)
tws <- twsConnect()
aapl.csv <- file("AAPL.csv", open="w")

# run an infinite-loop ( <C-c> to break )
reqMktData(tws, twsSTK("AAPL"), 
           eventWrapper=eWrapper.MktData.CSV(1), 
           file=aapl.csv)

close(aapl.csv)
close(tws)

This will send CSV style output to disk. Additionally the data can be stored in xts objects within the loop which can be appended to/filled to provide a constant in-memory object to use for analytics. Objects can be shared with many tools - including using the RBerkeley package on CRAN to share objects with other programs with Berkeley DB bindings. This latter approach, if managed intelligently is very, very fast.

Given the symbol limit of IB (100 concurrent more or less) and the 250ms updates - R can typically handle all of this without breaking a sweat (i.e. the JVM running IB's TWS or even IBGateway client is likely to be far surpassing the R/IBrokers process in terms of CPU usage).

You can even extend the syntax above to record more than one symbol by passing a list of Contracts, increasing the number on the eWrapper, and making sure you have a suitable list of files to write to.

In terms of something closer to long-term storage/access, the packages Josh referred to (mmap and indexing) are also very useful. I've given talks with some basic options data examples that are 3-4GB in size without derived columns (12GB total), and I can pull using R-style subsetting syntax any subset I need nearly instantly. e.g. finding 90k+ contracts for AAPL in 2009 (out of 70MM rows) took tens of milliseconds. All without keeping anything in RAM, and all running on a laptop with 2GB of RAM.

I'll likely get some more presentation material for the latter packages put together soon, and will be giving some talk(s) at the upcoming R/Finance conference in Chicago. I am also planning on some public workshops through lemnica related to R and IB for 2011.

Answer 2

What do you want to do with the tick data later? Run analytics? You can save tick data to a flat file for all the software cares, but that would be really slow to access later.

Instead, you should ideally save the data:

• Column-oriented - all elements in a field are stored contiguously for better caching
• Binary - all elements are ready for immediate use; no lexical casting required

There are number of column-oriented databases, though no production quality ones are open-source at the moment. You can try the non-commercial version of q/kdb+ to see what you think of it, though it's a huge learning curve if you aren't used to it already.

Something else to think about when storing tick data is the physical medium. Ideally you'll want:

• Local storage - fetching across NFS is going to be painful
• Solid state - fetching from disk is also painful

Answer 3

If you're planning on analyzing the data later in R, you should take a look at the indexing and mmap packages. Though, as @chrisaycock said, you'll need to save the data in a column-oriented, binary format.

If you're downloading the IB data with R, using IBrokers, you can write your own eWrapper to store the data in whatever format you want.

Answer 4

I am using just a filesystem to store raw tick data. I am using protocol buffers to easily allow multiple languages to consume the data. Part of the reason is that I am moving more stuff onto Amazon's EC2 to use their GPU compute instances and storing data in blobs allows for easy integration with S3. I would love a proper column store put right now this has worked well with low development overhead.

We have also tuned our workloads to get around these constraints. Data is typically worked on for minutes or hours after it is retrieved. We are doing pure algo development and don't have (or need) low latency access in a UI.

Answer 5

IB does not offer tick data. They consolidate their data every 0.3 seconds or so.

If you want to store your data temporarily for import into another system later on, then just store it as a CSV file.

Personally I use IQFeed to download tick data into SQL Server which I then use to run analyses on. When I need to run multiple test runs on the same data, I store the data locally into a file that I just read directly into memory.

Answer 6

If you have an IB account, you can use their API to request market data and save to a flat file. That being said, IB does not offer true tick data, it is filtered and you may want to consider a different data feed if you need true tick data.

Answer 7

I am planning on using a complex event processing platform such as Esper or StreamBase to handle the incoming tick data to generate buy/sell events. The tick data would also be be forwarded through a queue (0MQ or RabbitMQ) to be written to a datastore (file or SQL database). By doing so, I have the data necessary to backtest or analyze in R or MATLAB, while still being able to act on the data "real-time". It seems that, depending on transaction volumes and frequency, that a SQL database may become a bottleneck if you want to act on data near real-time since using a SQL database would require that you pick a trigger or a time period to generate a query.