Remi Bergsma's blog

To track my spendings, I started using the Account Tracker iPad app by Graham Haley. It’s a very solid and well written app that I’ve been using for some weeks now. I really like it, especially because it has a clearly designed gui and rich feature set. Another big plus is that the developer responds very quickly.

But, entering all transactions by hand for me was a no-go. I just don’t want to do that because it’s a lot of work and we might also forget to enter some. Furthermore, almost all transactions we do are done electronically. That’s when I started to think about importing transactions from our Bank Account into the iPad app. Fortunately the app has a feature that let’s you import a csv file. Our bank’s website also has an export feature so that should be doable!

Of course, the formats differ so a direct import is not possible. Also, I want to track spendings by category without entering the category for each and every transaction. I wanted to create something that would work as automatic as possible.

Good news: I just pushed the first public version to GitHub! Let me tell you more..

My goal was to enter all our 2012 transactions to the app. To start, I created the accounts in the app and set their opening balance to the balance on 01/01/2012.

You can create as many accounts as you like, order them in groups, sort them and so on.

That’s all (for now) you’ve to do in the app.

I assume you know how to export transactions from your Bank Account. There should be an option to download a ‘csv’ file, also often referred to as ‘Comma Separated Values’. Contact your bank, it’s a common feature.

Different banks, different export formats
Most banks have their own format (order of columns and number of them to be exact) in which they export transactions. Therefore, a solution that works for anyone out-of-the-box is not possible. But, I did the next best thing: wrote something that can be easily extended. I have accounts at two different banks: ASN Bank and Triodos Bank and have added support for both. I’ll ask some people I know for a dummy export, so I will be able to add support for more banks later on. If you want to do it yourself: the README on Github describes how to add support for your own bank.

Download the scripts
You can download my scripts for free from the GitHub project page.

Let’s see how this works
I login to my ASN Bank account and export a csv-file. The ASN Bank is pretty smart: they remember what transactions I downloaded last time, and only let me download new ones. This is pretty cool, since that prevents double transactions. Triodos Bank let’s you download transactions by a date range you enter. Be careful not to download the same transaction multiple times.

Save this file to: ./files/exported_from_bankaccount/asnbank/
If you have more than one file, just add them to the same directory. The script will combine them so you don’t have to worry about that. Different accounts is fine, as long as they are from the same bank.

To get started with the scrips, edit the settings. First, copy the example, then edit it:

cp -pr ./conf/settings.example.inc.php ./conf/settings.inc.php
vim ./conf/settings.inc.php

Add the accounts you also added to the app, like this:

$_SETTING['my_account']['0707711122'] = "ASN Account";
$_SETTING['my_account']['300.03.83.213'] = "Triodos Account";

The names of the Accounts (ASN Account and Triodos Account in this example) need to match the names in the app. See the image above. The number refers to the Account Number that is in the export file (this is your own account). Be careful to enter it exactly as in the export file. As you can see ASN Bank has no separators, while Triodos Bank has.

Categories
You will get better reports if you set a category with each transaction. The script supports a system where transactions can be matched to categories. For example, when I buy something at ‘Albert Heijn’ or ‘Digros’, it’s always ‘Groceries’. You enter them like this to the settings file:

$_SETTING['billing_category']['Albert Heijn'] = "Groceries";
$_SETTING['billing_category']['Digros'] = "Groceries";
$_SETTING['billing_category']['BP '] = "Fuel";
$_SETTING['billing_category']['Shell'] = "Fuel";

There is no limit in how many you can add. All you need to know is that the first one that maches, is used (it works top down). In the example above, when ‘Albert Heijn’ is in the description of the transaction, the category is set to ‘Grocaries’. When ‘Shell’ is found, the category is set to ‘Fuel’. I hope you get the idea. It works for both spendings and income.

If no match is found, the category ‘Unknown’ is used. (configurable in the settings file) When this happens, the script warns you, so you can easily see what line did not match any category. You can either safely ignore it, or add an extra category to the settings (and re-run the script). You just have to do it once, all transactions that match will be handled automatically from then onwards. But remember: you can always edit the categories in the app later on as well. Even split them between two categories.

Also have a look at the timezone setting, if you’re not in ‘Europe/Amsterdam’, like me. It is used to calculate the right date in the ’18/09/2012′ format since the app expects to read it like this.

Generating the import csv file
To start generating, run this command:

php run_import_asnbank.php

Make sure you select the script that matches the Bank Account(s) you want to process.

When everything goes well, it tells you where to find the generated file. If something goes wrong it tells you what went wrong.

Done! Wrote file ./files/generated_ipad_import/import_asnbank_20120918161644.csv
Please open the file for manual review, then upload it to the Account Tracker iPad app.

Importing the generated file
Time to import the generated file. I’ll show you using some screenshots:

1. Open the app, tap Settings, tap Backups, tap ‘Import / Export by WiFi’. It looks like this:

2. On your computer, open a browser and enter the url shown. A page will be displayed that looks like this one:

3. Click ‘Choose File’ and select the generated file on your computer. Then click ‘Copy’ to transfer it to your device.

4. Back to your device: click ‘Stop’ and select the file you just uploaded. A screen like this appears:

5. Click ‘Import CSV File’ to import the file. The app will validate your file. When the file is corrupt, a clear message is displayed:

In this case, the ‘Account Name’ in the app does not match the one in the settings file. Double check! If all goes well you’ll see:

6. Now, click ‘Import’!

The transactions are imported successfully. Now go to the Accounts screen and let’s a look to the ‘ASN Account’. You will notice the balance changed. When you tap on the account, you’ll see its transactions:

As you can see, the transaction has been added and it detected ‘Groceries’ as category. In a bit more detail:

Now, that’s cool, isn’t it?! 🙂 You should check if the balance now matches the one on your bank’s website. If you entered the correct opening balance and imported all transactions, the balance should match.

Budgets
It becomes even better when you setup Budgets in the app. You can create a Budget once you have a category. Just tap ‘Budgets’, then ‘+’, and select ‘Groceries’. Finally, enter the budget:

The spendings (and income too) are then calculated against this budget. It gives a pretty clear picture:

In this example we’ve imported a single transaction. I’ve added over 600 transactions at once and that works perfect. The app detects transfers between your own accounts and does not calculate those as spending. That only works when you add all your accounts, of course.

Once you have some data, you can play with the reports and see how money comes in and goes out. It’s pretty detailed and very well designed. You can even export to PDF to save the report somewhere offline.

Don’t forget to protect the app with a PIN code. This way, when you lose your device your data cannot be seen. A PIN code on the backups as well is coming.

I really recommend the Account Tracker app and hope my scripts will be useful to others as well. Let me know!