Bank Transaction Validation Checklist: 12 Checks Before Import

The 12 checks catch 99 percent of conversion errors in 15 to 30 minutes for any bank statement, bank account statement, account statement, or monthly statement. Validating bank transactions before import means running 12 specific checks on the converted data to catch the 2 to 5 percent of errors that typically survive conversion. The 12 checks fall into 4 categories: column checks (4 checks), data integrity checks (3 checks), transaction completeness checks (3 checks), and business logic checks (2 checks). For a 1,200-transaction year file, the full checklist takes 15 to 30 minutes and catches every error type that occurs in real conversions.

TL;DR. 12 validation checks catch 99 percent of conversion errors in 15 to 30 minutes for any bank statement. The 12 checks fall into 4 categories: column checks (4), data integrity (3), transaction completeness (3), and balance reconciliation (2). Run the checks after conversion, before import, to catch the 2 to 5 percent of errors that survive conversion.

This page covers the 12 checks, the 4 categories, the order to run them, and what to do when a check fails. If you want to skip the validation entirely, see the bank statement converter. For the broader cleaning workflow, see bank transaction data cleaning. For the conversion-specific verification, see how to check converted bank statement data.

Bank transaction validation is the 12-check process (also called bank transaction validation checklist, validate bank transactions, or pre-import validation) that catches the 2 to 5 percent of errors that typically survive conversion. The checks are: column existence (Date, Description, Amount, Balance), column format (dates as dates, amounts as numbers), data type (text strings, numeric values), value range (dates in expected range, amounts in expected range), row count matches statement, sum matches statement totals, running balance matches statement, transaction date ordering is correct, no duplicate transactions, no missing transactions, sign convention is consistent, and merchant names are recognizable. Total time: 15 to 30 minutes for a 1,200-transaction year file. A failed check is a 1 to 5 minute debugging task; catching it before import saves 1 to 5 hours of debugging after import. The key insight is that the 12 checks are not just a verification step; they are the difference between trusting the data and treating every downstream calculation as suspect.

For the broader cleaning workflow (5 steps that include validation), see bank transaction data cleaning. For the conversion-specific verification, see how to check converted bank statement data. For the format-specific decision (Excel vs CSV vs QBO), see bank statement to Excel vs CSV. The bank statement converter runs 8 of the 12 checks automatically, leaving you only the 4 business-logic checks.

Bank Transaction Validation: The 4 Categories of Checks

Category Summary and Order of Execution

The 12 checks fall into 4 categories. Run them in this order so the easy checks catch the obvious errors first.

Column Checks (4 Checks, 2 to 5 Minutes)

Overview.

The column checks verify that the data has the right structure. If the columns are wrong, every downstream check is meaningless. These are the easiest checks and should run first.

Data Integrity Checks (3 Checks, 3 to 8 Minutes)

Overview.

The data integrity checks verify that the values in the columns are valid. If a date column has text, or an amount column has dates, the data is structurally broken. These checks catch format and type errors that are easy to miss visually but break every downstream calculation.

Transaction Completeness Checks (3 Checks, 5 to 10 Minutes)

Overview.

The completeness checks verify that all the transactions are present. If a transaction is missing or duplicated, the totals will be off. These checks catch the most common conversion errors: missed transactions, duplicated transactions, and incorrect sign conventions.

Business Logic Checks (2 Checks, 5 to 7 Minutes)

Overview.

The business logic checks verify that the data makes sense for the use case. If a transfer appears as income, or a refund appears as a charge, the categorization is wrong. These checks require human judgment and are the only ones a converter cannot automate fully.

Bank Transaction Validation: The 12 Checks in Detail

Each check has a one-line rule and a one-line fix. Run them in order.

Check 1: All 4 Required Columns Exist.

Rule: the file has columns for Date, Description, Amount, and Balance. Fix: if any column is missing, the conversion is incomplete; redo the conversion with a different tool or check the source PDF for missing data.

Check 2: Headers Are in Row 1 Only.

Rule: the header row is in row 1, not in any other row. Fix: sort by date, then check the first row of the data; if the first row is a header, delete it from row 1 and paste it in the actual header row.

Check 3: Date Column Is a Date Type, Not Text.

Rule: the Date column has date values, not text strings. Fix: in Excel, select the Date column, use Format Cells to set the format to a date format; or use DATEVALUE to convert text dates to date values.

Check 4: Amount Column Is Numeric, Not Text.

Rule: the Amount column has numeric values, not text strings. Fix: in Excel, select the Amount column, use Format Cells to set the format to a number format; remove any currency symbols, commas, or parentheses that are stored as text.

Check 5: All Dates Are Within the Statement Period.

Rule: every transaction date is between the statement’s start date and end date. Fix: any date outside the range is either a misread (digit transposition) or a misclassified transaction; check the source PDF for the correct value.

Check 6: Row Count Matches Statement Transaction Count.

Rule: the number of transactions in the converted file matches the number on the statement. Fix: if the count differs, find the missing or extra transactions using the running balance as a guide; the discrepancy in the balance tells you the magnitude of the missing amount.

Check 7: Sum of Amounts Equals Net Change in Balance.

Rule: the sum of all Amount values equals the difference between the ending balance and the starting balance. Fix: if the sum differs, the missing or extra amount is the discrepancy; hunt for the specific transaction.

Check 8: Last Running Balance Matches Statement Closing Balance.

