Convert U.S. Bank Statements to Excel and CSV

Why a U.S. Bank statement is hard to convert

Subtotals sit in the middle of the data. Each group closes with a total line, and a tool that copies every line with a number in it will add those totals as if they were purchases. Your sheet then counts the same money twice.

A billing cycle does not follow the calendar. One cycle can start in one year and end in the next, and a row may not carry a year of its own. A converter that guesses the current year gives you December rows dated eleven months in the future.

Signs are easy to lose. A statement can mark a negative amount in a way a generic reader does not expect, and then a withdrawal shows up as income. Excel can also treat an unusual number as text unless it is converted first.

A reference number follows many descriptions. It can run into the merchant name, so two columns that look separate on the page turn into one run of characters when the text is copied out.

About U.S. Bank statements

• U.S. Bank provides checking statements as PDFs in online banking, and customers can ask for paper copies in the mail. Many people scan the paper copies for their records.
• A statement covers one cycle, so a year of records means a dozen files that all need to be merged into one list.
• Bookkeepers, tax preparers and people applying for credit are the usual reasons to turn the PDF into rows.

How we convert it

• Deposits are positive. Withdrawals of every type are negative.
• Amounts that the statement marks as negative come out as negative numbers.
• Every row gets the full year, and a cycle that crosses from December into January keeps each row in the right year.
• Subtotal lines and the balance summary table are left out of the transactions.
• 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

Quarterly tax work. Combine three cycles into one sheet, sort by date, and filter the purchases by merchant to pull out the deductible spending.

Year-end cleanup. Because the year is correct on every row, you can sort a full twelve months into a single list without a December row jumping to the wrong end.

Import to accounting software. Load the CSV into QuickBooks and categorize the rows there. Withdrawals are already negative, so nothing needs flipping.

Good to know

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

Questions