How to combine bank and credit-card statements into one spending report

From the field · Household budget coach

Combine checking, savings, and two credit cards

Four CSV exports use three date formats and different debit conventions. A checking payment reaches the card two days later, and a returned purchase is refunded in the following month.

Columns used in the example

account_id · transaction_date · posted_date · description · amount · currency · source_file

Follow it with your own file

1. Map and make consistent each account separately, preserving raw signs and descriptions.
2. Create transfer candidates using account relationships, amount, and timing.
3. Review transfer pairs and keep unmatched movements visible.
4. Categorize accepted external transactions and apply one refund-period policy.

Check: Each account's accepted transactions must reconcile to its statement activity. Household spending must exclude paired transfers and retain every transaction exactly once.

What needs resolving first

One bank exports debits as positive values, another uses negatives, and credit-card payments appear as an outflow from checking and inflow to the card. Appending the files without role rules overstates spending.

Warning signs in the file

• The same card payment appears in two accounts.
• Debit and credit signs reverse across exports.
• Statement dates and transaction dates are mixed.

What changes the answer

• Create canonical account, transaction date, posted date, description, amount, currency, and source fields.
• Use one cash-flow convention and retain source debit/credit indicators.
• Match account-to-account movements with date and amount tolerances and review uncertain pairs.
• Validate each statement before creating household category and trend summaries.

Combine accounts only after each one balances

Account-level reconciliation isolates source and sign problems before aggregation. Once files are mixed, a compensating error can make the household total look correct while individual accounts are wrong.

Where this usually goes wrong

Transfers with fees or currency conversion may not match exactly. Preserve the gross movements and disclose the fee or exchange component instead of broadening tolerance until a match appears.

Build the result step by step

1. Map each export: Create canonical account, transaction date, posted date, description, amount, currency, and source fields.
2. Standardize sign: Use one cash-flow convention and retain source debit/credit indicators.
3. Pair transfers: Match account-to-account movements with date and amount tolerances and review uncertain pairs.
4. Reconcile accounts: Validate each statement before creating household category and trend summaries.

Choose the approach that matches the evidence and repetition

Doing these steps in Prepyr

Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Combine multiple bank and credit-card CSV statements without double counting. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.

Prepyr steps used here: Data reconciliation · Version history · Verified analysis · Report generation

Make the output easy to check

• Schemas mapped
• Signs standardized
• Accounts reconciled
• Transfers paired
• Refund policy documented

Questions readers usually ask next

How do I avoid double-counting credit-card payments?

Pair the checking outflow with the card payment inflow and classify both as a transfer when individual card purchases are included.

Which date should a spending report use?

Choose transaction or posted date consistently and disclose the policy, especially around month end and refunds.