Convert a credit card statement to Excel or CSV

A credit card statement looks like a bank statement and behaves like its mirror image. Money you spend increases the balance instead of reducing it, the statement adds purchases and subtracts payments, most issuers print two dates per row and no running balance, and the period rarely lines up with a calendar month. Each of those differences trips up a generic converter and a hand-typed spreadsheet in its own way. This guide explains what to expect on the statement, how to check that the rows you end up with are complete, and how to get them into Excel, QuickBooks or Xero with the signs the software expects.

How a card statement adds up

Every card statement prints an account summary, usually on page one, that reads something like: previous balance, plus purchases, plus fees, plus interest charged, minus payments, minus other credits, equals new balance. Some issuers group purchases and cash advances separately, and most also print total credits and total charges for the period.

That equation is the check. If you have every row, then previous balance + charges − payments and credits = new balance, to the cent. A missing purchase leaves the total short; a payment read as a purchase throws it off by twice the amount; a refund typed as a charge does the same. Because most card statements print no running balance beside each row, this summary equation is often the only check available, so it is worth reading the summary box carefully before doing anything else.

A bank statement uses the opposite signs: deposits increase the balance and withdrawals reduce it. A converter or a spreadsheet template built for bank statements will report a card statement as badly unbalanced unless it knows it is looking at a card. The balance check has to run card-style: charges as money out, payments as money in, and the statement's own new balance as the closing figure.

Two dates, one of them the right one

Most issuers print a transaction date and a posting date for each row. The transaction date is when you tapped or typed the card; the posting date is when the issuer recorded it, typically one to three days later, longer for foreign purchases and hotel checkouts. The account summary and the statement period are built on posting dates. A purchase made on the last day of the period but posted two days later belongs to the next statement.

For bookkeeping, use the posting date. It is what the issuer's totals are built on, so the month's spend in your spreadsheet agrees with the statement. Keep the transaction date in the description if you need it for a receipt, or add a column for it. Xero and QuickBooks each take one date per line and match on the posting date when the card is connected to a feed, so importing on posting dates avoids near-duplicates when a feed and an imported statement overlap.

Note the year. Many issuers print MM/DD without a year on each row and rely on the statement period at the top. A period that runs from December to January means the rows need the year assigned from the header, and a spreadsheet that assumes the current year will put December's charges in the wrong year.

Signs, refunds and payments

The convention that causes the most trouble is the sign of a payment. On the statement, a payment appears with a minus sign, or the word CR after it, or in a separate Payments and Credits section with positive numbers. Refunds and cashback appear the same way. Purchases, fees and interest are positive or unsigned.

In a spreadsheet, pick one convention and keep it. The layout that works for reconciling and for importing into accounting software is two columns, Money Out for charges and Money In for payments and refunds, plus a signed Amount column where charges are negative. That is how Excel export columns are laid out here, and it is what the QuickBooks and Xero imports expect: money leaving you is negative.

Interest charged is a purchase-side row (money out); a statement credit for a dispute is a payment-side row (money in). A balance transfer in is a charge; a balance transfer fee is a separate charge row. Cash advances are charges with their own interest rate; the statement lists them separately, and the spreadsheet should keep them distinguishable, because they are usually not business expenses.

Foreign transactions

Purchases in another currency print with the original amount and currency beside the home-currency charge, sometimes on a second line, and a separate foreign transaction fee row (typically 1 to 3 percent) either directly under the purchase or grouped at the end of the period. The home-currency amount is the one the totals use and the one your books need. Keep the original amount in the description if you reconcile against supplier invoices in that currency; do not put it in the amount column.

A converter that reads the wrong one of the two amounts produces a statement that is off by the exchange difference on every foreign row, which is a good example of an error the summary equation catches immediately and a row-by-row visual check does not.

Getting the rows into Excel

Typing a card statement is slower than typing a bank statement for the reasons above: two dates per row to choose between, signs to flip, foreign rows with two amounts. A 6-page statement with 120 rows is an hour of careful work, and the summary equation will fail on the first attempt more often than not.

Converting the PDF instead takes seconds, and the converter should do three things a card statement needs: recognise it as a card so the equation runs the right way round, take the posting date and the home-currency amount, and check the result against the printed new balance before it lets you download. Bank statement to Excel does this: the account type is read from the statement, payments and refunds land in Money In, charges in Money Out, and the balance badge on the results page says whether previous balance plus charges minus payments equals the printed new balance. When it does not, the first row that does not fit is marked, and a one-click swap fixes a row whose sign came through backwards. The issuer pages for American Express, Chase cards, Citi cards, Capital One and Discover describe what each issuer's statement looks like.

The Excel file has a Transactions sheet with Date, Description, Money Out, Money In, Amount, Balance (blank for cards that print none), Category and Page, and a Summary sheet with the equation written out, so the workbook proves itself.

Into QuickBooks and Xero

QuickBooks treats a credit card as its own account type, and the .qbo Web Connect file for a card uses a different message set from a bank file. A converter has to write the card kind, or QuickBooks will insist on linking the file to a bank account. In QuickBooks Online, go to Transactions, Bank transactions, Link account, Upload from file, pick the .qbo and the credit card account; in QuickBooks Desktop, Banking, Bank Feeds, Import Web Connect File. Charges arrive as expenses to categorise, payments arrive as transfers from the bank account that paid the card, and QuickBooks matches the payment to the bank side when both are in. The QuickBooks import guide covers the file rules.

Xero treats a credit card as a bank account of type Credit Card. Add it under Accounting, Bank accounts, then import the statement CSV into it exactly as you would a bank statement; Xero's import wants a single signed Amount column with charges negative, which is the layout of the Xero CSV download. Reconcile charges to bills or spend money transactions and the payment to a transfer from the bank account. The Xero import guide walks through the columns.

Checking a month before you rely on it

Whether you typed the statement or converted it, run the equation before the rows go anywhere: previous balance, plus the sum of Money Out, minus the sum of Money In, against the printed new balance. Then check the count if the statement prints one (some issuers print the number of transactions in the period). Then look at the rows around the period boundaries, where a charge posted after the closing date is the usual reason one month is short and the next is over.

For a year of card statements, convert them together and let the continuity check do the boundary work: each statement's new balance must equal the next statement's previous balance, and a missing month shows as a gap. Card statements are the case where that check earns its keep, because the periods end mid-month and a missing one is easy to overlook.