Archiving Tick Data?

[darthtrader 10]

Does anyone on here archive tick data into a database for future use?

With DTN they give you 8 days but it just seems like a waste to not store this information.

What I would love is a program that can store the info into a database that is open as possible so that it could be used no matter what software I change to in the future.

Anyone into this stuff? Maybe a straight out access database or sql db?

[mister ed 10]

Very good question Darth ... I will do some research and see if I can come with anything.

Edited March 5, 2008 by mister ed
sp.

[Sparrow 10]

Darth, you could easily code that with NT, performance would get slightly worse because of having to use real time indicators. Either use market analyzer or 1 tick charts.

I'm doing a variation of this to export 1min price/volume data for a volume based price/volume histogram. But have been to lazy to code the histogram so far .

 

Edit: I complicated things a bit, NT can export tick data just fine, but I think it will only do some if it's already in the DB.

Edited March 5, 2008 by Sparrow

[Sparrow 10]

MySQL, or PostgreSQL would be an option for storing such data. Maybe also SQL Server Express or Compact but no idea if they have got space constraints or are really suitable for the job.

I'd go with MySQL because it's more optimized for speed and the stuff you'd like to do is fairly simple.

 

I suppose the tables could grow quite large but with today's hdds, it shouldn't be a problem.

 

If you get the data into some kind of csv format, you'd be able to load the data like this into a MySQL db.

 

I think it would be tedious work if you cannot automate the export/import. Probably there's a way to this but none of the limited amount of trading software I know allows command line export of data.

Edited March 6, 2008 by Sparrow

[darthtrader 10]

Hey guys,

I think Ninja can actually do this to some degree if you check "store realtime bar data" in options.

My interest though is to get into something more standard/open incase I'm not even using Ninja 3 years down the line. I guess in the meantime the market scanner in ninja would be the way to go.

I found some interesting post on elite searching for SQL. One thing that was interesting that I didn't know is that I guess relational databases aren't really ideal for time series data. It was mentioned a vector database is the way to go.

Someone posted a link to an open source vector DB and a commercial version

http://kx.com/

http://hdf.ncsa.uiuc.edu/HDF5/index.html

 

Could be interesting. MySQL would be cool but if I set this project up I don't want to hit a brick wall 3 years down the line because the DB is so huge and not using the right tool for the job.

[Sparrow 10]

Interesting Darth, however it's questionable if storing the data in a database is necessary at all. If you intend to generate statistical studies with secondary tools from the data it should be in a format that is widely used. The only thing you'd need to worry about is bringing it into the right format (5min/monthly/etc.)

In case you want to write your own studies, a database makes sense, otherwise text files are more efficient which you might choose to compress or not.

 

It could also be better to just purchase the data and save yourself all the hassle.

[darthtrader 10]

Ahh yea, I ran across that idea tonight too. That actually sounds like what the guys who are decent programmers were doing. Just throwing it in a binary/text file and reading in straight away.

Of course buying it would probly be alot better time spent. Nothing though that I really need right now, I would just like to learn this stuff to have a spare computer capturing data for the hell of it I guess.

[micro 10]

  darthtrader said:

Does anyone on here archive tick data into a database for future use?

<snip>

Anyone into this stuff? Maybe a straight out access database or sql db?

 

Hi,

 

Check out http://tree.sourceforge.net. Datafeed is restricted to IB.

 

Regards,

[BlowFish 10]

I used tradestation 2000i to store tick data for the longest time. I still maintgain that data in multicharts though it is less manageable. There are several off the shelf packages that have pretty robust data management facilities. (Neoticker instantly springs to mind).

[darthtrader 10]

I might have to just buy Neoticker at some point. I've read its pretty hard to top as far as its database goes. Still the only issue is see with that is running into something where you want to change software down the road and have this massive database of tick data that can't be converted and would cost far too much to buy.

 

In the meantime I'm just going with flat csv files and will progress as needed. From what i've read about sql is that its just overkill on the overhead and not really ment for time series data.

 

This DB was mentioned on elite, its for storing massive amounts of scientific data and might be interesting, free and open source.

http://www.hdfgroup.org/products/index.html

[mp_trader 10]

You may want to consider breaking up the data into a days worth of tick data per instrument and use the date as part of the filename. Ensign recently switched to a tree structure and individual files like this. Its easy to maintain, and hard drives are cheap.

 

-mp

[januson 10]

  darthtrader said:

From what i've read about sql is that its just overkill on the overhead and not really ment for time series data.

http://www.hdfgroup.org/products/index.html

 

SQL comes in many flavors, so just to say that SQL isn't for time series is overshooting the target. SQL can surely handle time data, in fact it handles time data much quicker and better than anything else on the market - but then again... It depends on your needs, if you want fast access agregated data down to 0ms then you need to code it in the businesslogic layer, this has nothing to do with the storage. If you need historical aggregated data you should look for multidimensional datastorage, but still SQL.

 

If you need quick writing? Wonder who does, but lets discuss, then you'll need Oracle, but MSSQL could be a better choice if you can control your writings in the datalayer

 

So... just to say that SQL is a bad choice is a bit ignorant What you've read could be very wrong!

