Accounting Data Acquisition and Management

Introduction

Accounting information is only as reliable as the data from which it is produced. In modern organisations, accounting data is acquired from enterprise resource planning (ERP) systems, point-of-sale applications, banking platforms, payroll systems, spreadsheets, websites, and databases. These sources rarely use identical structures, naming conventions, date formats, or levels of detail. Consequently, the accountant’s role increasingly includes data acquisition, preparation, validation, documentation, and governance.

Excel provides several facilities for this work. Excel Tables support structured, expandable datasets; Power Query provides a repeatable process for importing and transforming data; and data-validation and reconciliation procedures help establish whether the result is complete, accurate, and fit for accounting use. These tools do not replace accounting judgement. Rather, they help accountants apply that judgement consistently and transparently.

A useful way to view the process is:

Figure: A controlled pipeline from source data to accounting use.

Learning Objectives

By the end of this chapter, students should be able to:

1. Identify common accounting data sources and assess their suitability.
2. Import CSV, database, and accounting-system data into Excel.
3. Structure data using Excel Tables and sound field-design principles.
4. Clean and transform data with Power Query.
5. Merge datasets using appropriate keys and join logic.
6. Validate completeness, accuracy, consistency, and reasonableness.
7. Apply master-data, documentation, security, and governance principles.
8. Explain the limitations and risks of spreadsheet-based data management.

1. The Nature of Accounting Data

Accounting data consists of records describing economic events, resources, obligations, parties, and classifications. A transaction table may contain one row per invoice, payment, journal line, inventory movement, or bank transaction. Each row is an observation, while each column is an attribute such as date, amount, account, supplier, tax code, or currency.

The grain of a dataset specifies what one row represents. For example, a sales table may have a grain of “one row per invoice line,” whereas a general-ledger table may have a grain of “one row per journal line.” Confusing grains creates double counting. If an invoice header is joined to three invoice lines and the invoice total is repeated on every line, summing the joined invoice total will overstate revenue.

Important distinctions include:

• Master data: relatively stable entities such as customers, suppliers, employees, products, accounts, and cost centres.
• Transactional data: events such as invoices, receipts, purchases, payments, and journal entries.
• Reference data: controlled classifications such as tax rates, currencies, countries, and payment terms.
• Metadata: information about the data, including source, field definition, data type, refresh date, owner, and permitted values.

A well-designed accounting dataset normally has a unique identifier, consistent data types, explicit units or currency, and documented relationships to master data.

2. Sources and Acquisition Methods

CSV and text files

Comma-separated values files are common because they are portable and supported by almost every accounting application. However, CSV files do not inherently store data types, formulas, relationships, permissions, or formatting standards. Problems may arise from commas embedded in descriptions, inconsistent quotation marks, character encoding, different decimal separators, and ambiguous dates such as 04/05/2025.

When importing a CSV, the accountant should confirm:

• delimiter and text-qualifier settings;
• character encoding;
• date interpretation and regional settings;
• decimal and thousands separators;
• whether the first row contains headers;
• whether leading zeroes in codes must be preserved;
• record count and control totals.

An account code such as 00125 must usually be imported as text rather than a number; otherwise Excel may convert it to 125.

Databases

Relational databases store data in related tables. A transaction table may contain customer_id, while customer names and addresses reside in a customer table. Database connections can provide current information without repeatedly exporting files. They also support filtering at source, which can reduce the volume transferred.

Database extraction requires awareness of permissions, query logic, refresh schedules, and the possibility that a query may omit records. A query should be documented, tested, and preferably approved by the system owner. Accountants should not assume that a field labelled “amount” is gross, net, tax-inclusive, or expressed in the entity’s reporting currency.

Accounting and ERP systems

Accounting systems may provide Excel connectors, APIs, reports, or scheduled exports. These systems often contain the authoritative ledger, but an exported report may include presentation adjustments, hidden filters, or summarised balances. Before using an extract, determine whether it is:

• a source transaction listing;
• a subledger report;
• a trial balance;
• a management report; or
• a manually adjusted spreadsheet.

The source, extraction date, reporting period, filters, and user should be recorded in the workbook documentation.

3. Structuring Data with Excel Tables

An Excel Table is a named, rectangular range with a header row and structured-reference capability. Tables automatically expand when new rows are added and make formulas, filters, and Power Query connections more reliable.

Good table design follows several principles:

1. Use one header row with unique, meaningful field names.
2. Store one record per row and one attribute per column.
3. Avoid merged cells, blank separator rows, subtotals, and decorative headings inside the data range.
4. Keep raw data separate from calculations and presentation.
5. Use consistent data types within each field.
6. Preserve source identifiers and do not overwrite original values.
7. Use controlled fields for classifications such as account, department, and tax code.

