BankStatementDesk

How to match transfers between bank accounts in a spreadsheet

You move 800 from an operating account to a savings account. The operating statement calls it Internet Banking Withdrawal. The savings statement calls it Incoming Credit, and the posting date is one day later. Searching descriptions for the word transfer will miss both lines. Matching only the amount could connect the withdrawal to an unrelated customer payment. A useful transfer worksheet preserves each individual posting and records the evidence connecting the two sides of the movement.

In this guide
  1. Establish the accounts and reporting purpose
  2. Give the matching table enough evidence
  3. Work through a three-account example
  4. Separate fees from principal
  5. Handle recurring patterns
  6. Prepare a monthly exception view
  7. Validate the register and transfer report

Establish the accounts and reporting purpose

Give each account a short, unambiguous alias. Keep its currency alongside the alias. Record which statements and dates you actually possess. A transfer outside the accounts covered by your report may have no included counterpart. That is a valid outcome, worth distinguishing from an internal transfer whose second leg is missing. If several people own different accounts in a combined workbook, establish which accounts the report treats as belonging together before pairing anything.

Decide what final totals represent

Account balances must reflect every posted movement. A summary of external receipts and payments across multiple accounts usually needs a view that removes paired internal movements from those categories. Maintain both views from the same transaction rows. Hiding one side in the original register changes an individual account's balance. Leave both rows posted and attach a shared pairing reference. Your summary can select the classification it needs while reconciliations continue to use the full register.

Give the matching table enough evidence

For each side keep a source reference, posting date, amount, currency, account alias and original description. If a transfer confirmation code appears, record its exact text as an additional field. Add pair ID and review status in new columns. IDs such as TR-014 can be local to your worksheet. Explain that these are your identifiers. A transfer memo visible on only one side may still help, but distinguish a bank reference from an analyst-written note.

Create a short candidate list

Choose an outgoing movement and filter the other accounts for incoming movements in the same currency and amount. Widen the dates if settlement crossed a weekend. A wider search produces more candidates; it cannot establish a match. Look for account suffixes, posted references, supporting instructions and the owner's records. Avoid placing full account numbers into a shared review file when aliases and masked suffixes distinguish the accounts adequately.

Work through a three-account example

In this fictional example, Operating opens at 2,400, Reserve opens at 1,100, and Tax Savings opens at 400. Operating shows an 800 transfer out on July 30 with memo “to reserve 47.” Reserve shows an 800 receipt on July 31 with reference Z61. Tax Savings also gets an 800 credit that day, but its description identifies a separate paying party. You cannot choose between receiving accounts by amount and date alone. Compare the confirmation and destination details.

Pair the legs and test each balance

Suppose the outgoing confirmation identifies Z61 and the account ending in 47 is Reserve. Pair Operating's minus 800 with Reserve's plus 800 and write TR-001 beside both. Before considering other entries, Operating is 1,600 and Reserve is 1,900. Their combined cash remains 3,500. The separate incoming credit raises Tax Savings to 1,200. Counting it as a second transfer would make external receipts disappear. Two paired same-currency cash movements should have equal magnitudes and zero combined signed amount.

Explain a date boundary

If July's Operating statement closes on July 30 and Reserve's July statement also closes on July 30, the second leg belongs to a later statement. Keep the outgoing transfer in its original period. Mark its status waiting for receiving statement, then record where to look. When August arrives, connect the rows using one pair ID across periods. Changing the receipt date to July 30 damages the receiving ledger even though it makes the monthly report appear tidier.

Separate fees from principal

Consider an Operating withdrawal of 505 and a Reserve receipt of 500. A confirmation lists a 500 transfer and a five-unit service fee. When the statement itemizes 500 and five on different lines, match only the principal and handle the fee as its own source row. If the bank posts one 505 line, keep it intact in the register. Create a separate analysis allocation showing 500 transfer principal and five fee, with a link to the original transaction.

Confirm the allocation is complete

Add the analysis amounts and compare them with the original signed debit. Minus 500 principal and minus five fee reproduce the minus 505 posting. A second standalone fee in the register would reduce the account twice. Add a note describing whether each calculated row contributes to register totals or only to category analysis. Without itemized confirmation, mark the mismatch for investigation. Differences can also arise from partial transfers, exchange rates or unrelated transactions.

