Discussion for MtGox trade data downloader

[bitranox]

For other less popular currencies, however, I have found that there are many gaps, some quite long, and so without regular bq updates this is liable to break a script. Of course if you have an up to date database and can run your script regularly, at least once per day, this will not be a problem as you will catch all trades as they come in. Alternatively, you could manually advance the last_id if you haven't caught up to the current time yet, but you need to confirm the limit is indeed 86400s first.

The (very good and appreciated) "Unofficial Documentation for MtGox's HTTP API v2" has some glitches in the description for the API function money/trades/fetch :

(see https://bitbucket.org/nitrous/mtgox-api#markdown-header-moneytradesfetch)

there are not really "gaps" (except the one big gap for USD when they switch from a counter to microtimestamp)

the mtGox API function money/trades/fetch simply works as follows :

if You pass a microtimestamp as parameter :
- it will return maximum 1000 records of a given currencypair
- it will return maximum the trades that happend 86400 seconds after the given microtimestamp
meaning if there are less then 1000 trades within 86400 seconds, You will receive just the trades that happened 86400 seconds after the given microtimestamp.

if You dont pass a microtimestamp parameter :
- it will receive the tradedate for a given currencypair within the last 86400 seconds (24 hours) from now. In that case the "1000 record" limit does not apply.

so - if no trades happened within the timespan of 86400 seconds for a given currencypair, You will not get back any datarows - what will only happen for some rarely traded currencypairs
in that case You will need to add 86400 seconds to the timestamp (respectively 86400 * 1E6 microseconds) and query again for the next day.

As nitrox pointed out in some other posts, I would not recommend that every user should download the full trade history from mtgox because of serverload issues, etc ...

however - since the bq database is not updated until now, I will provide the full dumps until up-to-date in the next couple of days. (what will be updated daily or hourly - I wait for user requests on that issue)

the python script that 100x posted before will fail on some rare traded currencypairs for that reason.

[1715192869]

1715192869

[1715192869]

1715192869

[1715192869]

1715192869

[bitranox]

I am thinking to provide the tradedata by another method to the users.

I fetched all the data for myself, so if a considerable amount of users wants to use that, I will provide the tradedata in a different way (not rely on mtgox updating the data, not rely on big query).

please check out following post : https://bitcointalk.org/index.php?topic=282154.msg3017272#msg3017272 and let me know.

yours sincerely

bitranox

[nitrous]

The (very good and appreciated) "Unofficial Documentation for MtGox's HTTP API v2" has some glitches in the description for the API function money/trades/fetch :

(see https://bitbucket.org/nitrous/mtgox-api#markdown-header-moneytradesfetch)

there are not really "gaps" (except the one big gap for USD when they switch from a counter to microtimestamp)

the mtGox API function money/trades/fetch simply works as follows :

Yeah, I think in a previous commit to the docs I actually did say that, but I wanted to emphasise (a) that this was due to gaps in trades, ie no trades happened in a 24h window, and (b) that people should not try to circumvent this due to server load. Of course, for small date ranges (or for your centralised database project), that's fine, but I don't want to openly advocate such usage. After all, it's the reason MagicalTux introduced the bq database in the first place!

the python script that 100x posted before will fail on some rare traded currencypairs for that reason.

Yeah I mentioned that a few posts ago. In actual fact, they don't even have to be particularly rare. I know GBP has some very long gaps near the beginning. Obviously for rare currencies this will be even more of a problem.

[arvin8]

got an error during download


An error occurred that shouldn't have happened. Please report this on the tool's forum thread at bitcointalk (you should be taken there when you click ok).

Traceback (most recent call last):
  File "/Applications/MtGox-Trades-Tool.app/Contents/Resources/app.py", line 97, in thread_bootstrap
  File "mtgox.pyc", line 120, in run
  File "bq.pyc", line 100, in gen2
  File "oauth2client/util.pyc", line 128, in positional_wrapper
  File "apiclient/http.pyc", line 676, in execute
  File "oauth2client/util.pyc", line 128, in positional_wrapper
  File "oauth2client/client.pyc", line 494, in new_request
  File "oauth2client/client.pyc", line 663, in _refresh
  File "oauth2client/client.pyc", line 682, in _do_refresh_request
  File "httplib2/__init__.pyc", line 1570, in request
  File "httplib2/__init__.pyc", line 1317, in _request
  File "httplib2/__init__.pyc", line 1286, in _conn_request
  File "/usr/lib/python2.7/httplib.py", line 1027, in getresponse
  File "/usr/lib/python2.7/httplib.py", line 407, in begin
  File "/usr/lib/python2.7/httplib.py", line 371, in _read_status
BadStatusLine: ''

[nitrous]

got an error during download


An error occurred that shouldn't have happened. Please report this on the tool's forum thread at bitcointalk (you should be taken there when you click ok).

Traceback (most recent call last):
  File "/Applications/MtGox-Trades-Tool.app/Contents/Resources/app.py", line 97, in thread_bootstrap
  File "mtgox.pyc", line 120, in run
  File "bq.pyc", line 100, in gen2
  File "oauth2client/util.pyc", line 128, in positional_wrapper
  File "apiclient/http.pyc", line 676, in execute
  File "oauth2client/util.pyc", line 128, in positional_wrapper
  File "oauth2client/client.pyc", line 494, in new_request
  File "oauth2client/client.pyc", line 663, in _refresh
  File "oauth2client/client.pyc", line 682, in _do_refresh_request
  File "httplib2/__init__.pyc", line 1570, in request
  File "httplib2/__init__.pyc", line 1317, in _request
  File "httplib2/__init__.pyc", line 1286, in _conn_request
  File "/usr/lib/python2.7/httplib.py", line 1027, in getresponse
  File "/usr/lib/python2.7/httplib.py", line 407, in begin
  File "/usr/lib/python2.7/httplib.py", line 371, in _read_status
BadStatusLine: ''

Hmm, that sounds like a temporary problem with either Google or your internet connection.

For most errors, you should be able to start the program again. It should pick up where it left off and continue downloading.

[dirtyfilthy]

Hi,

I'm getting the following error when trying to download after being partially successful

Traceback (most recent call last):
  File "app.py", line 118, in thread_bootstrap
    self.thread_init()
  File "./bq/mtgox.py", line 153, in run
    raise Exception, "Insertion error: %d =/= %d" % (pos,size)
Exception: Insertion error: 1040000 =/= 1024000

[nitrous]

Hi,

I'm getting the following error when trying to download after being partially successful

Traceback (most recent call last):
  File "app.py", line 118, in thread_bootstrap
    self.thread_init()
  File "./bq/mtgox.py", line 153, in run
    raise Exception, "Insertion error: %d =/= %d" % (pos,size)
Exception: Insertion error: 1040000 =/= 1024000

This means that whilst downloading, something bad happened either on Google's end, your end, or with your internet connection, and a set of trades wasn't properly inserted into the database, so you're missing 16000 entries. You have a few options:

1) I would recommend starting again if it's not too much trouble for your internet connection, as your data could be corrupted somehow.
2) Alternatively, you could resume the download as it looks like the last 16k entries just somehow weren't inserted, and it may not be corrupted at all.
3) Or to be safe, you could manually remove the last 24k rows to try to remove any potential corruption and resume from 1 million.

