Five Stripe Sigma Queries for Your Month-End Close

Month end asks your Stripe account the same handful of questions every time. What was inside that payout. What did the fees come to, and for what. Which refunds were real. Which invoices are still sitting there unpaid. Answering them usually means downloading three reports, opening a spreadsheet, and losing twenty minutes to a pivot table you will rebuild next month.

Stripe Sigma answers all four in SQL, inside the Dashboard, against your own data. Stripe describes it as an interactive SQL environment "for querying your transactional data", and the data behind it is read-only: "Queries can't modify existing data or create new transactions." There is no way to break anything with a bad query. The worst case is a wrong answer.

The problem is that every Sigma tutorial on the internet is written for engineers. This one is written for whoever closes the books. Five queries, each tied to a reconciliation question, with every table and column checked against Stripe's own schema documentation rather than typed from memory.

<p><a href="https://app.acodei.com/signup">Start a free trial</a></p>

What Stripe Sigma queries give you that the Dashboard reports do not

The Dashboard reports answer fixed questions very well. Sigma answers yours. That is the whole difference, and it matters most in the cases where your business does something slightly unusual: two currencies, a Connect platform, a product line you want broken out, a fee type you want isolated.

Three practical things are worth knowing before you start.

You can rehearse for free. Stripe states that "Sigma is free to use in sandboxes, with no usage limits." Point a sandbox at test data, get the query right there, then run it against live data once you know it works.

The Dashboard reports exist as templates. Sigma ships template queries that generate the same reports you already download, including balance_change_from_activity.itemized.3 for the Balance report, payouts.itemized.3 for itemized payouts, and payout_reconciliation.itemized.5 for the Payout reconciliation report. If you only want the report you already run, start from the template and edit the dates.

You can see how current the data is. Sigma exposes a data_load_time parameter, which Stripe describes as "the timestamp that data is available through". If you are closing on the second of the month, check it before you trust a query that includes the last day. Stripe's own overview of what Sigma does lives at docs.stripe.com/stripe-data/access-data-in-dashboard.

Start from balance_transactions, not from charges

This is the single decision that determines whether your query reconciles or merely looks plausible, and Stripe puts it plainly: "Use the balance_transactions table as a starting point for accounting purposes."

The reason is structural. A charge is a sale. A balance transaction is a movement of money into or out of your Stripe balance, of any kind. Stripe lists charges, refunds, transfers, payouts, adjustments and application fees among the common types, which means the one table covers events that live in four different Dashboard reports.

It is also immutable. Stripe says each row "represents an individual balance_transaction object that doesn't change after it's created", and spells out the consequence: refunding a charge "creates a separate balance transaction of type refund, but it doesn't modify the original balance transaction". That is exactly the property double-entry bookkeeping wants. Nothing you reconciled last month gets quietly rewritten this month.

Query the charges table and you get the sales you made. Query balance_transactions and you get the money that actually moved. Only the second one ties to a bank account, which is why our guide to Stripe balance transactions treats it as the ledger and everything else as detail hanging off it.

Stripe's schema notes for the transaction tables, including the joins used in the next three queries, are at docs.stripe.com/data/query-transactions. It is worth ten minutes before you write anything of your own, mostly because it tells you which table owns which column and saves you guessing at names that do not exist.

Query 1: what is actually inside a payout

A payout hits your bank as one number. This returns the transactions that made it up, grouped by type.

select
  transfers.id as payout_id,
  date_format(date_trunc('day', transfers.date), '%Y-%m-%d') as payout_date,
  balance_transactions.type,
  count(*) as row_count,
  sum(balance_transactions.amount) as gross,
  sum(balance_transactions.fee) as fees,
  sum(balance_transactions.net) as net
from balance_transactions
inner join transfers
  on balance_transactions.automatic_transfer_id = transfers.id
where transfers.id = 'po_replace_with_your_payout_id'
group by 1, 2, 3
order by 3

The join is the important part. Every balance transaction that Stripe swept into a payout carries that payout's ID in automatic_transfer_id, so the join reassembles the deposit from its components. The net column is what actually left for your bank; gross minus fees is why it differs from the sales total.

One prerequisite catches people, and it is not a query problem. Stripe says this reconciliation works "as long as you're using automatic payouts". On manual payouts, "the amount in each payout to your bank account is arbitrary. As such, you can't reconcile it to specific balance transactions." The Payout Reconciliation report has the same restriction: it is "only available for users with the Automatic payouts setting enabled". If Query 1 returns nothing and you are sure the payout exists, check that setting before you debug the SQL.

