# How should I store tick data?

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?

-
Can use this one: datatime.eu/public/gbot/TickDataBacktesting.htm#tickgrabber –  Pam Apr 15 '13 at 20:55

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.

-
Hi Jeff thanks a lot for your answer. I'm having some problems to generalize your code to have IBrokers download and save more than one symbol at the same time. As you said I've created a list of Contracts and increased the n parameter in the eWrapper function up to the number of contracts contained in the list. Is there a chance you can show me how to do it? Thanks a lot! –  user1332 Sep 2 '11 at 18:05

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
-
I want to analyze the data later in R. Can i use Mysql to connect to the IB server, can you suggest any further information or books i can read related to this topic? p.s I have never used q/kdb+ thought its only commercial. –  Marko Feb 12 '11 at 19:12
@Marko MySQL is a relational (row-oriented) database; it's not column-oriented. As I had mentioned, there are no open-source column-oriented production-quality DBs. –  chrisaycock Feb 12 '11 at 20:19
Do you have a definition of "production-quality"? Seems slightly arbitrary since MonetDB and Infobright would likely argue otherwise. Additionally 'production' for IB data driven clients is going to be far different than GS, Citadel, or the like. –  Jeff R Feb 14 '11 at 3:52

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.

-

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.

-
What do you use to frame your Protocol Buffers? Presumably there are multiple buffers per file, and PB doesn't self-delineate. –  John Zwinck Feb 13 '11 at 21:41
What filesystem do you use? In my case the NTFS (initially empty) broke down after putting some 18 millions (about 10 years of historical data) files into it. Then 'chkdsk' took half a day... Putting the same amount of files into a DB has proven to be much better idea in terms of performance, space and stability. –  wburzyns Mar 6 '11 at 21:49
@John Z: the file is layed out as header + n number of tick messages. The header contains the number so I can skip through a file. I am thinking of implementing a PAX file format similar to Hive's RCFile since unobserved data would never be materialized off of disk. –  Steve Mar 23 '11 at 1:35
@wburzyns: You just need to use the raw NTFS API's which are different from the Win32 API's. NTFS itself can handle hundreds of millions if not billions of objects. –  Steve Mar 23 '11 at 1:36

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.

-

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.

-

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.

-

At AlgoTrader we also use Esper to store all arriving Tick Data in a local Esper Named Window. After a predefined interval, the latest Tick Data snapshot is written to both the filesystem and the database. The actual persistence is done in a separate thread by using Esper Threading. Currently we use MySql but you could just as well use some NoSQL Database (like MongoDB).

Have a look how this is done at our Open Source Page

The Algorithmic Trading Platform AlgoTrader is also available as a Commercial Version