1 is the safest, but you'll probably be fine with any of the options.

[toniquone]

Hi!

Thanks for a great tool. But I can't make it work properly. I started it several times, and always I get the same error, and my dump always the same size - 107 mb. I'm not tried to continue updating because it looks like it's corrupted.
Is there any way to fix it?
http://s12.postimg.org/opvn4mmp9/Screen_Shot_2013_12_12_at_7_32_48_AM.pnghttp://s10.postimg.org/8q04iicmh/Screen_Shot_2013_12_12_at_7_32_56_AM.pnghttp://s16.postimg.org/xmfafdztx/Screen_Shot_2013_12_12_at_5_56_13_PM.png

[nitrous]

Ok, it seems that the tool is now breaking for some unknown reason (changes to the bigquery API?), and I haven't updated it in a while so I can't be sure exactly why. Seeing as my tool hasn't changed, and it seems to be breaking around 1024000 rows for those who are having problems, I would guess it's a problem on Google's end, though more likely their API just changed and I should update my tool.

In addition, I'm sure many of you are rather frustrated that my app doesn't let you download data more up to date than May. So, rather than spend time I don't have updating the app, I'm going to release a database which is up to date to today, Thu, 12 Dec 2013 22:13:40 GMT. It is in a slightly different format, but I've tried to keep the columns as similar to the app as possible:

