Bank Reconciliation in Excel With Automation Macros

Bank reconciliation in Excel with automation macros works when a VBA routine imports the bank export, matches each line to the ledger on amount and a date window, and lists what is left. It saves hours on steady, clean data. It breaks quietly when the bank changes its export, amounts repeat, or nobody can see what the macro decided.

What a VBA bank reconciliation macro can automate in Excel

A VBA macro can take over the mechanical parts of a bank reconciliation: bringing the bank and ledger data into fixed sheets, cleaning them into the same shape, pairing lines that agree, and building the list of unmatched items. VBA, or Visual Basic for Applications, is the programming language built into Excel. The judgment parts stay with a person.

The steps a macro handles well

Most monthly reconciliations repeat the same keystrokes. Those are the ones worth recording or coding:

• Import. Open the bank's CSV or Excel download and the ledger export, and paste each into its own sheet with fixed column positions.
• Clean. Trim spaces, turn text amounts into numbers, convert dates stored as text, and put debits and credits into one signed amount column.
• Match. Pair each bank line with a ledger line of the same amount within a few days, and mark both as matched.
• Report. Copy the unmatched lines into a reconciliation layout: balance per bank, deposits in transit, outstanding checks, balance per books.
• Archive. Save a dated copy of the workbook so next month starts fresh.

The steps a macro should not take

A macro should not decide what an unmatched item means, write off a difference, or change a ledger amount to make lines agree. It also cannot tell you that the bank export itself is complete. A CSV download can miss a page of activity, and the macro will reconcile what it was given. Checking that the statement foots, opening balance plus activity equal to the closing balance, is a step to add before matching, not after.

A sample match routine for a bank reconciliation macro

A match routine is the part of the macro that pairs bank lines with ledger lines. The safest routine matches in passes, from the strictest rule to the loosest, and never lets one line match twice. Each pass writes a reason code next to the pair, so a reviewer can see which rule made the match.

The match routine, step by step

1. Check the inputs. Confirm both sheets have the expected headers in the expected columns. Stop with a clear message if they do not.
2. Foot the bank data. Add the opening balance to the sum of the amounts and compare it with the closing balance typed from the statement. Stop if they differ.
3. Pass one: check number and amount. For each bank line with a check number, look for a ledger line with the same number and amount. Mark both with reason "CHK".
4. Pass two: exact amount, same date. For remaining lines, pair on amount and posting date. Mark "AMT-DATE".
5. Pass three: exact amount, date within a window. Allow a window, such as three business days for deposits and longer for checks. Mark "AMT-WIN".
6. Refuse ambiguous pairs. If one bank line could pair with two ledger lines, match neither. Mark both "REVIEW".
7. List the rest. Copy unmatched bank lines and unmatched ledger lines to the reconciliation sheet.
8. Prove the result. Compute the adjusted bank balance and the adjusted book balance. Show the difference in a cell that turns red when it is not zero.

A short code sketch

The fragment below is an example of pass two, written to be readable rather than fast. It assumes amounts in column C and dates in column A on both sheets, with a status in column F.

For Each b In wsBank.Range("C2:C" & lastBank)
  If wsBank.Cells(b.Row, 6).Value = "" Then
    hits = 0
    For Each l In wsBooks.Range("C2:C" & lastBooks)
      If wsBooks.Cells(l.Row, 6).Value = "" And l.Value = b.Value _
         And wsBooks.Cells(l.Row, 1).Value = wsBank.Cells(b.Row, 1).Value Then
        hits = hits + 1: hitRow = l.Row
      End If
    Next l
    If hits = 1 Then
      wsBank.Cells(b.Row, 6).Value = "AMT-DATE"
      wsBooks.Cells(hitRow, 6).Value = "AMT-DATE"
    ElseIf hits > 1 Then
      wsBank.Cells(b.Row, 6).Value = "REVIEW"
    End If
  End If
Next b

The important line is the check for more than one hit. Without it, the routine would pair the first candidate it finds, which is how repeated amounts get matched to the wrong partner. Card statements, full of small repeated charges, hit this trap hardest; the guide to automated credit card reconciliation deals with that kind of account.

Why do Excel reconciliation macros break?

Excel reconciliation macros break because they depend on things the macro's author does not control: the bank's export layout, the ledger's report format, the data in each line and the computer the file runs on. When any of those change, the macro either stops with an error or, worse, keeps running and gives a wrong answer.

