Your Bank Feed Has Been Quietly Creating Duplicates for Months

I came into a service company's books, and one of the first things we ran into was that QuickBooks and the bank didn't agree. Not by a little, and not for one account.

The owner had already noticed. He looked at it and said, "that doesn't look right."

So we opened up the bank feed rules. There were a lot of them, and most were set to enter transactions on their own. Nobody saw those lines before they landed in the file. They showed up already recorded, already categorized, and already counted.

There were also sales receipts in there that weren't sales. Revenue was posted at the end of every month through its own entry, so every one of those receipts counted the same money a second time. It was junk data, and it was sitting in the revenue line the owner was reading.

Those two problems didn't cause each other. They came from the same place, which was processes nobody had set up carefully and nobody was checking.

This went back about a year.

The thing the owner said that stuck with me came later, once we knew what we were dealing with. He told me he didn't want to do checkbook accounting. He wanted his QuickBooks file to be his source of truth. That's the right standard to hold a file to, and it's the exact thing a bank feed running on its own will take away from you.

Match and Add are not the same button

Your bank feed is a list of what the bank says happened. It isn't your books. Every line that comes across has to get connected one of two ways, and QuickBooks puts both choices right next to each other.

Match means it's already in your file. You're telling QuickBooks that the bank's line and your line are the same event. Nothing new gets created, and the one you had gets marked as cleared.

Add means it isn't in there yet, so QuickBooks writes a brand new transaction straight off the feed.

One button connects, the other one creates. Click Add on something already in the file and that payment is now in your books twice.

Rules are useful. Auto-add is not.

QuickBooks lets you write rules for the feed. When a transaction comes across that matches what you told it to watch for, the rule fills in the payee, the account, the class, all of it, and hands it to you ready to go. That part is genuinely good and I use it.

Inside the rule there's a switch that tells QuickBooks to go ahead and enter the transaction automatically. Flip that on and the line stops waiting for you. It gets recorded on the rule's judgment, and the first time you lay eyes on it is when you're staring at a report wondering where a number came from.

I don't turn that on and I'd tell you not to either. A rule that fills in the blanks and waits for one click costs you about a second. A rule that enters on its own costs you the same second, right up until it's wrong, and then it costs you an afternoon working backward through a month of transactions nobody ever looked at.

I'd rather see every transaction and click it in than go hunting for where one went.

How they pile up

Nobody sits down and decides to enter things twice. The version I see most starts with somebody being on top of it.

You write a check on Tuesday and enter it the same day, because you want the record while it's fresh. Four days later the payment clears and comes across the feed. The date is different now. The payee reads as whatever the bank calls that vendor, which is usually not what got typed in. It doesn't look familiar, so it gets Added. The payment is in there twice, and the person was being careful both times.

Deposits do the most damage. Say you take in five payments and walk them to the bank as a single deposit. QuickBooks holds those five in Undeposited Funds until you group them into that deposit. When the feed brings the deposit over, it should match. Add it instead and you've booked the income a second time, and those five payments are still sitting in Undeposited Funds waiting on a deposit that already came and went.

None of this makes any noise the day it happens.

Where it shows up first

On the balance sheet, which is where I start on any file like this.

Open the bank account in QuickBooks and compare its balance to what the bank says, as of the same day. Those two numbers are either the same or they aren't. Then look at Undeposited Funds. Anything in there older than about a week is either a deposit that got Added instead of matched, or cash that never made it to the bank. Check the credit card balance against the statement while you're in there, because duplicated card payments make the balance look smaller than what you owe.

The P&L is where it changes your decisions. Duplicated expenses make a profitable month look thin, so you don't hire, or you don't take the distribution you'd earned. Duplicated income does the opposite and it's worse, because you make plans on revenue that was never there. Look for a Reconciliation Discrepancies account too. If it's carrying a balance, somebody hit a difference they couldn't find and let QuickBooks write it off. That entry doesn't fix anything. It moves the problem into an expense account where it stops asking to be looked at.

What a reconciliation is supposed to leave behind