Money_Trade__	int	The trade ID. As you may know, this is a sequential integer up to 218868, whereupon it is a unix micro timestamp.
Date	int	Unix timestamp (1s resolution).
Primary	str	'1' for primary (recorded in original currency), '0' for non-primary.
Item	str	Currently, only contains 'BTC'
Currency__	str	'USD', 'EUR', 'GBP', etc.
Type	str	'bid' or 'ask'.
Properties	str	e.g. 'market'. May be a comma separated list if appropriate, e.g. 'limit,mixed_currency'
Amount	int	Quantity traded in terms of Item.
Price	int	Price at which the trade occurred, in terms of Currency__.
Bid_User_Rest_App__	str	ID specific to the authorised application used to perform the trade on the bid side, if applicable.
Ask_User_Rest_App__	str	ID specific to the authorised application used to perform the trade on the ask side, if applicable.

The most significant difference is that Date is a unix timestamp rather than in ISO format.

Here's the link: https://docs.google.com/file/d/0B3hexlKVFpMpYmdoUUhXckRrT2s

The table is dump, and it is indexed on Money_Trade__ asc, [Primary] desc (index is named ix).

Sorry if you're annoyed that you spent time and bandwidth downloading the data using my app, however it was my belief that MtGox would complete their side of the service so that my app would then be used to maintain and update a local copy of the database in any format. Then you wouldn't have had to rely on a third party maintaining an up-to-date copy, giving you complete control over your backtesting.

I don't think this database will be compatible with the app unfortunately, so you won't be able to export it to different formats, and I don't plan on keeping it up to date, but at least it's better than the old May database and works. Also, sorry that I've been rather less active as of late, I've got some pretty big commitments currently and, as such, I unfortunately have far less time to do stuff like this

[toniquone]

Big thanks, Nitrous!

[nitrous]

Big thanks, Nitrous!

No problem

Also, for anyone who does want to maintain their own up to date local database and has managed to get my original app to work, siergiej has created a node.js script which you may find useful -- https://gist.github.com/siergiej/5971230 -- it should update the database in the correct format, but please note that it will probably not be compatible with the dump I just posted.

[poikkeus]

I've made trade data downloader with Java for my upcoming trading bot. I could modify it to only download trading data from the date you want and make CSV file. The files could be updated by running it again.

Or if you are a java coder you can do it yourself. https://github.com/jussirantala/goxbot

[BNO]

Hi all,

VERY interesting Thread here since i wanted to have a long time some reliable tickdata for bitcoin. I must say that i am absolutely no programmer - so i can not really worship all the great work Nitrous, Poikkeus and others have done, but it sounds really good and somehow i will get it running - hopefully

Could someone help me out to understand this better:

I saw that mt-gox at Google Bigquery consists of 2 Tables: trades and trades_raw. What is the difference between them?
Is it possible to convert the dump somehow in an .csv file?
The tool from poikkeus has one file history.dat can i download this and - same question - get this somehow into a .csv file?

Greetings to you all...

 

[nitrous]

Hi all,

VERY interesting Thread here since i wanted to have a long time some reliable tickdata for bitcoin. I must say that i am absolutely no programmer - so i can not really worship all the great work Nitrous, Poikkeus and others have done, but it sounds really good and somehow i will get it running - hopefully

Could someone help me out to understand this better:

I saw that mt-gox at Google Bigquery consists of 2 Tables: trades and trades_raw. What is the difference between them?
Is it possible to convert the dump somehow in an .csv file?
The tool from poikkeus has one file history.dat can i download this and - same question - get this somehow into a .csv file?

