· 5 min read
Credit-card statements to Excel and QBO
Sign conventions, why most card statements have no running balance, how to verify the totals instead, and importing into QuickBooks or Xero correctly.
A credit-card statement has dates, descriptions and amounts, so it looks like a bank statement with a different logo. Three things make it behave differently once it is in a spreadsheet or in accounting software: the signs point the other way, there is usually no running balance to check against, and the page is full of subtotals that are not transactions. This article takes the three in turn and ends with the import.
The sign convention
On a bank statement the balance is money you have. A deposit makes it bigger, a payment makes it smaller. On a card statement the balance is money you owe. A purchase makes it bigger, a payment makes it smaller. Same columns, opposite meaning.
In a spreadsheet you can use either convention as long as the header says which. In accounting software the choice is made by the account type: a credit-card account expects purchases, fees and interest in the money-out column (QuickBooks and Xero both label it Spent) and payments and refunds in the money-in column (Received). Put a card statement into a bank-type account and every row lands backwards; the first symptom is a card balance that shrinks when the client shops.
The statement itself does not help much, because issuers print signs inconsistently. Some show payments with a minus sign, some with a CR suffix (common in Australia and the UK), some in a separate section with no sign at all, and purchases are almost always unsigned. Whether you are converting by hand or checking a converter's output, the rule is to read the structure of the page, not the sign of the number: anything in a payments or credits section is money in, anything in purchases, fees or interest is money out.
The tell for a flipped sign is a total that is off by exactly twice one amount. A 500.00 payment read as a purchase moves the sum by 1,000.00.
Why there is usually no running balance
A bank statement prints a balance after every transaction. Most card statements print a previous balance at the top, a new balance at the bottom, and a list of transactions in between with no balance per line, often grouped by cardholder or by type rather than strictly by date. Some issuers include a running balance; most do not, and the ones that do often print it only in the online view rather than on the PDF.
That removes the row-by-row test. What remains is the sum, and the sum is worth doing every time.
Verifying the totals instead
Every card statement satisfies one identity:
previous balance + purchases + fees + interest − payments − credits = new balance
Take a statement with a previous balance of 1,204.55, one payment of 900.00, five purchases (42.10, 318.00, 12.99, 76.45 and 210.00, which sum to 659.54), no fees and 14.21 of interest. The new balance printed at the bottom is 978.30.
Check: 1,204.55 − 900.00 = 304.55; plus 659.54 is 964.09; plus 14.21 is 978.30. The extraction ties.
In Excel this is three cells. Sum the money-out column and compare it with the printed purchases, fees and interest subtotals (most issuers print each; add them). Sum the money-in column and compare it with the printed payments and credits total. Then compute previous balance − money in + money out and compare it with the new balance. If the issuer prints a transaction count, count your rows as well.
What the sum catches is the same list the running-balance check catches: a dropped row (the difference is its amount), a duplicated row (the same), a flipped sign (twice the amount), a misread digit (an amount that is on no line). What it cannot do is tell you which row, and a dropped row and a duplicated row of the same amount cancel out. So when the sum fails, work the sections: purchases against the purchases subtotal, payments against the payments subtotal, and so on, until the section that does not tie is found. Sections are usually short enough to compare line by line against the PDF.
A converter that runs this check for you should also say which check it ran. A card statement that prints only a closing total is reported as closing total checked, and one whose totals cannot be tied together says the check is unavailable, so a pass on a card statement is never mistaken for a full row-by-row reconciliation. The credit-card page shows the badge table; reconciliation explains both tests.
Rows that are not transactions
The other reason card statements break imports is that they carry more summary lines than bank statements do. None of these should become a transaction:
- Previous balance and new balance (used for the check, not imported).
- Total purchases, total payments and credits, total fees, total interest.
- Per-cardholder subtotals on statements with supplementary cards.
- The interest-charge table, which restates interest by rate and balance type. The interest transaction itself is real; the table is a summary of it.
- Minimum payment due, payment due date, credit limit and available credit.
- Rewards and year-to-date summaries.
- Installment-plan summaries that show the plan total and remaining balance. The monthly installment charged this period is the transaction; the plan total is not.
Two more lines are transactions but need care. Foreign-currency purchases show two amounts, the original and the billed one; import the billed amount and keep the original in the description. Refunds appear as credits inside the purchases section, sometimes with a minus sign and sometimes with CR, and belong in the money-in column.
Importing into QuickBooks or Xero
QuickBooks Online. Use, or create, an account of type Credit Card, and upload the QBO file through Upload from file in the bank transactions screen. If you import CSV instead, send a two- or three-row test file first and look at which column QuickBooks put the rows in, Spent or Received, before importing the rest; the way a single-column CSV's signs are interpreted for card accounts is easy to get backwards. After the import, run Reconcile with the statement's new balance as the ending balance and the statement date as the ending date. The QuickBooks import article covers the upload path and the error messages.
Xero. Add the card under bank accounts with the account type set to Credit Card, then import the OFX file (or CSV, mapping Spent and Received, or a single amount column with money out negative). Xero skips OFX rows it has seen before by their ID and warns about CSV rows that look like repeats. Reconcile until the balance in Xero equals the statement's new balance as an amount owed.
In both programs the statement's new balance is the reconciliation target. If the balance after import is off by the previous balance, the account's opening balance is missing or dated wrong; if it is off by one transaction, use the sum method above to find it.