Convert credit card statements to Excel

Are charges positive or negative?

Card issuers print amounts from the bank's point of view: a purchase increases what you owe, so it is often shown as a plain positive number and a payment as a credit. Copy that into a spreadsheet next to your checking account and the signs fight each other.

Every export uses one rule instead, the same as for a bank account: money you spend is negative, money that comes back to you is positive.

Because the signs are consistent, SUMIF over a category gives your spending directly, and a card and a checking account can be combined in one workbook.

Previous balance, new balance and minimum payment

A credit card statement shows several totals, and only two of them describe the transactions. The previous balance is what you owed when the last statement closed. The new balance, also called the statement balance, is what you owed when this one closed. Everything in between is the list of transactions.

That gives the check every statement must pass: previous balance + purchases + fees + interest − payments − credits = new balance. If the numbers don't agree, the rows to look at are flagged with their page number.

The minimum payment, payment due date, credit limit, available credit and interest rates are not transactions, so they aren't exported as rows. Neither are pending charges: they appear on the next statement once they post. The current balance in your card app can differ from the statement balance for the same reason, because it includes activity since the statement closed.

Foreign-currency purchases

When you pay in another currency, most issuers print the original amount, the currency and the exchange rate under the merchant line, and bill you in the card's currency. The billed amount is the one exported in the Amount column, because it is the one that adds up to the new balance.

The details printed with the line, such as 125.00 EUR at 1.0821, stay in the Description column, since the full text of each line is kept. A foreign transaction fee printed as its own line becomes its own row, usually categorized as Bank Fees; some issuers add the fee into the purchase amount instead.

Several cards or cardholders on one statement

Business and family cards often list several cardholders, each with their own section of charges, under one account balance. That is still one account, so it becomes one statement with one balance check, not one statement per card.

Rows stay in the order they are printed, so each cardholder's charges stay together, but there is no separate cardholder column. If you need spending per cardholder, ask the issuer whether it can provide a separate statement per card. A PDF that really contains separate accounts, each with its own opening and closing balance, is split into one statement per account.

Credit card statements in QuickBooks, Xero and other apps

Accounting software treats a credit card as its own account, so import card statements into a credit card account, not into checking.

QuickBooks Online: export the QuickBooks CSV (Date, Description, Amount), then in Bank transactions choose Link account → Upload from file and pick the credit card account. Intuit's limits are a file in English, 350 KB or less and up to 1,000 lines per upload (Intuit's upload guide). QuickBooks takes the sign from the file, so before you accept the batch, check that a known purchase shows as a charge and your payment as a payment. More in bank statements to QuickBooks.

QuickBooks Desktop, Quicken and other apps: the .qbo and OFX files mark the account as a credit card account, and the QIF file uses Quicken's credit card account type, so the importing app can tell it is a credit card account.

Xero: export the Xero CSV and import it into the credit card account. Charges are written as negative amounts with the transaction type Debit, payments as positive Credits. See importing statements into Xero.

Is a credit card statement the same as a bank statement?

No. A bank statement covers an account that holds your money, and its balance is what you have. A credit card statement covers money you borrowed, and its balance is what you owe, plus a minimum payment, a due date and interest. Lenders who ask for bank statements, for example for a mortgage application, usually mean your checking and savings accounts.

For bookkeeping you need both, and they overlap in one place: the payment to the card. It leaves your checking account and arrives on the card, so it appears on both statements. Categorize both sides as Transfers so the payment isn't counted as spending twice; the purchases on the card are the real expenses.

A year of card spending in one workbook

Select twelve monthly statements, or a card and a checking account together, and export them as one Excel file with every transaction in date order, optionally limited to a date range. The Summary sheet totals spending by category and by month, which is usually what an expense report or tax return needs.

Categories are yours to change: rename or add them, and when you correct a category, turn it into a rule so the same merchant is categorized the same way on every future statement. See how transactions are categorized. For American Express, see Amex statements to Excel.

Questions