Bank Statement Import — CSV, OFX, MT940 & Excel

How It Works

Four steps from bank statement to reconciled books — no manual data entry required

CSV & OFX File Formats by Canadian Bank

Each major Canadian bank exports transaction data differently. Here is what to expect when downloading statements for import.

Canadian banks offer several export formats, but not all formats are available from every bank. The two most common formats are CSV (Comma-Separated Values) and OFX/QFX (Open Financial Exchange). OFX files are structured and include built-in transaction categorization fields, while CSV files are simpler but may require column mapping during import. Here is what each of Canada's Big Five banks typically provides:

Bank Reconciliation Workflow: Step by Step

Bank reconciliation is the process of matching your accounting records against your bank statement to catch errors, missing transactions, and unauthorized charges. For CRA compliance, reconciling monthly is considered best practice. Here is the recommended workflow:

1. Download your bank statement in CSV or OFX format covering the reconciliation period (typically one calendar month).
2. Import the file into your accounting software. iBill automatically parses dates, amounts, and descriptions from all major Canadian bank formats.
3. Review auto-categorized transactions. Transactions matching existing categorization rules are automatically assigned to the correct GL accounts. New or unrecognized transactions need manual categorization.
4. Match transactions to invoices and expenses. Deposits should correspond to recorded customer payments; withdrawals should match recorded expenses or vendor bill payments.
5. Investigate discrepancies. Common issues include: bank fees not yet recorded, interest charges, NSF (non-sufficient funds) returned payments, and timing differences for cheques in transit.
6. Post matched transactions to your general ledger. Once reviewed, all categorized transactions create double-entry journal entries automatically.
7. Document the reconciliation. Save a reconciliation summary showing the opening balance, all reconciled items, and the closing balance. This is your audit trail for CRA purposes.

How Bank Import Supports CRA Audit Readiness

Under ITA Section 230, the CRA requires businesses to maintain adequate books and records. Regularly importing and reconciling bank statements provides several layers of audit protection:

• Complete transaction history -- Every deposit and withdrawal is captured and categorized, so no income or expense is missed from your records.
• Third-party verification -- Bank records serve as independent confirmation of your accounting entries. If the CRA questions an expense, the bank statement corroborates the payment date and amount.
• Revenue completeness -- By matching all bank deposits to recorded invoices, you can demonstrate to an auditor that all income has been reported. Unexplained deposits are a major red flag in CRA audits.
• GST/HST reconciliation -- Matching bank transactions to invoices ensures that all GST/HST collected on sales is properly reported, and all ITCs claimed are supported by actual payments to suppliers.
• Timely record-keeping -- Monthly reconciliation demonstrates to the CRA that you maintain your books contemporaneously, rather than reconstructing records at year-end.

Matching Bank Transactions to Invoices

The most time-consuming part of reconciliation is matching bank deposits to specific customer invoices. Deposits may not match invoice amounts exactly due to partial payments, combined payments for multiple invoices, or bank processing fees. Here are strategies for efficient matching:

• Exact amount matching -- If a deposit matches an outstanding invoice total exactly, the match is straightforward. iBill highlights potential matches automatically.
• Interac e-Transfer matching -- E-Transfer deposits often include the sender's name in the description field, making it easier to identify which client paid.
• Combined payment matching -- When a client pays multiple invoices in one transfer, split the deposit across the relevant invoices during reconciliation.
• Payment reference numbers -- Including an invoice number in your payment instructions helps match deposits later. For example: "Please include INV-2026-045 in your e-Transfer message."

Handling Multi-Currency Transactions

If you receive payments in USD or other foreign currencies, your bank statement will show the converted CAD amount based on the exchange rate at the time of deposit. For CRA reporting purposes, all amounts must be reported in Canadian dollars. The Bank of Canada daily exchange rate is the standard reference. When importing multi-currency transactions, note that the bank's conversion rate may differ slightly from the Bank of Canada rate. Any exchange gain or loss should be recorded as a separate line item in your general ledger under Foreign Exchange Gain/Loss (typically GL account 7100).