Hi Menaka, thanks for your request. We currently support the following tables for Connected Accounts: 

• Events
• Charges
• Payouts
• Balance Transaction
• Apple Domain
• Refunds
• Disputes

Is the value in the `AMOUNT` column in the `BALANCE_TRANSACTION` table different from what you're looking for?

Hi Anna,

Thanks for the information. 
The value in the `AMOUNT` column in the `BALANCE_TRANSACTION` table is different from what we're looking for.

`AMOUNT` is the transaction amount. We're looking for the balance in the account.

As I mentioned earlier, the following query fetches the balance in the account.

curl https://api.stripe.com/v1/balance -u sk_live_blahblahblah -H "Stripe-Account: acct_1234567890"



{
"object": "balance",
"available": [
{
"amount": 79,
"currency": "gbp",
"source_types": {
"card": 79
}
}
],
"livemode": true,
"pending": [
{
"amount": 0,
"currency": "gbp",
"source_types": {
"card": 0
}
}
]
}

Thanks.

Hi Menaka -  I work with Anna on the Product Team

I'm looking to the Balance amount on an account. From what I gather, BALANCE_TRANSACTION is lower granularity than the BALANCE table. Our Engineer mentioned that you should be able to aggregate BALANCE_TRANSACTION records at the Connected Account Level to the get the value that would appear in BALANCE. 

If this isn't the case, can you provide additional information? 

Thanks, 

Erin Cullen

Erin To chime in here, my organization needs the same information. Unfortunately, summing the amount from the balance transaction table does not get the same value you would get from the API request described by Menaka. I can't say for sure that the logic can't somehow be replicated, but it would be extremely helpful to have this information supported and pulled in via FiveTran. 

Thanks Erin,
Aggregating BALANCE_TRANSACTION records at the Connected Account Level does not provide the Account Balance. I think balance is a sum of all charges, less refunds, less fees, less payouts. Or something along those lines.

It looks like the same Feature Request has been made: https://support.fivetran.com/hc/en-us/community/posts/4403516618007-Connector-Improvement-Feature-Request-Stripe-Balances-API 

Hi Menaka -

Our team talked to someone at Stripe and they provided us with this information:

Erin What Stripe told you is dead wrong, lol. But I did manage to find the query logic to get the Balance for each account. Hopefully this helps anyone else that was blocked by this. See query below: 

WITH payments AS (
    SELECT
        connected_account_id AS account_id,
    	ROUND(SUM(amount) / 100, 2) AS amt
    FROM stripe.charge
    GROUP BY 1
)

, collected_fees AS (
	SELECT
	    connected_account_id AS account_id,
	    ROUND(SUM(amount) / 100, 2) AS amt,
	FROM stripe.fee
    GROUP BY 1
)

, payouts AS (
	SELECT
	    connected_account_id AS account_id,
		ROUND(SUM(amount) / 100, 2) AS amt
	FROM stripe.payout
    GROUP BY 1
)

SELECT
    payments.account_id,
    payments.amt AS payments_total,
    collected_fees.amt AS fees_total,
    payouts.amt AS payouts_total,
    ROUND(payments.amt - payouts.amt - collected_fees.amt, 2) AS balance,
FROM payments
LEFT JOIN collected_fees ON payments.account_id = collected_fees.account_id
LEFT JOIN payouts ON payments.account_id = payouts.account_id

 Erin

I am of the same opinion as Jonathan Talmi

I worked out a more complex query which calculares balance as

charges - fees 

- refunds + refund failures

- disputes + dispute reversals

- dispute fees + dispute reversal fees

- payouts + payout reversals

Though this works in most cases, there are cases where there are more types of items.