Credit Card Statement to Excel

Why credit card statements are harder than bank statements

A bank statement has money in and money out and a running balance. A credit card statement is the reverse of that in several ways. The balance is an amount you owe, so a bigger balance is worse. Purchases appear as positive numbers that increase the balance, payments and refunds appear as negative numbers or with a CR suffix, and the page usually shows two dates per line: when you made the purchase and when it posted. Interest, fees and rewards credits are mixed into the same list.

A generic converter that assumes positive means deposit will flip every sign. Statement2QBO treats credit cards as a separate account type, detects them from the wording and layout of the statement, and normalises everything to one rule: negative means money you spent or owe, positive means a payment or credit that reduces what you owe.

The reconciliation for a card

The check is previous balance plus purchases and fees minus payments and credits equals new balance. The converter finds the previous and new balances printed in the summary box of the statement and compares them with the sum of the table. If they match you see a green banner. If they do not, the difference tells you what is missing: a payment listed in a separate box, a rewards credit, or a transaction that wrapped onto a second line.

For statements that do not print both balances, the converter falls back to checking any running balance column and otherwise tells you the table could not be verified.

What you get in the workbook

• One row per transaction with the date it posted, a clean merchant description, and the amount as a signed number.
• Separate Debit and Credit columns so spend and payments can be summed independently. For a card, Debit is a charge and Credit is a payment or refund.
• A summary sheet with the opening (previous) balance, total money in, total money out, net change and closing (new) balance from the statement.
• Text that cannot run as a formula, even if a merchant name begins with an equals sign or a plus.

Converting to QuickBooks or Xero

For QuickBooks, download a QBO file. It is written with the credit card message set, so QuickBooks imports it into a credit card account and shows purchases as charges and payments as payments. Choose the credit card account on the upload screen, not a bank account. For Xero, use the Xero CSV and import it to the credit card account. For other software, OFX and QFX use the same credit card structure.

If your software shows charges as deposits after the import, the account was probably chosen as a bank account. Re-import into a credit card account, or flip the signs in the converter and re-export.

Which date is used

Many card statements show a transaction date and a posting date. The converter takes the first date on each line, which on most card statements is the transaction date you will recognise from receipts. If your accountant wants posting dates instead, check the dates in the preview and edit the Date column before exporting.

Statements that print only day and month take their year from the statement period, including periods that cross the new year, so a December charge on a January statement is dated in the previous year.

Typical uses

• Expense reports where a spreadsheet is required.
• Month-end bookkeeping when a card feed is not connected.
• Tax preparation: sorting a year of card spending by category.
• Splitting business and personal charges by hand in Excel.
• Checking rewards, foreign transaction fees and interest charges over a year.

More converters