Bank statements

One spreadsheet for four bank statements: a mapping-first method

Combine four banks by mapping each layout to one standard, converting signs with simple formulas, then reconciling every account in Australian dollars.

Editorial illustration for One spreadsheet for four bank statements: a mapping-first method

The reason four statements will not combine is that no two banks agree on what a column is called or which way a debit points. Solve that once, on paper, with a mapping table, and the rest is mechanical. Decide your standard columns, write down how each account converts to them, and apply the recipe every month.

What does a mapping table look like?

List your four accounts down the side and the standard columns across the top: Date, Description, Amount, Account. In each cell write where that data lives in that account's export, and any conversion needed.

AccountDate fromDescription fromAmount rule
Everyday accountColumn AColumn BMoney out is column C, money in is D: use D minus C
SavingsColumn AColumn CColumn B is already signed
Credit cardColumn BColumn DColumn E shows purchases as positive: use minus E
Offset or second accountColumn AColumn BColumn C signed, but check sign direction on a known line

Once the table exists, anyone, including future you, can build the sheet from it. It also tells you quickly if a bank changes its layout, because the mapping no longer matches the file.

How do I convert each export without damaging it?

  1. Copy the downloaded file and work on the copy.
  2. Delete summary lines above the header and totals below the last row.
  3. Insert columns until your standard four are in the right order.
  4. Use formulas for the amount rather than retyping. For debit and credit columns: =D2-C2. For a card file where spending is positive: =-E2.
  5. Paste the formula results as values over themselves so later edits do not shift them.
  6. Fill the Account column with a fixed word.
  7. Verify the sign on a known purchase: a A$48.60 fuel stop should come out as -48.60.

What is the check that proves nothing went missing?

Reconcile each account before you merge. Take hypothetical figures: an everyday account opens April at A$1,950.00, receives A$3,400.00 and spends A$3,126.75. Opening plus movement is 1950 + 3400 - 3126.75 = 2,223.25, and the statement's closing balance should say the same. If it does not, find the difference first: a difference of exactly A$48.60 would suggest that fuel stop was left out.

How do I merge once every tab matches?

Paste the four cleaned tabs one under another into a sheet called Master, then sort by date. Check the row count. If the tabs have 64, 9, 41 and 22 rows, Master should have 64 + 9 + 41 + 22 = 136 rows. In Google Sheets, a stacking formula such as =SORT({Everyday!A2:D; Savings!A2:D; Card!A2:D; Second!A2:D}, 1, TRUE) does the stacking for you. In Excel, paste, or use Power Query's Append.

Why does my spending look too high?

Because of transfers and card repayments. Moving A$600 to savings is a minus in one tab and a plus in another, and paying A$1,150 on the card leaves the everyday account while the purchases sit on the card. Add a Type column, label these lines Transfer and keep them out of spending totals. A formula for March spending, with dates in A, types in E, amounts in D, the month start in H1 and the next month start in H2, is:

  • =SUMIFS($D$2:$D$600, $E$2:$E$600, "Spend", $A$2:$A$600, ">="&$H$1, $A$2:$A$600, "<"&$H$2)

Can the mapping table save time elsewhere?

Yes. It documents your categories too. Add one more column, Usual category, for recurring items such as rent, power or phone, and use it later in a lookup. When the volume gets heavy, SpendixAI offers an alternative: choose the account, upload a PDF, CSV or Excel statement, review the rows and keep one categorised record. It is built around India and INR, so it is relevant mainly to readers who also have Indian accounts.

Keep this list near the sheet

  • Mapping table kept up to date.
  • Per-account reconciliation done before merging.
  • Row counts match.
  • Transfers labelled.
  • Originals stored untouched.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports