19
$\begingroup$

I'm looking to convert some tickdata .csv files into a database on a local disk and then use R to call the data and do my various analytics and modelling.

What are some best practices / implementation techniques that could be recommended to minimize any headaches?

Noted that packages such as mmap help enormously, but would like to try and find a more 'permanent' solution.

Which is the best db to play with? MySQL seems to be suboptimal as it's relational (rather than column-oriented). q/KDB seem to have a trial version to play with but has a very steep learning curve. Which is the best db to handle and serve up tick requests?

Any help much appreciated. Platform agnostic, but I suppose Linux is my preferred platform.

Improve this question
$\endgroup$

3 Answers 3

Reset to default
16
$\begingroup$

There are many specialised products for HF tick data. In addition to KDB which you mentioned, there is OneTick, Vertica, Infobright, and some open-source ones like MonetDB etc. (see http://en.wikipedia.org/wiki/Column-oriented_DBMS).

My experience is that Column Oriented Databases are overrated when it comes to tick data, because very often you request the entire tick or bar record (as opposed to just one column of a record - i.e. what Column-Oriented DBs are optimised for). In my experience, the key to speed is much more that you use a clustered index for your database, thus defining the order in which the data is stored on the harddisk. If you primarily query the timeseries of a given instrument (as opposed to the latest prices of a group of instruments), then you want to cluster by (Instrument, TickTimestamp), making queries extremely fast even for huge table sizes.

Then there is also the school of thought that plays around with new alternatives out of the NoSQL corner, such as BigTable, MongoDB, etc. It's an interesting area, but my personal believe is that they are made primarily for flexible datamodels, which is not our core requirement. You can make them work, and they'll work very fast, but this comes at the cost of more archaic tool support, steeper learning curves, etc.

I have been using many different databases (Oracle, MySQL, SQLServer, MongoDB, MonetDB) over the years, and my conclusion is that most of them work pretty decently for storing financial timeseries data if you understand them and design them accordingly. Currently, I'm using primarily SQLServer, which is somewhat faster than MySQL, free for smaller datasets, and does most of the things I want. Support for R (and Matlab and many other environments) is very decent through the ODBC R package.

Improve this answer
$\endgroup$
3
  • 4
    $\begingroup$ ODBC will always be slower than direct binary connections, so it is really just the fallback choice. I once built a direct R bridge to OneTick using their C++ API -- that was pretty fast for large data too. $\endgroup$
    – Dirk Eddelbuettel
    Commented Feb 2, 2012 at 18:05
  • 5
    $\begingroup$ Of course ODBC adds an overhead, just as JDBC does. But my experience is that for many applications you can neglect it, because it's just an overhead in terms of latency. Often, it's not the latency of a query that's important, but the execution of the query itself. And that is done in the respective database engine, no matter if you use a bridge to connect to it or not. My advice would always be to start with what's available for free, and scale up you tech stack (either by buying or by building) as need occurs. $\endgroup$
    – Christoph Glur
    Commented Feb 3, 2012 at 12:55
  • $\begingroup$ I think the the mongodb learning curve is much smaller than that of many other sql based databases. The query language is great and quite easy (especially if you have any familiarity with bson/json) $\endgroup$
    – NoviceCoding
    Commented Feb 8, 2012 at 3:29
6
$\begingroup$

Using MySQL for financial data is not unreasonable. But for tick data are you ever going to do anything except a query on a date range? For analyzing tick data in R I generally keep it in a disk file, one tick file per day, and load the files in as I need them. Using .RData files instead of csv files is quicker.

I've also used custom C++ classes before, to really get quick, but if the data is then to be analyzed in R, there is not much point.

Improve this answer
$\endgroup$
2
  • 3
    $\begingroup$ I do it similarly. There is no point in using DBs if there is no or limited concurrent access. I use plain binary files stored in a columnar manner from which I load the data into memory in one sweep. I've benchmarked this against a few DBs (including range retrievals on other criteria than time) and POBs win hands down. $\endgroup$
    – emsfeld
    Commented Feb 9, 2012 at 4:52
  • $\begingroup$ I currently also do this (for tick data, e.g. transaction, best bid, and best ask), but as my dataset has expanded I've found the number of files on my hard disk to be annoyingly large, i.e. ~2 million. If you'd be willing to expand your answer to discuss this issue I'd be very interested in reading it (as I'm currently looking for alternatives). $\endgroup$
    – Colin T Bowers
    Commented Jul 3, 2014 at 3:16
1
$\begingroup$

I have had success using MySQL to store both OHLCV, Options data and metadata such as earnings dates in MySQL and accessing both for reads and writes from R.

For me this works very nicely and is performant for daily data, if you are doing HFT you may want to consider a specialized tickdb, but at daily scales (252 returns per year per ticker - MySQL is plenty fast). Also, it's much better than flat files because you'll find at some point that you do want to issue relational queries to find time series that match certain aggregate patterns.

Examples that I typically do are to JOIN OHLC data with Options data for a given ticker and merge by date, sure R's XTS::merge can help here as well but it's often more performant to issue SQL queries direcltly and use R's dataframes/XTS for 'fine tuning' once you narrow down from Gigs of data (which R is not great at) to a few MB.

If there is interest - I could consider sharing my .R modules for interfacing with MySQL for both daily downloading and storing of data (e.g. from Yahoo for OHLC of course), as well as querying.

My advice is don't overdesign it - if you don't need the perf of a specialized tick DB, and for daily scales I don't think you do - go with what's proven and simple. MySQL works.

Improve this answer
$\endgroup$
2
  • $\begingroup$ I'd be interested in seeing those R modules! $\endgroup$
    – Patrick
    Commented Feb 1, 2012 at 13:39
  • 3
    $\begingroup$ The beancounter package has been available since the late 1990s to download price data, automatically store it in SQL backends with support for PostgreSQL, MySQL, SQLite and ODBC, and also run P/L and risk (VaR) reports. Runs just fine as a cron job, and its easy to then chain R jobs onto it once the data is in SQL. Beancounter itself is a small Perl package and runs on any OS just fine. $\endgroup$
    – Dirk Eddelbuettel
    Commented Feb 2, 2012 at 18:07

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.