Relying on data extracted from a client's system

How an auditor tests that a client's data extract is complete and unaltered before analysing it: reconciliations, row counts, hashes and the file record.

By Achekam · 11 October 2026 · 5 min read · For Accountants & audit firms in South Africa, Nigeria, Kenya

The client's bookkeeper e-mails a spreadsheet holding every journal for the year. Your senior loads it, runs the analytics and finds nothing worth chasing. The file looks thorough, the tests look thorough, and the working paper says "no exceptions". Yet every one of those conclusions rests on something nobody tested: that the spreadsheet held every entry, exactly as the accounting system recorded it.

That untested assumption is where audit analytics most often fails in owner-managed and mid-sized audits across South Africa, Nigeria and Kenya. A tidy extract is not evidence of a complete one. Below is a practical approach to the completeness of a client data extract in an audit: what can go wrong, how to reconcile and freeze the file, when to bring in a specialist, and what belongs on file.

The client controls the tap

ACCA's technical article on data analytics is direct about the weakness. It warns that the completeness and integrity of client data extracted for analytics cannot be taken for granted, and notes that a client may be able to make only selected data accessible, or manipulate what is available for extraction.

Most broken extracts have innocent causes:

• the report was run for the wrong company, branch or date range;
• the reporting tool capped the rows, or the spreadsheet program cut a large file short;
• year-end adjustments sit in a closing period that the standard report leaves out;
• someone "cleaned up" the spreadsheet before sending it, removing reversals or test postings.

Some are not innocent. The entries a dishonest manager would least like you to examine are exactly the ones worth leaving out of a file he prepares. Treat the extract as information produced by the entity, and test it before testing anything inside it.

Reconcile before you analyse

The first test is arithmetic against something the client did not prepare for you:

• Movements per account. Opening balance plus the extract's movements for each account should give the closing balance on the trial balance you are auditing. A difference on any account means stop and ask.
• Debits and credits. Total debits should equal total credits, and both should agree to the ledger's totals for the period.
• Period boundaries. Note the first and last posting dates in the file and confirm that adjustment and post-closing periods are in it.
• Sequence gaps. Where the system numbers journals, a jump in the numbers needs an explanation.

Be clear about what a clean reconciliation proves. Investopedia points out that a trial balance still balances when amounts sit in the wrong accounts, when transactions were never recorded, or when errors cancel each other out. An extract that agrees to the trial balance shows the file and the ledger tell the same story. It says nothing about whether the story is right; that is the job of the analytics and the rest of your evidence.

Freezing the file: row counts, control totals and hashes

Once the extract reconciles, fix it in place so that nobody can later dispute what was tested. Three records do it:

1. A row count, taken when the file arrives and again when it is loaded into the tool.
2. Control totals, such as total debits, total credits and the sum of any other numeric column you rely on.
3. A hash, a short fingerprint worked out from the file's contents, which changes if a single character in the file changes.

With those three, a reviewer can confirm months later that the population tested was the population received, and the team can show that no one edited the file in between. They also end disputes quickly. When a client insists that a journal "was in the file we sent", the count and the hash settle it.

How Achekam takes in ledgers and journal extracts

Achekam builds this kind of check into the way a firm brings client data in.

A firm can upload a client's general ledger in the layout its accounting package exports, as CSV, .xlsx or .xls, Pastel and Evolution exports included. Achekam adds the ledger up into a trial balance and compares every account total printed in the file with its own sum. Where the two disagree, the difference is named on screen and never quietly corrected. Trial balances themselves can arrive as an Excel workbook, a CSV file or a PDF with a text layer, and a stray quote mark in a CSV is read as an ordinary character and noted, so the rows after it are not lost.

For journal entry testing, staff load the whole-population extract as a CSV, map its columns, and Achekam flags entries such as manual, weekend, after-hours and post-close postings for staff to select and test. Achekam does not keep the CSV itself; it keeps the file's hash and row count. Our article on journal entry testing explains how to work through those flags.

Keeping only the fingerprint answers a second risk ACCA raises: an audit firm that copies and stores large volumes of detailed client data runs the risk of breaching confidentiality and data protection law. Hold what the file needs and nothing more.

When a specialist should run the extraction

ACCA observes that specialists are often needed to perform the extraction. Consider one, or insist on watching the extraction rather than receiving a file, when:

• the client runs an enterprise system or heavily customised software and the standard reports do not show the fields you need;
• the team cannot tell which tables, modules or ledgers the extract came from;
• the first extract failed to reconcile and the client cannot explain why;
• fraud risk in the area is assessed as high and management controls the system.

For a small client on packaged software, sitting with the bookkeeper while the standard export runs, and noting the report name and its settings, is usually enough. Our piece on IT environments in smaller audits helps you judge which situation you are in.

Recording the extraction on file

ACCA warns that evidence may be insufficient where analytics procedures and their inputs are not fully documented. A screenshot of a tool's results, for example, may not show the input conditions or the detail of what was tested. The extraction deserves the same care. For each extract, record:

• who ran it, from which system, report or query, with which settings, and on what date;
• how it reached the firm and who received it;
• the row count, control totals and hash at receipt;
• the reconciliation to the trial balance and how any difference was cleared;
• whether a specialist was involved, and what they did.

ACCA sets out these risks next to the benefits in its technical article on data analytics, a useful read for a team using analytics for the first time.

Before your next engagement that plans to use analytics, add the extraction record to the planning checklist and agree with the client, in writing, how the data will be produced. Testing a whole population is only as strong as the evidence that the population is whole.

General guidance only, not advice on your own circumstances. Tax and payroll rules change: check the current position with the authority or your adviser.