BMO Bank Statement to Excel: A Practical Guide

TL;DR To get a BMO statement into Excel, save the statement PDFs for every period you need, convert them so each transaction is one row, and then do three things before anything else: confirm the date format, turn withdrawals and deposits into one signed Amount column, and check that the opening balance plus all activity equals the closing balance. Use online banking downloads for quick recent checks, and statement PDFs when you need complete, official records for an accountant, a lender, a tax return or a reconciliation. Credit card statements follow the same process, but the signs run the other way.

BMO (Bank of Montreal) is one of the largest banks in Canada, and it also has a large US presence after bringing BMO Harris and Bank of the West under the BMO name. That means a lot of people, on both sides of the border, end up with the same job: a folder of BMO statement PDFs that need to become a spreadsheet. A freelancer in Toronto wants figures for their T2125. A small business in Chicago wants a clean year of transactions for its bookkeeper. A household wants to see where last year's money went. A mortgage applicant wants to check what an underwriter will see.

This guide covers the whole process: choosing between a download and a statement, what to expect on a BMO statement, three ways to convert it, the clean-up steps that matter, how to prove the result is complete, and what to do with the data once it's in Excel. Online banking screens change over time and differ between BMO's Canadian and US services, so we describe them in general terms rather than giving click-paths that may be out of date by the time you read this.

Download or statement: which one do you need?

Most online banking services, BMO's included, give you two different ways to get transactions out.

A transaction download exports recent activity as a file, typically CSV or a format intended for personal finance or accounting software. It's quick and convenient. The limits are that downloads usually only cover a recent window, may show pending items differently from the final statement, and don't carry the official opening and closing balances for a statement period.

Statement PDFs are the bank's formal record. Each covers a defined period, shows the starting and ending balances, and lists every transaction that posted in between. When an accountant, lender, landlord or immigration officer asks for "bank statements", this is what they mean. Statements also usually go back further than downloads.

A simple rule: if you need to reconcile, catch up on bookkeeping, or give your figures to someone else, work from statements. If you just want to see last month's spending, the download is fine.

Name each PDF predictably, for example bmo-chequing-2026-03.pdf or bmo-mastercard-2026-03.pdf. When you have two years of statements across three accounts, sortable names are what stop you from missing a month. If you need statements older than online banking shows, our guide to getting old bank statements covers the usual routes.

What a BMO statement usually looks like

Layouts differ between Canadian and US accounts, between personal and business accounts, and over time. In general, though, a deposit account statement contains:

• A header with the account holder's name, the account number (often partly masked) and the statement period.
• A summary showing the opening balance, total deposits or credits, total withdrawals or debits, and the closing balance.
• A transaction list with a date, a description, amounts in separate withdrawal and deposit columns (or a single amount column with signs), and often a running balance.

A few things are worth checking on your own statements before you convert.

Separate amount columns. Many chequing statements put money out and money in into different columns. That's easy to read but awkward in Excel. You'll combine them into one signed column below.

Running balance. If your statement prints a balance after each transaction, or after each day's last transaction, that column is your best tool for proving the conversion is right, line by line.

Wrapped descriptions. Long payee names, e-Transfer details or bill payment references sometimes wrap to a second line. A good conversion keeps the wrapped text on the same row as the amount.

Opening balance lines. Statements often begin with an "opening balance" or "balance forward" line, and some repeat a similar line at the top of each page. These are not transactions and must not be counted in totals.

Credit card statements look different. They typically show a previous balance, payments and credits, purchases, interest and fees, and a new balance. Purchases increase what you owe; payments reduce it. We cover the sign convention in a separate section.

Three ways to convert a BMO statement

Copy and paste

For a single short page, you can select text in a PDF viewer and paste into Excel. Expect columns to drift, wrapped descriptions to land on their own rows, and amounts to arrive as text. It works for one page, and it is miserable for a year of statements.

If you try it, paste into a blank sheet, then use Text to Columns or the TEXTSPLIT function in recent Excel to separate fields. Check every page. One shifted column can silently swap withdrawals and deposits.

Power Query

Excel's Power Query on Windows can read tables directly from a PDF. On statements with a clean table layout it often does a reasonable job, but you'll still need to remove repeated headers, filter out balance-forward lines, combine the amount columns and set the date type. Our Power Query bank statement guide walks through it.

A statement converter

SheetStatement reads the PDF, keeps dates and descriptions on one row, produces a signed amount, and checks the opening balance plus activity against the closing balance before you export. You can download Excel or CSV, or files formatted for QuickBooks and Xero. The free plan includes 3 pages a month, enough to test a real statement. Starter ($9), Pro ($19) and Business ($39) monthly plans cover larger volumes.

