Bank Statement Import and Matching Guide

Overview

Import Bank Statement helps you compare a bank statement with transactions already recorded in QBM. It reads supported statement files, identifies possible matches, and presents the evidence for review.

This workflow is different from the general Excel or CSV data-import utilities. Always open bank-statement matching from the active Bank Reconciliation screen.

Audience, Applies To, and Purpose

Before You Begin

• Obtain the statement for the correct bank account and reconciliation period.
• Confirm the selected account is set up as a bank account in QBM.
• Confirm all receipts, payments, transfers, charges, and other bank activity that should exist in QBM have been recorded.
• Matching considers existing unreconciled transactions; already reconciled activity is not offered as a new match.
• Open or begin the reconciliation with the correct Bank Account, statement date, and ending balance.
• Make sure your user has permission to create or edit Account Reconciliation work. If Import Bank Statement is disabled, ask an administrator to review your reconciliation access.
• Save an unencrypted copy if the bank supplied a password-protected Excel workbook.
• For the first import from a bank, test a short statement or a copy containing a few representative rows.

Normal Flow

1. Begin or continue the correct bank reconciliation.
2. Click Import Bank Statement.
3. Browse to an .xlsx, .xls, .csv, .txt, or .tsv file.
4. Click Analyze Statement.
5. Review every Suggested, Ambiguous match, Unmatched, and Invalid row result.
6. Keep only the Suggested rows you accept selected.
7. Click Apply Selected Matches.
8. Back on Bank Reconciliation, review all cleared items, ending balance, cleared totals, interest, service charges, and difference.
9. Click Reconcile only when the statement and QBM agree.

Supported Files and Limits

Practical Limits

• Maximum delimited-statement transaction rows: 10,000. Split a larger CSV, TXT, or TSV statement into smaller period files. An oversized statement is rejected as a whole rather than partially analyzed.
• Maximum delimited-text size: approximately 16 million characters, with a maximum of 512 KB per record and 256 KB per field.
• Maximum Excel file size: 64 MB.
• Maximum Excel worksheets: 32.
• Maximum allocated Excel rows per worksheet: 10,000 in total, including headings and preamble rows; maximum allocated columns: 256; maximum allocated cells: 1,000,000.
• Excess formatting in otherwise empty Excel rows or columns can count toward workbook limits. Remove unused formatting when a small-looking workbook is rejected as oversized.
• For Excel, place recognizable statement headings within the first 100 populated rows.
• For CSV, TXT, and TSV, place recognizable headings within the first 25 nonblank records.
• UTF-8 or Unicode text is recommended. English and Arabic statement text can be retained.
• Blank transaction rows are ignored.

Accepted Statement Columns

Heading matching ignores spaces, punctuation, and letter case. For example, Reference No., reference_no, and REFERENCE NO are treated alike.

Minimum Useful Layout

Include one recognized date column and one valid amount arrangement:

• Date + signed Amount; or
• Date + Debit + Credit; or
• Date + unsigned Amount + Direction.

Add Reference, Cheque Number, Description, and Counterparty whenever the bank supplies them. These fields greatly improve safe suggestions.

Date, Amount, and Currency Rules

Dates

• Recommended format: YYYY-MM-DD, for example 2026-08-03.
• Year-first dates, day-first dates, unambiguous month-first dates, English month names, two-digit years, and common date/time values are accepted.
• Arabic-Indic digits are accepted.
• A numeric date such as 03/04/2026 is ambiguous because it could mean 3 April or March 4. It is shown for review but is not eligible for automatic selection. Change it to 2026-04-03 or 2026-03-04.

Amounts and Direction

• For a signed Amount column, use positive values for deposits and negative values for payments.
• For separate Debit and Credit columns, enter a positive value in only one of them on each row.
• For an unsigned Amount plus Direction, use D, DR, Debit, Withdrawal, Money Out, Paid Out, C, CR, Credit, Deposit, Money In, or Paid In consistently.
• Parentheses, a trailing minus sign, DR/CR suffixes, and currency codes before or after the value are accepted, as are common decimal/grouping styles and Arabic numeric separators. Examples include 1,250.00, 1.250,00, (250.00), 250.00 DR, AED 1,250.00, and 250.00-.
• In a comma-separated file, quote a value that contains a grouping comma, for example "1,250.00", so the comma remains inside the amount field.
• Avoid visually ambiguous values such as 1.234 when the bank could mean either 1.234 or 1,234. Use an explicit currency-precision value such as 1234.00, or retain the typed numeric value in Excel.
• The sign must agree with QBM: a statement payment cannot match a QBM deposit, even when the absolute amount is the same.

Currency

• The Currency column is optional.
• If it is present, use the account's currency code consistently, for example AED.
• A missing currency does not invent a different currency. A supplied incompatible currency requires review and cannot be applied as a suggestion.

