If you do bookkeeping for Canadian small businesses, you've seen it: the client sends twelve months of bank statements as PDFs, and someone has to get every transaction into a spreadsheet. Here are four ways to do it, from free to fastest, plus a checklist for making sure the numbers are right.
1. Ask for a CSV instead
Most Canadian banks, including ATB, TD, RBC, BMO, Scotiabank, and CIBC, let account holders download transactions as a CSV file from online banking. If your client can log in and export, this is the cleanest data you'll get.
The catch: exports often only go back a limited number of months, and many clients find it easier to forward the PDF they already have. For older periods or closed accounts, the PDF is usually all there is.
2. Use Excel's built-in PDF import
In Excel for Microsoft 365 on Windows, go to Data → Get Data → From File → From PDF. Excel shows the tables it found on each page, and you can load or combine them.
- Works well on statements with simple, consistent table layouts.
- Struggles when descriptions wrap onto two lines, or when debits and credits share one column.
- You'll usually need to clean up headers, page breaks, and date formats.
3. Copy and paste (or retype)
For a single short statement, copying from the PDF into Excel and using Text to Columns can work. For anything longer, it's the slowest and most error-prone option, and it's where most year-end hours go.
4. Use a converter or document-capture tool
Statement converters read the PDF and output a structured spreadsheet automatically. Some document-capture tools used by bookkeepers can also fetch or process statements; see our comparison of receipt and document apps. If you handle statements in bulk and nothing off the shelf reads your clients' banks well, a custom converter built for your format is an option.
Tip: whatever method you use, test it on one statement from each bank your clients use before running a full year through it.
Check the output before you trust it
| Check | How |
|---|---|
| Balances reconcile | Opening balance + credits − debits should equal the closing balance on the statement. |
| Transaction count | Compare the row count to the statement. Wrapped descriptions sometimes create extra rows. |
| Year rollover | Statements spanning December to January often show dates without a year. Check January dates landed in the right year. |
| Signs | Make sure withdrawals are negative and deposits positive (or in the right columns) before importing. |
Which method should you use?
For one or two statements, Excel's PDF import or a CSV export is fine. If statements are a weekly job, the time saved by a converter adds up quickly. See more ways to cut year-end data entry.