Algorithmic Trading Group Forum

Algorithmic Trading Group Forum
https://www.algotradinggroup.com/cgi-bin/yabb2/YaBB.pl
Trading Strategies >> Software Infrastructure >> How do you store historical / tick data?
https://www.algotradinggroup.com/cgi-bin/yabb2/YaBB.pl?num=1219399564

Message started by kirppu on 08/22/08 at 11:06:04

Title: How do you store historical / tick data?
Post by kirppu on 08/22/08 at 11:06:04

Do you store data in plain files using NetCDF, standard relational databases such as MySQL or Postgres or vector databases such as kdb?

Title: Re: How do you store historical / tick data?
Post by Co0olCat on 08/22/08 at 11:09:08

My choice is to use Postgre. It is free and robust. Later on you can migrate to commercial solution. You can use ODBC for API.

Title: Re: How do you store historical / tick data?
Post by kirppu on 08/22/08 at 13:37:17

Interesting... Do you get an acceptable performance with Postgres to store tick data? Even for equitites, a typical index future (i.e DAX 30) contains 15k / 20k ticks a day, i.e. nearly 4mio a year. You'll have 10 times more data for options. Currently, I store data in flat files (using netCDF), load the dataset into memory and launch my algorithms.

Title: Re: How do you store historical / tick data?
Post by Co0olCat on 08/22/08 at 13:47:32

kirppu wrote on 08/22/08 at 13:37:17:

I did not have any performance issues. As for the quantity, one can use distributed approach.

Title: Re: How do you store historical / tick data?
Post by kirppu on 08/22/08 at 13:52:19

Thanks! How much data do you store?

Title: Re: How do you store historical / tick data?
Post by Co0olCat on 08/22/08 at 14:14:12

PM me to discuss it.

Title: Re: How do you store historical / tick data?
Post by Yury R on 08/22/08 at 14:49:16

What is relational in timeseries data? Does it have to be in a relational SQL database?

Or is it just indexing functionality for faster retrieval important?

Title: Re: How do you store historical / tick data?
Post by stargrazer on 10/05/08 at 13:06:37

kirppu wrote on 08/22/08 at 11:06:04:

Do you store data in plain files using NetCDF, standard relational databases such as MySQL or Postgres or vector databases such as kdb?

I have been using the C++ version of the HDF5 [1] library. I've written a template form of an iterator to store and retrieve four types of datasets: quotes, trades, bars, and marketdepth. As entries use the datetime format from the Boost Date_Time [2] library, a container using the iterator can easily select specific data ranges using date/time ranges or counts of values. An example iterator request might be to retrieve the last 20 bars of a bar range,l or to request 200 bars starting with some specific date.

[1] http://www.hdfgroup.org/HDF5/index.html
[2] http://www.boost.org/doc/libs/1_36_0/doc/html/date_time.html

Title: Re: How do you store historical / tick data?
Post by ocTrader on 01/22/09 at 01:14:31

Hi, I created tables in ms sql server for bid/ask tick data. Do you guys store data in each instrument ? eg. IBM table, GOOG table. Or put everthing in one big table and query it as needed ? Thanks.

Title: Re: How do you store historical / tick data?
Post by Co0olCat on 01/22/09 at 03:16:25

It is up to you. In either case you can write lookup procedure to automatically retrieve the data.

Title: Re: How do you store historical / tick data?
Post by ocTrader on 01/22/09 at 03:35:01

Co0olCat wrote on 01/22/09 at 03:16:25:

It is up to you. In either case you can write lookup procedure to automatically retrieve the data.

Thank you co0olCat ! BTW any suggestion on data vendor ? I currently getting data from CQG. But I will appreciate if you can recomand other good data vendor for Bid/Ask size. Thanks again.

Title: Re: How do you store historical / tick data?
Post by Algo Designer on 01/22/09 at 05:41:09

I used both combined tables and separated by symbol and exchange. If you are unlikely to perform complex queries, you might consider bypassing the SQL layer altogether and employ Oracle Berkley DB.

Title: Re: How do you store historical / tick data?
Post by Co0olCat on 03/08/09 at 01:15:22

ocTrader wrote on 01/22/09 at 01:14:31:

Hi, I created tables in ms sql server for bid/ask tick data. Do you guys store data in each instrument ? eg. IBM table, GOOG table. Or put everthing in one big table and query it as needed ? Thanks.

I came to the conclusion that from performance, distribution and storage prospectives keeping each instrument in individual tables are optimal solution.

Title: Re: How do you store historical / tick data?
Post by Alexander Temerev on 04/07/09 at 01:53:11

Oh, the pain...

If you want to invest around $20K, there is an excellent high-performance time series data storage solution called KDB+ (http://www.kx.com/Products/kdb+.php). I can't recommend it highly enough.

Or, if you want something simpler, you can contact me and I can sell you my own tick data storage system written in Java. It doesn't have all KDB+ features but you can store unlimited amounts of tick data and retrieve it for any time interval you want (no aggregation features yet, but will be available in next release). There is no significant performance degradation.

For a variety of reasons, you cannot store more than several millions of tick data entries in a relational DBMS (MySQL, PostgreSQL, Oracle etc.) without severe performance degradation.