Step-By-Step Procedure

1. Open the reconciliation. Go to Accounts > Reconcile Bank Account, then begin or continue the correct account and statement period.
2. Confirm the context. Verify the bank account, statement date, ending balance, and optional date filter before importing. The import uses this active account and includes the complete calendar days in the selected date range.
3. Open statement import. Click Import Bank Statement. The Account value is carried from the reconciliation and cannot be changed in the import window.
4. Select a file. Click Browse, choose a supported statement, and verify the displayed file path.
5. Analyze. Click Analyze Statement. QBM reads the rows and compares safe rows with existing QBM bank activity; it does not change the reconciliation during analysis.
6. Check the summary. Review the counts shown as Imported, Suggested, Ambiguous, Unmatched, and Needs review.
7. Review every row. Compare the statement date, reference, description, payment/deposit, proposed QBM number, QBM date, payee, amount, match reason, and text similarity.
8. Control the selection. Unique Suggested matches begin selected. Uncheck any suggestion you do not accept. Ambiguous, Unmatched, and Invalid rows cannot be selected in this window.
9. Apply accepted matches. Click Apply Selected Matches. If nothing valid is selected, the button remains unavailable or QBM asks you to select a unique match.
10. Complete manual review. On Bank Reconciliation, resolve unmatched statement activity, missing QBM transactions, bank-only charges, interest, timing differences, and ambiguous items through the normal accounting workflow.
11. Finalize separately. Verify cleared payments, cleared deposits, ending balance, and Difference. Click Reconcile only after the control totals agree.

Review Screen and Statuses

Review Columns

The review grid supports filtering and a field chooser. Only Selected is editable; all statement and proposed-match evidence is read-only.

Status Meanings

How Suggestions Are Chosen

QBM is deliberately conservative. It recommends only a unique existing transaction supported by strong evidence.

1. Exact signed amount is required. Comparison uses the selected account's currency precision, and payment versus deposit direction must agree.
2. Currency must be compatible when the statement supplies one.
3. The candidate date must be within five calendar days of the statement booking or value date and inside the active reconciliation search period.
4. Strong identity is preferred: exact cheque number and amount, or exact reference and amount.
5. Exact date, exact amount, and strong payee text can support a unique suggestion.
6. Nearby date, amount, and similar text is review evidence only; it does not become a selectable suggestion by itself.
7. Date and amount alone are not enough for automatic selection.
8. One QBM transaction is not recommended twice. A stronger unique claim wins; competing or tied claims stay for review.
9. Duplicate statement identities are held for review so repeated source lines cannot silently clear the same activity.

The Match Reason may show wording such as Exact cheque and amount, Exact reference and amount, Exact date, amount, and payee, or Nearby date, amount, and similar text, followed by supporting evidence.

Worked Examples

Example 1: Excel (.xlsx or .xls)

Place these headings in the first usable transaction worksheet. Keep dates and amounts as normal cell values.

• The first row is a deposit because the amount is positive.
• The second and third rows are payments because the amounts are negative.
• If the bank fee is not already in QBM, it will normally remain Unmatched. Record it through the normal approved workflow, then review the reconciliation again.

Example 2: Comma-Separated CSV

Transaction Date,Amount,Currency,Reference,Cheque Number,Payee,Description
2026-08-01,189.00,AED,RCT45049,45049,Customer A,Online order receipt
2026-08-02,-29.75,AED,EXP007821,,Office Supplier,"Office materials, stationery"
2026-08-03,-25.00,AED,FEE-0803,,Bank,Monthly bank fee

The quotation marks keep the comma inside Office materials, stationery as part of the description rather than starting another column.

Example 3: Semicolon-Separated TXT With Bank Preamble

sep=;
Example Bank
Account;****4321
Statement Period;2026-08-01 to 2026-08-31

Booking Date;Debit;Credit;Reference;Beneficiary;Narration;Currency
2026-08-02;29.75;;EXP007821;Office Supplier;Office materials;AED
2026-08-03;;500.00;RCT-0803;Customer B;Customer payment;AED

QBM finds the recognizable heading row after the preamble. Only one of Debit or Credit contains a value on each transaction row.

Example 4: Tab-Separated TSV

Posted Date	Debit	Credit	Transaction Ref No	Counterparty	Details	CCY
2026-08-02	29.75		EXP007821	Office Supplier	Office materials	AED
2026-08-03		500.00	RCT-0803	Customer B	Customer payment	AED

Each gap between fields above represents one tab. Save the file with a .tsv extension.

Example 5: Unsigned Amount With Direction

Date,Amount,Direction,Reference,Payee
2026-08-02,29.75,Debit,EXP007821,Office Supplier
2026-08-03,500.00,Credit,RCT-0803,Customer B