Handle recurring patterns

An account may receive several transfers of the same value on the same day, particularly when standing instructions run close together. Pair the strongly identified transactions first, then review remaining candidates. If two pairs have indistinguishable evidence, retain an unresolved group rather than forcing a one-to-one match from row order. Order on one statement may follow initiation time while the other lists posting sequence. Working backward from a desired zero difference can conceal a missing receipt.

Credit card payments need a different sign test

A card statement may show payments as negative amounts because they reduce an amount owed. A checking statement also commonly displays a payment as a withdrawal. Two negative values can represent the same payment across different account types. Match by payment description, absolute amount and destination, then document the conventions. The GnuCash guide to credit card activity illustrates the distinction between individual purchases and a payment transfer.

Different currencies need original amounts

When 600 units of one currency buy 545 units of another, a zero signed sum across currencies has no meaning. Keep each currency and amount on its own ledger. Record conversion evidence separately, together with documented charges and the direction of the exchange rate. Compare in one clearly named currency only when the applicable rate is available. A rate inferred from final postings may include costs. Label it as an implied rate if that is all you know.

Prepare a monthly exception view

Filter by review status and group entries into missing counterpart, multiple candidates, amount difference and awaiting evidence. Each category implies a different next step. For a missing counterpart, check coverage and dates before asking for confirmation. For multiple candidates, seek a reference or account suffix. For an amount difference, request the transfer advice if appropriate. Track the last evidence checked so someone else can resume without sending the same request twice.

Preserve reversal chains

An outgoing instruction may fail, leading to a return credit and a later successful attempt. Suppose a source account contains minus 250, plus 250, and another minus 250, while the destination shows one plus 250. Treat the first pair as a failed instruction and reversal only if references or descriptions support that interpretation. Match the retry to the destination. All four printed rows remain. Write separate identifiers for the return relationship and the inter-account relationship.

Validate the register and transfer report

Reconcile individual statements before using consolidated figures. Differences inherited from PDF extraction can make a valid transfer look unmatched. Use the bank statement to Excel converter for selectable statements and compare the extracted rows with each original. Check repeated page headings, multi-line descriptions and amounts shifted into the balance column. A perfectly paired transfer cannot repair a starting balance mistakenly counted as income.

Record a batch transfer

One source debit can fund several destinations. A 900 withdrawal might correspond to deposits of 400, 300 and 200 in three controlled accounts. Confirm the batch instruction and each destination receipt individually. Record four source positions under one batch identifier, with a distinct allocation number for each destination. Check that the allocations sum to the funding amount. Multiple combinations of unrelated receipts could add to 900, so the arithmetic is a test after identification.

If only the 400 and 300 receipts appear by cutoff, show documented principal of 900, matched receipts of 700 and an outstanding amount of 200. Leave the group open until evidence arrives. If the sole source is an outgoing statement without destination instructions, report the outgoing amount and the missing evidence. A partially documented batch should be distinguishable from a completed transfer whenever a reviewer filters the report.

What belongs in the handoff note?

Record account aliases, currencies, period ends, confirmed pair count and outstanding movements. State whether the summary excludes only confirmed internal principal or includes tentative pairs. Give source references for manual allocations. A useful handoff says, “TR-014 left Operating on August 30. Receipt expected in September Reserve. Excluded from confirmed pairs pending evidence.” The reviewer knows what to seek and which report will change after confirmation.

Before distributing the results, select a matched transfer at random and work backward. Starting with the pair ID alone, a colleague should find both original statements, recover both amounts in their native currency and explain any delay between posting dates. If the only evidence lives in a personal message or someone's memory, record a short authorized reference in the worksheet. Keep half-complete matches visible until the next statement arrives and record their eventual disposition.

Take a final look at how a confirmed pair affects a dashboard. If Operating's receipts total 1,400 and Reserve's receipts total 800, the raw account receipts total 2,200. When all 800 entering Reserve came from Operating, confirmed external receipts are 1,400. The separate account schedules still report the full 2,200 in gross receipts. Label both views with their inclusion rules so a manager reading two totals can understand exactly which question each one answers and how to trace the difference. Use separate columns for the report period and the day matching was confirmed, since those dates can legitimately fall in different months.