Whichever route you take, aim for this target layout:

The figures are illustrative. Use the actual columns on your own statement.

Step 1: confirm the date format

Dates are the most common silent error in bank statement conversions, and they deserve extra care with BMO because people on both sides of the border use the bank. Canadian documents may use month-name dates, year-month-day, or day/month/year depending on the context, while US documents generally use month/day/year. If your statement shows short dates such as "Mar 05" without a year, the year has to come from the statement period.

The danger is a date like 04/07/2026. Depending on the locale, Excel will read it as April 7 or 4 July. If your Excel locale doesn't match the statement, some dates will be swapped and others left as text, and the column will still look plausible.

How to fix it:

• Power Query: right-click the date column, choose Change Type, then Using Locale, and pick the locale that matches your statement (for example English (Canada) or English (United States)).
• Text to Columns: select the column, choose Delimited, go to step 3, choose Date and set the order (MDY, DMY or YMD) to match the statement.
• Formula for short dates without a year: if column A holds text like "Mar 05" and the statement year is in cell H1:

=DATEVALUE(A2 & " " & $H$1)

For statements that cross December and January, the January lines belong to the next year. A simple fix is a helper column that adds one to the year when the month is lower than the statement's starting month. Our guide to fixing bank statement dates in Excel covers these awkward cases with formulas.

A quick sanity test: sort oldest to newest. The first and last dates should fall inside the statement period. Anything outside means the format is still wrong.

Step 2: create one signed Amount column

Two amount columns make totals, pivot tables and imports harder than they need to be. Add a single Amount column where deposits are positive and withdrawals are negative:

=N(D2) - N(C2)

Here C is withdrawals and D is deposits. N() treats blank cells as zero, so rows with only one value don't throw errors.

If amounts arrived as text, perhaps with commas or a dollar sign, convert them first:

=VALUE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",",""))

Some statements show negative values in parentheses, such as (112.48). If you see those, our guide to converting parentheses to negative numbers in Excel has formulas for that case. And if a piece of software wants separate columns rather than one signed amount, our guide to separating debit and credit columns shows how to go back the other way.

Step 3: prove the statement balances

Before you categorize anything, prove the conversion is complete. Format the data as a table (Ctrl+T) and name it Tx. Then:

=ROUND(OpeningBalance + SUM(Tx[Amount]) - ClosingBalance, 2)

The result should be exactly 0. If it isn't, the size of the difference is a strong clue:

• Equal to one transaction: a row is missing, often the first or last line on a page.
• Twice one transaction: a sign is wrong; a deposit landed in withdrawals or the reverse.
• A round number like 100 or 1,000: a digit was misread, more likely on a scanned statement.
• A few cents: rounding, or a stray decimal place.

Line-by-line checks with the balance column

If the statement prints a running balance on every line, add a check column:

=ROUND(F1 + E2 - F2, 2)

where E is Amount and F is Balance. Every row should return 0. The first non-zero row shows exactly where the conversion went wrong.

If the balance is only printed at the end of each day, compare the printed balance with a calculated running balance at those points instead. Our guide to the running balance formula in Excel explains both versions.

Do this check for every statement, not just the first one. Layouts can shift slightly between months, and a page break in an unusual place is exactly where a row tends to go missing.

BMO credit card statements

Credit card statements need a different sign convention. On a card, purchases, interest and fees increase the balance you owe, while payments, refunds and credits reduce it. The statement's own math usually looks like:

Previous balance + purchases + interest + fees - payments - credits = new balance

For a spreadsheet, choose one convention and stick to it:

• From your point of view (spending is negative): purchases, interest and fees negative; payments and refunds positive. This matches how a chequing account looks and makes combining accounts easier.
• From the card's point of view (balance owed): purchases positive, payments negative. This is closer to how the statement prints, and it makes the balance check easier.

Then adjust the balance check accordingly. With the card's point of view, PreviousBalance + SUM(Amount) = NewBalance. Our guide to credit card reconciliation in Excel goes deeper on matching card statements to your records, and if you're importing card activity into accounting software, see importing credit card transactions into QuickBooks Online.

Remember that card payments appear twice in your data if you convert both the chequing account and the card: once as money leaving chequing and once as a payment on the card. That's correct, but when you summarize spending, treat these as transfers rather than expenses, or you'll count the same purchases twice.