[davla 10]

Has anyone put together an historical tick database?

I am working in MY SQL as was wondering if anyone has any thoughts on constructing such a task?

I intend on being able to read various file formats and converting to approriate format and also integrating levels of granularity ex: convert to X minutes I believe that there might be an intersting study to accomplish relative to the compositon of tick data in comparison to longer term studies.

I believe that the compositon of longer term bars might place trades/fills in different light and that observations of these fills relative to when the trade occured might yield interesting information

[januson 10]

  davla said:

Has anyone put together an historical tick database?

Have you made any space estimations?

 

For instance, 50.000ticks in a share pr. day, a tick consists of date, time, price and volume.

Date = 8 byte

Time = 8byte

Price = 4byte

Volume = 4byte

 

Date and Time should possible be normalized, which then goes down to 4byte with a pointer to 8 byte.

 

Roughly speaken

1 record of TickData = 4 + 4 + 4+ 4 and the related tables boiles down to 1byte approximately 17 bytes then.

 

50.000 * 17bytes = 850 kbytes pr. day, if you then wants to record 100 shares for a year it would require

850 * 100 * 240 = 20400 mb

 

I don't know if 1000 shares or 50.000 ticks is the reality?

 

Oh btw... I've been working with a database collecting flightinformation, we collected around 2gb of data each day with no problems, but our hardware did had a costprice of $400.000 - anything is possible I guess! The biggest problem we did experience was to get it out again and also if the Internet suddenly went down.

Cheers

[darthtrader2.0 10]

  januson said:

So... just to say that SQL is a bad choice is a bit ignorant What you've read could be very wrong!

 

No its actually a function of time, no matter how you cut it.

Its not hard to argue that HDF5 is vastly better than SQL for storing tick data...its a non issue. There is a reason we have the concept of time series and relational databses, outside of chance.

It is plain and simple.

IF the time put into HDF5 that can make it better than SQL though, is the real variable then do it, if not, then don't.

That doesn't change the fact that SQL is not built for high freq time series and there are DB that are.

you have no real point

[davla 10]

I have set up a sizeable array that can expand to 40Tb, right now we have it running ( hot swapable) with 8 TB. I eventually paln on setitng it up so others cna utilize it via the net, to access what they need if this is possible.

My only interests are in properly back testing trading scenarios.

 

Has anyone worked with OpenTick?owuld be

right now it is shut down, however there might be some people out there that have constructed decent datbases of tick data.

 

It would be nice to collaborate with some for the design.

Is anyone interested?

 

Could anyone possibly offer up a small amount of tick data to with?

 

My effort revolves around creating a platform based on a rules engine where I will create an easy to use rt/historical interface.

 

I know there are many systems out there that are great,

I am not looking to compete with them or to go and sell an application.

 

All I am tring to do is to create an internal module that will serve my needs whihc iis high frequency data anlysis.

 

MYSQL may not be the ideal environment , however until we can afford KX this is what we will use.

 

as mentioned in other threads there are some alternatives, but learning curve is steep.

 

thx

[davla 10]

one other thought

 

has anyone considered reviewing tick data relative to the bid/ask and their quantites at the partucular point in time ?

 

I notie there are other threads on tape reading and some new fancy tools tlaked about, such as CQG's products.

 

My own interstis to analyze their relationships in historical and then can easily set up a real time monitor forthese situations, so it is not so much trying to watch the tape, as it does move quite fast

any thoughts?

[AldPixto 10]

I save tick data from eSignal using QCollector. I have it configured to save separate ascii files for each futures contract I follow, e.g. YGZ8_0.csv , and when those files approach 2Gb I rename to YGZ8_yymmdd.csv (where yymmdd is the last day of full data) and then start collecting a new YGZ8_0.csv file. I configured the file content as:

yymmdd,hhmmss,<trade_price>,<trade_volume>,,,,

yymmdd,hhmmss,,,<bid>,<bid_size>,<ask>,<ask_size>

the contents are CSV as either trades or quotes with best bid/ask

When a contract is a back month I compress it with gzip, so

for example ESH8_0.csv becomes ESH8_0.csv.gz .

Gzip seems to achieve about an 8:1 compression on these ascii files.

My applications recognize the .gz file extension and either decompress the files

when needed or else directly read the .gz files (e.g. java GZIPInputStream).

[SNYP40A1 10]

http://www.oracle.com/database/docs/Berkeley-DB-v-Relational.pdf

[alibaba5055 10]

  SNYP40A1 said:

http://www.oracle.com/database/docs/Berkeley-DB-v-Relational.pdf

 

The updated link for the above mentioned Technical White Paper by Oracle is this -

 

http://www.oracle.com/us/products/database/berkeley-db-v-relational-066565.pdf

 

Thanks

[davla 10]

yes thanls I am very much awatre of Berkley's DBN

... do you have it? u can email me at dmz163

thats my prefix at yah hoo

[alibaba5055 10]

  davla said:

yes thanls I am very much awatre of Berkley's DBN

... do you have it? u can email me at dmz163

thats my prefix at yah hoo

 

Sorry but I do not have it currently.