Tick Database Version 2 | Page 2 | Forex Factory

Options

Go to
Permalinks
Print Thread

Similar Threads

An approach to get MT4 tick data into a PostgreSQL database 10 replies

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

Tick Database 25 replies

Community Tick Database 5 replies

Brand-spanking new tick database. 4 replies

Attachments: Tick Database Version 2
Exit Attachments

Tick Database Version 2

  • #21
  • Dec 23, 2009 4:14pm Dec 23, 2009 4:14pm
I'm trying to add those in, but I have a problem...

Some brokers add suffixes to their pairs. So GOLD on one broker would be GOLD_fx on another. I'm trying to figure out how to detect and use those.

The new host is www.Webhostingpad.com, and I have a 4 year contract with them for only $80. According to them, I'm hovering around 5-6% usage (my cap is 10%), so I figure as long as the traffic is about the same, I'll be safe.

I already built a module for load distribution, so if it becomes necessary, I can send traffic to any number of other servers that I need to, and consolidate the data somewhere.
  • #22
  • Dec 23, 2009 6:32pm Dec 23, 2009 6:32pm
Quoting Ronald Raygun
I'm trying to add those in, but I have a problem...

Some brokers add suffixes to their pairs. So GOLD on one broker would be GOLD_fx on another. I'm trying to figure out how to detect and use those.

The new host is www.Webhostingpad.com, and I have a 4...
They all have the actual names in common (GOLD, GBPUSD, etc.) so why not do some sort of LIKE statement?

$pair = "GOLD";
SELECT * FROM table WHERE field LIKE '%'.$pair.'%'

Anyway, I was wondering... is it possible to download all the ticks you have gathered so far?
  • #23
  • Dec 23, 2009 7:59pm Dec 23, 2009 7:59pm
Quoting MoForce
They all have the actual names in common (GOLD, GBPUSD, etc.) so why not do some sort of LIKE statement?

$pair = "GOLD";
SELECT * FROM table WHERE field LIKE '%'.$pair.'%'

Anyway, I was wondering... is it possible to download all the ticks you have gathered so far?
Each pair has its own table. Are you suggesting I make one large super-table?

As for data access, I'm working on it. The old database was 4GB (30million rows), and it's taking a long time to query data. I'm currently trying to export that to a bunch of CSV files.
  • #24
  • Dec 23, 2009 8:02pm Dec 23, 2009 8:02pm
Actually....

If you prefer, I could upload a raw sql file somewhere and you can try and play with the data there.
  • #25
  • Dec 23, 2009 8:10pm Dec 23, 2009 8:10pm
Ever thought about amazon web services?

http://aws.amazon.com/rds/

They've got a relational database service that's quite inexpensive.
  • #26
  • Dec 23, 2009 8:15pm Dec 23, 2009 8:15pm
Quoting Ronald Raygun
Each pair has its own table. Are you suggesting I make one large super-table?

As for data access, I'm working on it. The old database was 4GB (30million rows), and it's taking a long time to query data. I'm currently trying to export that to a bunch of CSV files.
You can still do it with each pair having its own table

A quick look at your code and we see how you're capturing ticks
http://www.ronaldraygunforex.com/TickDB/Load.php?TN="+SymbolUsed+ ....

Just looking at this it seems like you could do something like this:
Inserted Code
if[color=#000000]([/color][color=#000000]strstr[/color][color=#000000]([/color][color=#000000]$symbolused,[/color][color=Black]"GOLD"[/color][color=#000000]))
{
mysql_query("INSERT INTO goldtable VALUES (w/e values)");
}
[/color]

strstr() can look for a string within a string
http://php.net/manual/en/function.strstr.php

An SQL dump works for me
  • #27
  • Edited 3:58am Dec 24, 2009 12:11am | Edited 3:58am
Then how are you dealing with InterbankFx symbols..they have "m" suffix i think to all Fx pairs. I know a few brokers who have other stuff also attached to forex pairs even. one broker has some "lfx" in the end..i remember

1) I was just thinking to feed from interbankfx and tadawul(this has proper symbols though)? So is it fine?

