Canadian Bank Statement to Excel: Layouts and Dates

To convert a Canadian bank statement to Excel, first settle three things: whether the statement is in French or English, which order its dates use, and which currency the account holds. Then convert one account per sheet, label Interac lines clearly, and check each account's balance in its own currency, never mixing Canadian and US dollars.

French and English Canadian statement layouts

Canadian banks may send statements in French or English, depending on the language chosen for the account. The transactions are the same; the labels and the number style change. A French statement may write a space between thousands and a comma before the cents, as in 1 104,20, which Excel in an English setup reads as text.

Labels to recognise

The column and line labels on a French statement translate directly, and a small glossary is enough to map them:

• Date stays Date, sometimes Date de l'opération for the transaction date.
• Description may appear as Description or Détail.
• Retraits are withdrawals, money out.
• Dépôts are deposits, money in.
• Solde is the balance, and solde d'ouverture or solde précédent is the opening balance.

Keep the original French descriptions in the sheet. Translating them by hand invites mistakes and breaks any later search for a payee name as the bank printed it.

A worked example

The three lines below are made-up examples in a French layout. The opening balance is 3 412,50.

To turn each amount into a number in an English Excel, use =NUMBERVALUE(SUBSTITUTE(C2,CHAR(160),""),","," "). The two extra arguments tell Excel the comma is the decimal mark and the space is the group mark. The SUBSTITUTE removes non-breaking spaces, which PDFs often use between thousands and which look exactly like ordinary ones. The results are 250.00, 87.35 and 1104.20. Signed as money in or out, they become 250.00, -87.35 and -1104.20, and the opening balance of 3,412.50 plus those three gives 2,470.95, the closing balance on the statement.

English statements

An English statement uses the comma for thousands and the point for cents, so amounts usually arrive as numbers without help. The date column is where English statements cause more trouble, as the next section explains.

Which date order does a Canadian statement use?

It depends on the bank and the document. Canadian statements can be found with day first, month first, or year first in the ISO style 2026-03-05, and some print month names. Do not assume. Find a date on your statement where the day is above 12; it tells you the order for that whole statement.

A date-order test

Run this short test before converting each bank's statements for the first time:

1. Find a transaction dated on the 13th or later of a month. The position of 13 to 31 in that date is the day.
2. Check the statement period on the first page. Every transaction date must fall inside it.
3. After conversion, sort the date column and look at the first and last rows. They should be the first and last days with activity.
4. Count the months in the date column. A one-month statement should show one month, or two if the period crosses a month end.
5. Repeat for each bank. Two banks in the same household can use different orders.

Fixing a wrong order

If Excel has read the dates in the wrong order, select the column, open Text to Columns, and at the last step choose Date with the order the statement uses, such as DMY or MDY. Values Excel had left as text are then read correctly. Values it had already turned into the wrong date are safer to fix by importing the original file again with the right order set.

Statements that cross a year end

Some statements print only day and month. For a period from mid-December to mid-January, the year has to be taken from the statement period, and the January lines belong to the later year. Check those rows by hand once.

Interac lines on a Canadian bank statement

Interac lines are transactions on Canada's shared debit and transfer network. Interac's page on e-Transfer describes e-Transfer as a way to send money directly from your bank account and says nearly all Canadian banks and credit unions offer it. On a statement, those transfers and debit card purchases are easy to spot by the Interac name.

How the lines tend to read

Each bank words them in its own way, but the pattern is consistent enough to sort with. Transfers sent and received usually carry the e-Transfer name and sometimes the other person's name or a reference. Debit purchases usually show the merchant name, and sometimes a short code for the type of payment. A transfer you sent and later cancelled may appear as an outgoing line followed by a returning credit of the same amount.

A Channel column

Add a column that sorts lines into a few channels, such as e-Transfer sent, e-Transfer received, Debit purchase, Bill payment and Other. A formula with SEARCH on the description fills most rows. With that column, a pivot table shows how much went out by e-Transfer in a month, which is often the figure people want when they are tracking rent, shared bills or money sent to family.

Cancelled and returned transfers

Keep both lines when a transfer is sent and then returned. Each is real, and the balance check needs both. Mark them in a notes column so the pair is easy to explain later.

US dollar accounts beside Canadian dollar accounts

Many Canadians keep a US dollar account alongside their Canadian dollar one, and some banks print both on related statements. Keep them in separate sheets, or in one sheet with an Account column and a Currency column. A US dollar amount and a Canadian dollar amount must never be added together without converting one into the other.

Labelling the currency

Statements show the currency in the account name or the header, not usually on each line. That makes it easy for a row to lose its currency once it is in Excel. Add a Currency column filled with CAD or USD for every row, taken from the header, before you combine anything.

Transfers between the two

A transfer from the Canadian account to the US dollar account shows on both statements, in different currencies, for amounts that do not match. The difference is the exchange rate and any spread the bank applied. Do not try to make the pair cancel. Each line is correct for its own account in its own currency.

Converting for a total

If you need one figure in Canadian dollars across both accounts, add a converted column that applies a rate you choose and note the rate and date in a cell at the top. Keep the original amounts untouched beside it. FastBankConvert is getting ready to open, and it is built to read statement layouts and flag balances that do not add up before you download; the get started page is where to sign up to hear when trials begin.

Can CAD and USD rows share one Canadian balance check?

No. Each account's check uses its own currency: its opening balance plus its own lines must equal its closing balance, all in that account's currency. A combined check would mix two units and prove nothing. Run one check per account, then build any combined view on top of checks that pass.

The per-account check

With an Account column and signed amounts, a SUMIFS per account gives the net change: =SUMIFS(F:F,A:A,"USD account"). Add the opening balance typed from that account's statement and compare with the closing balance. Repeat for the CAD account. Both should be zero differences before you do anything else.

When a long statement is involved

Some households convert a year of statements at once to answer a tax question. A year of two accounts can run to many pages. Converting a large PDF of statements to Excel covers how to split and stitch months so the per-account checks still work at that size.

Canadian statements that are not bank account statements

Some documents from Canadian banks look like statements but are not lists of bank transactions: registered savings plan statements, investment account summaries, mortgage statements and credit card statements. Each needs its own sheet and its own check, and some are better summarised by hand than converted line by line.

Investment and registered accounts

A registered retirement or tax-free savings account statement lists contributions, holdings and market values. Those values change without any transaction, so a balance check built for a bank account will not work. Retirement plan statements elsewhere share this shape, and the guide to converting a 401(k) statement to Excel explains how to handle holdings and contributions as separate tables.

Credit unions and other banks

Credit unions in Canada issue statements too, and one document can hold several accounts. Converting a credit union statement to Excel covers that layout. If you also bank abroad, converting an Australian bank statement to Excel shows the same steps for a country with its own date order and wording.