Automate Payment Reconciliation
The bank deposit says $4,617.38. Stripe says you sold $4,812.00 this week. Your books say something else again, because someone recorded each sale as it happened and nobody has yet recorded what the bank actually received. You are a bookkeeper, it is the last day of the month, and you are staring at a bank feed with one line where the sales report has forty.
That gap is not an error. It is the normal shape of card payments. Reconciliation is the work of proving, line by line, that every dollar the business earned either arrived in the bank or has a known reason for not having arrived yet. Done by hand it is slow and dull, and the dullness is what makes it dangerous, because tired people approve things that nearly match.
This article walks through how to automate that proof for a small business that takes card payments through Stripe and receives payouts into a bank account. The same ideas apply to other processors, though the field names change.
Why one deposit never equals the sales report
Four things sit between a customer's card and your bank balance, and each one creates a difference you will need to explain.
Fees are taken before you are paid. The processor keeps its percentage and fixed charge on each transaction and sends you the remainder. The sales total and the deposit can never match unless you add the fees back.
Payouts are batches. Stripe groups many charges, refunds and adjustments into one payout. The bank sees a single credit. Your sales system sees many separate events.
Time moves differently in each system. A sale made on the 30th may be paid out on the 2nd or the 3rd, depending on your payout schedule and on bank holidays. For a business that closes its books on the last day of the month, that sale belongs to one month in the sales ledger and another in the bank.
Money flows in both directions. Refunds, disputes and dispute fees reduce a payout. A payout can even be negative in a bad week, in which case the processor may debit your bank account instead.
Once you accept that the deposit is a derived number, the automation target becomes clear. You are not trying to match a deposit to a sale. You are trying to match a deposit to a payout, and a payout to the set of transactions inside it.
The three layers to match
Think of reconciliation as three stacked comparisons rather than one big one.
| Layer | Left side | Right side | What a break means |
|---|---|---|---|
| 1. Sale to order | Order or invoice in your system | Stripe charge | A sale was never charged, or a charge has no order |
| 2. Charge to payout | Stripe charges, refunds, fees | Stripe payout total | A balance transaction is missing or double counted |
| 3. Payout to bank | Stripe payout amount and arrival date | Bank feed deposit | The money has not landed, or landed in the wrong account |
Most manual work collapses all three into one hunt, which is why it feels endless. Separating them lets each layer be checked by a simple rule, and lets you see immediately which layer has the problem.
Use the balance transaction as your ledger
Stripe records every movement of money as a balance transaction. Each one has a type (charge, refund, fee, adjustment, payout and so on), a gross amount, a fee amount, a net amount, and a reference to the payout it was settled in. The sum of net amounts for all balance transactions in a payout equals the payout amount. That is an arithmetic fact, and it is the foundation of everything else.
So the first automated step is to pull balance transactions for each payout, not to pull charges. Charges tell you what customers paid. Balance transactions tell you what moved. If your reconciliation starts from charges, you will miss refunds processed in a later period, dispute holds and fee adjustments, and you will spend your time wondering why the totals drift.
Pull them by payout. For each payout, store the payout identifier, the arrival date, the amount, and the list of balance transactions with gross, fee and net.
Worked example (illustrative)
The numbers below are made up to show the mechanics. Your fee schedule will differ.
Suppose a small service business has these card events in one payout.
| Event | Gross | Fee | Net |
|---|---|---|---|
| Charge, invoice 1041 | $1,200.00 | $35.10 | $1,164.90 |
| Charge, invoice 1042 | $450.00 | $13.35 | $436.65 |
| Charge, invoice 1043 | $2,100.00 | $60.90 | $2,039.10 |
| Refund on invoice 1038 | ($300.00) | $0.00 | ($300.00) |
| Charge, invoice 1044 | $912.00 | $26.75 | $885.25 |
| Totals | $4,362.00 | $136.10 | $4,225.90 |
The payout that reaches the bank is $4,225.90. The sales report for those same invoices shows $4,662.00 in charges, because the $300 refund belongs to an older sale and the fees are invisible on the sales side. A person comparing $4,662 to $4,225.90 sees a $436.10 gap and starts digging. The automation sees something different. It sums nets inside the payout, gets $4,225.90, matches the bank deposit exactly, and then posts three journal lines for the difference between gross charges and the deposit. The refund goes against the original sale. The $136.10 goes to merchant fees expense. The deposit goes against the Stripe clearing account.
That last phrase deserves attention.
Post to a clearing account, not straight to the bank
A clearing account (sometimes called a processor receivable or undeposited funds) is a holding place on the balance sheet. When a customer pays, you debit the clearing account and credit the receivable. When the payout lands, you debit the bank and credit the clearing account. When reconciliation is correct, the clearing account balance equals the money Stripe still owes you, which is a number you can check against the Stripe dashboard on any given day.
This design absorbs timing differences without drama. A sale on the 30th sits in the clearing account until the payout lands on the 2nd. Nothing is wrong, and the balance in the clearing account on the 31st is the explanation. If you skip the clearing account and debit the bank at the time of sale, you will always be wrong by the amount in transit.
Matching rules and tolerances
Once payouts are tied to bank lines, you need rules for what counts as a match. Keep them boring and explicit.
- Exact match on amount and a date window. The payout amount equals the bank deposit amount, and the bank posting date is within a few business days of the payout arrival date. This should resolve the large majority of rows.
- Reference match. Some banks include the payout identifier or a descriptor in the memo. When present, use it in addition to the amount. It breaks ties between two payouts of the same size.
- Tolerance match for rounding. If you work with currency conversion, a tolerance of a cent or two may be justified. For a single-currency US business, a tolerance above zero usually hides a real problem. Start at zero and raise it only for a named reason.
- Group match. Occasionally a bank posts two payouts as one deposit, or splits one into two. Allow a rule that tests combinations of up to a small number of payouts against one bank line, and flag it for review rather than accepting it silently.
Write each rule so that it either produces one clear match or produces nothing. Rules that produce "probably" belong in the exception queue.
Idempotency, so a rerun does not double post
Your automation will run more than once on the same data. Someone will restart a job, a network call will time out and retry, or you will backfill a month. If each run creates new journal entries, you will double count.
The fix is an idempotency key, which is a stable identifier that lets the receiving system recognize "I have already done this." For reconciliation, a good key is the Stripe balance transaction identifier combined with the posting purpose. Before creating a journal line, check whether one with that key exists. If it does, skip it. Most accounting platforms either accept an external reference field or let you store one, and a unique constraint on that field is a cheap guarantee.
Stripe webhooks add a second reason for care. Stripe can deliver the same event more than once, so the handler should verify the webhook signature (a cryptographic check that the message really came from Stripe and was not altered) and then deduplicate on the event identifier.
Build an exception queue instead of chasing every break
No matching logic will be perfect, and pretending otherwise produces silent errors. What you want is a short, honest list of things that need a human, each with enough context to decide in under a minute.
Common exception types and what to do with each.
| Exception | Typical cause | Suggested handling |
|---|---|---|
| Payout with no bank line | Not yet arrived, or paid to a different account | Wait until the expected date plus a buffer, then escalate |
| Bank line with no payout | Manual transfer, loan, owner deposit, or another processor | Classify by hand, and create a rule if it repeats |
| Charge with no order | Payment link used outside the invoicing system | Create or attach the order, or refund |
| Order paid with no charge | Cash, check or other method recorded as card | Correct the payment method |
| Refund in a later period | Customer returned an old purchase | Post against the original sale, in the current period |
| Dispute or chargeback | Cardholder challenged the charge | Hold amount, track the response deadline, post fee |
| Negative payout | Refunds and disputes exceeded sales | Expect a bank debit, match it to the payout |
Give each exception an age. An item that has been open for seven days is a different kind of problem from one opened this morning. A simple dashboard with counts by type and age tells you whether the books are healthy without reading every row.
Timing differences and the month end
For month end, you need two numbers that people often confuse. One is the clearing account balance in your books. The other is the amount Stripe reports as pending or in transit for that date. They should agree. If they do not, one of the three layers has a break, and the table above tells you which layer to inspect first.
Payout schedules matter too. If you use automatic daily payouts, the in-transit amount is small. If you use weekly or monthly payouts, it can be large, and the clearing account will carry a meaningful balance through the close. Neither is wrong, but you should know which one you have before a lender or an accountant asks.
What a small Puerto Rico business should add
Many island businesses run more than one payment path, such as card through Stripe, ATH Móvil, cash at a counter and checks. Each path has its own timing and its own record. A reconciliation that only handles Stripe will make the other paths look like exceptions every day, so decide early whether they get their own clearing accounts and their own matching rules.
Connectivity and power are worth a thought as well. If a storm interrupts your internet for several days, payouts still arrive and webhooks still fire into a void. Design the process so it can catch up. Pulling payouts by date range on a schedule, in addition to reacting to webhooks, means a missed event does not become a permanent gap. Idempotency, described above, makes that catch-up safe.
If you collect sales tax (IVU in Puerto Rico), keep it out of revenue from the first journal line. A payout is a mix of revenue, tax collected, fees and refunds, and the clearing account approach is much cleaner when each of those has its own line from the start. Your accountant will tell you the specific treatment for your situation.
A Monday morning plan
You do not need a project to start. Here is a sequence that fits in a normal week.
- Pick one closed month and export the Stripe payouts and the matching bank deposits. Match them by hand once and write down every rule you applied.
- Confirm that net amounts of balance transactions add up to each payout. If they do not, find out why before automating anything.
- Create the clearing account if you do not have one, and decide where fees, refunds and tax post.
- Turn your written rules into a script or a configured tool, with an idempotency key on every posted line.
- Run it against the closed month and compare with your manual result. Differences are either bugs or rules you forgot.
- Add the exception queue with ages, and review it on a fixed day each week.
- Only then switch it on for the current month.
What to ask a vendor
If you are considering a reconciliation product or an accounting app connector, ask a few pointed questions.
- Does it work from balance transactions and payouts, or only from charges?
- Does it post fees, refunds and disputes as separate lines?
- Can it handle a payout that bundles several days, and a negative payout?
- How does it avoid double posting after a retry?
- Where do unmatched items go, and who sees them?
- Can you export every matching decision, along with the rule that made it, for your accountant?
A vendor who answers these clearly has probably done the work. A vendor who says "it just matches everything" has probably not met a dispute yet.
Where this goes wrong
Three failure modes show up repeatedly. The first is matching on amount alone, which works until two payouts have the same total. The second is hiding differences in a catch-all "miscellaneous" account, which keeps the reconciliation green while the books quietly drift. The third is automating before the rules are written down, so that nobody can explain why a match happened.
Reconciliation is one of the best early candidates for automation because the rules are arithmetic and the cost of a mistake is visible. It is also a place where a small, well-kept exception queue beats a clever matching engine.
If you would like a second pair of eyes on your payout and bank feed setup, I am glad to talk it through. Ozcorp InfraOps builds and runs this kind of plumbing for small teams, and InfraOps Base starts at $500 per month with vendor and license charges billed separately. Whether or not we work together, write your rules down first. It is the most useful hour you will spend.
Discuss your infrastructure requirements
Review the current service offer or schedule a conversation about the scope you need.
Stay in touch
Follow Ozcorp
Leave your details for occasional notes about new articles and Ozcorp updates.