Convert an American Express Statement to Excel

Amex statement conversion, the supplementary cardholder grouping, foreign currency rows, and what verification is possible.

American Express statements have a distinctive structure, and two features in particular affect conversion: cardholder grouping and foreign currency handling.

Transactions grouped by cardholder

On accounts with supplementary cards, Amex groups transactions under each cardholder with a subtotal per person. The cardholder name is worth keeping as a column — it is the only way to split spending by person afterwards, and on a business account that is usually the whole point.

The per-person subtotals, as always, are summary rows rather than transactions. They carry an amount but no transaction date.

Foreign currency transactions

Amex prints foreign transactions with the original currency amount, the exchange rate, and the billed amount in your statement currency. Three numbers per row, only one of which belongs in the debit column.

Take the billed amount — that is what actually moved against the account. Including the foreign amount instead, or as well, will make your totals disagree with the printed figure by exactly the foreign spend.

Value on the row	Where to put it
Billed amount in statement currency	The amount you total
Original foreign amount	Its own column, for reference
Original currency code	Its own column
Exchange rate	Its own column, where printed

The converted file keeps each row as Amex printed it, so the foreign amount, currency and rate stay wherever the statement prints them — often in the description. A formula pulls them into their own columns when you need them.

Verification on a charge card

Amex charge cards have no running balance and, on some products, no revolving balance either — the full amount is due each period. Verification compares extracted totals against the printed new charges, payments and credits, and total due.

That is a partial check rather than a full one, and it should be reported as partial rather than dressed up as complete.

Business expense reporting

Amex business cards with supplementary cardholders are frequently converted for expense reporting, and the cardholder column is what makes that practical.

Convert the statement The rows come through in printed order, grouped under each cardholder as Amex prints them.
Add a cardholder column and pivot Fill it down from where each cardholder's section starts, then pivot on cardholder and month for per-person spend.
Separate foreign transactions Filter on the currency code in the description, so exchange effects are visible rather than buried.
Match against expense claims The reference and merchant columns are what you match on.

Frequently asked questions

Can I split spending by supplementary cardholder?

Yes. The rows keep Amex's grouping by cardholder, so add a cardholder column filled down from where each cardholder's section starts, and pivot on it.

Why should I not sum the foreign amount column?

It contains amounts in several different currencies. Only the billed amount, in your statement currency, is meaningful to total.

Which amount is used for foreign transactions?

Nothing is chosen for you: each row comes through as Amex printed it. Total the billed amount in your statement currency, never the foreign one.

Convert a statement now

Bank Statement PDF to Excel, or see every format. More in the guides.