Greetings to you all...

 

Hi BNO,

The 2 tables relate to a discussion I had with MagicalTux (CEO of MtGox). My tool only really works if the bigquery data is presorted, but that can't be guaranteed if data is repeatedly being inserted into the table due to the way BQ works. I asked MagicalTux if he could implement a system of two tables, where one is updated, and the other is a sorted version of the updated table, hence the two tables. Since no updates have occurred since May though, it doesn't make a difference currently. Long story short, there is no difference between the tables .

If you download the data using my tool, then there are export options which can convert into a few different formats, CSV included (you can also choose which data to use and even do candles). Obviously though, the tool can only get data up to May (and has been a bit buggy lately). As far as I know, there aren't any other tools that can give you bulk csv data, although you might want to look at http://wizb.it which may at some point provide a working data service. I believe there are some charting websites out there though which can give you csv data, though with much lower resolution.

Please note that my tool isn't compatible with the full dump I just posted, and so this cannot be used to generate CSV. I might reformat it into a compatible version though seeing as there's quite a lot of demand for this data.

Sorry that your options seem a little sparse currently, if I had more time I'd love to develop a proper tool or set up a web service to make it easy to get data for any exchange, I know there's demand for it. I think I might make this a side project of mine, though it'll probably be quite slow.

[nitrous]

The tool from poikkeus has one file history.dat can i download this and - same question - get this somehow into a .csv file?

I haven't looked at his source code yet, but looking at the contents of history.dat it looks to be in a binary format, which means you'll need a script to convert it. Perhaps poikkeus could add conversion to CSV to his tool? I'm sure if not someone else could quickly hack up a conversion script, assuming the format is simple enough (it looks like it's a java object dump).

[Exther]

I just tried the latest Windows binary (without status bar) and can confirm it has successfully exported 3600 second candles. I"ll try some other values as well and report if I encounter any errors. Win7/64.

[nitrous]

I just tried the latest Windows binary (without status bar) and can confirm it has successfully exported 3600 second candles. I"ll try some other values as well and report if I encounter any errors. Win7/64.

That's good (although I was sure that the dump I uploaded would be incompatible...) Anyway, in case it still is, I've just finished another dump, up to date for 2013-12-17 15:48:26.308886, which should be fully compatible (the difference with the first one is that the date column is now in ISO format rather than unix timestamp, and the primary column is [true, false] instead of [1, 0]).

Here are the two dumps:
1) https://docs.google.com/file/d/0B3hexlKVFpMpYmdoUUhXckRrT2s (2013-12-12 22:13:40.483107; 665.1 MB)
2) https://docs.google.com/file/d/0B3hexlKVFpMpdVhxd1NvQ0Fqanc (2013-12-17 15:48:26.308886; 899.2 MB) <--- fully compatible with my tool for export (don't try to update it though)

The size discrepancy is because the ISO date format (2) is stored as text while the unix timestamp (1) can be stored more compactly as an integer.

[BNO]

Hi nitrous,

i was just stopping by. Wanted to thank you for your answer first. Googled the last 2 hours to become a bit smarter on databases and how to get  this somehow done (didn't get far though )

Do i understand you right, that  i could download the 2nd file and convert it with your tool? That would be awesome...

[nitrous]

Hi nitrous,

i was just stopping by. Wanted to thank you for your answer first. Googled the last 2 hours to become a bit smarter on databases and how to get  this somehow done (didn't get far though )

Do i understand you right, that  i could download the 2nd file and convert it with your tool? That would be awesome...

Hi, yep, you should be able to just load it in and export

[BNO]

Hi,

thanks a lot, i'm already downloading and post when i'm done.

Some things i wanted to ask you - since you have lots of experience with this data now.
How would you rate the data quality in terms of acurateness and "rightness". Any known bugs oddities? Is this data actually as all trades went through on Mtgox? something to consider if i might want to use this data to develop a strategy (need to read first, to get better with this)?