The Stripe export says transaction_id. The Xero CSV says Reference. They can point to the same payment, but Excel will not know that unless you build the match deliberately.
That is where the reconciliation usually starts to break.
A freelance bookkeeper rarely has one clean Stripe-to-Xero setup across every client. One client exports Stripe balance transactions. Another sends Xero account transactions. A third gives you a bank statement, a Stripe payout report, and a Xero manual journal export with different date ranges. The work is not hard because Stripe or Xero is mysterious. It is hard because the file structure changes from client to client.
The answer is not to force every client into the same master spreadsheet. The answer is to build a file-based workflow that can survive different headers, date fields, amount signs, and reference columns.
What the two files are actually showing
Stripe and Xero are not exporting the same view of the transaction.
Stripe is usually showing payment activity. Depending on the export, the file may include charges, refunds, fees, disputes, balance movements, payout IDs, and net amounts. Xero is usually showing accounting entries. The Xero CSV may contain bank transactions, invoice payments, journal lines, account codes, contact names, references, and reconciled status.
That means the two files can describe the same cash event from different angles.
| Stripe field | Xero field that may carry the match | What can go wrong |
|---|---|---|
transaction_id | Reference | Xero may store the Stripe ID inside a longer text field |
payout_id | Description or bank statement reference | The payout ID may not be recorded in Xero at all |
created | Date | Stripe timestamp may be UTC while Xero uses local posting date |
available_on | Date or bank date | The payout availability date can differ from the accounting date |
gross | Amount | Xero may record the net receipt instead of the gross sale |
fee | Fee expense line | The fee may be missing or posted in a monthly batch |
net | Bank receipt amount | Net can match the bank while gross does not match sales |
If you try to match the wrong pair of columns, good records look missing.
For example, a Stripe charge can have:
| Stripe field | Value |
|---|---|
| transaction_id | ch_3P9K2L |
| created | 2026-05-30 23:42:11 UTC |
| gross | 400.00 |
| fee | -12.20 |
| net | 387.80 |
| payout_id | po_1AB982 |
The Xero export might show:
| Xero field | Value |
|---|---|
| Date | 2026-05-31 |
| Reference | ch_3P9K2L |
| Description | Stripe payment - Customer A |
| Amount | 400.00 |
| Account | Sales |
Those two rows match by transaction ID and gross amount, but not by date and not by net amount. If you match on date plus amount, this payment fails. If you match on reference plus gross amount, it passes.
That choice is the reconciliation.
Decide which Stripe export you are reconciling
Do not start by opening both files and searching for the same total. First decide what the Stripe file represents.
A Stripe transaction export, payout reconciliation report, balance transaction export, and fees export all answer different questions.
| Stripe export type | Best use | What it should be matched against |
|---|---|---|
| Payments or charges export | Proving sales activity | Xero invoice payments or sales entries |
| Balance transactions export | Explaining gross, fee, and net movement | Xero sales, fees, refunds, and adjustments |
| Payout reconciliation report | Proving what reached the bank | Xero bank account CSV or bank statement |
| Fees report | Checking processor fees | Xero fee expense account |
| Refunds or disputes export | Finding reductions missing from books | Xero refund, credit note, or adjustment entries |
This step prevents the most common false mismatch: trying to make Stripe gross payments equal Xero bank receipts.
They are not supposed to equal each other.
Stripe gross payments are before fees, refunds, disputes, and payout timing. Xero bank receipts may show only the cash that arrived. If the client records Stripe activity net, the Xero bank line will be lower than the Stripe gross amount. That is not automatically wrong. It means you need to explain the bridge from gross to net.
For a deeper payout-level workflow, use the same logic as matching a Stripe payout CSV to a bank statement: start from the bank deposit, identify the payout, then unpack the transactions inside it.
Build the match key before running formulas
The match key is the field that proves two rows represent the same event.
For Stripe and Xero, the best key is usually the Stripe transaction ID if Xero captured it. That might appear in Xero as Reference, Invoice Number, Description, Payment Reference, or a manually entered note.
If Xero does not contain the Stripe transaction ID, use a composite key. A composite key combines fields that are weak alone but strong together.
Useful combinations include:
| Composite key | When it works |
|---|---|
| Amount + customer/contact + date window | Matching individual customer payments |
| Payout ID + net amount | Matching payout deposits |
| Invoice number + gross amount | Matching Stripe payments to Xero invoice receipts |
| Order reference + amount | Matching e-commerce payments posted into Xero |
| Date window + amount + description fragment | Matching bank-feed lines where no transaction ID exists |
The date window matters because Stripe and Xero dates often differ. A payment captured late at night in UTC can appear one day later in Xero. A payout can include payments from several days. A bank deposit can post after Stripe marks the payout available.
So do not use exact date matching unless you have already confirmed that both files use the same date basis.
For this workflow, create four helper fields before comparing:
| Helper field | Purpose |
|---|---|
| Normalized reference | Removes spaces, casing differences, and extra description text around the ID |
| Date window | Allows a one-to-three-day tolerance where timing differs |
| Signed amount | Makes debit and credit signs comparable |
| Amount basis | Labels the amount as gross, fee, net, refund, or adjustment |
For the Normalized reference field, the core formula is:
=TRIM(SUBSTITUTE(UPPER(A2),CHAR(160)," "))Column A is the reference field — the Stripe transaction ID, payout ID, or invoice number. TRIM removes standard spaces; CHAR(160) covers the non-breaking space Xero sometimes exports. If the Stripe ID appears inside a longer description like "Payment ch_3P9K2L for client," extract it with MID before normalizing. For Signed amount, use ABS(VALUE(B2)) where column B is the amount — VALUE converts text amounts, ABS removes the sign difference.
This is where VLOOKUP with TRIM often fails. TRIM handles spaces. It does not solve the harder problem: the two files may not store the same business event in the same column, on the same date, or at the same amount basis.
Separate gross, fees, refunds, and net before comparing totals
A Stripe-to-Xero reconciliation should not start with one total and one formula. It needs a bridge.
Use this structure:
| Reconciliation layer | Stripe source | Xero source | Expected result |
|---|---|---|---|
| Gross sales | Charges or payments | Sales or invoice receipts | Should agree after timing differences |
| Fees | Stripe fee column or fees export | Merchant fee expense account | Should agree or be listed as missing |
| Refunds | Refund rows | Refunds, credit notes, or adjustments | Should agree by reference or amount |
| Disputes | Dispute rows | Chargeback or adjustment entries | Should be recorded separately |
| Net payout | Payout report | Xero bank account or bank CSV | Should match cash deposited |
This structure keeps the reconciliation from collapsing into a single unexplained difference.
Suppose Stripe shows:
| Stripe category | Amount |
|---|---|
| Gross charges | 8,000.00 |
| Fees | -248.00 |
| Refunds | -300.00 |
| Net payout | 7,452.00 |
Xero shows:
| Xero category | Amount |
|---|---|
| Sales receipts | 8,000.00 |
| Stripe fees posted | -248.00 |
| Refunds posted | 0.00 |
| Bank receipt | 7,452.00 |
The bank receipt is correct. The sales are correct. The fee is correct. The missing item is the refund entry.
If you start with "Stripe does not match Xero," you have no answer. If you separate the layers, the answer is precise: one refund exists in Stripe and has not been posted in Xero.
That is the output a client can act on.
Handle the Xero CSV before it damages the match
Xero exports can change depending on the report, date range, account, and columns selected. That matters because a spreadsheet formula built for one export may silently point at the wrong column in the next export.
Before matching, check these Xero file issues:
| Check | Why it matters |
|---|---|
| Column headers | Reference, Description, and Invoice Number can carry different match values |
| Date format | Xero may export local dates while Stripe uses timestamped UTC fields |
| Amount signs | Receipts and payments may export as positive or negative depending on the report |
| Account filter | A bank account export will not contain all fee or refund postings |
| Reconciled status | Unreconciled Xero rows may be valid payments still waiting for bank matching |
| Manual edits | Client-added references can be inconsistent across months |
If the Xero file is a bank account export, match it against Stripe payouts or bank-facing net activity. If the Xero file is an invoice payments export, match it against Stripe charges or payment records. If the Xero file is a general ledger export, separate the account codes first so sales, fees, refunds, and bank receipts do not sit in the same comparison.
The file type decides the match logic.
This is also why a process that worked for last month's client can fail this month. The export may still be from Xero, but it is not the same Xero report. If the headers or included accounts changed, the old lookup is no longer safe.
For more detail on this specific export problem, read how Xero bank statement exports differ from raw bank CSVs.
Use a repeatable workflow for every client
For freelance bookkeepers, the practical question is not "Can I reconcile this one Stripe file against this one Xero CSV?"
The practical question is whether the process survives the next client.
Use the same sequence every time, even when the columns change:
- Identify the Stripe export type.
- Identify the Xero export type.
- Decide the amount basis: gross, fee, refund, dispute, or net.
- Choose the strongest match key available.
- Normalize references and amount signs.
- Allow a date window where payout timing or timezone differences apply.
- Match exact references first.
- Match composite keys second.
- Separate expected timing differences from real missing records.
- Produce an exceptions list with the reason each row failed.
The exceptions list is the part that matters most.
It should not say:
| Bad output | Why it fails |
|---|---|
| Unmatched | Gives no reason |
| Difference | Does not say whether the amount, date, or reference failed |
| Check manually | Pushes the work back to the bookkeeper |
It should say:
| Better output | What it tells you |
|---|---|
| Stripe refund missing in Xero | Post or investigate the refund |
| Xero receipt has no Stripe transaction ID | Check whether it was paid outside Stripe |
| Same reference, different amount | Review fee treatment or partial payment |
| Same amount, date outside tolerance | Check payout timing or posting date |
| Stripe payout matches bank, but fees missing | Post merchant fees separately |
That is the difference between a spreadsheet that highlights cells and a reconciliation report that closes the question.
What a correct reconciliation report should show
The finished report should prove three things.
First, the cash is explained. The Stripe payout or net activity should tie to the Xero bank account or bank-facing entry.
Second, the revenue is explained. Stripe gross charges should tie to Xero sales or invoice receipts after timing differences.
Third, the differences are classified. Fees, refunds, disputes, date shifts, missing Xero entries, and missing Stripe references should not sit in one bucket.
A useful report looks like this:
| Section | What it contains |
|---|---|
| Summary | Stripe total, Xero total, explained difference, unexplained difference |
| Matched rows | Records that agree by reference, amount, and acceptable date window |
| Date differences | Same transaction, acceptable timing gap |
| Amount differences | Same transaction, amount does not agree |
| Missing from Xero | Stripe records not found in the Xero CSV |
| Missing from Stripe | Xero records not found in the Stripe export |
| Fee/refund/dispute bridge | Expected reductions from gross to net |
| Client action list | Entries to post, correct, or confirm |
If the report cannot show why a difference exists, the reconciliation is not finished. The client does not need a colored spreadsheet. They need to know whether the books are wrong, Stripe timing is different, or a record is missing.
Where the process usually breaks
Most failed Stripe-to-Xero reconciliations break in one of five places.
The wrong Stripe total is compared to the wrong Xero total. Gross charges are compared to bank receipts, or payouts are compared to sales. The numbers differ because they represent different stages of the payment flow.
The match key is hidden inside a description field. Xero may include the Stripe ID, but not in a clean reference column. If the formula looks only at Reference, valid matches fail.
Dates are treated as exact when they are timing fields. Stripe transaction time, Stripe available date, payout date, bank date, and Xero posting date are not interchangeable.
Fees are posted net instead of separately. The bank amount may agree while the sales and fee accounts remain wrong.
The process depends on one client's file layout. Copying last month's formula into a new client workbook assumes the same headers, signs, and references. That assumption fails often.
The fix is not a better lookup formula. The fix is a workflow that names the file type, names the amount basis, and produces a classified exception report.
The clean way freelance bookkeepers reconcile Stripe data against Xero CSV exports is to stop treating the files as two versions of the same table. They are not. Stripe shows payment movement. Xero shows accounting records. The reconciliation works when you define the bridge between them, match on the right key, and explain each difference in a report the client can use.