Debit makes 29.75 a payment; Credit makes 500.00 a deposit.

Example Outcomes

Expected Result

• The import window displays every usable statement line and keeps unsafe rows visible for review.
• The summary accurately separates Imported, Suggested, Ambiguous, Unmatched, and Needs review counts.
• Only unique Suggested matches are selected and editable.
• Apply Selected Matches marks the accepted existing QBM transactions in the current Bank Reconciliation screen.
• Previously selected manual reconciliation items remain selected.
• No accounting transaction or completed reconciliation is created by Browse, Analyze Statement, Cancel, or Apply Selected Matches.
• The user finishes only after reviewing the final cleared totals and clicking Reconcile.

Final Reconciliation Checklist

• The imported statement belongs to the selected QBM bank account.
• The statement date, ending balance, and optional date filter are correct.
• Every Suggested row was compared with the original statement and the proposed QBM transaction.
• No unwanted suggestion remained selected when Apply Selected Matches was clicked.
• Ambiguous, Unmatched, and Invalid rows were investigated through the normal accounting workflow.
• Bank charges and interest are recorded once only and in the correct accounts.
• Outstanding payments and deposits in transit are understood and supported.
• Cleared payment, cleared deposit, ending-balance, and Difference totals are correct.
• The final Reconcile command is used only after the statement agrees with QBM.
• The statement file is retained or removed according to company security and document-retention policy.

Common Problems

Troubleshooting Decision Tree

1. Can you open Import Bank Statement? If no, verify an active bank reconciliation and writable reconciliation access.
2. Can you select and analyze the file? If no, confirm the supported extension, readable location, unencrypted Excel status, and file limits.
3. Are rows recognized? If no, verify delimiter, heading position, accepted headings, date column, and amount layout.
4. Are rows marked Invalid? Correct ambiguous dates, invalid numbers, conflicting Debit/Credit values, and incompatible currency.
5. Are rows Unmatched? Verify statement period, signed amount, QBM transaction existence, reference, cheque number, and payee.
6. Are rows Ambiguous? Do not force them. Compare source documents and clear the correct QBM item manually.
7. Did accepted rows return to Bank Reconciliation? If no, confirm a Suggested row remained selected before Apply and that access was not changed.
8. Is the Difference correct? If no, continue normal reconciliation review. Import is only a matching aid and does not replace the final control check.

What To Send To Support

If the problem remains after using the decision tree, send:

• The QBM screen name and the exact step that failed: Browse, Analyze Statement, review, Apply Selected Matches, or final reconciliation.
• The file type and approximate row count.
• The heading row and one or two masked example rows that reproduce the issue.
• The status counts shown for Imported, Suggested, Ambiguous, Unmatched, and Needs review.
• A screenshot of the warning or affected review rows with account numbers, customer names, and sensitive references masked.
• The expected result and the actual result.

Security Notes

• Store downloaded statements only in an approved secure location.
• Do not weaken workbook security or share a password. Create an authorized unencrypted working copy only when company policy permits it.
• Verify the selected account and period before analysis; a valid file for the wrong account can still produce misleading review work.
• Review every Suggested row. Automation reduces repetitive work but does not replace accounting approval.
• Delete temporary statement copies according to your organization's retention policy after the reconciliation is complete.

Related Guides

• Banking & Reconciliation Guide
• Bank Reconciliation Lists and Begin Reconciliation Guide
• Bank Transaction Lists Guide
• Import and Export Troubleshooting Guide
• Excel Import Guide — for general record imports, not bank-statement matching.
• CSV Import Guide — for general record imports, not bank-statement matching.

AI Summary and Keywords

AI Summary: Use this guide when a user needs to import an Excel, CSV, TXT, or TSV bank statement into an active QBM Bank Reconciliation. First verify the correct account, period, ending balance, and writable access. Then verify the file type, heading position, recognized date and amount layout, and unencrypted workbook status. Analyze the statement, review all status counts and evidence, allow selection only for unique Suggested matches, apply accepted selections, and finally return to Bank Reconciliation to resolve remaining items and verify the Difference before clicking Reconcile. Import and Apply do not create transactions or finalize reconciliation.

Keywords: Bank Statement Import, Import Bank Statement, Automated Bank Reconciliation, Bank Matching, Analyze Statement, Apply Selected Matches, Suggested, Ambiguous Match, Unmatched, Invalid Row, Needs Review, Text Similarity, Exact Cheque and Amount, Exact Reference and Amount, Excel Statement, XLSX, XLS, CSV, TXT, TSV, Booking Date, Value Date, Signed Amount, Debit, Credit, DR, CR, Reference, Cheque Number, Bank Transaction ID, Counterparty, Narration, Currency, Bank Reconciliation Difference.