GnuCash, despite all its bugs, is one of the best open source accounting programs going around.
Since it is not hard to export the history from a myki (public transport) card, I figured that it would be nice to track it as an account in Gnucash.
 |
→ |  |
The data on the statements is not quite suitable for an accounting program. Some changes that need to be done are:
- Conversion of date format to YYYY-MM-DD.
- Single-field descriptions for each transaction (“Top up myki money”, “Travel zone 2”), rather than multiple fields.
- Entries which have a 0.00 cost need to be removed.
Once you have a CSV of your data, the script below will filter it to be ready for import:
#!/usr/bin/env php
<?php
/* Dump Myki CSV file to a file suitable for gnucash
(c) Michael Billington < michael.billington@gmail.com >
MIT Licence */
$in = fopen("php://stdin", "r");
$out = fopen("php://stdout", "w");
$err = fopen("php://stdout", "w");
$lc = 0;
while($line = fgets($in)) {
$lc++;
$a = str_getcsv($line, ',', '"');
if(count($a) == 8) {
$date = implode("-", array_reverse(explode("/", substr($a[0], 0, strpos($a[0], " ")))));
$credit = $a[5] == "-" ? "" : $a[5];
if($credit != "") { // Probably a top-up or reimbursement
$description = $a[1];
} else if($a[3] == "-") { // Probably buying myki pass
$description = trim($a[1], "*");
} else { // Probably travel charges
$description = "Travel: " . $a[2] . ", Zone " . $a[3];
}
$debit = $a[6] == "-" ? "" : $a[6];
$balance = $a[7] == "-" ? "" : $a[7];
if($balance != "") { // Ignore non-charge entries
fputcsv($out, array($date, $description, $credit, $debit, $balance));
}
}
}
fclose($in);
fclose($out);
fclose($err);Why would you track it as an account?
There are lots of reasons why a public transport card is account-like enough to put into GnuCash:
- Money is not spent until you touch on and off — note that no GST is payable until the card balance is used. This means that if you record a top-up under Expenses, it’s not quite correct.
- You can cancel a myki and have its balance moved to another card.
- A card can be handed in, and the balance paid back to you as cash.
Update 2014-05-04: All of these myki-related scripts are now available on github.