Tick Database Storage - Platforms and Indicators | futures io social day trading
futures io futures trading


Tick Database Storage
Updated: Views / Replies:17,923 / 40
Created: by MXASJ Attachments:3

Welcome to futures io.

(If you already have an account, login at the top of the page)

futures io is the largest futures trading community on the planet, with over 90,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors Ė all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you donít need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

Reply
 3  
 
Thread Tools Search this Thread
 

Tick Database Storage

  #21 (permalink)
Elite Member
Chicago
 
Futures Experience: Intermediate
Platform: NinjaTrader
Broker/Data: Mirus/Zen-FIre
Favorite Futures: GC, ES
 
Posts: 73 since Mar 2010
Thanks: 40 given, 31 received

Trader.Jon,

Have you used this? I'm very interested and may be able to help him.
Looks like the last update was in 2008.

Reply With Quote
 
  #22 (permalink)
Elite Member
Near the BEuTiFULL Horse Shoe
 
Futures Experience: Beginner
Platform: NinjaTrader
Broker/Data: MBTrading Dukascopy ZenFire
Favorite Futures: $EURUSD when it is trending
 
Trader.Jon's Avatar
 
Posts: 500 since Jul 2009
Thanks: 401 given, 176 received

No: I have not made any steps other than connecting with the source code maintainer, and also trying to contract a local programmer to build a linux box with Barchart API connectivity to TREE. YES: I was trying to build exactly what MXASJ described in his 'Tick Database' initial post.

The programmer I actually met in real time (ie a local with many years experience varying from micocode controllers to neural nets) stopped responding to emails and phone calls after I passed on (to him) the TREE info and the Barchart.com (development tools logins) I had aquired for the project. I got discouraged and moved on. Too much to do: not enough time

The TREE source code provider has a contact info on the webpage (or I can pass it on to you [along with the only email I exchanged with him]): I contacted him to see if there was an API already built for TREE>>Barchart as I did not want to use the available IB API (as a data source IB is lacking as not all data is sent). Only the IB API is available.

Long term my plan was:
1. build the TREE server with a cluster or parallel configuration
2. use data for NT live (less stress on NT [lol] and historical for building neural net/CUDA
3. data exchange 24 hour delay basis for 'fills' with others that might have had disconnects etc

Reply With Quote
 
  #23 (permalink)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,352 given, 83,230 received


I want to bring this thread alive again, and I'm going to cheat and move it out of the NinjaTrader section because I think the thread itself could be platform independent.

I'm not a DBA so could use some advice. As I previously wrote in post #2, I had previously used NinjaTrader to store tick data into a MySQL database, but it was expensive. And I haven't even booted up Ninja in well over a year, in fact I don't even have it installed anymore, so I need a new way to do this.

Right now I have a huge amount of data in a proprietary database (MultiCharts), but I'd like to decentralize it and just have my own private database so I can import and export to it as I please.

At this stage, I am not trying to interface with an API to record incoming ticks live as they come, although that naturally is the ultimate goal. For phase 1, I'd just like to import raw data which I've already got (exported from existing platform) in tick form into a SQL db.

I think the database also needs to be smart enough to handle bid/ask sequencing, which means we need more than just instrument, date, time, last trade, and volume --- we need bid and ask plus a tickid sequencing (like dtn iqfeed).

An example:
Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).


So, who wants to help build this? I'm looking for advice on the db structure, storage engine, layout etc. I'm happy to share the data I've accumulated in exchange (Elite only).

Mike

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

Reply With Quote
 
  #24 (permalink)
Elite Member
London, UK
 
Futures Experience: Beginner
Platform: NinjaTrader, home-grown Java
Broker/Data: IB/IQFeed
Favorite Futures: EUR/USD
 
Adamus's Avatar
 
Posts: 1,085 since Dec 2010
Thanks: 470 given, 778 received

Yes Mike, I'm interested in doing this kind of thing.