A table called tblTransactions might contain TransactionID, TransactionDate, AccountCode, CustomerID, Description, Currency, Amount, TaxAmount, and SourceSystem. A calculated column can use a structured reference such as =[@Amount]+[@TaxAmount], which is easier to interpret than a cell reference whose meaning depends on its location.

Excel Tables should not be treated as databases without qualification. They lack strong relational constraints, may be altered by users, and can become slow or fragile at large volumes. Nevertheless, they are highly effective for small and medium-sized accounting analyses when combined with controls.

4. Data Quality and Master-Data Principles

Data quality is multidimensional. The principal dimensions are:

Master-data management establishes common definitions and ownership for entities used across processes. A supplier should ideally have one master record, one approved supplier identifier, and defined procedures for creation, amendment, suspension, and review. Names alone are unsafe matching keys because two suppliers may share a name or one supplier may appear under several spellings.

A business key has meaning in the business, such as a tax registration number. A surrogate key is an internally assigned identifier, such as SupplierID 10482. Matching should use a stable, unique key wherever possible. If no reliable key exists, approximate or fuzzy matching may be used only with review controls because similar names do not prove identity.

5. Power Query: A Repeatable Transformation Process

Power Query provides an extract-transform-load process within Excel. Its general stages are:

1. Connect: select a file, folder, workbook, database, web source, or accounting connector.
2. Profile: inspect row counts, nulls, errors, distinct values, and data types.
3. Transform: remove unwanted rows, promote headers, split columns, replace values, standardise formats, and derive fields.
4. Combine: append similar tables or merge related tables.
5. Load: place the result in a worksheet or Data Model.
6. Refresh and document: repeat the process and record exceptions.

The key advantage is repeatability. A manually cleaned file may produce a correct result once, but a Power Query process can apply the same steps to the next monthly extract. Repeatability reduces inconsistent judgement and improves auditability, although the query itself must be reviewed.

Cleaning operations

Common transformations include:

• trimming leading and trailing spaces;
• cleaning non-printing characters;
• standardising case where appropriate;
• converting dates using an explicit locale;
• changing data types deliberately;
• replacing nulls only where the accounting meaning is known;
• splitting composite fields;
• removing duplicate records after defining what “duplicate” means;
• filtering invalid or out-of-scope records;
• adding source-file and import-date columns.

A blank amount is not automatically zero. A missing tax code is not automatically “no tax.” Transformations must preserve economic meaning rather than merely improve appearance.

Append and merge

Append places rows from similarly structured tables beneath one another. It is appropriate for January, February, and March transaction files when the columns represent the same attributes.

Merge combines columns from related tables using a key. For example, a transaction table can be merged with a customer table using CustomerID. Join types matter:

• Inner join retains matching records from both tables.
• Left outer join retains every row from the primary table and matching attributes from the secondary table.
• Anti-join identifies records with no match and is valuable for exception testing.

Before merging, check whether the key is unique in the secondary table. If CustomerID occurs three times in the customer table, a one-to-many merge may multiply transaction rows. This is a structural error unless the multiplication is intended.

6. Validation and Reconciliation

Validation should occur at several levels rather than only at the final report.

Structural validation

Check that required columns exist, data types are suitable, identifiers are present, and the expected number of fields is returned. A schema change in a source system can otherwise cause a query to refresh incorrectly or fail silently.

Record-level validation

Examples include:

• invoice date is not after the extraction date;
• amount is numeric and within a reasonable range;
• account code exists in the chart of accounts;
• tax code is valid for the transaction jurisdiction;
• debit and credit indicators contain only permitted values;
• required foreign keys match master records.

Control totals

A control total is an independently calculated quantity used to compare source and processed data. For a transaction listing, useful controls include record count, sum of debit amounts, sum of credit amounts, minimum and maximum dates, and totals by currency or entity.

For a balanced journal, the fundamental condition is:

Here, Debit_i and Credit_i are the debit and credit amounts of journal line i, and the summation covers all lines within the defined journal or reporting population. The condition applies when debits and credits are measured in the same currency or consistently converted into a specified functional currency. It does not, by itself, prove that the entries are correct or complete.

Reconciliation

Reconciliation compares two sources expected to agree, such as the bank statement and cash ledger, or the accounts receivable subledger and the general ledger. Differences should be classified as timing differences, mapping differences, missing records, duplicates, currency effects, or genuine errors. A reconciliation that merely reports a difference without investigating its cause is incomplete.

7. Worked Example: Monthly Sales Import

Assume a business receives three CSV files, one for each month. Each file contains InvoiceNo, Date, Customer, NetSales, Tax, and Currency. The April file uses 2025-04-01, the May file uses 01/05/2025, and the June file contains amounts with currency symbols.

