best SQL schema for tick data | Forex Factory

Options

Permalinks
Print Thread

Similar Threads

What platform has tick by tick (multiple chart filling) data replay? 4 replies

Tick Size, Tick Rate, Tick Spread, and Tick Logger indicators 36 replies

Where can I find tick-by-tick data for various currency pairs? 9 replies

download data tick by tick ??? help 1 reply

best SQL schema for tick data

  • #1
  • First Post: Oct 12, 2010 10:11pm Oct 12, 2010 10:11pm
I am planing to dump the tick data from dukascopy into mssql database for offline statistical analysis.

I have heard people saying that relational database is not best suited for the time series data, but it is easy to do different query with some sql script, which save you time for writing code to analyze binary or csv data. So I decide to go the database way.

The data from dukascopy is:
time; bid; bid volume; ask; ask volume

so I am assuming naturally the sql table structure is:
time(datetime); double bid(decimal); bid volume(int); ask(decimal); ask volume(int)

for these who has done this, please advise.
  • #2
  • Oct 13, 2010 12:32am Oct 13, 2010 12:32am
Well if your database isn't constantly expanding, it doesn't really matter what schema you use. I would be more worried about getting the queries working first then focusing on optimizing the database.
  • #3
  • Oct 13, 2010 7:12am Oct 13, 2010 7:12am
Thanks for responding Ron. How is trading doing?

I am actually considering break up dateTime column into date and time, so it is flexible to query. I was wondering if anyone has done it this way.
The advantage of doing this is you can use date as a foreign key criteria and compare price of different date same time period with less query string.
  • #4
  • Oct 15, 2010 4:02pm Oct 15, 2010 4:02pm
Just an idea.

First of all I am very new to this kind of stuff but I'll mention a few things here that may help you along the way.

http://csved.sjfrancke.nl/

This is a really handy .csv editor if you need to do any editing on your data before importing it. Whats quite good about this is that you can add characters in to already existing data. For example forex tester data has no separators between Hours Min's Seconds, and hardly any programs recognize it like that, but with csved you can add the separators.

Secondly and I'm still working on this but you can use a data miner to extract the time relevant data. These 2 are real easy to use just look at tutorials on you tube.

http://www.knime.org/

http://rapid-i.com/

Also I believe(haven't tried it yet) but I believe you can export to R project.

http://www.r-project.org/

Which is a real powerful statistical analysis tool.

The best thing with the last three mentioned software names is that there open source and free to use! : )

http://www.forexfactory.com/showthread.php?t=167720

The thread above was started by Mikkon who's also using SQL to interrogate a database.

Good luck, hope this helps.

James.
  • #5
  • Oct 15, 2010 5:38pm Oct 15, 2010 5:38pm
I'm doing the exact same thing...
The dukascopy data seems very good but would take ages to download as the files are split by hour.

I'm using MySQL as my database but here's my SQL code:
Inserted Code
CREATE TABLE `tick`
(
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`providerId` INT UNSIGNED NOT NULL DEFAULT 0,
`pairId` INT UNSIGNED NOT NULL,
`time` DATETIME NOT NULL,
`msec` INT UNSIGNED NOT NULL DEFAULT 0, -- used with high-quality tick feeds (eg. Dukascopy)
`bid` INT UNSIGNED NOT NULL,
`ask` INT UNSIGNED NOT NULL,
`gcId` BIGINT UNSIGNED UNIQUE, -- GAIN Capital's ID, unique to each tick (used for reference)

INDEX(`pairId`,`time`,`msec`),
INDEX(`time`,`msec`)
);

I don't yet have the BidVolume and AskVolume but I'll probably add them too. I use 'providerID' to denote the data soruce: dukascopy/Gain Capital/Alpari ... etc.

Hoppe this helps
  • #6
  • Oct 16, 2010 4:50am Oct 16, 2010 4:50am
relational databases are a poor choice for timeseries data, and doubly so for high frequency tick data. I am logging 100000+ ticks on EURUSD _alone_ _per_ _day_. If you're tracking 10 pairs you're looking at a million rows per day. I doubt you can support that on mssql (or even oracle) on consumer grade hardware..
  • #7
  • Oct 16, 2010 10:21am Oct 16, 2010 10:21am
Thanks James for the info about the csv editor, i will experiment and break down to datetime column into day, hour, minutes column. Some of these will be foreign keys so you can query the pattern against hour, minutes, etc of each day.

will look further into knime and R. Seem like very good tool for analyze data.
Quoting redbaron1981
Just an idea.

First of all I am very new to this kind of stuff but I'll mention a few things here that may help you along the way.

http://csved.sjfrancke.nl/

This is a really handy .csv editor if you need to do any editing on your data before importing it. Whats quite good about this is that you can add characters in to already existing data. For example forex tester data has no separators between Hours Min's Seconds, and hardly any programs recognize it like that, but with csved you can add the separators.

Secondly and I'm still...
  • #8
  • Oct 16, 2010 10:25am Oct 16, 2010 10:25am
Frinks,
Bid volume and Ask volume is sometime good for momentum analysis. You can concatenate the individual tick csv into one big file.
Thanks for the schema by the way.

Quoting FrinkFX
I don't yet have the BidVolume and AskVolum.e but I'll probably add them too. I use 'providerID' to denote the data soruce: dukascopy/Gain Capital/Alpari ... etc.

