How to match Stripe payments to accounting invoices
By the Analistable team · Updated · 5 min read
Export open and paid invoices from QuickBooks or Xero, and Stripe payments with their description or metadata. Match on the invoice number where it's included in the payment; otherwise match on customer + amount. Then sum payments per invoice to catch partial and over payments, and list invoices with no payment as your receivables to chase.
Part of our guide: How to reconcile data in Excel
Get the invoice number into Stripe
Matching is easy when every Stripe payment carries the invoice number — in the description, statement descriptor or a metadata field. If your billing integration doesn't add it, fix that first; every other method is a fallback.
Match and sum
Paid =SUMIFS(Stripe[Amount], Stripe[Invoice], [@Invoice])
Payments =COUNTIFS(Stripe[Invoice], [@Invoice])
Status =IF([@Paid]=0, "Unpaid", IF([@Paid]<[@Total], "Part paid", IF([@Paid]>[@Total], "Overpaid", "Paid")))| Invoice | Customer | Total | Paid | Payments | Status |
|---|---|---|---|---|---|
| INV-301 | Bakery Lune | 480 | 480 | 1 | Paid |
| INV-302 | Hart & Co | 1200 | 600 | 1 | Part paid |
| INV-303 | Nordic Supply | 350 | 0 | 0 | Unpaid |
| INV-304 | Bakery Lune | 90 | 180 | 2 | Overpaid — charged twice? |
Payments without an invoice number
For the leftovers, match on customer email and amount within a few days of the invoice due date. Then list Stripe payments that matched nothing: =COUNTIFS(Invoices[Invoice], [@Invoice])=0. These are often payments for invoices raised in another system, or duplicate charges to refund.
Fees and payouts
Invoices are paid at the gross amount; Stripe fees come off later in the payout. Record the payment against the invoice at gross and the fee as an expense, then reconcile the payout separately — see reconciling orders with Stripe payouts.
To chase what's left, sort the Unpaid invoices by due date — see finding unpaid invoices older than 30 days.
Monthly routine
- Export invoices issued and Stripe payments received for the month.
- Match on invoice number, then customer and amount for the remainder.
- Review part-paid and overpaid invoices with the customer.
- Record unmatched payments as unapplied cash until they're identified.
Getting the exports
- Accounting side: an invoice list or aged receivables report with invoice number, customer, issue date, due date, total and amount outstanding. Both QuickBooks and Xero can export reports to Excel or CSV; report names differ between the two and change over time.
- Stripe side: payments (or balance transactions) for the period with amount, customer email, created date, description and metadata. If your invoices are created in Stripe Billing, the Stripe invoice ID is usually on the payment as well.
Pull the invoice number out of the description with TEXTAFTER or REGEXEXTRACT if it isn't in its own field, exactly as you would an order number. Make sure both sides use the same format: “INV-0302” and “INV-302” won't match.
Second example: one payment for several invoices
Hart & Co pays £1,050 in one card payment with no invoice number. They have two open invoices:
| Invoice | Due date | Outstanding | Allocated | Left after allocation |
|---|---|---|---|---|
| INV-310 | 15 Aug | 700 | 700 | 0 |
| INV-311 | 15 Sep | 350 | 350 | 0 |
| Total | 1050 | 1050 | 0 |
The two outstanding balances add up to exactly £1,050, which is strong evidence the payment covers both. Where the sum isn't exact, allocate oldest first and leave the remainder on the newest invoice, or ask the customer. A running allocation formula, with invoices sorted by customer and due date:
Allocated =MAX(0, MIN([@Outstanding], [@[Customer payment]] - SUMIFS([Outstanding], [Customer], [@Customer], [Due date], "<"&[@[Due date]])))It subtracts the outstanding balances of the customer's older invoices from the payment, so each invoice gets what is left, never less than zero. Two invoices with the same due date need a tie-breaker, such as the invoice number, added to the criteria. For thousands of invoices, a running total in Power Query or SQL is easier to audit.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Paid amount slightly lower than the invoice | Fee deducted before export, or a net amount column used | Use the gross amount field |
| Invoice paid twice | Customer paid by card and by bank transfer | Refund one, or hold it as credit on the account |
| Payment in a different currency | Invoice in EUR, Stripe balance in GBP | Compare in the invoice currency |
| Payment marked unmatched but invoice shows paid | Payment recorded manually in the accounting software | Check the invoice's payment history before chasing |
| Refunded payment still counts as paid | Refund rows excluded from SUMIFS | Include refunds with a negative amount |
Checking the result
- Total paid across all matched invoices plus unmatched payments equals total Stripe payments for the period.
- Invoices outstanding at the end of the month agree with the accounting software's aged receivables report.
- Every unmatched payment has an owner and a note.
If the same customers also exist in your CRM, matching deals to invoices closes the loop from sale to cash — see matching CRM deals to invoices.
Which method to use
- Accounting software with a Stripe integration: let it match payments that carry the invoice number, and use the spreadsheet only for the exceptions it can't match.
- A few dozen invoices a month without an integration: the SUMIFS sheet above, rebuilt from fresh exports each month.
- Hundreds of invoices or several Stripe accounts: stack the exports in Power Query and merge on invoice number, then on customer and amount, as two steps in one refreshable query.
Common mistakes are matching on customer name instead of a stable customer ID or email, comparing a net payout amount with a gross invoice, and treating a refund as a new payment. Each one produces part-paid or overpaid statuses that aren't real, so check those first when the list of exceptions looks too long. To see payments and invoices side by side for a single customer, a quick way is the compare spreadsheets tool on two filtered exports.
Part payments and credit notes
A credit note reduces what the customer owes, so include it in the invoice's outstanding amount before matching, not in Stripe. If INV-302 for £1,200 later received a £200 credit note, the outstanding balance is £1,000; the £600 already paid leaves £400 to collect, not £600. Export the outstanding amount column from the accounting system rather than recalculating it, so payments recorded there by other means are included.
Doing it in Google Sheets
Import both exports into one spreadsheet on separate tabs. SUMIFS and COUNTIFS work with ranges exactly as above, and REGEXEXTRACT(C2, "INV-\d+") pulls an invoice number out of a free-text description. Sort the result by Status so the Unpaid, Part paid and Overpaid rows sit together for review.
Frequently asked questions
- How do I match Stripe payments to invoices?
- Match on the invoice number in the payment's description or metadata, then sum payments per invoice to find partial and duplicate payments.
- What if a customer pays several invoices in one payment?
- Split the payment across invoices in your accounting system; in the spreadsheet, match it to the invoices whose totals add up to the payment.
- Should invoices be matched to the Stripe payout?
- No — match invoices to individual payments, then reconcile payouts (net of fees) with the bank separately.
- Can Xero or QuickBooks match Stripe payments automatically?
- Both offer Stripe integrations and bank-feed rules that suggest matches. A spreadsheet check is still useful for the payments those rules miss and for reviewing part payments.
- How do I handle refunds against paid invoices?
- Record the refund against the original invoice (or as a credit note) so the invoice's balance reflects it, then include refunds when summing payments.