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.

Mt. Gox Trustee Balances

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:

• Proof of Reserves for Bitcoin Holdings — How to Compute in BigQuery
• An Easy Way to Access Bitcoin Block Data Using BigQuery
• Bitcoin: Monitoring Miner Revenue Using BigQuery

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.