Bitcoin Forum

Hi there,

As I've mentioned before, the extra load caused by people downloading the entire MtGox trade history from the API is unsustainable, and recently I published my own dump to reduce the load on the servers. However, since the 23rd of May, MagicalTux has uploaded a full dump to Google's BigQuery service. This database contains trades for all currencies and contains all information available by the API (and more). The service isn't yet fully integrated, however soon it will start being updated automatically every 10mins-1hr. Until then, you can still access all data before the 23rd of May, and it is the recommended method.

To gain access to it, follow these instructions:

• First, you need to sign up for Google's BigQuery - see here (https://developers.google.com/bigquery/sign-up) for instructions on doing so.
• Now that you are signed up, go to the BigQuery console: https://bigquery.cloud.google.com (https://bigquery.cloud.google.com)
• Now each time you go to the console, you will need to add the MtGox data set -- click on the drop down array next to API Project on the lefthand sidebar.
• Go to 'Switch to project' > 'Display project...'
• In the resulting popup box (Add Project), enter the Project ID, 'mt-gox'.
• In the sidebar, mt-gox:mtgox should appear. Click on it to reveal the tables, which currently consists of 'trades'.

Now you can perform SQL queries on this using [mt-gox:mtgox.trades] for the table name, e.g.:

The fields mean the following:

Field	Description	Type
Money_Trade__	This is the tid value, a unique id for the order. The first trades used (almost) sequential integers up to 218868, the majority of trades however came after this (~6 million) and use a Unix microstamp instead.	int
Primary	Whether the trade happened in the currency specified (see Currency__). Some trades may appear on multiple tickers, so to ignore duplicates you may want to `select where primary is true'.	bool
Currency__	The symbol of the currency against which BTC was bought or sold.	str
Bid_User_Rest_App__	App ID of the bidding party (the person buying BTC)*	str
Ask_User_Rest_App__	App ID of the asking party (the person selling BTC)*	str
Type	bid or ask (bid means to buy BTC by selling the specified currency, ask means to buy the specified currency by selling BTC)	str
Properties	A comma separated list of properties of the order, e.g. limit,market,mixed_currency	str
Item	The thing being bought or sold, always BTC.	str
Amount	The volume of the item (BTC) being bought/sold in satoshis (1 BTC = 100,000,000 satoshis).	int
Price	The price of each item in the currency specified. For most currencies, this is given in units of 0.00001 of the currency, so $12.3456 would be 1,234,560. Some currencies, currently JPY and SEK use units of 0.001 instead.	int
Date	The date of the trade in ISO format.	str

* These fields represent the app ID when an app is being used to make the trade. Currently, these are null for all but 4% of trades. There are two fields because there are two parties to every trade, one who is buying the BTC (the bidder) and another who is selling it (the asker). For most of these 4% app trades, only one of the parties is an app, however there are also ~3500 trades for which both parties are apps and both of these fields are non-null.

I plan on writing a python program soon to download a dump of this data straight from Google to a local sqlite3 database. Once you are able to obtain this data, feel free to then keep it up-to-date using the API methods (see here (https://bitbucket.org/nitrous/mtgox-api#markdown-header-moneytradesfetch) for info from my own documentation to do this using the v2 api). MagicalTux has confirmed that this usage is permissible, as the load on the API is minimised this way.

Thanks,
nitrous

Just for info, automatic update of these data is scheduled to start this week or next week

This is really sweet, thanks!

Great  ;D because my charting app does not accept Unixtime.

Okay, we have Date like ''2013-05-28''. Isn't Time like ''hhmmss'' missing?

The dates are formatted as strings and include the time, e.g. "2013-03-12 23:52:16". So the format is "yyyy-mm-dd hh:mm:ss".
Here's a sample of the database:

Money_Trade__	Primary	Currency__	Bid_User_Rest_App__	Ask_User_Rest_App__	Type	Properties	Item	Amount	Price	Date
1363132336180895	true	USD	e7b6384e-3981-42e7-b3b7-7719c4dc74a5	null	bid	market	BTC	22276676	4435000	2013-03-12 23:52:16
1363132369821802	true	USD	null	null	bid	limit	BTC	8174066	4435000	2013-03-12 23:52:49
1363132372700876	true	USD	null	e75e9f56-4b37-4440-9b10-b56fc6a47ee1	ask	limit	BTC	64250873	4434351	2013-03-12 23:52:52
1363132372786101	true	USD	null	e75e9f56-4b37-4440-9b10-b56fc6a47ee1	ask	limit	BTC	8174066	4433910	2013-03-12 23:52:52
1363132386679941	true	USD	null	e75e9f56-4b37-4440-9b10-b56fc6a47ee1	ask	limit	BTC	160000000	4414717	2013-03-12 23:53:06

Thank you very much for the information.

Is it possible (if it's not too late) to separate Date from Time? Most widely used trading and charting applications for retail traders (Multicharts or Ninjatrader and others) will not be able to use the data in the format where date and time are merged.

http://www1.picturepush.com/photo/a/13187014/img/13187014.png (http://picturepush.com/public/13187014)

Does the application not allow any way of doing this directly?

If not, you have a few different options:

• You can ask MagicalTux and see if he'll consider it for when he begins regular updates (although that would be 1 or 2 weeks away before you see any actual changes).
• When I am able to write a program to download the entire database, I will release the code. If you have any programming experience, it should be relatively simple to then modify the data to your needs before input into your application. If not, I may be able to convert it to any format you need, however I'm not too sure of the logistics of then giving you the database.
• You could try doing a SQL query along the lines of:
  

N.B. though, that BigQuery may not let you download all the data at once using this method.[/li]

Thanks, this is great info!

Unfortunately not.


I wouldn't have the courage to bother him  :)

I have no programming experience. Out of curiosity, would it be possible somehow get the data into a CSV file in my computer that would be updated real-time? I would not like to bother you with this. I only want to know if this is feasible.

Anything more complicated than Word or Excel overwhelms my circuits  :D[/list]

Just a thought: could 2 additional (to already existing Date) columns / fields to collect be added to the API: Date_Only and Time_Only (derived from Date)? Would this be something that requires a lot of work?

Just look, there is an application called Sierra Chart (I think some trials were made with this app to accept MtGox data) that has Date and Time separated for intraday data in CSV format http://www.sierrachart.com/index.php?l=doc/doc_TextCSVDataFormat.html#ExampleIntraday

I will ask him when I next catch him, if what you're saying is true then it would make a lot of sense to include all three to accommodate all users. On the other hand, Google I believe charges for the amount of data uploaded, so that may factor into their decision as it may seem a bit redundant.

I don't think google BigQuery makes it very easy for non-programmers to actually download bulk data from them, and I haven't had a chance to read up on using it programmatically yet. Once I have though, it would be really easy for me to make a CSV as you asked for though, and perhaps I could even make a small application to keep a local database updated from the BQ servers. What platform are you on? I assume from your screenshot it is Windows?

Fantastic  :)


This would be for MtGox to decide if they are inclined to incur this additional cost. There are tens of thousand of third party charting (I am not talking about toys like MT4) software's users: forex and futures and stock traders like myself who, in the future when Bitcoin goes mainstream, would potentially be excluded from being able to load MtGox data into their apps, if MtGox decides not to to avail two additional fields to collect.


I do not know how much would creating this application cost. I hope crowd financing might work (with a little help from MtGox). Yes, I am using Windows.

Well I'm already creating the underlying mechanism of the app for my own personal use, and I am more than happy to do the little bit of extra work to make it cross-platform for free. Of course I always welcome donations :P but most of the bitcoin work I do/am doing is open-source. Hopefully I can release a self-contained python app which won't need you to already have installed python so that it's as intuitive as possible to use.

So I spotted this thread right on time.

I have no problems with donations. Actually I got interested in Bitcoin because I wanted to make a donation (to a philosopher). But I like programmers too.


Fantastic :)

Wow, the query speed is impressive. Great! Thank you for sharing.

Edit:


The data given is already enough for all those purposes. What data format do you need? I'm happy to help. :)


@nitrous: Currently the date has the data type string. I suggest to change it to timestamp. String operations are slow, take much more storage and something like DAYOFWEEK(date) doesn't work.

1. At the moment I only need BTCUSD trade tick data (no need for Bid and / or Ask data). For a CSV file (I will later import this data into my application) I need:
Date,Time,Price,Volume (in Satoshis)
yyyy-mm-dd,hh:mm:ss,65000000000

2. In future I will need / want / beg for a mechanism / application that will update the CSV file with missing historical data and will update real-time data from MtGox. I will later map this CSV file (ASCII mapping) with my charting application to dynamically connect the constantly updating BTCUSD file to this charting application.

The query language for BigQuery is pretty similar to SQL. You can just ask bigQuery to return the date data in the format you want:

For everyone who needs timestamp instead of a date string:


This will return:


For everyone who needs the date and the time data in separated columns:


This will return:


Edit, here is a list (https://developers.google.com/bigquery/docs/query-reference#datetimefunctions) of stuff you can do related to date/time.

Step 1: Go to the sign up website (picture (https://i.imgur.com/L4QYfUs.png))


Step 2: Create project.. (picture (https://i.imgur.com/iDcb3z0.png))

Step 3: Enable "BigQuery API" (picture (https://i.imgur.com/tmsjHoM.png))

Step 4: If you agree, accept the ToS (picture (https://i.imgur.com/8EfO0Db.png))

Step 5: Compose a query (picture (https://i.imgur.com/V6F4DuG.png))


Step 6: Edit the query for your needs and after processing you should be able to download the data as csv.


If you wish, delete the row with the date. This will return the newst transactions (or the newst before the 23rd of may).

The maximum number of results is limited to 16000, otherwise it would contain too many rows for direct download, but you can repeat step 5.

I should make it clear that I am not the maintainer of this database, nor am I an employee of MtGox, however MagicalTux at MtGox is.

Just pay attention when using bigquery.cloud.com. It is very easy to get charged.

Hello dexX7,

This is a fantastic guide :)

I needed the data sorted in ascending order (by time: from earliest to most recent date) so I changed DESC to ASC and <2013-05-19 to > 2010-07-16 and it works.

Is the number of 16k rows imposed by Google or could it be somehow increased by the user to e.g. 160k? I am not complaining, just asking :)

The issue is that Google won't let you download a CSV copy of the results if there are more than 16k rows. It is possible to download more, but you have to do it programatically. My tool can currently download a complete copy of the database (https://bitcointalk.org/index.php?topic=221055.0 (https://bitcointalk.org/index.php?topic=221055.0)), but I don't think it's ready for your needs yet. I would be interested in your opinion on the poll, though. I need to know whether you would be fine for it to download the entire database to a sqlite3 dump (about 460mb) before then processing it to your specifications. This would also enable you to then quickly create a csv copy of any query without having to download any more data.

What a misfortune  ;D


I am just a ''paste and copy'' guy. I write movie dialogues and song lyrics for a living (not in English; my English is terrible). My hobby is trading forex and futures. I will of course leave my opinion in your poll, but not until a few days from now when I get an understanding of what ''sqlite3 dump'' is. Yes, I am that dumb when it comes to computers  :P

What I normally do if I want to get data for my charting application is I go here http://iqfeed.net/ and pay on a monthly basis for data feeds I want, from exchanges I want. And get the historical and real-time data in format customized to my platform and streamed to my platform, without the need to worry about technicalities.

Maybe in future MtGox could speak to some data vendors and avail its feed to these data vendors. These data vendors will then redistribute (using their data centres) the data to many users for a fee. Here is http://www.iqfeed.net/index.cfm?displayaction=data&section=software#products a list of charting packages that MtGox can reach in a minute without the need to invest in infrastructure too much.

Don't worry about that, sqlite3 is just a type of database, which I can hopefully embed into the app. I still can't guarantee that it'll be trivial to create a self-contained application, but if I can do, you should be able to just download and double click :) Basically, the dump would just be a local copy of the bigquery database behind the scenes. The app would first download it, and then process it to create any format you want and much more quickly (without downloading any more data, nor risking any processing charges from google - completely free!).

The only issue is that it will require a half gigabyte of overhead, if you don't mind that, you won't need to worry about the dump, it'll just sit there hidden [this is option 1 -- download a complete dump then process]. If, however, I don't use the sqlite3 dump in the app, then each time you want to download a copy of the data, it will have to download it all again (and depending on how many queries are needed, it could end up costing you money) [this is option 2 -- process during download].

Okay. I voted for option 1. Disk space is cheap now-a-days.

This is how beautifully BTCUSD can look with high quality data in a charting software ;D I downloaded and merged only a tiny portion of the tick data from Google.

You can easily add indicators, e.g. geometrical ones, you can draw trendlines, you can apply algorithmic strategies and whatnot. On the right there is a panel for manual entering orders, without the need to go to the broker's / exchange's website.

Yeah, I would love to have this possibility with MtGox.

At the moment I need go to bitcoincharts. com, copy the BTCUSD image and then process the image in an image processing software to draw a single trendline  :-\


http://www3.picturepush.com/photo/a/13202381/img/13202381.png (http://picturepush.com/public/13202381)

Well I have my tool generating data according to your format now :) There's no integrity checking of the csv currently though. I may not be able to work on it very much for the next few days/weeks though as I've got some important exams coming up.

It's okay. I can wait. Thank you very much for the work that you do  :)

Guys, I have a couple of questions:

1. Is the minimum price tick size of BTCUSD at MtGox 0.00001?

2. Is Bid and Ask data from the dark pool https://bitcointalk.org/index.php?topic=5564.0;all reflected in the data?

3. Is traded volume from the dark pool reflected in the data?

4. What is MtGox time (Greenwich, Tokyo, other)?

I don't know about the dark pool, but for 1 and 4:


Yes, so a price of 12345678 for a USD order would be $123.46 (rounded). I've compiled a list of minimum tick sizes for each currency here: https://bitbucket.org/nitrous/mtgox-api#markdown-header-currencies (https://bitbucket.org/nitrous/mtgox-api#markdown-header-currencies) - USD is listed as having 100,000 divisions, so the tick size is 1/100000 = 0.00001


The timezone used for the dates in the bigquery data is GMT

Thank you.

Loozik - I discovered that Big Query has an Excel connector which is handy for auto-updating spreadsheets: https://bigquery-connector.appspot.com/ (https://bigquery-connector.appspot.com/)

You still need to understand the setup of the Big Query project to load the Mt-Gox dataset and the query example provided by dexX7.

Best of all it isn't limited to 16K rows like the csv option, rather my Excel spreadsheet stopped at 110K rows, so I added another worksheet and pulled the next 110K results into there.

Thanks 8bitPunk for the info. This Big Query thing is too difficult for me. I am a silly non-techie :D I will wait till a more elegant solution is availed.

I found a temporary remedy for my charting pains as far BTCUSD from MtGox goes https://www.tradingview.com/e/?symbol=MTGOX:BTCUSD# It isn't a perfect solution (I can't make use of custom indicators and strategies) but at least I can draw trendlines.

so this appears to mirror large datasets.  is anyone mirroring mt gox last?  I need to built an effective ticker but I find mt gox's feed is wholly unreliable.  Any advice?

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

Does the data end in May 2013?

Unfortunately yes. MtGox has not updated the BigQuery database in quite some time, however the data is complete up to May 2013.

It's like MagicalTux has been on strike. Everybody's mean to him I guess  :'(