2) Is there a way to start the tick loader to feed ticks again..say after pc is restarted from hibernation? I once did ...it was not feeding again..I had to restart again. (i read some mysql connection lost in journal though)

Byethost.com are known in the industry, economical too..just in case.


EDIT:
2 errors in one broker journal
1)2009.12.24 12:30:52 Tick Loader 4 GBPUSD,M1: GBPUSD Error: <br />
<b>Warning</b>: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Lost connection to MySQL server at 'sending authentication information', system error: 32 in <b>/home/ronaldra/public_html/TickDB/Load.php</b> on line <b>11</b><br />
Could not connect: Lost connection to MySQL server at 'sending authentication information', system error: 32

2)2009.12.24 13:27:37 Tick Loader 4 USDJPY,M1: USDJPY Error: <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>500 Internal Server Error</title>
</head><body>
<h1>Internal Server Error</h1>
<p>The server encountered an internal error or
misconfiguration and was unable to complete
your request.</p>
<p>Please contact the server administrator,
webmaster ***AT*** ronaldraygunforex ***DOT*** com and inform them of the time the error occurred,
and anything you might have done that may have
caused the error.</p>
<p>More information

whats the 2nd one? is server feeling the heat?
100 Fold Challenge->Interested? ->https://www.forexfactory.com/thread/32152
  • #28
  • Dec 24, 2009 5:54am Dec 24, 2009 5:54am
The Tick uploaders on the metatrader terminal only upload the first 6 characters of the pair's name. This will always upload the currency pairs properly, and silver...gold and oil are trickier.

Now that MoForce introduced me to the substr function in php, gold, oil and silver should be feasible. Just need to put those in...AFTER the holidays.

The current back-end is as follows:

I have two hosting accounts at www.webhostingpad.com
The primary account has a distributor. This randomly selects one of the two databases, and inserts the incoming tick into that database.

I have already written in a capability to add more receivers as the load increases.

Every saturday at noon server time (whenever that is), a cron job runs which exports the week's data to csv files, and then saves them in www.RonaldRaygunForex.com/TickDB/Archive/ the first live test was supposed to be last week, but the powers that be at JustHost.com closed the account because the traffic was just too large for them to handle.

This coming weekend is the next live test. If all goes well, I'm going to start gradually opening the flood gates.
  • #29
  • Dec 24, 2009 7:35am Dec 24, 2009 7:35am
Files from 9/14 to 12/18 will show up here in a few hours: www.RonaldRaygunForex.com/TickDB/Archive

Total size: 2.66 GB

The old DB's files are the ones without dates.
  • #30
  • Dec 24, 2009 8:22am Dec 24, 2009 8:22am
Just for the info.. there is a very sexy free file host out there www.box.net.
NO file Limits /Deletions/IP bans and other shit& lot of other useful features

Upload once and you are done. May be of help to you.
BANDWIDTH is always a a issue with paid hostings..esp when this DBase will grow, more.
100 Fold Challenge->Interested? ->https://www.forexfactory.com/thread/32152
  • #31
  • Dec 24, 2009 8:32am Dec 24, 2009 8:32am
If I wanted to host the files, I would just use rapidshare, megaupload, storage.to, hotfile, etc.

The tricky part is hosting the database itself.
  • #32
  • Dec 24, 2009 8:55am Dec 24, 2009 8:55am
Did you look into the amazon web service I pointed out? It looks pretty solid.
  • #33
  • Dec 24, 2009 9:06am Dec 24, 2009 9:06am
At the minimum Rabid, I think I'd be pushing $80/month with Amazon. I'm keen on not spending too much.

I'm considering something akin to the BitTorrent infrastructure to collect and distribute ticks, but I'm not entirely sure how that will work--yet.
  • #34
  • Dec 24, 2009 9:57am Dec 24, 2009 9:57am
Ohh, I see your problem.

You're talking about 2000 inserts per minute. LOL. There's no shared platform built for that. You'd need a dedicated server to handle that level of activity. Or at least pricing at that level.

I suspect that a relational database is the wrong approach to this. SQL is insert heavy, designed to simplify complex queries at the cost of inserts. What you actually need is a mirrored set of DBs, something that's insert efficient that is then mirrored off to SQL.