Two smaller notes. transfers.date is the date the payout is scheduled to arrive in your bank, not the date Stripe created it, which is usually the date you want for matching a bank line. And if you are reaching back into old history, Stripe warns that "payouts before 04-06-2017 have a TRANSFER_ID with a tr_ prefix" rather than po_.

Once you have the composition, our complete guide to Stripe payout reconciliation in QuickBooks covers what to do with it on the QuickBooks side.

Query 2: last month's fees, split by what Stripe charged them for

Fee totals are easy. Fee composition is the part that takes an afternoon.

select
  balance_transaction_fee_details.type as fee_type,
  count(*) as fee_rows,
  sum(balance_transactions.fee) as fee_on_transaction
from balance_transactions
inner join balance_transaction_fee_details
  on balance_transaction_fee_details.balance_transaction_id = balance_transactions.id
where balance_transactions.created >= timestamp '2026-08-01 00:00'
  and balance_transactions.created < timestamp '2026-09-01 00:00'
group by 1
order by 3 desc

balance_transaction_fee_details is the table that says what each fee was for, joined to the ledger on balance_transaction_id. Stripe's own example of this join returns a type of stripe_fee, and the value of the table is everything that is not that.

Read the result carefully. The fee amount here comes from the balance transaction, so a single transaction carrying more than one fee detail row will be counted once per row. Treat the breakdown as composition, and take the true total from the ledger alone:

select sum(fee) as total_fee
from balance_transactions
where created >= timestamp '2026-08-01 00:00'
  and created < timestamp '2026-09-01 00:00'

If those two totals disagree, the difference is multi-fee transactions, not missing data. The Stripe Fees report and the month a fee belongs to explains why the same fee can legitimately land in two different months depending on which date you pivot on, which is the other reason a fee total refuses to tie.

Query 3: the refunds that are not refunds

If you take authorizations and capture less than you authorized, some of what your books call refunds never were.

Stripe documents the mechanism directly: authorizing 10 USD and capturing only 7 "creates a charge for 10 USD" and "also creates a refund with the reason partial_capture for the remaining 3 USD". Its own guidance is to "use the refund's reason field to filter out partial capture refunds when retrieving payment information".

select
  date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as day,
  refunds.charge_id,
  refunds.reason,
  balance_transactions.amount
from balance_transactions
inner join refunds
  on refunds.balance_transaction_id = balance_transactions.id
where balance_transactions.type = 'refund'
  and refunds.reason != 'partial_capture'
order by 1 desc

Drop the reason filter and you have the other half: the authorization releases. They matter separately, because a released hold is not a customer sending money back. Counting it as one overstates gross sales and refunds by the same amount, which nets to zero on the bottom line and makes every revenue and refund-rate number above it wrong. How Stripe partial captures land in QuickBooks covers the bookkeeping treatment.

Query 4: invoices with nothing behind them

select
  invoices.id as invoice_id,
  invoices.subscription_id,
  invoices.currency,
  invoices.amount_due,
  date_format(date_trunc('day', invoices.period_end), '%Y-%m-%d') as period_end
from invoices
where invoices.charge_id is null
  and invoices.amount_due > 0
order by 5

This is the closest honest approximation of "issued and never collected" using columns Stripe documents on the invoices table. It finds invoices with an amount still due and no charge attached.

Call it a worklist, not a number. An invoice settled outside Stripe, or through a flow that does not attach a charge, can appear here while being perfectly collected. Run it to produce a list of things to check, not a receivables balance to post.

Two cautions on invoice data generally. Stripe warns that period_start and period_end represent "when invoice items might have been created" and are "not always definitive of the period of service that the customer is being billed for", so do not build revenue cutoffs on them without checking. And there is no discount column: Stripe states that "there is no column to represent the discount amount on an invoice", and the amount has to be aggregated from invoice_line_item_discount_amounts instead. An invoice total that refuses to tie to the sum of its lines is usually one of those two things. The billing tables are documented separately at docs.stripe.com/data/query-billing-data.

Query 5: the daily roll-up your close runs on

select
  date_format(date_trunc('day', created), '%Y-%m-%d') as day,
  type,
  currency,
  count(*) as row_count,
  sum(amount) as gross,
  sum(fee) as fees,
  sum(net) as net
from balance_transactions
where created >= timestamp '2026-08-01 00:00'
  and created < timestamp '2026-09-01 00:00'
group by 1, 2, 3
order by 1, 2

One row per day, per transaction type, per currency, with gross, fees and net. It is the smallest query on this page and the one worth saving, because it is the shape a Stripe month actually has: a ledger summarised to the level your general ledger wants it.

