Bank statements

Foreign currency transactions on a statement: importing them in pounds

Handle foreign currency lines on a UK-style statement for a freelancer who pays in dollars, with columns for original amount, pounds billed and a rate check.

Editorial illustration for Foreign currency transactions on a statement: importing them in pounds

If you pay for software or ad credits in another currency, your statement shows the pound amount that left the account, often with the original figure only in the description. Importing it properly means keeping the pound amount as the main amount and recording the original currency beside it. Then your monthly total stays in pounds and you can still see what you were charged abroad.

What does a freelancer's month of foreign lines look like?

Take a hypothetical designer who pays for a stock photo licence, a design tool and a cloud storage plan, all priced in US dollars, plus an advert on a social platform priced in euros. The statement shows each in pounds. Say the lines are £15.80, £39.20, £7.95 and £24.60 for the month.

ItemOriginalCurrencyPounds billed
Stock licence20.00USD-15.80
Design tool50.00USD-39.20
Cloud storage10.00USD-7.95
Advert29.00EUR-24.60

Why keep two amounts at all?

The pound amount is your cash reality. The original amount is your proof of what the supplier charged, and it matters when you reconcile with an invoice or receipt in dollars. Dropping either one makes a later question hard: either you cannot match the invoice, or you cannot say what the month cost you.

How do I set up the columns?

  1. Date, Description, Amount in pounds (signed), Original amount, Original currency.
  2. Keep amount in pounds as the column used by totals and charts.
  3. Enter the original currency as a three-letter code in capitals, so it filters cleanly.
  4. Leave the two original columns blank for ordinary pound lines.
  5. Add a Receipt column where you note the invoice number, so you can find the paperwork later.

Does the rate look right?

Divide the pounds billed by the original amount. For the stock licence, 15.80 divided by 20.00 is 0.79. For the design tool, 39.20 divided by 50.00 is 0.784. For storage, 7.95 divided by 10.00 is 0.795. They sit close together, which is plausible for one month. The advert, 24.60 divided by 29.00, is 0.848 and belongs to a different currency pair, so it should be compared only with other euro lines.

A formula for this check, with original amounts in D and pounds in C: =IF(D2="", "", ABS(C2)/D2). Sort by currency and glance down each group. One value far from its neighbours is usually a typing slip or a line that includes an extra charge.

What do I do when the importer chokes?

  • If the tool wants one currency, import only the pound column and put the original amount in the description, for example Design tool USD 50.00.
  • If it accepts a currency column, make sure the code is three letters and nothing else.
  • If dates fail, check the day-first order and convert to YYYY-MM-DD.
  • If amounts fail, strip the symbols.

How do I total by currency?

With codes in E and pounds in C, =SUMIFS($C$2:$C$200, $E$2:$E$200, "USD") adds the pounds spent on dollar-priced items. For the table above that is -15.80 + -39.20 + -7.95 = -62.95. The euro advert is -24.60, so the foreign total is -87.55.

SpendixAI keeps the original currency and a base-currency amount for each transaction and has a foreign-currency spend report, with the base currency chosen before importing and fixed once transactions exist. It is built around India and INR, so it is for readers who also track Indian accounts; the column layout here works in any sheet.

Monthly habit

  1. Mark every foreign line as you import.
  2. Check the implied rate column.
  3. Attach the invoice reference.
  4. Total by currency.
  5. Note anything odd for next month.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports