Bank statements

Online orders in US dollars: importing them from a Canadian card

US dollar purchases on a Canadian dollar statement need two amounts and a note. Learn to import them, match refunds and check the implied rate.

Editorial illustration for Online orders in US dollars: importing them from a Canadian card

Ordering from a website priced in US dollars leaves a statement line in Canadian dollars, with the original figure either in the description or on a separate sub-line. To import it without confusing your tracker, record the Canadian dollar amount as the amount, the US figure and its code as extra fields, and a note about the order. Refunds then have something to match against.

What does an online order look like on the statement?

Imagine three orders in a month. A pair of boots at US$120.00 shows as C$163.20. A book at US$18.00 shows as C$24.48. A returned jacket, originally US$85.00, comes back as a credit of C$114.90 although the original charge was C$115.60. The two jacket lines are the interesting ones.

LineUS amountCanadian dollar amountNote
Boots120.00-163.20Order placed 3rd
Book18.00-24.48Gift
Jacket purchase85.00-115.60Original charge
Jacket return85.00114.90Refund, 0.70 less than the charge

Why do refunds not match the original charge?

Because the conversion happens on the day each line posts. The same US$85.00 converts at a slightly different rate when it returns, so 114.90 is not 115.60. If your records force them to cancel exactly, they will no longer agree with the statement. Record both lines as they appear, link them with a note, and accept the 0.70 gap as part of the cost of the purchase.

How do I record the extra detail?

  1. Make a column for Original amount and one for Original currency.
  2. On the purchase line, type 85.00 and USD.
  3. On the refund line, type 85.00 and USD too, with a note saying Refund of jacket.
  4. Keep the Canadian dollar column as the signed Amount.
  5. If your statement shows a separate line for a conversion cost, record it as its own row with a note.

Does the implied rate help me?

It does, as a sense check. Boots: 163.20 divided by 120.00 is 1.36. Book: 24.48 divided by 18.00 is 1.36. Jacket purchase: 115.60 divided by 85.00 is 1.36, and the return gives 114.90 divided by 85.00, which is 1.3518. A small difference on the return, a very different number on a typing slip. In a sheet: =ABS(C2)/B2 beside each row, filtered to one currency.

What about orders that are still pending?

Pending lines may show only the US dollar figure, or a provisional Canadian amount that changes when the order posts. Do not import pending lines. Wait until the posted version appears, then import once. If you must record something sooner, add it by hand with a note saying Pending, and remove it when the real line arrives, otherwise you will count it twice.

How do I find all my US spending in one go?

  • Filter the Original currency column to USD.
  • Sum the Canadian dollar column: =SUMIFS($C$2:$C$300, $E$2:$E$300, "USD").
  • For the four lines above, -163.20 - 24.48 - 115.60 + 114.90 = -188.38.
  • Divide by the number of orders if you want an average per order, remembering that refunds reduce it.

SpendixAI keeps an original currency and a base-currency amount for each transaction, with a foreign-currency spend report; the base currency must be chosen before importing and is fixed afterwards. It is built around India and INR, so it fits only readers who also track Indian accounts. The structure above is a pattern you can use in any sheet.

Before importing a statement with US lines

  • Decide the base currency and stick to it.
  • Skip pending lines.
  • Add the original amount and code.
  • Link refunds to purchases with a note.
  • Check the implied rate for outliers.
SPENDIXAI JOURNAL

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

Explore your reports