2

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?

2 Answers 2

2

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. enter image description here

Indexers

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

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

Apis

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

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

2
  • What if i wanted to query ALL polkadot accounts and their historical balances? would I still have to parse every block and all events associated for each block? How about other modules like batch transfers and crowdloans(deposit/refunds?) these counts as transfers but not in the balance module Commented Sep 22, 2023 at 6:37
  • To get the free/reserved balance of all accounts you do not need additional events. The system.account storage function takes into consideration all events related to balance of the account. If an event related to balance came from a batch call, a crowdloan return or a regular balance transfer, they would all be accounted for with the system account function. If you wanted to see how DOT was moved between accounts, then you would need the events. Otherwise, adjusting Karim's code below to loop through all addresses would get you all address balances over time.
    – ehpeeeye
    Commented Sep 25, 2023 at 7:08
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.