Stellar balances

Question

I am trying to write a query to find out stellar balances(for all addresses)but I am not able to get the correct operations to consider in that , maybe because I don't understand stellar very well .

So , the questions I have are as follows .

1. How to calculate balance of an address with query. I have a very simple query but not giving correct balances . Here currency_id is for stellar .

SELECT 
 
  sum(in_value_xlm) total_in, 
  sum(out_value_xlm) total_out, 
  tx_date,
  total_in - total_out AS balance

  FROM 
  (
SELECT
   0 AS in_value_xlm,
   sum(toFloat64(value_from) / 10000000) AS out_value_xlm,
   tx_date
   
FROM stellar_flow.transfers_from
WHERE (transfer_from = '{{ addr }}') AND (currency_from_id = 417896) 
GROUP BY tx_date
UNION All 
SELECT
   sum(toFloat64(value_to) / 10000000) AS in_value_xlm,
   0 AS out_value_xlm,
   tx_date
FROM stellar_flow.transfers_to
WHERE (transfer_to = '{{ addr }}') AND (currency_to_id = 417896) 
GROUP BY tx_date
)
GROUP BY tx_date 

2. Is there any website/page where I can find daily address stellar balance of an address , similar to stellar expert .

Thank you

Answer 1

If you have operation type included in your table, make sure to include the following operations, which impact the account balance:

• 1 - payment
• 2 - path_payment_strict_receive
• 3 - manage_sell_offer, only once the trade executes
• 4 - manage_buy_offer, only once the trade executes
• 8 - account_merge
• 13 - path_payment_strict_receive
• 15 - claim_claimable_balance
• 22 - liquidity_pool_deposit
• 23 - liquidity_pool_withdraw

Stellar Expert is an excellent real time source for account balances; you can use the website or their Directory API. The Horizon API also fetches account balance details in real time.

If you want a daily dump of the data, you could use the publicly available GCP dataset to query the accounts table directly and create an extract.

Answer 2

Bitquery has APIs to get balances and balance updates on Stellar. Check: https://ide.bitquery.io/latest-balance-of-stellar-wallet

More examples here: https://docs.bitquery.io/v1/docs/category/stellar

You can sign up for a free developer account at https://ide.bitquery.io/.

Full disclosure: I work at Bitquery