Articles · Statement import

Bank statement to Excel: how to convert it without losing data

Your bank hands you a PDF. You want to sum, filter and see where the money went. Between those two things sit three or four traps that break the numbers without ever showing an error. This guide covers the options, what goes wrong with each, and what to check at the end.

Why copy and paste is not enough

A PDF statement is not a table. It is text positioned on a page. When you paste it into Excel, what looked like a neat grid becomes one line of text per transaction, all in a single cell. A typical line from a Portuguese statement looks like this:

8.06  8.06  COMPRA 4521 CONTINENTE MODELO CASC CONTACTLESS     14,61     534,83

Two dates, a description with the card digits buried in the middle, the amount, and the running balance after the movement. To be usable in Excel that has to be split into five columns, and that is where the problems start.

1. The thousands separator is a space

Several Portuguese banks print one thousand one hundred and twenty two euros and thirty cents as 1 122,30, with a space grouping the thousands. If you split the line on spaces, the amount splits in two: 1 lands in one cell and 122,30 in the next. Excel accepts both without complaining, and you silently lose a thousand euros from that transaction.

This is the most expensive mistake on the list, because it only hits amounts above one thousand, which usually means your salary. The monthly total comes out wrong and it is not obvious why.

2. Debits and credits sit in different columns

Most Portuguese statements do not use a minus sign. They use two columns, debit and credit, and the amount appears in one or the other. When the PDF is converted, empty columns vanish, and a 30 euro expense becomes indistinguishable from 30 euros of income.

The reliable way to recover the sign is the balance column: if the balance went down compared with the previous line it was an expense, if it went up it was income. That is why it is worth keeping the balance column even if you think you do not need it.

3. Descriptions arrive truncated

The description field has a fixed width, so merchant names are cut off: MARQUES E BERNARDO .ALP, EST SERVICO A.S. ADELINO AMARO DCAS. That is not a conversion error, it is genuinely how the bank prints it. If you were planning to sort by merchant, expect to clean this up by hand.

4. Some lines are not transactions

Headers repeated on every page, A TRANSPORTAR and TRANSPORTE lines carrying the balance across pages, and at the end a small overdraft interest table that has dates and amounts just like a real transaction. If you do not delete them, they end up in your totals.

The options you have

Export CSV straight from the bank

The cleanest option when it exists. In your online banking, find the transaction list and look for an export or download button, usually offering CSV, Excel or OFX. The wording differs per bank and it is sometimes hidden behind an icon.

The usual limit is the period: many banks only let you export the last 3 or 12 months, and only offer PDFs for older ones. If you want history, you will probably end up handling PDFs anyway.

Online PDF converters

They work, but they mean uploading a complete bank statement to somebody else's server. That file has your name, your address, your IBAN, your balance and where you buy bread every morning. It is worth reading what the service says it does with the file afterwards, and assuming a copy stays there.

Paste into Excel and clean it manually

Workable for one month, with patience. The approach that tends to work best: paste everything into one column, use Data > Text to Columns with fixed width rather than a delimiter, and only then fix the amounts. If you split on spaces, you are back to the thousands separator problem.

For twelve months and two accounts it stops being viable. It is repeated work that has to be redone next month.

Read the statement with something that understands statements

The alternative is not converting to Excel at all, and using something that already reads the format. That is what Focus does: you load the PDF, it identifies the transactions, recovers the sign from the balance movement, joins the thousands correctly and categorizes each entry. The file is read inside your browser and is not sent to any server.

If the statement covers more than one account in the same document, as combined statements do, each account is separated, so transfers between them do not show up as duplicated income and spending.

What to check at the end, whichever route you took

Three quick checks catch nearly every conversion error:

1. Does the closing balance match? Take the opening balance, add every credit and subtract every debit. It has to land exactly on the closing balance printed on the statement. If it is off by a round number like 1000, that is the thousands separator.
2. Does the line count match? Count the transactions in the PDF and in the sheet. If the sheet has more, you picked up headers or carry-over lines.
3. Is anything income that should be an expense? Sort by amount and look at the largest credits. That is where flipped signs usually stand out.