Preparing your accounting data before automating

The single highest-return activity in an accounting automation project is unglamorous data cleanup, done before anything is configured.

Data quality is the real constraint makes the argument. This is the checklist.

1. Deduplicate suppliers and customers

Run this: export your supplier list, sort alphabetically, read it.

That is genuinely the method. Duplicates are visually obvious to a person scanning a sorted list and invisible in any report, because the system considers them different entities.

What you are looking for: the same organisation as "ABC Trading Sdn Bhd", "ABC Trading", "A.B.C. Trading S/B". Also near-identical names that are genuinely different companies, which matters equally — merging those causes a different problem.

Bad answer: more than about 5% duplicates suggests master data has never been maintained, and payment terms and bank details may differ between versions. Check those before merging, because merging the wrong direction propagates the wrong bank details.

Why it matters: duplicate suppliers cap matching accuracy structurally, distort spend analysis, and enable duplicate payment.

2. Prune the chart of accounts

Run this: every account with its transaction count for the last 24 months.

What you are looking for:

• Zero-transaction accounts — close them
• Pairs that mean nearly the same thing — merge, and decide which name survives
• Accounts whose name no longer describes their contents
• Accounts created for a one-off situation years ago

Then: write one sentence per surviving account saying what belongs in it. This is the part people skip and it is worth more than the pruning — it settles arguments, gives reviewers something to check against, and tells a new joiner what the previous team meant.

Bad answer: fewer than half your accounts used in two years. Common, and it means the analysis has been carrying dead weight for a long time.

3. Check coding consistency

Run this: for your ten largest recurring suppliers, list every account their invoices have hit in two years.

What you are looking for: more than one or two accounts per supplier without a clear reason.

Why it matters most: this is the input that most directly degrades a learning system. Shown the same cost coded three ways, it correctly infers that the right answer is ambiguous — so it keeps asking, the exception queue never shrinks, and the team concludes the automation does not work.

Fix: decide the correct treatment, and correct recent history so the system learns one answer. You do not need to fix years of it; the last twelve months is what matters.

4. Age every control account

Run this: every balance sheet control account, with items aged.

What you are looking for:

• Unmatched items over a year old
• Suspense account contents
• Debtors and creditors that will not decompose into nameable items
• Accruals that were never reversed

Why before automating: the first exception queue will otherwise arrive containing hundreds of historical items. Nobody can tell the genuine ones from the archaeology, everyone concludes the system does not work, and confidence is lost before a single current transaction has been processed.

This is the step most often skipped and the most damaging to skip.

5. Confirm the opening position

Every balance you are carrying into the automated period should decompose into items you can name.

If it does not, you are adopting a problem — and it is far easier to resolve now than after six months of new transactions have been posted on top of it.

What you do not need to do

Worth stating, because data cleanup can become a reason never to start:

• You do not need to recode years of history
• You do not need a complete master data overhaul
• You do not need perfect consistency

Models learn mainly from recent history. What matters is that the last year is consistent and the current master data is clean. Inconsistency in periods you will never process again is largely harmless.

The half-day version

If two or three days is not available, do this much:

1. Sort the supplier list and merge the obvious duplicates
2. Close accounts with no transactions in two years
3. Check your ten biggest suppliers for coding consistency and fix what is wrong
4. List anything in suspense and decide what it is

That covers most of the value. The remainder can be done in parallel with the parallel run.

Common questions

What data cleanup is needed before automating accounting?

Deduplicating the supplier and customer masters, pruning the chart of accounts and defining what belongs in each remaining account, resolving inconsistent coding on your largest recurring suppliers, ageing every control account and clearing historical unmatched items, and confirming that opening balances decompose into nameable items. For most small finance teams this is two or three days.

Which step matters most?

Coding consistency, because it most directly degrades a learning system — shown the same cost coded three ways, the system correctly concludes the answer is ambiguous and keeps asking, so the exception queue never shrinks. Clearing the historical backlog runs a close second, since otherwise the first exception queue arrives full of archaeology and people conclude the automation does not work.

How do I find duplicate suppliers?

Export the list, sort it alphabetically and read it. Duplicates are obvious to a person scanning a sorted list and invisible in any report, since the system treats them as separate entities. Before merging, check whether payment terms and bank details differ between the versions, because merging in the wrong direction propagates the wrong details.

Do I need to clean historical data too?

Only the recent portion. Models learn mainly from the last year or two, so consistency in that period and clean current master data are what matter, while inconsistency in periods you will never process again is largely harmless. Treating full historical cleanup as a prerequisite is a common reason projects never start.

Related: data quality is the real constraint · how AI learns your chart of accounts · migrating historical accounting data