Some things legitimately haven't cleared yet. A check you mailed last week, a deposit you made Friday afternoon. Those sit as outstanding items, they clear next month, and that's how it's supposed to work.

An outstanding item still sitting there six months later is a different animal. It got lost, or it got voided and reissued and nobody took the first one out, or it was never a real check to begin with. Carry enough of those and your reconciliation ties to the bank statement while describing money that isn't going anywhere.

Every company should have a policy on this and apply it the same way every month. Plenty of businesses print "void after 180 days" right on the check, and if that's what yours say then you already have your number.

How the item comes out matters. Voiding the original check reaches back and changes the month it was written in, which is a month you already reconciled and probably already reported. If that period is closed, and it should be, you don't go back in there. That's most of what I'll get into in next week's article, and it's the reason this one is worth doing first.

What I do instead is one journal entry dated in the current period. Call it bank account voided transactions, and give every stale item its own line so you can still tell what each one was. The balance lands where it belongs and last year's reports still say what they said. If doing that every month is more than you'll actually keep up with, do it once at year end. That way the next year's balance sheet starts clean.

Then clear that journal entry and the checks it reverses in the same reconciliation. They offset each other and both drop off your outstanding list. Skip that part and you'll have fixed the balance while the same stale checks keep showing up every month.

This is monotonous work for an owner. It's bread and butter for a bookkeeper.

Why an unreconciled month gets expensive

Reconciling is how you prove the books match the bank. You take the bank's ending balance for the month, tick off everything that cleared, and the difference should come to zero. When it does, you know the month is real.

Skip it once and the next month starts from a balance nobody checked. Now a difference could have come from either month, so you're searching two months of transactions to find it. Skip it again and it's three. Every month you let go adds another full month to the search, and the file keeps taking in new transactions on top of the unverified ones.

An unreconciled month doesn't stay one month.

Finding them and unwinding them

Start by finding the last month that actually reconciled clean. Everything after that is your window, and everything before it you can leave alone.

Pull up the bank register for that window and sort by amount. Duplicates land next to each other as pairs of identical numbers a few days apart, and sorted that way they're hard to miss. Do the same on each credit card, and give Undeposited Funds its own pass.

Unwinding is where people create the second problem, so the order matters.

Back up the file first, which is harder than it sounds. QuickBooks Online has no undo button, and unless you're on a plan that includes backup and restore, there's nothing built in to fall back on. Third-party apps handle it. If you don't have one, at least export the register for your whole window to Excel before you touch anything, so you have a record of what the file looked like going in. The Audit Log will also show you what changed and who changed it, which has saved me more than once.

When you find a pair, check which one is already marked as reconciled and delete the other one. Deleting a reconciled transaction knocks the leg out from under a month you'd already closed, and every month after it. If a reconciliation was forced with a discrepancy adjustment, that adjustment comes out last, after the real duplicates are gone, because until then you don't know how much of it was real.

Then reconcile forward, one month at a time, oldest first. It's slower than trying to fix the whole window at once, and it's the only version that finishes.

Check your own file this week

1. Open your bank feed rules and read down the list. Anything set to automatically add a transaction, turn that off. You'll still get the rule. You'll just get to see it first.
2. Compare the QuickBooks balance on your main account to your actual bank balance as of last night. Write both numbers down and subtract.
3. Open your last reconciliation and look at the outstanding items. Anything older than six months needs a decision, not another month of waiting.
4. Search your P&L for "Reconciliation Discrepancies." If it's carrying a balance, a reconciliation got forced, and the difference is still in the file somewhere.

If step two didn't come out to zero, you've either got duplicates or you've got something missing. It's the same afternoon either way.

Your file should be able to answer the question

That owner had the right idea. A QuickBooks file is supposed to be the thing you check when you want to know where you stand, not a rough guide you check against your online banking before you trust it.

Getting there isn't complicated. It's tedious, and tedious is the part that gets put off.

If yours has been putting itself off for a while, book a fifteen-minute call and tell me the last date your bank account reconciled. That one number tells me most of what I need to know about how big the job is.