32
$\begingroup$

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?

Improve this question
$\endgroup$
1

9 Answers 9

Reset to default
25
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • $\begingroup$ 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! $\endgroup$
    – user1332
    Sep 2, 2011 at 18:05
  • $\begingroup$ any ways to cap the CSV file like keep only 100 latest lines $\endgroup$
    – PirateApp
    Apr 13, 2018 at 13:54
18
$\begingroup$

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
Improve this answer
$\endgroup$
3
  • $\begingroup$ 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. $\endgroup$
    – Marko
    Feb 12, 2011 at 19:12
  • 1
    $\begingroup$ @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. $\endgroup$
    – chrisaycock
    Feb 12, 2011 at 20:19
  • 3
    $\begingroup$ 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. $\endgroup$
    – Jeff R
    Feb 14, 2011 at 3:52
13
$\begingroup$

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.

Improve this answer
$\endgroup$
4
$\begingroup$

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.

Improve this answer
$\endgroup$
4
  • $\begingroup$ What do you use to frame your Protocol Buffers? Presumably there are multiple buffers per file, and PB doesn't self-delineate. $\endgroup$
    – John Zwinck
    Feb 13, 2011 at 21:41
  • $\begingroup$ 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. $\endgroup$
    – wburzyns
    Mar 6, 2011 at 21:49
  • $\begingroup$ @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. $\endgroup$
    – Steve Severance
    Mar 23, 2011 at 1:35
  • $\begingroup$ @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. $\endgroup$
    – Steve Severance
    Mar 23, 2011 at 1:36
2
$\begingroup$

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.

Improve this answer
$\endgroup$
2
$\begingroup$

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.

Improve this answer
$\endgroup$
2
$\begingroup$

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

Disclosure: I work for AlgoTrader

Improve this answer
$\endgroup$
4
  • 2
    $\begingroup$ There are a whole slew of newer unanswered questions. Constantly posting about your employer on really old, heavily answered questions doesn't help anyone. $\endgroup$
    – chrisaycock
    Jun 13, 2013 at 11:30
  • $\begingroup$ ok, will keep that in mind. I was trying to answer questions that sounded most relevant to what we have done with AlgoTrader and that I assumed would help others. But you are right, the questions are quite old, so I guess not many people would actually look at them anymore. $\endgroup$
    – Andy Flury
    Jun 13, 2013 at 14:38
  • 3
    $\begingroup$ You've done the same thing on Stack Overflow, where you posted numerous times about the company you own on old questions. This is just noise. $\endgroup$
    – chrisaycock
    Jun 13, 2013 at 14:42
  • $\begingroup$ Actually his comment was helpful. On old questions I often scan for the newest answers to see what else may have come out in the past couple of years. $\endgroup$
    – Ethereal
    Feb 4, 2014 at 16:24
1
$\begingroup$

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.

Improve this answer
$\endgroup$
0
$\begingroup$

I keep seeing that IB does not offer true tick data and I don't know if this is because these comments are old and they DID NOT offer true tick data at the time, but from my research they DO OFFER TRUE TICK DATA. (I called them and asked, because I saw in a few places where people said they do not offer it. You have to sign up for market data to get the true tick data, though.) Just wanted to put this out here for other people that come across it.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Edit: Note, this true tick data is for Level II data, not Level I. $\endgroup$
    – HelloWorld
    Jun 27, 2017 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.