Tag: Php

3 posts

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!

Lately I’ve been coding to automate the deployment of Virtual Machines that will run in our cloud. It’s inspired by blogs and code from Jason Hancock but implemented in a different way to meet our specific needs. Since CloudStack has a very flexible API, you can make it do anything you want. It works pretty cool so far.

From a command line script we can deploy a new VM with a certain “role”. This role has a name, like “web server” or “test-vm-db” and contains the OS template name, the datacenter name, the networks to connect to, the hardware offering (CPU/Memory/Disk) and the account and domain to use. These are all CloudStack specific settings. When calling the deployVirtualMachine() API command, CloudStack will create a VM with exactly these specs. We use a simple php cli script to add a new server, like this:

Result:

Since it is in a specific domain (defined by the role), other users in that domain can “see” and work with the new VM immediately. When you deploy for a client, they will “see” the new VM like this:

When the VM boots, it is receives its role name, “web server” for example, and sends this info to Puppet. This way, Puppet knows how to configure the new VM. It will do so and when it’s finished you have an exact same “web server” as the others. Run the script again and you’ll have another one. Puppet does not only do initial configuration, it will make sure the configuration stays consistent across the all servers it manages. When you want to change a setting, Puppet distributes it and makes sure it’s the same on all servers. All automatically!

Imagine the power behind this concept. All of a sudden you can create a script that can deploy a new web server in just minutes. Now even the loadbalancer itself can spin up a new web server to handle the higher load it receives. Or destroy a web server when there are too many idle web servers. That’s what you call elastic!

What you need to build this yourself is:
1. PHP CloudStack Client (to talk to CloudStack from PHP)
2. CloudStack API docs
3. Script to grab meta-data when VM has booted

Please see Jason Hancock‘s blog for some working examples of how he implemented it. As I said, CloudStack’s API is very flexible. But you can even extend the API, if you want to go further. Thanks for the inspiration Jason!

When working in shells, it’s very useful to have syntax highlighting. In vim it’s just ‘:sy on’ and you’re done. For those of you using GNU Nano, I’ll show you how to enable syntax highlighting as well.

To start, we’ll need some files which tell nano what to display in what color. Here is an example of such a file for PHP:

nano /usr/share/nano/php.nanorc

syntax “php” “.php|.inc$” color white start="<?(php)?" end="?>" color magenta start="<[^?]" end="[^?]>" color magenta “$[a-zA-Z_0-9]” color brightblue “->[a-zA-Z_0-9]” color cyan “([)|(])” color brightyellow “(var|class|function|echo|case|break|default|exit|switch|if|else|elseif|@|while|return|public|private|proteted|static)\s” color brightyellow “&lt;(try|throw|catch|operator|new)&gt;” color white “=” color green “[,{}()]” color green “==” color brightgreen “(’[^’]’)|("[^”]")" color yellow start="" color yellow start="/*" end="*/" color yellow start="#" end="$" color yellow “//.*"

Then we tell nano where this file is to be found:

nano /etc/nanorc

And add this line:

## PHP
include "/usr/share/nano/php.nanorc"

You then should see this when editing a PHP file with nano:

You can do the same for Bash files. Here’s the config file:

nano /usr/share/nano/sh.nanorc

syntax “sh” “.sh$” icolor brightgreen “^[0-9A-Z_]+()” color green “&lt;(case|do|done|elif|else|esac|exit|fi|for|function|if|in|local|read|return|select|shift|then|time|until|while)&gt;” color green “({|}|(|)|;|]|[|`|\|$|<|>|!|=|&||)” color green “-[Ldefgruwx]&gt;” color green “-(eq|ne|gt|lt|ge|le|s|n|z)&gt;” color brightblue “&lt;(cat|cd|chmod|chown|cp|echo|env|export|grep|install|let|ln|make|mkdir|mv|rm|sed|set|tar|touch|umask|unset)&gt;” icolor brightred “${?[0-9A-Z_!@#$?-]+}?” color cyan “(^|[[:space:]])#.$” color brightyellow “”(\.|[^”])"" “’(\.|[^’])’” color ,green “[[:space:]]+$"

It’s pretty easy to customize if you want. So now you never have to edit scripts without syntax highlighting again!