Convert Bank of America Statements to Excel and CSV

Why a Bank of America statement is hard to convert

The rows are grouped by type, not by day. A grocery purchase from the sixth of the month and a payroll deposit from the third are in different groups, so the output from a simple converter is out of order and mixes up inflows and outflows. You want one list you can sort by date yourself.

Paper checks have almost no description. A check usually shows only a number and an amount, so a converter built around merchant names can skip it or glue the number onto the neighboring row. Checks are often the payments an accountant asks about first.

Totals and balance tables look like data. Every group ends with a subtotal, and the statement closes with a table of balances listed side by side. Copied into a spreadsheet, those lines turn into extra rows that double count money.

Long statements run across many pages, and a group can break in the middle. The rows at the top of the next page have no heading of their own, so a page-by-page reader cannot tell whether they are credits or debits.

About Bank of America statements

• Bank of America sends checking statements as PDFs through online banking, and also by mail. People who keep paper copies often scan them, and a scan has to be read with OCR before anything can be extracted.
• A statement covers one month, so most people who need a full year of records end up with twelve separate files.
• Business owners, landlords and anyone filing a loan or tax package are the usual reason to move these statements into a spreadsheet.

How we convert it

• Each group of rows gets the right sign: deposits and other additions are positive, while withdrawals, checks and service fees are negative.
• Subtotal lines and the table of daily balances are not turned into transactions.
• Every date is returned with the full year.
• A check shows up as its own row with its number in the description.
• The second line of a wrapped description is not counted as a separate transaction.
• Scanned pages are read with OCR, and sideways pages are turned upright.

Every conversion also checks each page against the page image, and lines that continue a wrapped description are not counted as extra transactions.

The result is a CSV with Date, Description and Amount columns (plus Balance when the statement has one) that opens in Excel and imports into Google Sheets and QuickBooks. You pay per transaction, not per page. See plans and pricing.

What people do with the file

Tracing a payment. Put the CSV in a spreadsheet, sort by date, and find the transfer or check you are looking for without paging through a PDF. Filter the description column for a payee name to see every payment to it.

Preparing for a loan or a lease application. Lenders often ask for several months of statements. A sorted sheet lets you add up total deposits for each month in one formula and spot the months that look different.

Small business books. Import the CSV into QuickBooks and categorize the fees, transfers and checks there. Because checks and fees come out negative, you do not have to flip any signs before the import.

Good to know

• Scans can contain OCR digit errors. Compare totals with the statement.
• Bank of America credit card statements and business accounts may be laid out differently from the checking statement described here.

Questions