Worked example: a quarter of chequing statements

Say you have three monthly chequing statements for January to March and want one clean sheet.

1. Convert all three PDFs. Put each statement's rows on the same sheet, adding a Statement column with values like 2026-01, 2026-02, 2026-03.
2. Remove balance-forward lines. Filter the Description column for "opening balance" or "balance forward" and delete those rows, after writing down each statement's opening balance.
3. Fix dates using the approach above. Sort oldest to newest.
4. Add the Amount column with =N(D2)-N(C2).
5. Check each statement separately. Build a small summary table:

Use SUMIFS(Tx[Amount], Tx[Statement], A2) for the middle column. The difference column is =ROUND(B2+C2-D2,2).

6. Check continuity. Each month's opening balance should equal the previous month's closing balance. If February opens at a different figure from January's close, you are missing a statement or have one from the wrong account.

Only once every row in that summary shows 0.00 should you move on to categories, pivot tables or imports. For more on stitching statements together, see combining multiple bank statements into one Excel file.

What to do with the data next

Categorize. Add a Category column. For recurring payees, a lookup table and an XLOOKUP or keyword-matching formula saves a lot of typing. Our guide to Excel formulas for categorizing transactions has several approaches.

Summarize. A pivot table with months across the top and categories down the side shows spending patterns quickly. See our bank statement pivot table guide.

Import into accounting software. If you use QuickBooks Online, follow our guide to importing a bank statement into QuickBooks Online. For Xero, check the Xero CSV import format. For both, remove balance-forward lines and make sure the date format matches what the software expects.

Prepare for tax. For Canadian sole proprietors, a categorized year of transactions is a useful working file for business income and expenses; for US filers, the same file helps with Schedule C or for your preparer. This is general information, not tax advice. Our guide to bank statements for tax preparation covers what preparers commonly ask for.

Reconcile. If you keep books, compare the converted statement with your ledger. Our guide to reconciling a bank statement in Excel walks through the full process.

Common problems and fixes

Amounts show as text and won't sum. Look for leading or trailing spaces, non-breaking spaces, currency symbols or commas. VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) handles most cases.

Some transactions appear twice. This often happens when a page header or a wrapped description is mistaken for a new transaction, or when you combine statements with overlapping periods. Our guide to removing duplicate transactions in Excel helps, but be careful: two genuine identical transactions on the same day (two coffees, two identical transfers) are not duplicates. Use the balance check to decide.

Foreign currency transactions. If you have a US dollar account at a Canadian bank, or card purchases in another currency, keep each account's statement in its own currency. Don't mix CAD and USD rows in one Amount column without a Currency column and a clear conversion method.

Scanned or photographed statements. Older statements requested from a branch may arrive as scans. OCR works, but check the balance on every page. Our guide to OCR accuracy on scanned bank statements covers what to watch for.

The opening balance doesn't match last month's closing balance. You're probably missing a statement, have one from another account mixed in, or there's a correction on the bank's side. See opening balance does not match bank statement.

Convert your BMO statements

Upload your BMO statement PDFs to SheetStatement, review the rows and the balance check, and download Excel or CSV. Try it with the free plan's 3 pages a month before deciding whether you need a paid plan.

FAQ

Can I download BMO transactions straight to Excel?

Online banking usually offers a transaction download in a spreadsheet-friendly format for recent activity. For full statement periods with official opening and closing balances, or for older months, convert the statement PDFs instead.

Why are some of my dates wrong after converting?

The statement's date format and Excel's locale probably don't match. Convert the date column using the correct locale or order (MDY, DMY or YMD), and take the year from the statement period if dates are printed without one.

How do I combine withdrawals and deposits into one column?

Add a column equal to deposits minus withdrawals, wrapping each in N() so blank cells count as zero. Deposits become positive and withdrawals negative.

How do I know nothing is missing?

Check that the opening balance plus the sum of all amounts equals the closing balance, then use the running balance column to check each line. The first line that fails shows where the problem starts.

Does this work for BMO US accounts formerly with BMO Harris or Bank of the West?

The same approach works for any statement that has dates, descriptions, amounts and balances. Older statements from before an account changed names may use a different layout, so run the balance check on each layout.

Can I use the spreadsheet for my tax return?

It's a useful working file for preparing figures, and many accountants are happy to receive one alongside the original PDFs. This is general information, not tax advice; check with your accountant or the relevant tax authority.

Is it safe to upload bank statements?

See the security page for how SheetStatement handles files, and read any service's policy before uploading financial documents.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39 for higher volumes.