Stripe marketplace payout files do not behave like a normal Stripe payout export because the cash movement belongs to the platform while the activity belongs to multiple connected accounts. One bank deposit can be tied to dozens of sellers, fees, refunds, reversals, transfers, and timing differences.
That is where the account ledger spreadsheet starts to drift.
The payout file says money moved out of Stripe. The ledger says which seller or internal account should receive credit. Those two files answer different questions, so a row-by-row comparison will produce false mismatches even when both files are correct.
The reconciliation has to prove three things:
- Which connected account created each payout movement
- Which ledger account owns that movement internally
- Which differences are expected because of fees, refunds, reserves, or timing
Matching Stripe marketplace payout files against account ledger spreadsheets is not a search for one shared ID. It is a layered match from payout to connected account to ledger account, with exceptions separated before the final report is trusted.
Start by Separating the Payout File From the Ledger File
A Stripe marketplace payout file usually contains movement data. It tells you what Stripe paid out, reversed, adjusted, or held. An internal account ledger usually contains ownership data. It tells you which seller, vendor, sub-account, wallet, or internal balance should receive the money.
Those files overlap, but they are not substitutes for each other.
| Stripe payout file field | Internal ledger field | What it proves |
|---|---|---|
payout_id | Payout batch reference | Which payout batch the row belongs to |
connected_account_id | Seller account ID / vendor ID | Which account owns the activity |
balance_transaction_id | Transaction reference | Which Stripe movement created the row |
amount | Gross or payable amount | The value before or after deductions |
fee | Platform fee / processor fee | The deduction applied to the movement |
net | Net settlement | The cash effect after fees |
available_on | Expected settlement date | When cash became available |
type | Ledger transaction type | Whether the row is a charge, refund, transfer, fee, adjustment, or reserve |
The first mistake is treating amount in both files as the same kind of amount. It might not be.
In the Stripe file, amount may describe the gross charge, the transfer to a connected account, a negative refund, or a payout movement. In the ledger, the amount may describe seller payable, platform revenue, net cash, or internal balance change. The numbers can be related without being equal.
Before matching anything, label the amount basis in both files: gross charge, platform fee, processor fee, connected account transfer, payout net, refund, reversal, reserve, or hold. If the amount basis is unclear, the match will look wrong later. You will compare a seller payable amount to a net payout amount and call the difference unexplained, even though the difference is the platform fee or processor fee.
Build the Match Around the Connected Account
The connected account is the control point. The bank line can confirm that Stripe paid money out, but it cannot prove which sellers were included in that payout or whether each seller's ledger balance is correct.
Use this order of matching evidence:
| Match layer | Stripe side | Ledger side | Purpose |
|---|---|---|---|
| Payout batch | payout_id or payout date | Batch ID or settlement run | Groups rows into the same cash movement |
| Connected account | connected_account_id | Seller/vendor/account ID | Assigns each row to the right owner |
| Transaction reference | balance_transaction_id, charge ID, transfer ID | Payment, order, or ledger reference | Proves row-level identity |
| Amount basis | Gross, fee, net, transfer | Payable, fee, revenue, cash | Prevents wrong amount comparisons |
| Date window | created, available_on, payout date | Transaction date, ledger date, settlement date | Separates timing differences from missing rows |
Do not start with the payout amount. It is too aggregated.
Start with the connected account. Then group the Stripe rows for that account. Then compare those rows to the account ledger. After each account ties out, roll the results back up to the payout batch.
That order keeps the reconciliation readable. If the platform payout is short by 3,842.18, you want to know that connected account acct_72K has two missing refund rows, connected account acct_91P has a reserve hold, and the remaining payout batch is clean.
For a standard payout-to-bank workflow, the adjacent problem is matching a Stripe payout CSV to your bank statement. Marketplace payout work adds another layer because the payout has to tie back to account ownership, not only to cash received.
Normalize the Two Files Without Rewriting the Source
Do not edit the source exports. Make working columns beside them.
The working columns should make the two files comparable while preserving the raw values that came from Stripe and from the internal ledger.
| Working column | Stripe source | Ledger source | Why it matters |
|---|---|---|---|
normalized_account_id | connected_account_id | Seller ID, vendor ID, wallet ID | Aligns account naming |
normalized_reference | Charge, transfer, or balance transaction ID | Order ID, payment ref, ledger ref | Creates row-level match candidates |
signed_amount | Amount, fee, net with sign preserved | Debit or credit amount with sign aligned | Prevents debit-credit reversals |
amount_basis | Type and reporting category | Ledger account or transaction type | Stops gross-to-net confusion |
settlement_window | Created, available, payout date | Posting or settlement date | Controls timing differences |
currency | Currency column | Ledger currency | Blocks cross-currency false matches |
For normalized_reference, the core formula is:
=TRIM(SUBSTITUTE(UPPER(A2),CHAR(160)," "))Column A is the reference field — balance transaction ID, charge ID, transfer ID, or internal ledger reference. TRIM removes standard spaces; CHAR(160) covers the non-breaking space some systems export. If the ledger stores only a fragment of the Stripe reference, extract with MID or RIGHT before normalizing.
The most important helper is amount_basis.
Without it, a lookup can return a match that is mathematically close but conceptually wrong. A 100.00 transfer to a seller and a 100.00 gross customer charge are not the same record. Exact values do not prove identity when the transaction types differ.
The same rule applies to signs. Stripe may export refunds, reversals, and fees as negative values. The ledger may store the same economic movement as a debit in one column or a positive number in an expense column. Normalize the sign for comparison, but keep the raw sign visible in the report.
Match Each Connected Account Before You Match the Whole Payout
The clean workflow is account first, payout second.
Suppose the Stripe payout file contains this:
| Payout ID | Connected account | Type | Reference | Gross | Fee | Net |
|---|---|---|---|---|---|---|
| po_8841 | acct_A | Transfer | tr_1001 | 500.00 | -20.00 | 480.00 |
| po_8841 | acct_A | Refund | re_1008 | -75.00 | 0.00 | -75.00 |
| po_8841 | acct_B | Transfer | tr_1002 | 900.00 | -36.00 | 864.00 |
| po_8841 | acct_C | Reserve hold | adj_4421 | -120.00 | 0.00 | -120.00 |
The internal ledger might show this:
| Ledger account | Reference | Account movement | Platform fee | Settlement status |
|---|---|---|---|---|
| Seller A | tr_1001 | 480.00 | 20.00 | Settled |
| Seller A | re_1008 | -75.00 | 0.00 | Refunded |
| Seller B | tr_1002 | 864.00 | 36.00 | Settled |
| Seller C | adj_4421 | 0.00 | 0.00 | Held |
If you compare the payout total first, the reserve hold looks like a missing payment. If you compare each connected account first, Seller C is not missing. The ledger has classified the row as held instead of settled.
That distinction changes the final answer.
The report should not say:
Seller C is short by 120.00.
It should say:
Seller C has a 120.00 reserve hold in Stripe that is not payable in the current ledger settlement.
That is a usable reconciliation note. It tells the operator what happened and what to do next.
Use a Candidate Match, Then a Confirmed Match
Marketplace files often do not give you one perfect key across every file. The same activity may appear as a charge ID in one export, a transfer ID in another, and an order reference in the ledger.
Use candidate matching first.
| Candidate rule | When to use it | Risk |
|---|---|---|
| Exact reference match | Same charge, transfer, or balance transaction ID appears in both files | Low |
| Connected account + reference fragment | Ledger stores only part of the Stripe reference | Medium |
| Connected account + amount + date window | Reference is missing but amount and timing align | Medium |
| Connected account + order ID + amount | Ledger tracks the order instead of Stripe movement | Medium |
| Amount-only match | No reliable reference exists | High |
Candidate matches are not final. They are a shortlist.
A confirmed match should pass these checks:
- The connected account agrees.
- The transaction type or amount basis agrees.
- The signed amount agrees after fee treatment is understood.
- The date sits inside the expected settlement window.
- The currency agrees.
- The row has not already been matched to another ledger entry.
This last check matters. Marketplace exports can contain repeated values. If the matching process allows one Stripe row to match two ledger rows, the reconciliation will balance on paper while hiding a duplicate.
Separate Fees, Refunds, Holds, and Timing Differences
The exception list should not be one bucket called "unmatched."
That bucket is too vague to close. It mixes real errors with expected marketplace behavior.
Use exception categories that describe the actual cause:
| Exception category | Meaning | Action |
|---|---|---|
| Missing in ledger | Stripe row exists but no ledger row exists | Add or investigate the ledger entry |
| Missing in Stripe file | Ledger row exists but no Stripe row exists | Check date filters, export scope, or manual entry |
| Account mismatch | Reference exists but connected account differs | Review seller mapping |
| Amount basis mismatch | Same activity, different amount type compared | Reclassify gross, fee, net, or payable amount |
| Fee difference | Platform or processor fee does not agree | Check fee posting rules |
| Refund or reversal timing | Negative movement relates to an earlier transaction | Link to original activity |
| Reserve or hold | Stripe delayed payout rather than paying seller | Carry forward to next settlement |
| Date difference | Same row, different posting or payout date | Mark as timing, not missing |
| Duplicate ledger entry | One Stripe row matches multiple ledger rows | Remove or reverse the duplicate |
Refunds and reversals deserve their own treatment. A refund might belong to a charge from a prior period, but appear in the current payout. If the ledger only contains current-period orders, the refund will look unmatched. The answer is not to force the refund into the current sales list. The answer is to identify the original charge, record the refund against the right account, and show that the current payout was reduced by that refund.
Subscriptions add a related timing problem when invoices, payments, and payout settlement fall into different periods. If recurring invoices are part of the marketplace flow, the same date discipline used for Stripe subscription invoice reconciliation applies here: invoice date, payment date, available date, and payout date are separate fields.
Tie Out the Payout Batch Only After Account-Level Matching
Once each connected account is matched, roll the results into a payout summary.
The summary should answer four questions:
- Does the Stripe payout total agree to the bank deposit or cash movement?
- Does each connected account's Stripe activity agree to the internal ledger?
- Are fees, refunds, holds, and timing differences separated from true errors?
- Is the remaining unexplained difference zero?
A useful payout summary looks like this:
| Category | Count | Amount |
|---|---|---|
| Matched connected account transfers | 82 | 48,210.00 |
| Matched platform fees | 82 | -1,928.40 |
| Matched refunds and reversals | 9 | -1,144.20 |
| Reserve holds carried forward | 3 | -640.00 |
| Missing ledger entries | 2 | -318.75 |
| Duplicate ledger entries | 1 | 120.00 |
| Unexplained difference after review | 0 | 0.00 |
The exact labels depend on your marketplace, but the structure should not change. Matched activity, expected differences, and true exceptions belong in separate lines.
The final report should also include an account-level exceptions table:
| Connected account | Issue | Amount | Evidence | Action |
|---|---|---|---|---|
| acct_A | Missing ledger refund | -75.00 | Stripe refund re_1008 | Record refund against Seller A |
| acct_C | Reserve hold | -120.00 | Stripe adjustment adj_4421 | Carry forward until released |
| acct_D | Duplicate ledger transfer | 120.00 | Two ledger rows match tr_4410 | Reverse duplicate |
That table is the part the operator can use. It points to rows, accounts, evidence, and actions. A highlighted spreadsheet without this structure still leaves the question open.
What a Correct Marketplace Reconciliation Proves
A finished marketplace payout reconciliation does not prove that every number in every file is identical. It proves that every difference has a correct label.
The platform payout may differ from gross marketplace sales because seller transfers, fees, refunds, disputes, reserves, and payout timing all sit between the sale and the cash movement. The connected account ledger may differ from the payout file because some rows are not payable yet, belong to prior-period transactions, or were posted under a different reference.
The correct output should say:
- This payout batch ties to cash.
- These connected accounts matched cleanly.
- These rows are expected timing, reserve, refund, or fee differences.
- These rows require ledger correction.
- The remaining unexplained difference is zero, or it is isolated to specific rows.
That is the standard to use before closing the reconciliation.
Checklist Before You Close the File
Before the payout report is handed off, confirm the batch is identified, connected account IDs are mapped to ledger accounts, amount basis is labeled in both files, gross and net rows are not mixed, refunds and holds are separated, duplicate matches are removed, and true missing ledger rows are listed with references.
The key is the order. Account-level matching comes before payout-level comfort. If the payout total agrees but connected account balances do not, the reconciliation is not finished. If every connected account ties out but the payout batch does not agree to cash, the final cash movement still needs review.
A marketplace payout file carries too much information for one lookup formula. Treat it as a controlled reconciliation: identify the batch, map the connected accounts, match the rows, classify the expected differences, and then roll the result into a report that explains the payout instead of hiding it.
