Turning a bank CSV with odd columns into a register

Import it into Money by Zaweba and let the column wizard sort it out. The wizard looks past any preamble to find the real header row, guesses which column is which from their names and the data, reads several date layouts, and handles either one signed amount column or separate money in and money out columns. Once you've confirmed it, the layout is remembered for that bank, and the rows land in a proper register with categories and a running balance.

Bank CSV files vary a lot. Some put account details above the headings, some split the amount into two columns, some write dates in the US order, and card files often show spending as positive numbers. The wizard is designed for exactly that mess.

Before you begin

Download the CSV from your bank's website and save it. Make sure the account you're importing into exists in your money file; if it doesn't, use New account on the File menu and give it an opening balance and opening date from just before the first transaction in the file. You don't need to open or tidy the CSV in a spreadsheet first. In fact it's better not to, because spreadsheets sometimes change dates or numbers when they save.

Working through the wizard

Press Ctrl+I and choose the file. If the bank is one Money knows, such as Nationwide, Barclays, Monzo, Starling, Lloyds and Halifax, Santander or HSBC and First Direct, the columns are matched straight away. For any other layout, the wizard makes its best guess from the column names and the values beneath them.

Check each part of the guess. The date column should show sensible dates in the preview; if the bank uses dd/mm/yyyy, dd Mon yyyy, yyyy-mm-dd or the US style, choose the matching layout so days and months aren't swapped. The description column becomes the payee. For amounts, tell the wizard whether there's one column with negatives for money out, or separate money out and money in columns.

Credit card files deserve a second look. Many show purchases as positive figures, which would otherwise come in as money received. Tick the money out is positive option and they'll arrive the right way round.

The review, then the register

After the wizard comes the review screen, where nothing has been added yet. New rows are listed separately from ones that already exist in the account. A matching bank id counts as a duplicate; otherwise rows with the same amount, dates within a few days (3 by default, and you can change it there) and similar payees are set aside, after bank jargon, numbers and dates are stripped out of the descriptions. Add the new rows, and payee rules put them into categories.

Open the account and you now have a register: date, payee, amount, category, memo, a cleared or reconciled flag and the running balance. If the running balance agrees with the bank's balance on the last day, the columns were read correctly.

Next time

The layout is stored inside your money file, keyed by the CSV's header row. The next file from the same bank goes straight past the wizard to the review screen. If the bank changes its export, the header row changes too, so the wizard appears again rather than guessing wrongly.

When it still looks wrong

If amounts have the wrong sign, the money out is positive choice is usually the cause, so look at that first. If dates look shuffled, the date layout is the setting to change. And if the bank offers OFX, QFX or QIF as well as CSV, those formats carry their own structure, so they skip the column question entirely. Money by Zaweba imports all of them, so try another download format before spending long on a stubborn file.