I can definitely help on the table structure.

I'm not so confident I know enough to say that using an SQL database is definitively a good idea, given the comments above, but I don't see why not if the database tables are correctly indexed. However I think it would be easy to test performance to see whether it meets expectations.

What documentation method do you use for database schemas? I used to use entity relationship diagrams that could output SQL, like Visio, but more recently I just write the schema create sql.

You can discover what your enemy fears most by observing the means he uses to frighten you.
Reply With Quote
 
  #25 (permalink)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,352 given, 83,230 received

Nothing so fancy, just the schema in sql directly. I guess I will need to see the "downside" with my own eyes for reasons to not use mysql, I really can't imagine that performance wouldn't be more than adequate. Main question for step 1 is just the table structure and if one storage engine makes sense over another (myisam, innodb, etc).

Mike

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

Reply With Quote
 
  #26 (permalink)
Elite Member
London, UK
 
Futures Experience: Beginner
Platform: NinjaTrader, home-grown Java
Broker/Data: IB/IQFeed
Favorite Futures: EUR/USD
 
Adamus's Avatar
 
Posts: 1,085 since Dec 2010
Thanks: 470 given, 778 received

As far as I remember, innoDB gives you the ability to use relational integrity so you can lock down foreign keys to primary keys that actually exist. I think there are other engines that are better for faster access.

I always only used innoDB in team projects because it gives more control to keep the data in the database in order, by preventing anyone (inc. myself) from stuffing it full of orphan records, e.g. lots of records in the Contract table without a valid Market foreign key.

IIRC with innoDB, there's an extra indexing statement you have to add to apply the indices - no big deal though.

So how many tables are you talking? First off, do bid/ask ticks go in the same table as last ticks?

 
Code
create table TICK (
    ID integer,
    CONTRACT_ID integer,
    TIME datetime not null,
    LAST decimal(15,6),
    LAST_SIZE integer,
    BID decimal(15,6),
    ASK decimal(15,6),
    constraint TICK_PK primary key (ID),
    constraint TICK_CONTRACT_FK foreign key (CONTRACT_ID) references CONTRACT (ID)
);
That's a completely FIRST atttempt! I guess you also want running volume total in there too if it doesn't get calculated by the app as you go along - but I'd be tempted to leave it out since a lot of charts won't be intereested in the time frame used to calculate the current total and would calculate it anyway. That's just a guess, I've never worked with volume.

It looks like the tick id is going to be your primary key value - as long as you guarantee the tick id is always unique.

Extra question - date/time - I would put the timestamp with date and time in one column since that's what the datetime data type is designed for. It might be better to stick with seperate date and time fields though if most software wants them that way. I don't know if it would be possible to merge 2 seperate fields at import - it might require a seperate SQL operations afterwards.

You can discover what your enemy fears most by observing the means he uses to frighten you.
Reply With Quote
The following user says Thank You to Adamus for this post:
 
  #27 (permalink)
Membership Revoked
Szczecin
 
Futures Experience: Intermediate
Platform: Ninja, writing own now
 
Posts: 212 since Mar 2010
Thanks: 47 given, 102 received

I tried doing that for some months now on the side (besides a freaking 10 hour per day project) and I am giving up - makes zero sense. Well, at least for me. Storing ticks book 2 with all bid / ask changes for the complete cme group (around 600 million rows per day) is not somethign I think can be effectively done. Jsut to give you an idea - my current hardware is a dedicated SQ LServer ,16gb ram, 4 cores assigned (virtual machine, but I control the underlying hardware) with the storage consisting of 8 Velociraptors and one SSD.

The overhead is just too much.

I am now moving towards storing binary blobs for one base symbol + instrument type (example: YM-Futures) for a specific interval (one hour) and trying to use highly efficient extraction methods. This is a LOT more efficient in terms of storage

Reply With Quote
The following user says Thank You to NetTecture for this post:
 
  #28 (permalink)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,352 given, 83,230 received


