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
Topic: New method to pull MtGox trade data (Read 18568 times)
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
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.
Does the data end in May 2013?
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://bitcointalksearch.org/topic/m.3017272 and let me know.
yours sincerely
bitranox
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://bitcointalksearch.org/topic/m.3017272 and let me know.
yours sincerely
bitranox
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?
Loozik - I discovered that Big Query has an Excel connector which is handy for auto-updating spreadsheets: 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.
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
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.
Loozik - I discovered that Big Query has an Excel connector which is handy for auto-updating spreadsheets: 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.
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.
Thank you.
Guys, I have a couple of questions:
I don't know about the dark pool, but for 1 and 4:
1. Is the minimum price tick size of BTCUSD at MtGox 0.00001?
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 - USD is listed as having 100,000 divisions, so the tick size is 1/100000 = 0.00001
4. What is MtGox time (Greenwich, Tokyo, other)?
The timezone used for the dates in the bigquery data is GMT
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)?
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 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
This is how beautifully BTCUSD can look with high quality data in a charting software 
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
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
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.
This is how beautifully BTCUSD can look with high quality data in a charting software 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
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
Okay. I voted for option 1. Disk space is cheap now-a-days.
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 
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].
The issue is that Google won't let you download a CSV copy of the results if there are more than 16k rows.
What a misfortune
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://bitcointalksearch.org/topic/discussion-for-mtgox-trade-data-downloader-221055), 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.
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
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§ion=software#products a list of charting packages that MtGox can reach in a minute without the need to invest in infrastructure too much.
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
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://bitcointalksearch.org/topic/discussion-for-mtgox-trade-data-downloader-221055), 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.
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
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
Just pay attention when using bigquery.cloud.com. It is very easy to get charged.
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.
Edit:
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.
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.
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.
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
Date,Time,Price,Volume (in Satoshis)
yyyy-mm-dd,hh:mm:ss,65000000000
Step 1: Go to the sign up website (picture)
Code:
https://bigquery.cloud.google.com/
Step 2: Create project.. (picture)
Step 3: Enable "BigQuery API" (picture)
Step 4: If you agree, accept the ToS (picture)
Step 5: Compose a query (picture)
Code:
SELECT DATE(TIMESTAMP(date)) as date, TIME(TIMESTAMP(date)) as time, price/100000 as price, amount
FROM [mt-gox:mtgox.trades]
WHERE Currency__ = "USD"
AND date < "2013-05-19 17:30:00"
ORDER BY date DESC, time DESC LIMIT 16000;
Step 6: Edit the query for your needs and after processing you should be able to download the data as csv.
Code:
date,time,price,amount
2013-05-19,17:29:46,120.88,18501496
2013-05-19,17:29:45,120.83,84148500
2013-05-19,17:29:42,120.83,50000000
2013-05-19,17:28:57,120.12501,1138895
...
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.
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 of stuff you can do related to date/time.
For everyone who needs timestamp instead of a date string:
Code:
SELECT PARSE_UTC_USEC(date) as timestamp FROM [mt-gox:mtgox.trades] WHERE price = 2122000 LIMIT 1
This will return:
Code:
Row timestamp
1 1359647088000000
For everyone who needs the date and the time data in separated columns:
Code:
SELECT DATE(TIMESTAMP(date)) as date, TIME(TIMESTAMP(date)) as time FROM [mt-gox:mtgox.trades] WHERE price = 2122000 LIMIT 1
This will return:
Code:
Row date time
1 2013-01-31 15:44:48
Edit, here is a list of stuff you can do related to date/time.
What data format do you need? I'm happy to help.
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.
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.
Edit:
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.
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.
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.
So I spotted this thread right on time.Of course I always welcome donations but most of the bitcoin work I do/am doing is open-source.
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. 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.
Fantastic
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.
Fantastic
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.
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 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?
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
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. 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.
Fantastic
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.
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 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?
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.
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".
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?
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?
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
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".
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?
Does the application not allow any way of doing this directly?
Unfortunately not.
- 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).
I wouldn't have the courage to bother him
- 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:
Code:
SELECT LEFT(Date,10),RIGHT(Date,8) FROM [mt-gox:mtgox.trades] LIMIT 10;
[/list] 
Thanks, this is great info!
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".
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.
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:
Code:
SELECT LEFT(Date,10),RIGHT(Date,8) FROM [mt-gox:mtgox.trades] LIMIT 10;
- N.B. though, that BigQuery may not let you download all the data at once using this method.[/li]
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".
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.
| Date | The date of the trade in ISO format. | str |
Great
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 |
| Date | The date of the trade in ISO format. | str |
Great
because my charting app does not accept Unixtime.Okay, we have Date like ''2013-05-28''. Isn't Time like ''hhmmss'' missing?
This is really sweet, thanks!
Just for info, automatic update of these data is scheduled to start this week or next week
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:
Now you can perform SQL queries on this using [mt-gox:mtgox.trades] for the table name, e.g.:
The fields mean the following:
* 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 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
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 for instructions on doing so.
- Now that you are signed up, go to the BigQuery console: 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.:
Code:
select * from [mt-gox:mtgox.trades] where Currency__='GBP';
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 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
Jump to: