Algorithmic Trading Group Forum - How do you store historical / tick data?

kirppu Newbie Offline I like this forum! Posts: 15 Dublin Gender:	How do you store historical / tick data? 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?
Back to top	IP Logged

Co0olCat Forum Moderator Offline Posts: 128 Gender:	Re: How do you store historical / tick data? Reply #1 - 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.
Back to top	"Yesterday is history. Tomorrow is a mystery. But today is a gift, and that is why it's called the present." IP Logged

kirppu

Newbie

Offline

I like this forum!

Posts: 15
Dublin
Gender: male

Re: How do you store historical / tick data?
Reply #2 - 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.

Back to top

IP Logged

Co0olCat

Forum Moderator

Offline

Posts: 128

Gender: male

Re: How do you store historical / tick data?
Reply #3 - 08/22/08 at 13:47:32

kirppu wrote 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.

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

Back to top

"Yesterday is history. Tomorrow is a mystery. But today is a gift, and that is why it's called the present."

IP Logged

kirppu Newbie Offline I like this forum! Posts: 15 Dublin Gender:	Re: How do you store historical / tick data? Reply #4 - 08/22/08 at 13:52:19 Thanks! How much data do you store?
Back to top	IP Logged

Co0olCat Forum Moderator Offline Posts: 128 Gender:	Re: How do you store historical / tick data? Reply #5 - 08/22/08 at 14:14:12 PM me to discuss it.
Back to top	"Yesterday is history. Tomorrow is a mystery. But today is a gift, and that is why it's called the present." IP Logged

qroach Full Member Offline Posts: 102 London Gender:	Re: How do you store historical / tick data? Reply #6 - 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?
Back to top	IP Logged

stargrazer

Newbie

Offline

I like this forum!

Posts: 7

Re: How do you store historical / tick data?
Reply #7 - 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

Back to top

IP Logged

ocTrader Newbie Offline I like this forum! Posts: 4 Gender:	Re: How do you store historical / tick data? Reply #8 - 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.
Back to top	IP Logged

Co0olCat Forum Moderator Offline Posts: 128 Gender:	Re: How do you store historical / tick data? Reply #9 - 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.
Back to top	"Yesterday is history. Tomorrow is a mystery. But today is a gift, and that is why it's called the present." IP Logged

ocTrader

Newbie

Offline

I like this forum!

Posts: 4

Gender: male

Re: How do you store historical / tick data?
Reply #10 - 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.

Back to top

IP Logged

Algo Designer Forum Administrator Offline Nihil desperandum Posts: 182 Sydney Gender:	Re: How do you store historical / tick data? Reply #11 - 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.
Back to top	"Success is the sum of small efforts, repeated day in and day out..." IP Logged

Co0olCat

Forum Moderator

Offline

Posts: 128

Gender: male

Re: How do you store historical / tick data?
Reply #12 - 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.

Back to top

"Yesterday is history. Tomorrow is a mystery. But today is a gift, and that is why it's called the present."

IP Logged

Alexander Temerev

Newbie

Offline

Mercata facti
consequens

Posts: 4
Geneva, Switzerland
Gender: male

Re: How do you store historical / tick data?
Reply #13 - 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.

Back to top

IP Logged

	Welcome, Guest. Please Login