NetTecture View Post
I tried doing that for some months now on the side (besides a freaking 10 hour per day project) and I am giving up - makes zero sense. Well, at least for me. Storing ticks book 2 with all bid / ask changes for the complete cme group (around 600 million rows per day) is not somethign I think can be effectively done. Jsut to give you an idea - my current hardware is a dedicated SQ LServer ,16gb ram, 4 cores assigned (virtual machine, but I control the underlying hardware) with the storage consisting of 8 Velociraptors and one SSD.

The overhead is just too much.

I am now moving towards storing binary blobs for one base symbol + instrument type (example: YM-Futures) for a specific interval (one hour) and trying to use highly efficient extraction methods. This is a LOT more efficient in terms of storage

Are you saying the load was too high?

Were you storing in real time, I assume? That is not required for me in phase 1, I am just importing.

If the issue were table scans, perhaps a unique DB per instrument instead of one gigantic db? Again, not a dba, which is why I'm looking for input.

The server is a Dual Xeon 5430, 24GB. I'm looking for input as to what the issue was on your side, and how many instruments you were archiving. I plan to only archive about a dozen instruments, perhaps that is the major difference between us.

Mike

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

Reply With Quote
 
  #29 (permalink)
Elite Member
CO
 
Futures Experience: Intermediate
Platform: NinjaTrader
 
Posts: 29 since Sep 2009
Thanks: 228 given, 45 received

MySQL Issue

Another consideration is that MySQL timestamps do not support millisecond resolution. If millisecond resolution is needed, you cannot use a Timestamp field.

Instead, use a Decimal(17,3) field.

Here's a link describing a work around: Once upon a timestamp(milliseconds)…. MySQL Preacher

Reply With Quote
The following user says Thank You to samurai for this post:
 
  #30 (permalink)
Elite Member
Paris
 
Futures Experience: None
Platform: NinjaTrader
 
Posts: 1,176 since Oct 2009
Thanks: 227 given, 4,095 received


I remember that on SQLServer you can use table partitioning and automatically split your tables on your disks.

Also remember data rows are stored on disk in the order of the clustered index, so it must be chosen wisely, depending on your queries.

To add on performance issues, I'm not sure SQL is the way to go, it just eats up too much space if you use the basic data types. So you end up implementing your own binary database inside an SQL database, like NetTecture, and then you're using the SQL server more or less like a file system.

You could try the easy solution (brute insert in SQL, dunno what SQL server you will use but I'm sure they all have some sort of bulk copy for massive inserts), if you have performance issues you can always think of a more optimized solution.

Reply With Quote
The following user says Thank You to gomi for this post:

Reply



futures io > > > Tick Database Storage

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)

Jigsaw Trading: TBA

Elite only

FuturesTrader71: TBA

Elite only

NinjaTrader: TBA

Jan 18

RandBots: TBA

Jan 23

GFF Brokers & CME Group: Futures & Bitcoin

Elite only

Adam Grimes: TBA

Elite only

Ran Aroussi: TBA

Elite only
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
U.S. Strategic Reserve Oil May Be Put in Storage by Traders kbit News and Current Events 1 July 6th, 2011 08:28 PM
Database for NT bomberone1 NinjaTrader Programming 6 April 29th, 2011 10:11 AM
PLT_NTDataCollect store bid ask tick data to its local database? rcabri Trading Reviews and Vendors 3 December 27th, 2010 11:00 AM
Tick Data Storage timmyb The Elite Circle 9 June 16th, 2010 05:44 AM
My 2 cents in mysql database iwannatoscript NinjaTrader Programming 5 March 7th, 2010 09:56 AM


All times are GMT -4. The time now is 04:10 AM.

Copyright © 2017 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts
Page generated 2017-12-14 in 0.16 seconds with 20 queries on phoenix via your IP 54.91.38.173