Payment Gateway Reconciliation in CSV: Match Charges, Fees, Refunds, and Bank Deposits

Payment gateway reconciliation sounds simple until the exports arrive.

Your payment processor has one CSV for charges, another for balance activity, a payout report, and sometimes a separate dispute or refund export. Your bank has a deposit line that only shows the net payout. Your accounting system may have invoices or orders at the gross amount. The question is never just "did this transaction exist?" It is usually:

• Which customer charges funded this payout?
• Did fees, refunds, disputes, chargebacks, taxes, and adjustments explain the difference between gross sales and the bank deposit?
• Which transactions are unmatched and need follow-up?
• Can we export the matched and unmatched rows for month-end review?

This guide shows a practical CSV reconciliation workflow using Excel first, then explains where Excel breaks and how FireLookup helps finance and ops teams reconcile faster with exact, fuzzy, and grouped matching.

The real-world workflow

A typical payment gateway reconciliation compares at least two datasets:

1. Payment gateway export - Stripe, PayPal, Square, Adyen, Braintree, Authorize.net, or another processor CSV.
2. Bank/accounting export - bank deposits, ledger entries, invoices, orders, or ERP transactions.

The most common reconciliation is:

> Gateway transactions grouped to payout totals, then compared against bank deposits and ledger entries.

That means a single bank deposit may represent hundreds of charges minus fees, refunds, disputes, and adjustments. A simple VLOOKUP only works when there is one row on each side with the same identifier and amount. Payment gateways rarely stay that tidy.

Example payment gateway reconciliation table

Imagine these simplified exports.

Gateway payout activity CSV

| payout_id | available_date | type | reference | customer | gross | fee | net | currency |
|---|---:|---|---|---|---:|---:|---:|---|
| po_1042 | 2026-05-01 | charge | ch_8001 | Acme Co | 500.00 | -15.10 | 484.90 | USD |
| po_1042 | 2026-05-01 | charge | ch_8002 | Northwind LLC | 300.00 | -9.20 | 290.80 | USD |
| po_1042 | 2026-05-01 | refund | re_1130 | Acme Co | -100.00 | 0.00 | -100.00 | USD |
| po_1042 | 2026-05-01 | adjustment | adj_210 | Processor reserve | 0.00 | 0.00 | -25.00 | USD |
| po_1043 | 2026-05-02 | charge | ch_8003 | Bluebird Inc | 750.00 | -22.05 | 727.95 | USD |

Bank deposit CSV

| bank_date | description | deposit_amount | currency | bank_reference |
|---:|---|---:|---|---|
| 2026-05-02 | STRIPE TRANSFER PO1042 | 650.70 | USD | BAI-9911 |
| 2026-05-03 | STRIPE PAYOUT PO1043 | 727.95 | USD | BAI-9912 |
| 2026-05-03 | PAYPAL TRANSFER | 421.15 | USD | BAI-9913 |

For payout po_1042, the grouped net is:

| payout_id | charge gross | fees | refunds | adjustments | expected bank deposit |
|---|---:|---:|---:|---:|---:|
| po_1042 | 800.00 | -24.30 | -100.00 | -25.00 | 650.70 |

That matches the bank deposit. The challenge is proving it quickly, preserving the rows that make up the group, and isolating anything that does not match.

Step-by-step CSV reconciliation process

Step 1: Normalize both CSV files

Before matching, standardize the fields that will be compared.

Recommended cleanup:

• Convert all dates to the same timezone and date format.
• Convert amount fields to numbers, not text.
• Split gross, fee, refund, and net amounts into clear columns.
• Normalize payout IDs by removing spaces and inconsistent punctuation.
• Standardize currency codes.
• Add a source column such as gateway_export or bank_export.

Excel can handle this with Power Query or formulas such as TRIM, SUBSTITUTE, TEXT, and VALUE. The key is not to start matching until the data types are consistent.

Step 2: Decide the match logic

Payment gateway reconciliation usually needs more than one matching rule.

Use these rules in order:

1. Exact ID match - payout ID, transaction ID, invoice ID, order ID, or gateway reference.
2. Exact amount and date window - net payout equals bank deposit within a 1-3 day settlement window.
3. Grouped match - many gateway rows sum to one payout or bank deposit.
4. Fuzzy description match - STRIPE TRANSFER PO1042 should match po_1042 even if the bank description has extra text.
5. Exception review - anything unmatched becomes the review list, not something hidden in the spreadsheet.

Step 3: Reconcile gateway rows to payouts

If the processor provides a payout ID on every row, group by payout ID and sum the net amount.

Example grouped output:

| payout_id | row_count | gross_sum | fee_sum | refund_sum | adjustment_sum | net_sum |
|---|---:|---:|---:|---:|---:|---:|
| po_1042 | 4 | 800.00 | -24.30 | -100.00 | -25.00 | 650.70 |
| po_1043 | 1 | 750.00 | -22.05 | 0.00 | 0.00 | 727.95 |

This is where many spreadsheet reconciliations become fragile. If you only match individual rows, the bank deposit will never equal a single charge because the deposit is net of fees and refunds.