Run it twice, once for the month you are closing and once for the month before, and the anomalies stand out without any analysis at all. A type that appears for the first time is a new kind of transaction your chart of accounts has probably never been asked about. The Stripe Balance report versus the Payout reconciliation report explains which of the two models this roll-up should be matched against, which depends on how your QuickBooks file is set up rather than on preference.

Four reasons your Sigma number will not match the Dashboard

All four are documented, none is a bug, and each one has produced at least one wasted afternoon somewhere.

Time zone. Stripe filters financial reports in the Dashboard "by the local time zone by default", while "Sigma filters templates by the UTC time zone". For a business whose evening is busy, that moves real revenue across a month end.

What a date range means. A Dashboard range of Jan 13 to Jan 14 covers "January 13 00:00:00 up to January 14 23:59:59". The same range on a Sigma template covers "Jan 13 00:00:00 up to January 13 23:59:59". One day, silently.

Currency. Dashboard financial reports "always filter data to a single currency". Sigma report templates "return all currencies" by default. If you sell in more than one, a Sigma total will be larger than the Dashboard total and both are correct. Add a where clause on currency, or group by it as Query 5 does.

Metadata. Financial reports let you include metadata. Sigma templates do not, and Stripe points you at its "Metadata to column" template to add it back. If your revenue breakdown depends on a metadata key, that template is the starting point rather than the report.

Where these numbers land in QuickBooks

The queries above produce three things your books need: gross revenue, the fees deducted from it, and the net that arrived in the bank. QuickBooks wants all three separately, which is the entire reason Stripe reconciliation is harder than pasting a bank feed.

Gross revenue is income. Fees are an expense, not a discount on revenue. The gap between them lives in a clearing or holding account until the payout clears, at which point the bank line and the holding account cancel out. Query 1 tells you what any single payout contained. Query 5 gives you the daily totals to post. Query 2 splits the fee expense if you want it split.

The sequencing is what most people get wrong rather than the accounts. A payout arriving on the 3rd can contain charges from the 29th, 30th and 31st of the month before. If you post the deposit when the bank shows it and the revenue when Stripe earned it, your holding account is supposed to carry a balance across the period end, and that balance is a real number you can prove: it is the sum of net on everything not yet swept into a payout. If it is not provable, something was posted on the wrong side of the cutoff.

A clean test of the whole arrangement is that the holding account returns to roughly zero once the last payout of the period clears, and that its balance at any moment equals Stripe's own pending balance. If the two drift apart and stay apart, the cause is nearly always a transaction type nobody mapped, which Query 5 will have already shown you.

If you are assembling that sequence for the first time, our step-by-step Stripe and QuickBooks month end close checklist walks the whole close in order, and these queries slot into steps one through four of it.

Frequently asked questions

Do I have to pay for Stripe Sigma to try these queries?

Not to try them. Stripe says Sigma "is free to use in sandboxes, with no usage limits", so you can run unlimited queries against test data at no cost. Running them against your live account data is a paid Sigma subscription, which you can cancel from your Sigma settings and keep using until the end of the billing cycle.

Why start from balance_transactions instead of charges?

Because charges are sales and balance transactions are money. Stripe recommends the balance_transactions table "as a starting point for accounting purposes" because it is a ledger-style record of every type of movement into and out of your balance, including refunds, adjustments, transfers and payouts that the charges table never sees.

Why does my payout query return no rows?

Almost always because the account is on manual payouts. Stripe is explicit that payout reconciliation works with automatic payouts, and that a manual payout amount "is arbitrary" and cannot be reconciled to specific balance transactions. Check the payout setting before assuming the join is wrong.

What is a partial_capture refund?

It is the uncaptured part of an authorization, recorded as a refund. Stripe's example: authorize 10 USD, capture 7, and Stripe creates a charge for 10 and a refund with reason partial_capture for 3. No customer money came back, so filtering these out is what keeps your refund rate and gross sales honest.

Can a Sigma query change my Stripe data?

No. Stripe states that "the available data within Sigma is read-only" and that queries "can't modify existing data or create new transactions". A query can return a wrong answer, but it cannot refund a customer, alter a payout, or delete a record.

The point

Five queries cover most of what a Stripe month end actually asks. They are small, they are read-only, and once saved they run in seconds instead of the twenty minutes the spreadsheet version takes.

What they will not do is post anything. Every number above still has to reach QuickBooks as gross revenue, fee expense, and a payout that clears a holding account, in that shape, every month, without anyone retyping it.

Acodei connects Stripe to QuickBooks Online. Start a free trial.