Stan's Blog

Tips and tricks on how to use Maxprog products

Importing transactions from a Bank statement

iCash offers the possibility to import transactions from bank statement files directly into your personal finance. This is a great time-saving feature since you no longer have to type what you can already download from your online bank account. You just need to connect to your Bank, download the statement or the transactions as a text file and then import that file to iCash.

The magic is made thru a special window that includes a rule editor and a live preview. All you have to do is to select the Bank Account (the statement is of), default Income and Expense target accounts and finally create some rules. The importation system processes the Account Statement file applying the rules you have defined to all the transactions. If a rule validates a transaction, the data provided in that rule will be used. If no rules can be applied to a transaction default target accounts will be used. You can preview the result, how the transactions will be added to your accounting, and only when you are fully satisfied you can actually import them.

At first glance it can look complex but actually this system is based on logic, keep reading and you will understand how it works.

Starting a Bank Statement Import

First you have to connect to your Bank and download either a Bank Statement or the list of transactions you want to add to iCash. The file has to be a plain text file. If you download an Excel sheet you will have to open it with Excel and save a text copy of it. If you download a Word document you will have to open it with Word and save a text copy of it. iCash is unable to work with Excel or Word documents, only plain text files. If your Bank only allows QIF, OFX or QFX exports then just use the iCash QIF, OFX or QFX import options.

To start importing transactions from your Bank Statement click on the toolbar Import button, you will get a window with several choices, one of them is Account Statement, it is actually the default option. Click on the Import button and select the import file. iCash then displays the Account Statement Import wIndow.

The Account Statement Import window

The Account Statement Import window is made o 5 panels, the Import Presets (#1) to save and restore your settings, the Settings (#2) to select the bank account (the statement is of) and tell iCash about how your file is formatted, the Data (#3) to see the records from your file and to line-up fields, the Rules (#4) to set default income and expense accounts and to tell iCash how the transactions has to be created and the Transactions (#5) where you can finally see what will be imported. Let see those 5 panels in detail.

The Import Presets (#1), Settings (#2) and Data (#3) panels

The Import presets (#1)

This feature allows you to restore your import settings with just one click. It is made of a pull-down menu that lets you restore previously saved import settings from panel #2, #3 and #4 (except rules). Presets can be edited and saved whenever needed, you just have to click on the Update button. That button will actually enable itself automatically as soon as a change is detected. Note that rules are not really saved along with the other import settings but are associated to the selected Bank Account instead.

The Settings (#2)

This is the place where you tell iCash about the Bank Account the statement file is about, the Field Delimiter, the Text Encoding and how dates are formatted. Those settings are very important, iCash can get into troubles to display records if you don't select the right field delimiter, the imported text may contain weird symbols if you don't select the right text encoding and records may not get imported at all if the date format is wrong. In addition you can also change the case of the input text. You can quickly see the impact of this in the Data panel on the right, #3.

The Data (#3)

This panel is a viewer of your file following the settings you have selected. You can go thru records by using the navigation arrow buttons. It is important to make your file fields (left) match the panel fields (right). To do that select the target field (right) and move it to the right place either by drag and drop or by using the up and down arrow buttons. Repeat the operation as many time as needed, from bottom to top. At the end your file date field will be in front of the panel date field, your file description field will be in front of the panel description field and so on. Note that the fields that have to be imported has to be checked (center).

You can look at the record current validation status right below the field list. It will indicate if a problem has been found:

- Invalid date, the record can't be added (you may have select a date format that doesn't correspond to the file format or the date field doesn't match iCash date field)

- Empty comment, rules can't be applied (comment field doesn't match iCash comment field, it is unchecked or really empty in such case rules will be useless for that record)

- Null amount, check the record field order (amount field doesn't match iCash amount field, it is unchecked or really null, this is just a warning, null transactions can be added)

or if everything is OK:

- Record ready to be added using default data (the record doesn't match any rule, it will be added using the default Income and Expense accounts)

- Record ready to be added using rule #xx data (the record match rule number #xx, it will be added using the data from that rule)

In addition a grey, blue, red or yellow square is displayed next to the record number in case the record:

- doesn't match any rule (grey)

- match a rule (blue)

- has a date format problem (red)

- has an empty comment (yellow)

- has a null amount (yellow)

Note that next to the navigation buttons you will find a button that lets you jump to the next unchecked record (the next record not yet handled by a rule) and a Tool button allowing you to go to a specific record or find a given peace of text in all the records.

The Rules panel (#4)

First select the default Incomes and Expenses accounts. Those accounts will be used for records not matching any rule.

To add a rule first move to the record to process in Data (#3) by using the navigation arrow buttons (or the jump to the next unchecked record button next to them) and then click on the (+) button in the Rules list, the search column will be automatically filed with the selected record comment. Then select the Target Account, the Payee, the transaction type and modify the comment as needed. That's all. Repeat the same steps for any of the records you want to be processed by a rule.

The Transactions panel (#5)

You can click on the Preview button at any time and see how the transactions are being created from your Bank Statement file using the current rules and settings.

It is easy to identify the transactions that are using defaults since they have no transaction type nor Payee defined but a red background. On the right you can see two numbers x/y, the number of transactions created thanks to the rules (x) followed by the total of transactions in the list (y). In the example above only 3 transactions were added using defaults (17 - 14 = 3).

The Bank Statement file

As you can see in the previous screenshots, the file we have used in this example actually contains 19 records, 2 of them are actually invalid, the one with the column names and the one with the initial balance. Above is the file opened with Excel (front) and with a Text Editor (back).

Final touch and Importing

As said before, transactions not handled by any of your rules will get a red background, a blue background is displayed otherwise. The Transaction list is partially editable. Indeed you have a 'last chance' to modify and adjust some fields before importing. The editable fields are the transaction type, the origin & target accounts, the payee and the comment. The date and the amount fields can't be modified. Please note that clicking on the Preview button will overwrite all your changes, once you have modified fields next step is to click on the Import button and get all your transaction added to your accounting.