A Stripe reconciliation spreadsheet template, and where it breaks

A spreadsheet is a fine way to reconcile a Stripe account with a few hundred transactions a month. This is the template, laid out so you can build it in an hour. The last section is honest about where it stops working.

The exports to pull

All three are under Reports in the Stripe dashboard. Set the date range to the month and the timezone to your own.

1. Balance change from activity (Balance section): one row per balance transaction. Columns you need: balance_transaction_id, created, available_on, type, gross, fee, net, currency, description, and the source id (charge, refund, dispute, payout).
2. Payout reconciliation (Balance section): the same rows with automatic_payout_id and automatic_payout_effective_at. This is what proves each payout's composition.
3. Balance summary: starting balance, activity, payouts, ending balance for the month.

Then the bank statement for the month as CSV, filtered to lines containing STRIPE.

Sheet 1: Activity

Paste the Balance change from activity export. Add these columns:

Sheet 2: Payouts

One row per payout in the month. Columns: payout id, arrival date, amount, sum of net from Sheet 1 for that payout id, difference, bank line matched, bank amount, bank difference.

Match bank lines by amount and date within three business days. A bank line with no payout is usually a manual payout or last month's in-transit payout; a payout with no bank line is in transit or failed.

Sheet 3: Tie-out

Three checks, each a single cell that should read zero:

1. Activity check. For each payout, sum of net equals the payout amount. Sum of the differences column on Sheet 2 is zero.
2. Balance check. Starting balance from the Balance summary, plus sum of net for all non-payout rows, less sum of payouts, less ending balance, is zero.
3. Bank check. Sum of payouts less sum of matched bank amounts, less payouts in transit at month end, plus payouts in transit at the previous month end, is zero.

Sheet 4: Journal entries

A pivot of Sheet 1 by Account (and Product if used), with gross for revenue lines and fee for fee lines, gives the month's summarised entries. The debits and credits must sum to zero before you type them into QuickBooks Online. Record payouts as transfers from the clearing account to the bank, matched to the bank feed.

Where the spreadsheet breaks

• Manual payouts. Stripe's exports attach their transactions to the next automatic payout. The payout id column is wrong for them and the activity check fails until you reassemble the group by available_on date and amount.
• Refunds of split invoices. An invoice with two products, tax and a discount, refunded in part, needs the refund allocated in proportion across all of them. The export gives one refund row.
• Cross-month disputes. A dispute opened in October on a September charge, won in November, touches three months. Each month's sheet has to know about the others.
• Re-pulling. Pull the export twice and paste twice and every number doubles. Nothing in the sheet keys rows by their Stripe id unless you build it.
• Volume. At a few thousand rows a month the lookups slow and the month-end takes a day. At a few clients it takes a week.
• History. A fix in the September sheet does not update the balance check in October's.

These are the cases ClearDeposits was built for. It keys every row by its Stripe id, reassembles manual payouts, allocates refunds in proportion, carries disputes across months, runs the three checks continuously, and drafts the Sheet 4 entries for QuickBooks Online for you to review. The template above is the same method; the difference is that the checks run daily, on every row, without a paste.