The accountant creates a folder connection in Power Query and performs the following steps:

1. Import all files from the approved folder.
2. Add the file name as SourceFile.
3. Remove temporary files and promote the correct header row.
4. Convert Date using the appropriate locale rather than relying on the computer’s regional setting.
5. Remove currency symbols from amount fields and convert amounts to decimal numbers.
6. Append the monthly tables.
7. Merge the result with the customer master using CustomerID. Because the files contain names rather than IDs, the customer master is first used to create an approved name-to-ID mapping, and unmatched names are placed in an exception table.
8. Validate the row count and net-sales total against the three source reports.
9. Load the clean result to an Excel Table named tblSales.

Suppose the source reports show 2,400 invoice lines and net sales of £480,000. The transformed table contains 2,397 lines and £479,200. The result must not be accepted simply because the query completed. The three missing lines and £800 difference require investigation. Possible causes include rejected records, duplicate removal, a date filter, or an incorrectly parsed amount. The query’s status is therefore “not validated” until the difference is explained and documented.

8. Illustrative Case: Northbridge Retail Ltd

Background. Northbridge Retail Ltd consolidates sales from stores, an online platform, and a recently acquired subsidiary. Management reports have been delayed because each source uses different product codes, date formats, and sales definitions.

Problem. The finance team imports files manually, changes column headings, and copies totals into a reporting workbook. The same customer appears under several names, and online sales are sometimes included both in the sales report and in a payment report.

Analysis. The team identifies three grains: store invoice line, online order line, and payment transaction. It documents that payment transactions are not sales transactions and should not be appended to sales. It also finds that product codes are not unique across two subsidiaries.

Approach. Northbridge establishes a product master with a group-wide ProductID, retains each source code as a reference attribute, and assigns an owner for new-product approval. Power Query imports files from controlled folders, records the source file, standardises dates and currencies, and appends only datasets with the same grain. A separate merge adds product categories. Anti-joins identify unmatched products and customers. Reconciliation compares sales totals with approved source reports, while payment data is reconciled separately to bank and receivables records.

Outcome. Monthly preparation becomes repeatable, exceptions are visible, and management can distinguish data errors from legitimate timing differences. The process does not eliminate judgement: finance staff still review unusual transactions, master-data changes, and unexplained variances.

Learning points. The case demonstrates that successful automation depends first on definitions, grain, keys, and ownership. Power Query can execute a process, but it cannot decide whether two similarly named customers are legally the same entity or whether a payment represents revenue.

9. Governance, Ethics, Security, and AI

Accounting data often includes personal data, bank details, tax identifiers, payroll information, and commercially sensitive prices. Access should follow least-privilege principles: users receive only the access necessary for their duties. Workbooks should be stored in approved locations, shared through controlled permissions, and protected from unauthorised alteration. Password protection alone is not a complete security strategy.

Data governance should define the owner, steward, source, permitted use, retention period, refresh frequency, and change history for important datasets. Audit trails should show who extracted data, when it was transformed, which exceptions occurred, and who approved the result.

AI-assisted tools may help classify descriptions, suggest matches, detect anomalies, or generate transformation code. However, confidential data should not be uploaded to an unapproved service. AI outputs may be statistically plausible but factually wrong, and matching suggestions require human review. The accountant remains responsible for evidence, confidentiality, explainability, and the accuracy of reported information.

10. Limitations and Best Practices

Excel and Power Query are powerful but have limitations. They are vulnerable to accidental edits, undocumented changes, local file paths, version differences, and excessive data volume. Refreshing a query does not guarantee that the source is correct. A clean-looking table can still contain omitted transactions or incorrect master-data mappings.

Best practices include:

• retain an immutable or read-only copy of source extracts;
• separate raw, transformed, validated, and presentation layers;
• use explicit names and documented assumptions;
• preserve source keys and provenance fields;
• test queries with known records and deliberate exceptions;
• reconcile totals before publication;
• review joins for unintended row multiplication;
• maintain a data dictionary and change log;
• restrict editing of queries and master data;
• design refresh procedures that another competent person can operate;
• archive outputs and evidence according to organisational policy.

Chapter Summary

Accounting data management begins with understanding the source, grain, structure, and purpose of data. CSV files are portable but lack type and relationship information; databases and accounting systems can provide stronger structure but require controlled access and documented extraction logic. Excel Tables support disciplined tabular design, while Power Query enables repeatable extraction, transformation, appending, merging, and loading.

Reliable results require more than successful imports. Data must be assessed for completeness, accuracy, validity, consistency, uniqueness, timeliness, and integrity. Master-data principles provide stable identifiers and controlled classifications. Validation uses structural tests, record-level rules, control totals, anti-joins, and reconciliations. Governance, security, provenance, and human review remain essential, especially when AI-assisted tools are used. The central professional principle is that automation should make accounting work more reproducible and transparent, not less accountable.

