Merchant Settlement CSVs: Define Fields Finance Can Actually Reconcile

Header image: conceptual illustration.

A settlement CSV can be downloadable and still be unusable. If it contains only date, amount and status, finance may be unable to distinguish two batches, identify a refund's original payment or explain why net value differs from gross.

Define a report field contract before building or buying the export. The contract says what a row represents, which identifiers connect it to other records, how amounts are signed, and what changes between versions.

Decide the grain before the columns

Is each row a customer payment, a provider balance transaction, a settlement batch or a bank movement? Those are different grains. Combining them in one unlabelled row can duplicate totals or conceal many-to-one relationships.

For transaction-level settlement analysis, preserve the provider's balance or adjustment identity where available. For batch summaries, expose batch membership and the source of aggregated totals. Use separate report types when a single grain cannot answer both operational and bank-matching questions.

The reconciliation guide explains the records being compared. This article translates that requirement into a usable export specification.

Core identification fields

• Provider and merchant-account scope, without exposing credentials.
• Environment, so test records cannot be mistaken for live activity.
• Original order and attempt identifiers where applicable.
• Provider payment, refund or adjustment identifier.
• Settlement or payout identifier when the provider supplies one.
• Operation category and link to the original payment for adjustments.

Identifiers should be preserved as text. Spreadsheet software can remove leading zeros or convert long digit strings to scientific notation. A visually plausible value may then be a different reference. Tell consumers how to import the fields and test round trips through common spreadsheet tools.

Amount and date semantics

Expose currency alongside every amount. Document whether the export uses decimal major-unit values or integer minor units. Do not make finance infer units from the number of digits.

Define gross, fee, net and other adjustment signs. State whether net already includes a listed fee. Separate customer-facing currency from settlement currency where conversion occurred. Unknown fee values should be missing with an explanation, not silently zero.

Provide event time, availability date, settlement date and bank posting date only where those observations actually exist. Label missing values clearly. Include the report's time zone, interval, generated-at time and revision/version information.

Stripe's payout report schema is a primary example of explicitly defined currency, gross, fee, net and linked-resource fields. Its definitions apply to the selected Stripe report, not automatically to all merchant CSVs.

A field contract in a test export

Invented example: A fictional report contains one payment row for DEMO-P-1 and one refund row for DEMO-R-1. Both reference the same original order. The payment gross is BDT 1,000; the refund adjustment is BDT minus 200. A separately documented fee adjustment is BDT minus 20.

The batch summary shows an expected BDT 780 net movement. Finance can reproduce that total from signed rows and identify the linked operations. If the bank posting has not been observed, the bank-posted-at field remains empty rather than copying the settlement date into it.

The export includes a report identifier, schema version, interval and currency. A second export of the same period is labelled as a revision so the import job can replace or reconcile the earlier dataset according to an approved policy, rather than append it twice.

Make CSV delivery safe and predictable

Specify encoding, delimiter, decimal separator, quote handling and null representation. Escape cells that spreadsheet software might interpret as formulas when the value comes from untrusted text. Do not remove meaningful reference characters in the name of sanitization; retain the original in a safe representation.

Restrict report downloads to authorized users. Avoid exporting phone numbers, identity documents, credentials or full payment-card information where reconciliation only needs transaction identities. OWASP's logging guidance provides a useful principle of excluding sensitive data and handling untrusted content safely; a report is not a license to expose more data than the task requires.

Test the import as well as the export

A successful file download does not prove correct reconciliation. Test duplicate import, reordered columns, new optional columns, missing required fields and an unsupported schema version. Validate row counts and totals per currency before committing financial imports.

Use the payment metrics guide to make export failures and reconciliation exceptions visible without confusing them with payment declines.

Export acceptance checklist

• One row grain and an explicit schema version.
• Preserved text identifiers and adjustment links.
• Documented units, currencies, signs and date meanings.
• Totals reproducible from the included rows.
• Missing values distinguish unknown from zero.
• Reimports cannot duplicate financial effects.
• Authorized access and minimized sensitive fields.

Questions to settle with the report consumer

Is more detail always better?

No. Include the data needed to reconcile and investigate, with access controls. Unnecessary personal information increases risk without improving the match.

Can columns change without warning?

A versioned contract should identify breaking changes. Consumers must reject unsupported versions rather than silently misread financial values.