Importing myki data into GnuCash

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.