Hoppe this helps
  • #9
  • Oct 16, 2010 11:25am Oct 16, 2010 11:25am
Quoting pippero
If you're tracking 10 pairs you're looking at a million rows per day. I doubt you can support that on mssql (or even oracle) on consumer grade hardware..
Maybe smaller is better. MySQL will run on "consumer grade" hardware and is said to be amongst the fastest known databases when it comes to read only queries. I would not install something of the caliber of Oracle or a comparably huge monster database with thousands of features you won't ever need for a task like this.
  • #10
  • Oct 16, 2010 1:50pm Oct 16, 2010 1:50pm
There are many discussion about tick storage on ET:
http://www.elitetrader.com/vb/showth...threadid=81345
http://www.elitetrader.com/vb/showth...hreadid=201252
http://www.google.com/search?q=tick%...litetrader.com

I use compressed binary files for tick data storage, since I have over 4 billion ticks - all available instrument from Dukascopy, GAIN, dbFX, FXCM and TrueFX.

I used SQLite first, then PostgreSQL, but they just don't scale to this amount of tick data (at least on high-end desktop hardware).
  • #11
  • Oct 16, 2010 2:29pm Oct 16, 2010 2:29pm
Quoting 7bit
Maybe smaller is better. MySQL will run on "consumer grade" hardware and is said to be amongst the fastest known databases when it comes to read only queries. I would not install something of the caliber of Oracle or a comparably huge monster database with thousands of features you won't ever need for a task like this.
Sure, as long as you dont plan on logging more than a few instruments for a few months.
  • #12
  • Oct 17, 2010 11:20am Oct 17, 2010 11:20am
Quoting pippero
Sure, as long as you dont plan on logging more than a few instruments for a few months.
How can you query your huge monster CSV files faster than a DB server could do it with its highly optimized storage and query engine?
  • #13
  • Oct 17, 2010 1:09pm Oct 17, 2010 1:09pm
Hey Guys,

In my above post I mentioned my idea of breaking the data down with a data miner to separate the time groups I was looking for. This is actually quite an easy task and I can export the results to .csv or R for analysis.

I want to finish with a database where I can extract information such as average hourly ranges, when statistically that the High or low of the day was made etc etc a little bit like these charts on Mikkoms thread.

http://www.forexfactory.com/showthre...17#post2720917

I know that I can extract the time/date data I want quite easily however my problem would be from this point trying to extract the exact values for high lows etc to be able to generate a chart.

Do you guys think that it would be better for me to use Mysql for Db interrogating rather than a data miner and would it then be easier for me to extract what was needed rather than using a script to extract it?

Your advice would be much appreciated.
  • #14
  • Oct 18, 2010 6:59am Oct 18, 2010 6:59am
Quoting neomn
Frinks,
Bid volume and Ask volume is sometime good for momentum analysis. You can concatenate the individual tick csv into one big file.
Thanks for the schema by the way.
No problem. The reason I have the bid/ask as unsigned integer BTW is because my system avoids floating-point arithmetic as much as possible and does every thing in pips and pennies. This provides a huge boost (I hope)

To doubt I'll include bid volume and ask volume once I've done some work on the system and got some sort of stable version of it up-and-running.
  • #15
  • Nov 3, 2010 4:12pm Nov 3, 2010 4:12pm
To visit this website could be a good idea. It works perfectly.

http://eareview.net/tick-data

Cheers...
  • #16
  • Nov 7, 2010 4:17am Nov 7, 2010 4:17am
Quoting 7bit
How can you query your huge monster CSV files faster than a DB server could do it with its highly optimized storage and query engine?
Where do you think a DB stores its data, all in RAM? No, it's on disk like a csv. A simple file and directory naming scheme will go a long way.

Let me remind you that tick data is generally strictly sequential and immutable -- it's not like you need to be able to do DELETEs and UPDATEs on tick data that you logged yesterday. and when you need to do a SELECT, you usually need to select a bunch of "records" that are in sequential order.

the only thing faster than a flat file is a binary flat file.. Don't use relational dbs when there are no relations to keep track of.
  • #17
  • Nov 7, 2010 1:10pm Nov 7, 2010 1:10pm
but dont take my word for it -- try theirs: http://www.puppetmastertrading.com/b...rt-2/#more-280

some more appropriate dbs for columnar tick data:

http://www.infobright.org/ $$$
http://monetdb.cwi.nl/ opensource
http://kx.com/ $$$

and file formats:

http://en.wikipedia.org/wiki/Hierarchical_Data_Format
  • #18
  • Feb 16, 2011 10:52pm Feb 16, 2011 10:52pm
Quoting pippero
but dont take my word for it -- try theirs: http://www.puppetmastertrading.com/b...rt-2/#more-280

some more appropriate dbs for columnar tick data:

http://www.infobright.org/ $$$
http://monetdb.cwi.nl/ opensource
http://kx.com/ $$$

and file formats:

http://en.wikipedia.org/wiki/Hierarchical_Data_Format

Hi, Pippero,

Is there a step-by-step instruction to build those HDF5 files from ground up?

Thank you in advance!
  • #19
  • Last Post: Feb 16, 2011 11:09pm Feb 16, 2011 11:09pm
I keep all my data in Ninja Trader ver 6 database, which is MS Access. (Ver 7 has been upgraded to SQL). It's not a huge amount. NT has a good built-in Strategy Tester.
0 traders viewing now