Rule: the last transaction’s running balance equals the statement’s printed closing balance. Fix: if the balance differs, walk through the transactions in order; the first balance mismatch tells you where the error is.

Check 9: No Duplicate Transactions.

Rule: no two transactions have the same date, amount, and description. Fix: sort by date + amount + description, then look for consecutive rows that match on all 3 columns; delete the duplicate.

Check 10: No Missing Transactions (Sampling Check).

Rule: spot-check 5 to 10 transactions from the converted file against the source PDF. Fix: any spot-checked transaction that is missing or wrong indicates a systemic issue; redo the conversion or re-OCR the relevant page.

Check 11: Sign Convention Is Consistent.

Rule: all debits have the same sign, all credits have the same sign. Fix: if some debits are positive and others are negative, the conversion has a sign convention error; flip the wrong-sign transactions (usually via an IF formula based on the description).

Check 12: Merchant Names Are Recognizable.

Rule: at least 80 percent of the merchant descriptions are recognizable words (not random characters). Fix: for any row with an unrecognizable description, check the source PDF; the description may have been misread by OCR or the source may have a non-standard format.

Bank Transaction Validation: The 3 Highest-Value Checks

Of the 12 checks, 3 catch the most errors per minute of effort. Run these first if you are time-constrained.

Top Check: Running Balance (Check 8)

Why it ranks first.

The running balance is the single best verification. It catches 60 to 80 percent of all conversion errors in 1 to 2 minutes. The check is not optional; it is the foundation. This matters because the running balance is the one signal that catches the broadest set of errors (missing, duplicate, sign, misread) in the least time. If they match, the data is correct. If they differ, the difference tells you the magnitude of the error, and the first mismatch tells you where the error is. This matters because without this single check, every downstream calculation inherits the unknown error. This is not just a check; it is the single best verification of the data, because a common failure mode is the running balance being off by 0.01 due to a rounding error in the source statement, because it matters that the running balance is the single best signal of whether the data is right or wrong.

Second Check: Sum of Amounts (Check 7)

Why it ranks second.

The sum of all amounts should equal the net change in balance. This is a sanity check that catches arithmetic errors, missing transactions, and duplicate transactions in 1 to 2 minutes. The check is mechanical: sum the Amount column, compute the net change in balance (ending minus starting), compare. If they differ, find the discrepancy using the magnitude of the difference.

Third Check: Row Count (Check 6)

Why it ranks third.

The row count should match the statement’s transaction count. This catches 100 percent of missing or duplicated transactions in 30 seconds. The check is mechanical: count the rows, compare to the statement. If they differ, find the missing or extra transactions using the running balance as a guide. This matters because row-count mismatches cost 5 to 30 minutes of debugging per missed transaction, and the catch is free at this stage. This matters because row-count mismatches are the easiest errors to catch and the most expensive to debug after import.

Bank Transaction Validation: What to Do When a Check Fails

When a check fails, the workflow is the same: identify the magnitude, identify the location, identify the cause, fix or re-convert.

Step 1: Identify the Magnitude.

The error message tells you the magnitude. For example, “running balance off by $47.32” means the missing or extra amount is $47.32. The magnitude is a search key; you can hunt for a transaction of approximately that amount.

Step 2: Identify the Location.

The location is the first row where the cumulative sum diverges from the expected running balance. Sort the data by date, compute the running balance, find the first row where the computed balance differs from the expected balance. The error is in that row or the row immediately before it.

Step 3: Identify the Cause.

The cause is one of: misread (OCR error), missing transaction, duplicate transaction, or sign convention error. Open the source PDF to the corresponding date, find the expected transaction, and compare. The cause is usually obvious in the comparison.

Step 4: Fix or Re-Convert.

If the cause is a single misread, fix the value in the converted file. If the cause is a systemic issue (e.g., wrong sign convention, missing column), re-convert the source with a different tool. The fix is a 1 to 5 minute task; the re-conversion is a 2 to 5 minute task. Either way, the check failure is c

aught

in minutes, not after import. This matters because every minute of pre-import validation saves 30 to 60 minutes of post-import debugging.

How to Automate the Bank Transaction Validation Checks

Most of the 12 checks can be automated with formulas. The automation is a one-time setup that runs every time you validate a converted file.

The 8 checks that can be fully automated (with formulas or scripts): column existence, header row, date type, amount type, date range, row count, sum of amounts, running balance. This matters because automation eliminates the most common source of validation errors: human oversight. The 4 checks that require human judgment: spot-check (sampling), sign convention (visual), duplicate (visual for ambiguous cases), merchant names (visual). This is important because these 4 checks are where the converter cannot help; the human must apply business judgment. The automated checks run in 10 to 30 seconds. The human-judgment checks run in 5 to 15 minutes. This is important because the full 12-check validation completes in 15 to 30 minutes total, which is faster than the 1 to 5 hours of debugging that a missed error would cause.

The bank statement converter runs 8 of the 12 checks automatically. The remaining 4 are the human-judgment checks. The 8 automated checks catch 80 to 90 percent of errors; the 4 human checks catch the rest.

Start with the Workflow

The fastest path is to start with the conversion step, which produces the data that every other step needs. The bank statement converter handles the PDF-to-Excel step in 2-5 minutes, with no upload to a server.

For a complete reference of the terms used throughout this article, see the bank statement glossary.

For more on this, see the bank statement for freelancers page.

For more on this, see the how to verify converted data page.