Tracking Mt. Gox Trustee Balances with Google BigQuery
Mt. Gox was once the largest cryptocurrency exchange in the world, handling more than 70% of Bitcoin transactions at its peak. However, in February 2014, Mt. Gox filed for bankruptcy protection, and soon after, it was revealed that the exchange had lost approximately 850,000 Bitcoins. Subsequently, the exchange was able to recover approximately 150,000 BTC, representing about 20% of the hack.
Following a 10-year liquidation process, creditors of Mt. Gox are about to recover some of their funds. The trustee overseeing the Mt. Gox bankruptcy proceedings announced a plan to distribute the recovered funds, with early repayments to start from March 2023 and a deadline to pay out by September 2023.
This announcement has sparked renewed interest in tracking the movements of the Mt. Gox trustee wallets, as creditors and other stakeholders seek to understand how the distribution of funds will be carried out. In this article, we will show how to track the Mt. Gox trustee balances using Google BigQuery, and examine how this analysis can provide insights into the upcoming repayments for the creditors. The cold wallet addresses belonging to the Mt. Gox trustee are shown in this list.
The full query to compute the BTC balances for the Mt. Gox trustee is shown below. We will use BTC transaction data provided in the Google Public Dataset. In particular, we will analyze the data provided in the bigquery-public-data.crypto_bitcoin
table. Details of the code was discussed in Guide to Query Bitcoin Balances Using BigQuery.
The wallet addresses belonging to the Mt. Gox trustee are identified with WHERE contains_substr('address','string')
. This clause searches for records in the daily_balances_gappy
table that contain a specific string in the address
field. The specific string corresponds to the cold wallet addresses.
WITH double_entry_book AS (
-- debits
SELECT ARRAY_TO_STRING(inputs.addresses, ",") AS address, inputs.type,
-inputs.value AS value, block_timestamp
FROM `bigquery-public-data.crypto_bitcoin.inputs` AS inputs
UNION ALL
-- credits
SELECT ARRAY_TO_STRING(outputs.addresses, ",") AS address, outputs.type,
outputs.value AS value, block_timestamp
FROM `bigquery-public-data.crypto_bitcoin.outputs` AS outputs
),
double_entry_book_by_date AS (
SELECT DATE(block_timestamp) AS date, address, SUM(value / POWER(10,0)) AS value
FROM double_entry_book
GROUP BY address, date
),
daily_balances_gappy AS (
SELECT date, address,
SUM(value) OVER (PARTITION BY address ORDER BY date) AS balance,
LEAD(date, 1, CURRENT_DATE()) OVER (PARTITION BY address ORDER BY date) AS next_date
FROM double_entry_book_by_date
),
all_dates AS (
SELECT date FROM UNNEST(GENERATE_DATE_ARRAY('2009-01-12', CURRENT_DATE())) AS date
),
daily_balances AS (
SELECT address, all_dates.date, balance
FROM daily_balances_gappy
JOIN all_dates ON daily_balances_gappy.date <= all_dates.date AND all_dates.date < daily_balances_gappy.next_date
-- MT Gox
WHERE CONTAINS_SUBSTR(`address`, '12KkeeRkiNS13GMbg7zos9KRn9ggvZtZgx') OR
CONTAINS_SUBSTR(`address`, '12T4oSNd4t9ty9fodgNd47TWhK35pAxDYN') OR
CONTAINS_SUBSTR(`address`, '13ahgw8sM95EDbugT3tdb8TYoMU46Uw7PX') OR
CONTAINS_SUBSTR(`address`, '13dXFMyG22EsUsvaWhCqUo7SXuX7rBPog6') OR
CONTAINS_SUBSTR(`address`, '13sXfpp2V16nnxYvW9FHHoBdMa3k98uJw8') OR
CONTAINS_SUBSTR(`address`, '13Wv5hGhubAWgSPWtXYh6s1s7HX2N1psYg') OR
CONTAINS_SUBSTR(`address`, '13xGCc4TPSYY9GYxBGVNox82KxyjkFnxMX') OR
CONTAINS_SUBSTR(`address`, '1439q4Na8v88kPBqoyg8F4ueL9SYr8ANWj') OR
CONTAINS_SUBSTR(`address`, '14mP6caC5dFhHdVAPCjPKM8Nm36MBDR5pM') OR
CONTAINS_SUBSTR(`address`, '14p4w3TRCd6NMRSnzTmgdvQhNnbrAmzXmy') OR
CONTAINS_SUBSTR(`address`, '14USZ558Rr28AZwdJQyciSQkN4JT1cEoj2') OR
CONTAINS_SUBSTR(`address`, '155FsTtEFq4eGCcBxDseuwLKPbmtWbyHJR') OR
CONTAINS_SUBSTR(`address`, '156HpsWfgkWYLT63uhTAGUSUF3ZMnB9WWj') OR
CONTAINS_SUBSTR(`address`, '15kNZcrhxeFZgVVLK2Yjzd69tRidbFdJEZ') OR
CONTAINS_SUBSTR(`address`, '15QcKCa84ZCHxbsqXDoKhi5XbmQB8jPEAd') OR
CONTAINS_SUBSTR(`address`, '15SeCwVCFx5cWyrcdD1Zp1D1zxjH2SELPg') OR
CONTAINS_SUBSTR(`address`, '15U4VsmWG1cdXAtizvQsW4r7iMxzp64Tgu') OR
CONTAINS_SUBSTR(`address`, '16jZZkMYqjUWUtQ9DfDvHdH5ko5BcnH9XQ') OR
CONTAINS_SUBSTR(`address`, '16W4XcUAKPmSES9MiUCio28msSCp8rDZgs') OR
CONTAINS_SUBSTR(`address`, '16w6sZBDP58yyeyZAcvnxcEGJpwR9amM6g') OR
CONTAINS_SUBSTR(`address`, '17etv2L3nhk6SCcWSNW4eoZkBy84izAm17') OR
CONTAINS_SUBSTR(`address`, '17KcBp8g76Ue8pywgjta4q8Ds6wK4bEKp7') OR
CONTAINS_SUBSTR(`address`, '17Tf4bVQaCzwWrDWGRPC97RLCHnU4LY8Qr') OR
CONTAINS_SUBSTR(`address`, '18hcZVFPqDNAovJmb9vA6hEJrDz6uWXNGh') OR
CONTAINS_SUBSTR(`address`, '18KDS3q6a4YV9Nn8jcyMvNoVPfcrfemeag') OR
CONTAINS_SUBSTR(`address`, '18M1Z337NqLtK9V69bssnQUYsvb7hmfSFS') OR
CONTAINS_SUBSTR(`address`, '18ok25NTkdrUzdByFJCNVsqVYkujZ8aP45') OR
CONTAINS_SUBSTR(`address`, '18YDgRhxsomuBZ1g9d8Y1JuRmxDhF8Bvff') OR
CONTAINS_SUBSTR(`address`, '195HvmjXgoF3M5vFaBC8swZPhwrE7VhxRD') OR
CONTAINS_SUBSTR(`address`, '199Yxz2TJGtND3QKsHTptTJivqSaUZBvku') OR
CONTAINS_SUBSTR(`address`, '19c8sUa54yQuRTVDfJa3iDkkCaFkzBJLPB') OR
CONTAINS_SUBSTR(`address`, '19Cr4zXpKw43xLJhFZW9iv4DDNtQk2TDeB') OR
CONTAINS_SUBSTR(`address`, '19eihBKk6e5YD2QXAe4SVUsxRLLnTDKsfv') OR
CONTAINS_SUBSTR(`address`, '19KiFrafXEyJCUDYFEv3B6tBUwyfFo7kNU') OR
CONTAINS_SUBSTR(`address`, '1Ar6meJQCkNoC9wnPcyRNNpzX5fBDaGcKd') OR
CONTAINS_SUBSTR(`address`, '1AZu7TQmKBAes2duNDctYwjAB9nhHczUnA') OR
CONTAINS_SUBSTR(`address`, '1B6kJM75iu5ty1HAHMMz6tT1HhjoGNTCa9') OR
CONTAINS_SUBSTR(`address`, '1BDZBTb4KE5oq6wAgA6EvAe3uCFRrAbPao') OR
CONTAINS_SUBSTR(`address`, '1BXyJc6BVuTFnHQCcjiWX2xmCPNVfaSZeb') OR
CONTAINS_SUBSTR(`address`, '1BzK87zuqidZn489Wb2oLSktrjKrX7TLKe') OR
CONTAINS_SUBSTR(`address`, '1C5aU4Xnpd3txbxehk46UZgiuNB8QdpHCH') OR
CONTAINS_SUBSTR(`address`, '1CRjKZJu8LvTutnSKq4zTJ4yiqrzMAArYW') OR
CONTAINS_SUBSTR(`address`, '1CZsoJfkknbnW5fKrt1oR7N1ALE5WmDGP1') OR
CONTAINS_SUBSTR(`address`, '1DedUxzgwErg4ipNi988wPgLk5thwciKcc') OR
CONTAINS_SUBSTR(`address`, '1Drshi4RAuvxk4T6Bkq959ZvLbvy7b1wvD') OR
CONTAINS_SUBSTR(`address`, '1EiiKCCnFgHjEvPZdu29qqgdBm8zTvpU3U') OR
CONTAINS_SUBSTR(`address`, '1EK8vW7UYaYHKiW4TZmYJKtwcZLM14VjvP') OR
CONTAINS_SUBSTR(`address`, '1FhRuUkk8Bfx8FJDemtxhKAR4F8GCNKrXG') OR
CONTAINS_SUBSTR(`address`, '1FrV9hv1AW34BGJvobJatyzUWYDWB9epRW') OR
CONTAINS_SUBSTR(`address`, '1Fu4YgM3Y9CxvioGPqkSzkydAC8MVaPN1D') OR
CONTAINS_SUBSTR(`address`, '1G23Uzwj55k2A9TRwaTknqGav66oDTkWCu') OR
CONTAINS_SUBSTR(`address`, '1GkZQcDy8V6pmHFZqUBUBCnN9dc2hoWasD') OR
CONTAINS_SUBSTR(`address`, '1GyDutntMuYyA2vQGW5HFcKLfx4cbDdbJq') OR
CONTAINS_SUBSTR(`address`, '1H4K3dGfNbAN4AUfyUrpkGpjrd83sntDpV') OR
CONTAINS_SUBSTR(`address`, '1Hb8DmmvvtTYv5RBLuGtDxznkZwVpd5Vjy') OR
CONTAINS_SUBSTR(`address`, '1HdKXsNQtzDcfB6PGM7DWTgX9vhBWsz1ak') OR
CONTAINS_SUBSTR(`address`, '1Hm6XDmhKCHz68wDEYTapN9MEanke8iwUk') OR
CONTAINS_SUBSTR(`address`, '1HuPVqz2xvf1rdNFUqd62vRTyxP3jeX9Ch') OR
CONTAINS_SUBSTR(`address`, '1HweN9p41BY2RBunsPqyVuheEq7gVoxA9u') OR
CONTAINS_SUBSTR(`address`, '1HX4s3JeFU3x1eQgPNQVAdx6FoCtbb1hr8') OR
CONTAINS_SUBSTR(`address`, '1HzEPuenagLEWj68igDXBBXrzc293RuR5V') OR
CONTAINS_SUBSTR(`address`, '1JtgU6Uo1RAt5eiMf34EehyatUezBQP36C') OR
CONTAINS_SUBSTR(`address`, '1JVmoJT3471FjsX5H4hAeR1RyrDgpkHbpm') OR
CONTAINS_SUBSTR(`address`, '1JVU43LNKXqa9W5fCh8tppxDDEWgfeNg46') OR
CONTAINS_SUBSTR(`address`, '1JztCg7eKSkb1vi7NzGJynXpLZmoaFtYud') OR
CONTAINS_SUBSTR(`address`, '1KFDUSZuapMv7YaDmL6cyrHTQhma1MtFYs') OR
CONTAINS_SUBSTR(`address`, '1LLc8aA9C9LLULGbYCYSFKXgxKP2DXdCqP') OR
CONTAINS_SUBSTR(`address`, '1LS5EFRRMDgMQusW6zokQUHjzNUfy6HHCQ') OR
CONTAINS_SUBSTR(`address`, '1LueUjEuBgc7cQhsWT8zAfTjcWmrNBZXaR') OR
CONTAINS_SUBSTR(`address`, '1LXi3x7hyt17cxncscGE887WCrC6XDNZ4P') OR
CONTAINS_SUBSTR(`address`, '1LzwbLgdKd4eFLkpRdeajkH1YJkVCip2zj') OR
CONTAINS_SUBSTR(`address`, '1MkyfwJf7uhWTmVGGQXfcT5ip31DoHMxsz') OR
CONTAINS_SUBSTR(`address`, '1Mm9brripN4RPTzkGnRrbt5uDWdqbfk2iX') OR
CONTAINS_SUBSTR(`address`, '1MPJJzRaT8vLhowNB4dVyWRxxu79dq7WkB') OR
CONTAINS_SUBSTR(`address`, '1MvpYtqgBH7CXbTutrSVCTNHPzm9vakuRy') OR
CONTAINS_SUBSTR(`address`, '1N5X4kcZ56uRh24XrZoztS9Vb8G7j1Joop') OR
CONTAINS_SUBSTR(`address`, '1NA3Tj4b1jtx9eGELe31Jw4DrzTqKP3ayH') OR
CONTAINS_SUBSTR(`address`, '1Pq7hooZbEAz5y3QMnqFY8C5xqTdrjUwcA') OR
CONTAINS_SUBSTR(`address`, '1PRXQEoL8vzEzoJJ9hbtAP6NaV2daccAUn') OR
CONTAINS_SUBSTR(`address`, '1PxGTuJzDx1ceFHx4Z5CHaWuhiPBNovmZD')
),
total_daily_balances AS (
SELECT DISTINCT date, SUM(balance) OVER(PARTITION BY date)/POWER(10,8) AS mtgox_balance
FROM daily_balances
)
SELECT * FROM total_daily_balances
ORDER BY date
Conclusion
In conclusion, the use of Google BigQuery to track Mt. Gox trustee balances has highlighted the potential for technology to enhance transparency in the cryptocurrency space. Creditors can use this tool to gain valuable insights into the distribution of the remaining assets, and through which build trust and confidence in this rapidly evolving industry.
Also read:
Thank you for reading!
If you liked the article and would like to see more, consider following me. I post regularly on topics related to on-chain analysis, machine learning and BigQuery. I try to keep my articles simple but precise, providing code, examples and simulations whenever possible.