Bank statements

Credit card refunds look like purchases? Flip the sign on card files (Canada)

Card statements often list purchases as positive and payments or refunds as negative, the reverse of a chequing file. Learn to flip them, with C$ examples.

Editorial illustration for Credit card refunds look like purchases? Flip the sign on card files (Canada)

A credit card file and a chequing file often use opposite signs. On the card, a purchase may be a positive number and a refund a negative one; on chequing, spending is negative. Mix the two without adjusting and your refunds can look like purchases.

The same refund in two files

FileA C$58.20 purchaseA C$58.20 refund
Chequing, money out negative-58.20+58.20
Card, balance owed positive58.20-58.20

Importing the card file unchanged into a tracker that expects spending to be negative would read the purchase as money in and the refund as money out. Everything is reversed, though not always obviously, because purchases still add up to the right size.

A worked example

Say the card file has three rows: a purchase of C$220.00, a purchase of C$75.40 and a refund of C$58.20. The correct net spend is 220.00 + 75.40 - 58.20 = C$237.20. If the tracker adds the absolute values, it shows 220.00 + 75.40 + 58.20 = C$353.60, which is C$116.40 too high. The error is twice the refund again: 2 x 58.20 = 116.40.

How do I flip the sign in a spreadsheet?

  1. Open the card file in Excel or Google Sheets.
  2. Insert a new column beside the amount, say column E, with the amount in column D.
  3. In E2 enter =-D2 and copy it down to the last row.
  4. Check that purchases are now negative and refunds positive.
  5. Copy column E, paste as values only, delete column D and save as a CSV.

Do this only for card files whose purchases are positive. If your bank already exports them as negative, flipping them again would put you back in trouble, so look at a few rows you know before you change anything.

What is the quickest sanity check?

  • Find a payment you made to the card; it should be money in on the card account.
  • Find a refund you know about; it should be money in too.
  • Find a purchase; it should be money out.
  • Compare the total of the column with the net on the statement.

What if I have many files?

Fix the first one, write down which direction your bank uses for cards, and apply the same step to the rest. When you import into a tracker that lets you choose the account, pick the card account for the card file, so the app can handle that account separately.

If you are unsure which direction your bank's card file uses, test it with a payment you know you made to the card. A payment reduces what you owe, so it should have the opposite sign to a purchase. Whichever sign it carries, purchases carry the other, and refunds share the sign of the payment.

Keep a short note of this for each account, for example 'card file: purchases positive, flip on import'. When you come back to the file months later, that one line saves you from working out the convention all over again.

Finally, remember that a refund can arrive weeks after the purchase and may land on a different statement cycle from the original charge. That is normal, so match the two by amount and merchant when you review, and do not worry if they sit in different months.

Next steps

Look at the most recent card import. Find one refund and one purchase and check their directions. Then compare the net with the statement. Terms for returns and disputes are in your cardholder agreement; this article only keeps your records straight.

SPENDIXAI JOURNAL

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

Explore your reports