Key Terms

Accounting data: Information describing financial events, balances, entities, and classifications.

Append: Combining rows from tables with comparable structures.

Business key: A meaningful identifier used to identify a business entity.

Control total: An independently calculated total used to test processed data.

Data grain: The business meaning of one row in a dataset.

Data integrity: Preservation of accurate relationships and values throughout processing.

Data lineage: The documented path of data from source to output.

Data steward: A person responsible for the quality and controlled use of a data domain.

Foreign key: A field referring to an identifier in another table.

Inner join: A merge retaining only records with matching keys in both tables.

Master data: Stable entity data such as customer, supplier, product, or account records.

Metadata: Information describing data, its source, structure, meaning, and use.

Power Query: Excel’s tool for connecting, transforming, combining, and loading data.

Reconciliation: Comparison of related records or totals to identify and explain differences.

Reference data: Controlled classifications such as currencies, tax codes, and countries.

Surrogate key: An internally generated identifier without direct business meaning.

Table grain: The precise event or entity represented by each table row.

Validation: Testing whether data conforms to defined structural, accounting, and business rules.

Review Questions

Short Answer Questions

1. What is meant by the grain of a dataset?
2. Distinguish master data from transactional data.
3. Why should account codes containing leading zeroes usually be imported as text?
4. What is the difference between appending and merging in Power Query?
5. Why is a control total useful but insufficient as the only validation procedure?

Long Answer Questions

1. Explain the stages of a Power Query process and discuss how repeatability improves accounting control.
2. Discuss the principal dimensions of data quality and give an accounting example of each.
3. Explain how poor master-data design can cause duplicate suppliers, incorrect reporting, or failed reconciliations.
4. Compare CSV files, databases, and accounting-system extracts as sources of accounting data.
5. Discuss the governance, security, ethical, and professional responsibilities associated with spreadsheet-based accounting data management.

Analytical / Application Questions

1. A merged sales table contains 10,000 source rows but 10,450 output rows. Diagnose the likely structural cause and design tests to confirm it.
2. A monthly ledger extract balances debits and credits but is £25,000 lower than the general-ledger control total. Develop a systematic investigation and reconciliation plan.
3. Design an Excel and Power Query solution for consolidating three subsidiaries whose customer names, currencies, date formats, and product codes differ. Specify the required master data, transformations, validation tests, and governance controls.

Multiple Choice Questions with Answers

Question 1

Which characteristic describes the grain of a dataset?

A. The colour applied to the worksheet

B. What one row represents

C. The number of formulas in a workbook

D. The file format used for export

Correct Answer: B. What one row represents

Question 2

Which Power Query operation combines rows from similarly structured tables?

A. Merge

B. Transpose

C. Append

D. Group By

Correct Answer: C. Append

Question 3

Why should a code such as 00125 generally be imported as text?

A. To preserve leading zeroes

B. To increase its numerical value

C. To convert it into a date

D. To allow automatic summation

Correct Answer: A. To preserve leading zeroes

Question 4

Which join retains every row from the primary table and matching records from the secondary table?

A. Inner join

B. Left outer join

C. Right anti-join

D. Full duplicate join

Correct Answer: B. Left outer join

Question 5

Which is an example of master data?

A. A one-time bank payment

B. A monthly journal batch

C. A supplier record

D. A sales invoice line

Correct Answer: C. A supplier record

Question 6

What is the principal risk when a supposedly unique key occurs multiple times in the table being merged?

A. Dates become automatically valid

B. Rows may be unintentionally multiplied

C. All null values disappear

D. The source file becomes encrypted

Correct Answer: B. Rows may be unintentionally multiplied

Question 7

Which test is especially useful for finding transactions with no matching master record?

A. Anti-join

B. Conditional formatting only

C. Sorting by font colour

D. Freeze Panes

Correct Answer: A. Anti-join

Question 8

For a balanced journal measured consistently, which condition should hold?

A. Total debits equal total credits

B. Total assets equal total revenue

C. Every transaction has zero tax

D. Every account has the same balance

Correct Answer: A. Total debits equal total credits

Question 9

Why is a successful Power Query refresh not proof that accounting data is correct?

A. Refreshes always delete source files

B. The query may reproduce omitted or incorrect source data

C. Power Query cannot import any numbers

D. Excel Tables cannot contain dates

Correct Answer: B. The query may reproduce omitted or incorrect source data

Question 10

Which practice best supports data provenance?

A. Overwriting the original extract

B. Deleting source-file names after import

C. Recording the source, extraction date, and transformation history

D. Using unnamed worksheets

Correct Answer: C. Recording the source, extraction date, and transformation history