Step 4: Compare payout groups to bank deposits

Now match the grouped payout result against the bank CSV.

Recommended matching fields:

• Payout ID if present in the bank description.
• Net amount vs deposit amount.
• Available date vs bank date with a settlement window.
• Currency.
• Processor name if multiple gateways are used.

Result:

| payout_id | expected deposit | bank deposit | variance | status |
|---|---:|---:|---:|---|
| po_1042 | 650.70 | 650.70 | 0.00 | Matched |
| po_1043 | 727.95 | 727.95 | 0.00 | Matched |
| PayPal unknown | 421.15 | 421.15 | 0.00 | Needs source detail |

Step 5: Export matched and unmatched rows

A reconciliation is only useful if the exception list is clear.

At the end, create at least three outputs:

1. Matched payouts - payout groups with bank deposit matches.
2. Unmatched gateway rows or groups - payouts, refunds, disputes, fees, or adjustments with no bank/ledger match.
3. Unmatched bank deposits - deposits that do not tie back to gateway exports.

For audit readiness, keep the original source rows behind each grouped match. Reviewers need to see why a deposit matched, not only that it matched.

Where Excel breaks

Excel is useful for early analysis, but payment gateway reconciliation exposes its weak points quickly.

1. One-to-many and many-to-one matching gets messy

A payout deposit is often a group of charges, refunds, fees, and adjustments. XLOOKUP is built for row-to-row matching, not explaining a many-row settlement deposit.

2. Power Query is strong, but review workflows are manual

Power Query can group rows and merge tables. But once there are fuzzy descriptions, settlement windows, partial matches, multiple currencies, or exception review notes, the workflow can become difficult for non-technical users to maintain.

3. Fuzzy matches need judgment

Bank descriptions may say:

• STRIPE TRANSFER PO1042
• STRIPE PAYOUT PO_1042
• ST*PAYOUT 1042
• PAYMENT PROCESSOR DEP

A formula may miss these. A fuzzy matching tool can propose likely matches while still letting the user review the result.

4. Audit exports are easy to forget

Month-end reconciliation needs evidence. If the final workbook only shows formulas and hidden helper columns, it can be hard to prove which rows were matched and which rows remained unmatched at the time of review.

5. Spreadsheet files become risky as volume grows

Large processor exports can contain tens or hundreds of thousands of rows. A workbook full of formulas, helper tabs, and manual filters is easy to break and hard to hand off.

How FireLookup helps

FireLookup is built for finance and ops users who need to reconcile Excel and CSV files without turning every reconciliation into a spreadsheet engineering project.

For payment gateway reconciliation, FireLookup can help you:

• Upload gateway and bank/accounting CSV files.
• Match using exact keys such as payout ID, transaction ID, invoice ID, or order ID.
• Use fuzzy matching when descriptions or customer names are inconsistent.
• Handle grouped matching when many transactions roll up to one bank deposit.
• Review matched and unmatched results instead of hunting through filters.
• Export matched and unmatched files for audit-ready follow-up.

FireLookup does not need to replace Excel for every finance workflow. It fits the point where formulas, XLOOKUPs, Power Query merges, and manual filters become too brittle for recurring reconciliation.

Suggested FireLookup workflow

1. Export the payment gateway payout activity CSV.
2. Export the bank deposits or ledger CSV for the same period.
3. Upload both files to FireLookup.
4. Choose match fields such as payout ID, date, amount, currency, and description.
5. Enable grouped matching for payout-to-bank deposit scenarios.
6. Review exact matches first.
7. Review fuzzy matches where descriptions or references differ.
8. Export matched rows and unmatched exceptions.
9. Attach the exports to the month-end reconciliation package.

FAQ

What is payment gateway reconciliation?

Payment gateway reconciliation is the process of comparing processor exports, payout reports, bank deposits, and accounting records to confirm that charges, fees, refunds, disputes, and net deposits are recorded correctly.

Can I reconcile Stripe or PayPal payouts in Excel?

Yes, especially for small volumes. Excel formulas, PivotTables, and Power Query can group transactions and compare totals. The workflow becomes harder when payouts include many transactions, refunds, chargebacks, fuzzy descriptions, or recurring exception review.

Why does my bank deposit not match individual payment transactions?

Most bank deposits are net payouts. They combine many customer charges and subtract fees, refunds, disputes, reserves, or adjustments. You usually need grouped matching rather than one-to-one matching.

What files should I export from my payment processor?

Export payout activity or balance transaction CSVs with payout ID, transaction ID, type, gross amount, fee, net amount, currency, and available date. Also export bank deposits or ledger entries for the same period.

How should unmatched rows be handled?

Unmatched rows should be exported and reviewed. Common causes include timing differences, missing exports, refunds posted in a different period, duplicate deposits, chargebacks, reserves, or description mismatches.

How does FireLookup differ from a generic CSV diff tool?

A generic CSV diff tool compares rows for changes. FireLookup is focused on reconciliation: exact and fuzzy matching, grouped matching, matched/unmatched exports, and workflows for finance and ops users.