Historical Balance of Polkadot Accounts (python substrate)

Question

Is there a way to retrieve the historical balance of a Polkadot account without the need to query every individual block? My current approach involves querying blocks in batches, but it's quite time-consuming (10 minutes for 1000 blocks), and querying all 17 million blocks would take an impractical 152 days.

Ideally, I'm looking for a method to obtain the historical balances of multiple accounts from the genesis block (0) up to the current block (17 million) without extensive data collection. Are there reliable historical nodes or index providers available for Polkadot that can streamline this process?

Answer 1

Instead of getting all the events and adding up balances, you can directly query system.account(AccountId32) for the balance of the account/accounts at a specific block number. You are still required to loop through different block heights to get the balances over time, but it will be much faster and lighter on the data intake than reading events.

You'll end up with a result like 'data' below.

Indexers

subsquid: https://docs.subsquid.io/

subquery: https://academy.subquery.network/

Apis

colorfulnotion: https://github.com/colorfulnotion/polkaholic

subscan: https://support.subscan.io/#introduction

Answer 2

I agree with ehpeeeye, a very good approach would be to use the system.account storage function or leverage the features an indexer provides. The Substrate-ETL dataset would provide granular information for balances in BigQuery.

Here's some code to get you started for your specific question, in Python:

from substrateinterface import SubstrateInterface
import time 

substrate = SubstrateInterface(url="wss://rpc.polkadot.io")

def format_balance(amount: int):
    amount = format(amount / 10**substrate.properties.get('tokenDecimals', 0), ".15g")
    return f"{amount} {substrate.properties.get('tokenSymbol', 'UNIT')}"


for block_number in range(17370000, 17379181, 1000):

    block_hash = substrate.get_block_hash(block_number)

    result = substrate.query(
        "System", "Account", ["14uufggn6CkHWpDUeyNEDjZMkYLb7waWtcrLC57sUfEwVPQa"], block_hash=block_hash
    )

    balance = (result.value["data"]["free"] + result.value["data"]["reserved"])

    print(f"Balance @ {block_number}: {format_balance(balance)}")

    # Being nice
    time.sleep(1)

It's also listed as example in PySubstrateInterface here.

There are possible optimizations instead of looping through all the blocks if you're willing to sacrifice precision to gain speed. Another optimization could be as simple as connecting to multiple RPC's at the same time and query them in parallel.

Either way, I hope this helps.