There are other database concepts out there. A simple flat file would be way more efficient at inserts, you could take the PHP request, create an SQL insert, and just append that to the end of an .sql file rather than actually doing the full insert. Keep the file size small, hourly, maybe daily, and appending to the file would be easy.

As for distributing ticks via bit-torrent, piece of cake. Find someplace that offers torrent hosting and automatically add each new file to the tracker. Add a link for the tracker to the page. If you felt so compelled you could compile the files into weekly, monthly or yearly datasets to make downloads easier.

The big question comes to distributing ticks via request URL. Because of the frequency of requests, that becomes unsustainable very quickly unless you start charging a lot of money to maintain your infrastructure. As the number of user-pairs increases, the problem begins to scale out of control.

But if you drop the real-time tick request service, you could do all of this very simply. You really don't even need to include the actual SQL server thru most of it. You just need a way to collect the tick and store to the file, a way to automatically build the .SQL file and a way to get it into torrent. If you automatically tar.gz the sql files you could save a lot of bandwidth too, making it easier to seed the files.
  • #35
  • Dec 24, 2009 12:16pm Dec 24, 2009 12:16pm
Uhh... are you saying just have the ticks added to a csv file or similar? I'm not too sure about the file-handling functions in php. My other concern is weeding out bad data. Some users have reverse-engineered the request page and were sending bad ticks before I found out. I have a cron job running every so often to clean out ticks from these users.

Tick files via bittorrent is certainly a piece of cake. Hard part is consolidating the tick files to a usable super tick file.

Ticks via request URL I stopped trying to give that. The users who wanted streams of ticks I gave them local versions of the tick collection scripts.

You obviously know more than I do in this field. You know what I'm trying to do (have a community-generated tick database), how best should I do it?
  • #36
  • Dec 24, 2009 4:17pm Dec 24, 2009 4:17pm
Quoting Ronald Raygun
Uhh... are you saying just have the ticks added to a csv file or similar? I'm not too sure about the file-handling functions in php. My other concern is weeding out bad data. Some users have reverse-engineered the request page and were sending bad ticks before I found out. I have a cron job running every so often to clean out ticks from these users.
How are you tracking them, IP? You can do that real time, quietly reject the ticks. You could consider creating a small username/password thing, but that gets back into the database problem again. Personally, I would consider doing approved IPs only. Users could sign up and you'd match their IP off a hash array in the script. Simple and to the point.

As for output file, what file do ppl use the most? CSV? SQL? I'd just output directly to that format.

File writing in PHP is very easy:
http://www.tizag.com/phpT/fileappend.php

Quote
Tick files via bittorrent is certainly a piece of cake. Hard part is consolidating the tick files to a usable super tick file.

cat source1.sql >> bigfile.sql
cat source2.sql >> bigfile.sql
cat source3.sql >> bigfile.sql

Write a shell script, call it via crontab. File append functions are native to both linux and windows.

Quote
Ticks via request URL I stopped trying to give that. The users who wanted streams of ticks I gave them local versions of the tick collection scripts.

Nod, about the only thing you can really do.
  • #37
  • Dec 29, 2009 10:18am Dec 29, 2009 10:18am
I'm just curious, how is the time kept on the system? Is it precise to the microsecond level? It seems by using TimeCurrent() you're limiting yourself to the precision on the order of seconds. Would connecting the EA to a DLL obtain higher precision (microsecond level)?
  • #38
  • Dec 29, 2009 10:39am Dec 29, 2009 10:39am
Microsecond level would be nice. It's very possible to capture time at the milisecond level within metatrader. The problem is the lag between the contributing EA, and my webserver.

I figure what is more important is the order of the ticks as opposed to the absolute specific time they happened.
  • #39
  • Dec 29, 2009 11:03am Dec 29, 2009 11:03am
How do you handle time zones? Is there a UTC function somewhere?
  • #40
  • Dec 29, 2009 11:10am Dec 29, 2009 11:10am
All terminals sync themselves off of www.ronaldraygunforex.com/TickDB/Time.php
0 traders viewing now