Account Tracker supports importing transactions from a CSV file, as documented here. The reason the format is relatively strict is because I only really expect people to import transactions once, and that it is quick and easy to convert CSV files in a spreadsheet like Excel or Numbers.

For those of you not familiar with spreadsheets, I thought I’d write this quick guide/tutorial about converting the export format from a sample bank (Nationwide in the UK) to Account Tracker format.

First of all, here is how Nationwide exports transactions …

Account Name:,Flexaccount ****12345
Account Balance:,£0.00
Available Balance: £0.00

Date,Transaction type,Description,Paid out,Paid in,Balance
29-Dec-14,Transfer to,ABC,£1.23,,£0.00

If you open your exported CSV file with Excel (or another spreadsheet application) you will see the data in table format, with columns (A to F) and rows (1-6 or more, depending on the number of transactions).

Lets get rid of the redundant rows and columns first. Click on the number 1 to the left of the first row and drag down to row 5. This will highlight the first 5 rows. Right click and select Delete. Select the sixth column (F) and delete that in the same way. Finally select the second column (B) and delete that. You will be left with one row per transaction, with four columns (date, description/details, paid out and paid in).

To fix the date format in column A, simply select the whole column and choose Format then Cells. Select Custom and enter dd/mm/yyyy in the Type box.

To convert the two separate columns for paid in and paid out into one, select cell E1 and enter the formula =D1-C1. Select cell E1 again, copy it (control c), select all the remaining cells in that column (one per row) and paste (control v). You now have a single column with amounts. To convert these cells from formulas to values, select the whole column, copy (control c), then choose Edit then Paste Special then Values. To get rid of the currency symbols, select the column and choose Format then Cells. Choose Number, set the right number of decimal places (2), don’t use a 1000 separator and keep the – sign for negative numbers. You can now delete columns C and D as they are no longer needed.

Finally you need to add in the five missing columns. First of all select column A and choose Insert then Column. Enter your account name from the app in cell A1, then double click on the little box at the bottom right of the cell. This will autofill the account name for each row. Alternatively you can select cell A1, copy it (control c), select the remaining cells in that column (one per row) and paste (control v).

Now select column D, and choose Insert then Column three times (to add columns for the category, notes and cheque number. Note that Nationwide includes the cheque number in their description field so you might have to find these and move the cheque numbers from column C to column F. This can be automated, but only if you are a more advanced Excel user!

Now select cell H1 (for the reconciled flag). Enter the text Y, then double click on the the little box at the bottom right of the cell. This will autofill the flag for each row. Alternatively you can select cell H1, copy it (control c), select the remaining cells in that column (one per row) and paste (control v).

To export your new CSV file, choose File then Save As, making sure you select the format Comma Separated Values (.csv).