Guides

How to convert a credit card statement to Excel

Credit card statements look like bank statements, but they behave differently. Charges increase what you owe, payments reduce it, and many card statements don't print a running balance at all. Those differences cause most of the mistakes people make when moving card transactions into a spreadsheet or accounting software.

This guide covers how to get the data out, how to handle signs and special lines, and how to check the result.

First choice: the card issuer's download

Most card portals let you download recent transactions as CSV, OFX or QFX. If that covers the period you need, use it. It comes straight from the issuer's system.

PDF conversion is useful for older statements no longer available online, statements a client has sent you, or closed cards.

Getting the transactions out of the PDF

The methods are the same as for bank statements:

• Excel Power Query: Data → Get Data → From File → From PDF in Excel for Microsoft 365 on Windows. Select the transaction tables, append the pages, and set column types. This connector isn't available in every Excel edition.
• Copy and paste: select the transaction text, paste into Excel, and split with Data → Text to Columns.

The PDF bank statement to Excel guide covers both in detail. Card statements add a few problems of their own, below.

Card-specific problems

Two dates per line. Many card statements show a transaction date and a posting date. Keep both columns at first. Most accounting imports want one; the transaction date is usually the better choice for expense records, but follow your firm's convention.

Sections instead of one list. Statements often split activity into blocks: payments and credits, purchases, cash advances, fees, interest. When you combine them into one table, add a Type column so you can still tell them apart.

Multiple cardholders. Statements for accounts with supplementary cards may list each cardholder's transactions separately, with subtotals. Keep a Cardholder column and delete the subtotal rows.

Foreign currency lines. An overseas purchase may print the original currency amount, an exchange rate, and the converted amount, sometimes across two lines. Keep only the amount in the card's billing currency in your Amount column. If you need the foreign amount, put it in a separate column.

Refunds and reversals. These appear as credits. Make sure they come through with the opposite sign to purchases, not as another charge.

Sign conventions

This is the part that causes the most errors. There are two common views:

Neither is wrong, but you need to know which one the destination expects.

• QuickBooks Online and Xero credit card accounts: check how the import screen treats signs. Both let you review the transactions before accepting them, so import a small sample first and confirm that a purchase increases the card balance.
• A spreadsheet for expense analysis: most people prefer purchases as positive numbers, so SUM gives total spend.

Normalise everything before you analyse it. If the statement marks credits with CR or a trailing minus, convert them:

=IF(RIGHT(TRIM(B2),2)="CR", -VALUE(TRIM(SUBSTITUTE(B2,"CR",""))), VALUE(B2))

Adjust for your layout. Parentheses, trailing minus signs and separate debit/credit columns each need their own handling.

Checking the result

Bank statements usually print a running balance after each line, which makes row-by-row checking easy. Many card statements don't. They print a summary instead, typically:

• Previous balance
• Payments and credits
• Purchases and other charges
• Fees and interest
• New balance

Use that summary as your check:

1. Sum each Type in your sheet with SUMIFS.
2. Compare each sum to the matching line in the summary.
3. Confirm: previous balance − payments and credits + purchases + fees + interest = new balance.

If one category is off, the problem is in that section, which narrows the search. If the count of transactions per section is printed, compare that too.

Where Rowsafe helps

Rowsafe converts text-based PDF card statements in your browser, without uploading them.

• Local processing. The statement is read by JavaScript on your own computer. You can confirm in developer tools that the file isn't sent anywhere.
• Balance checking where the statement allows it. Where a statement shows a running balance, Rowsafe recomputes it for every row and flags any row that doesn't add up. Where it shows only opening and closing balances, use the totals check above against the statement summary to confirm the conversion is complete.
• Column fixes in the preview grid if the layout needs adjusting.
• Password-protected PDFs are supported; the password stays on your device.

The free plan converts 3 statements a day with CSV export. Pro ($12/month or $96/year) adds Excel (XLSX), QuickBooks (QBO), OFX, Xero CSV and JSON exports, unlimited statements, and batch conversion for a year of statements at once. See pricing.

Limitations: text-based PDFs only. Scanned or photographed statements are not supported yet. Card statements with unusual section layouts may need a manual column fix.

A useful sheet layout

Turn it into an Excel Table (Insert → Table) so filters and SUMIFS ranges extend automatically as you add statements.

FAQ

Should payments to the card be treated as expenses? No. A payment moves money from a bank account to the card; it's a transfer, not spending. Keep it out of expense totals.

My card statement has no running balance. How do I know nothing is missing? Check each section total against the statement summary, and the overall previous-to-new balance movement. If those agree, the transactions are complete.

Why does my imported card account show the balance with the wrong sign? The sign convention in the file didn't match what the software expected. Flip the Amount column (=-A2) and import a small sample to confirm.

Can I convert a year of card statements at once? Yes. In Excel, append each statement in Power Query. Rowsafe Pro can batch-convert and merge multiple PDFs into one file.