Bank statements

Refunds counted as spending after a CSV import? Check the money in column (UK)

Bank CSVs often split Paid in and Paid out, and a refund can land with the wrong sign. Learn the formula and checks that fix it, with a pounds example.

Editorial illustration for Refunds counted as spending after a CSV import? Check the money in column (UK)

If a refund turns up as spending after you import a file, the amount column probably lost its direction on the way in. Many statement files carry money in and money out in separate columns, and an importer that reads both as plain positive numbers cannot tell a refund from a purchase.

The two common file layouts

LayoutHow a £39.99 refund appearsRisk
One Amount column, negative for money out39.99Usually read correctly
Two columns, Paid out and Paid in39.99 under Paid in, blank under Paid outBoth can be read as a positive amount
One Amount column, all positive, plus a Type column39.99 with Type 'Credit'The Type is ignored and the row is a spend

Open the file in Excel or Google Sheets before importing, and look at the layout. Your bank's file may have different headings, so go by what you see.

Build one signed column

Suppose column B is Paid out, column C is Paid in, and your data starts on row 2. In a new column D, enter this formula and copy it down:

  • =N(C2)-N(B2)
  • This gives a positive number for money in and a negative number for money out, even if one of the cells is blank.
  • Check the total of the column with =SUM($D$2:$D$500) and compare it with the net movement on the statement.

Save a copy as CSV with the date, description and this signed column, and import that. N() turns text and empty cells into zero in Excel, so a stray blank will not break the sum.

A worked check in pounds

Say the statement shows money out of £1,284.70, money in of £2,150.00 including a £39.99 refund, and net movement of +£865.30. If the importer treats the refund as a purchase, your tracker shows money out as 1,284.70 + 39.99 = £1,324.69 and money in as 2,150.00 - 39.99 = £2,110.01. The net is 785.32, not 865.30, and the gap of £79.98 is exactly twice the refund. That doubling is the fingerprint.

How do I fix it after the import?

  1. Compare totals of money in and money out with the statement.
  2. If the gap equals twice an amount, search for that amount.
  3. Flip the row so it is money in, and give it the category of the original purchase.
  4. If many rows are wrong, delete the import, correct the file and import again.

What if we cannot edit the file?

Then fix the rows one at a time after importing, but search the amounts first so you do not miss one. Rules about how your bank labels reversals and refunds differ, so read the description on each row. A refund description often mentions the shop and the word refund or credit, but not always.

If you cannot edit the file, you can still check the signs without a formula. Sort the original by the Paid in column, and look at the top rows; each should be money you know you received. Then sort by Paid out and confirm the largest items are purchases. A few seconds of sorting often reveals a refund sitting in the wrong column.

Be careful when you save the edited file: keep the original untouched, and name the corrected copy clearly, such as the month followed by 'signed'. If something goes wrong later, you can return to the bank's own file and start again.

A short routine

Each month, sum the signed column, compare it with the net movement printed on the statement, and only then import. It takes two minutes and keeps every refund on the right side.

SPENDIXAI JOURNAL

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

Explore your reports