Failure modes and the symptom each leaves

The table lists common failures. The symptoms are what a reviewer would notice, if they look.

The most dangerous rows are the silent ones: hard-coded ranges, sign flips and partial downloads. Each gives a reconciliation that looks finished. A footing check at the start of the routine catches the partial download. A count of rows read, shown on the reconciliation sheet, catches the hard-coded range.

Upkeep is the hidden cost

Every fix to a macro is a change to a program, usually made by the one person who understands it. When that person leaves, the macro becomes a black box that people keep running because it has always worked. The same pattern appears with screen-recording bots, and the post on RPA bank reconciliation and where bots break compares the two approaches.

Macro security and the blocked-macro banner in Excel

Macro security is the reason a reconciliation workbook that worked last month may refuse to run today. Office treats macros in files from the internet, including email attachments, as a risk. A workbook emailed from a colleague or downloaded from a shared drive can arrive with its macros switched off.

What Microsoft changed

According to Microsoft Learn's article on macros from the internet being blocked by default, Office applications on Windows now block VBA macros in files that carry the "Mark of the Web", a flag Windows adds to files downloaded from the internet. Users see a security risk banner instead of an option to enable content. The same article lists the ways to allow trusted files: unblock an individual file in its Properties dialog, save files to a Trusted Location, or have macros digitally signed by a trusted publisher.

What this means for a reconciliation team

For a reconciliation macro, the practical choices are these:

• Keep the workbook in one controlled location and open it from there, rather than emailing copies.
• Ask IT whether macros must be signed. Microsoft's article recommends a policy that allows only macros signed by a trusted publisher for users who need them.
• Do not tell staff to click through security warnings. A habit of unblocking every file is how malicious macros get in.

These are not reasons to abandon macros. They are reasons to treat the macro as software with an owner, a location and a version, rather than as a spreadsheet trick.

Is automated bank reconciliation in Excel auditable?

Automated bank reconciliation in Excel is auditable only if the workbook records what the macro did. A reviewer needs to know which lines were matched, by which rule, and which were left for a person. A macro that overwrites the sheet with a final layout leaves the result and erases the path to it.

What a reviewer cannot see by default

A reviewer opening a finished workbook usually sees the reconciliation sheet and a zero difference. They cannot see:

• Whether the macro ran on the full bank export or a partial one.
• Whether anyone edited a cell after the macro ran.
• Which version of the macro produced the result.
• Whether an ambiguous pair was matched by the macro or by a person.

Making it reviewable

Small additions make a large difference. Keep the raw imports on their own sheets and never edit them. Write a reason code next to every match. Add a log sheet where the macro writes the run date, the user name, the file names it imported and the row counts. Protect the result sheets so that manual edits leave a trace. These steps turn a fast workbook into one a reviewer can rely on.

One area where macros often skip a step is deposits. A deposit on the statement may be several receipts in the ledger, and an amount-only routine cannot pair them. The guide to reconciling cash receipts to bank deposits explains how to group receipts into a deposit before matching.

When to retire reconciliation macros for dedicated software

Retire reconciliation macros when keeping them costs more than replacing them. The signs are practical: the macro breaks more than once a quarter, only one person can fix it, the number of accounts keeps growing, or a reviewer has started asking questions the workbook cannot answer.

Questions that point to a change

Ask these of the current setup:

1. How many hours went into fixing the macro in the last year?
2. Who could fix it if that person were away at month-end?
3. Has a reconciliation ever been signed with a macro error in it?
4. Does the macro check that each statement foots before matching?
5. Can a reviewer see every match and the rule that made it?

Two or more uncomfortable answers mean the macro has become a system without the controls a system should have. Before you shop, the list of questions to ask account reconciliation software vendors helps you test a replacement on the points your macro handled poorly.

What dedicated software adds

A dedicated tool reads statements rather than relying on a fixed export layout, checks that each statement foots, and keeps the matching rules and their results visible. PreciseBankFlow, for example, reads statement PDFs line by line, checks opening plus activity against the printed closing balance, and lists differences, duplicates and gaps in an exception report. Whether that is worth paying for depends on volume. A single account with twenty lines a month may never outgrow a well-built macro. You can see the reconciliation checks on the PreciseBankFlow homepage and compare them with what your workbook does today.