Handy quick-link when you need a map of Charlottetown: ruk.ca/map.

The second piece useful analysis to come from my bank statement parsing exercise (the first being how much I spend on coffee) is a graph showing where I spend my money. It’s not a complete picture of my finances because it doesn’t include expenses paid with my credit card, which includes a lot of things like household expenses at Canadian Tire and travel expenses. But it’s a start:

Building on the work I did earlier in the week to automate the download of credit union statements, I’ve now created code to parse those statements and dump the transactions into a MySQL database table.

The memberdirect-parsestatements.php code will ingest a directory full of MemberDirect electronic statements and insert a database record for each transaction. Once you have your transactions in a database, you can then do fun things like this:

SELECT date_format(date,"%Y-%m") AS yearmonth,sum(amount) AS total 
  FROM transactions 
  WHERE amount < 0 AND item = 'CASA MIA' 
  GROUP BY yearmonth

that shows month-by-month spending at Casa Mia.

I’m not sure how much the electronic statement format varies from MemberDirect credit union to credit union, so the script may require tweaking if you’re not using it to parse Metro Credit Union statements.

Remember my promised credit union statement parsing code. Well, it’s coming soon. But in the meantime I’ve learned that since July 4, 2007 (my first visit) I’ve spent $1982.67 at Casa Mia on 179 separate visits for coffee and related items.

This is less than I spent on electricity ($3142.92) and property taxes ($4475.38) over this period, but more than I spent on telephone service ($1402.40), shampoo and sundries at Shoppers Drug Mart ($904.34), and water and sewer for our house ($783.53).

Here’s my Charlottetown restaurant spending breakdown for January 1, 2007 through May 31, 2009:

Conclusion: I eat out a lot (I spend an average of $7 per day at Charlottetown restaurants). Is there a possible [[reboot]]-style action item here? At the very least, I’m well on my way to my “spend $2,000, get a free cup of coffee” plateau at Casa Mia!

The Alibi Lounge opened recently at 77 University Avenue in downtown Charlottetown, next to Cedars and across from the Atlantic Technology Centre. I had lunch there.

The Good

• Transformed interior: barely a hint of former life as failed Indian and Chinese restaurants.
• The music is the best restaurant music in Charlottetown: perfect volume, excellent mix.
• Friendly staff (although my jury is out on being called “darlin’ ” by someone younger than me).
• Thai chicken soup: tasty, hot, homemade.

The Bad

• Password-protected wifi (staff advice was to “use the open wifi from next door at Babas”).
• Club sandwich was run-of-the-mill; hint of tarragon elevated it, but the poorly-toasted bread took it back down. It wasn’t horrible, but it didn’t change my life or leave me wanting to have it again.
• The napkins are made of a silk-like fabric. Napkins should never by slipperly.
• Fortune cookies: they taste bad in Chinese restaurants, they taste bad here. I get the irony, but how about something that’s ironic and tasty?

At the end of the [[reboot]] conference this year I ended up walking to dinner with Christian Dalager and I mentioned that my “one actionable idea” contributed at the end of the conference was to create an RSS feed of my banking transactions.

As it turned out, I was talking to the right man: not only was Christian interested in this notion, but already had done work to parse his own Danish bank’s electronic data (in his case he had to screen-scrape, a considerably more ambitious task than I hoped to require).

My “bank” is actually a credit union, and my credit union uses the same MemberDirect web banking platform that many other Canadian credit unions do.

My first thought when approaching the task was to simply grab data in the handy comma-delimited ASCII file format that’s available under “Activity Format” on the “Account Activity Request” page of the site. Unfortunately the transaction data this provides only includes “metadata” — like the actual place I made a debit card payment, for example — for the current month’s transactions; before then it’s only dates and amounts. This would be fine if I wanted to start my data with the current month (I could just made sure I grabbed the data before it lost its metadata layer at the end of the month), but I want to go back in time as far as possible.

Fortunately there’s another option: MemberDirect now makes “Electronic Statements” available for download going back, in my case, to the start of 2007. While the statements are PDF files, and therefor require some additional parsing to grab transaction data, they are metadata-rich and thus the best candidate for my project.

Rather than going through the process of downloading the 29 PDF files currently available to me, I decided to write a script that would login to my MemberDirect account and grab all available statements automatically. Fortunately this proved to be quite easy: the MemberDirect authentication model simply accepts an HTTP POST with the username and password, and sends back a session cookie that can then be sent back for all future requests.

After some experimenting and parsing of the HTML that gets returned, I’ve come up with memberdirect-getstatements.php, a PHP script that uses cURL and the PHP Simple HTML DOM Parser to login, grab the index of available years, and then download all available PDF electronic statements.

This script isn’t for the faint of heart: you’ll need a PHP-equipped host with cURL (if you have a modern Mac you’ve already got both) and it’s possible that the script might need tweaking for credit unions that aren’t mine.

More information is (or shortly will be) available on this wiki page. Comments and experiences welcome.

My next step is to write a parser for the Electronic Statements that will allow me to do some basic “how much am I spending on coffee”-type data analysis.

Update: you can grab the statement parsing code here to extract transaction data from the electronic statements into a MySQL table.

8:25 a.m. - Arrive on Bus #1 at the University of PEI. Bus pulls right into the parking lot as it usually does.

10:03 a.m. - Ready to depart UPEI. See posters on bus shelter advising the the bus will not pull into parking lot as it usually does, but ignore these given 8:25 a.m. experience.

10:07 a.m. - See a bus drive by on University Avenue. Assume it was my bus and that posters are actually accurate. Phone Charlottetown Transit and seek confirmation; friendly operator puts me on hold, calls the bus that drove by and asks driver to come back and get me.

10:10 a.m. - Bus comes back to get me.

This is what it’s like to live in a small town with a customer service-driven transit company.

When we arrived at Copenhagen airport earlier this week to fly to Toronto via Helsinki on Finnair the check-in area in Terminal 2 was in chaos due to a broken luggage system: every few minutes the system would start up again, a few bags would get through, a few more people would get checked in, and then things would break again and everything would come to a standstill for another 20 minutes.

Eventually we did get through and, somewhat surprisingly, our flight took off almost exactly on time. In retrospect we should have known this was a bad sign.

Eleven hours later when we arrived in Toronto I was happy to see my bag roll onto the carousel at the head of the line. Then we waited and waited and waited for Catherine’s bag — checked in at the same time and almost identical but for the colour. It never arrived.

As a result I got to know a lot about the lost luggage system that the airlines have built. Here’s a few things I learned that you might find helpful when this happens to you:

• If your destination is an airport that’s not a “hub” for your airline, but is rather a rarely-visited or seasonal destination, it’s likely that the airline won’t have a dedicated staff on the ground and has outsourced “ground handling” to another company (indeed this is often the case even if you are flying through their hub). In our case Finnair appeared to have no local staff in Toronto and Servisair was the local contractor.
• Don’t throw away the luggage tags of any bags that do arrive. I made the mistake of throwing away the tags on my suitcase before we realized that Catherine’s had gone missing, and so the Servisair clerk had no way of telling which bag needed a trace. Fortunately I was able to fish my tag out of the garbage.
• Make sure you hold onto your luggage tag “receipt” — that’s the portion of the luggage tag they peel off and stick onto your boarding pass or passport when you check in. I never realized how important this was to the process of tracking a missing bag: it has the all-important tag number that’s needed to initiate everything.
• Before you leave the airport make sure you have (a) the phone number of the ground handling agent where you can call for updates and (b) the WorldTracer file number for your lost luggage. WorldTracer is a cross-airline system for tracing baggage, and for airlines that belong you can use a web application — here’s the one for Finnair — to get status updates on your trace.
• The “file number” that the ground agent gives you might not be the full file number. We were given one in the form “AYXXXXX” (AY is the airline code for Finnair) that WorldTracer wouldn’t accept: it turns out that we needed to prepend the airport code for our destination airport — YYZ — to this code (so that it became “YYZAYXXXXX”).
• Don’t expect there to be a 24/7 call centre that you can call for updates. In our case the Servisair office in Toronto was open from 10:00 a.m. to 6:00 p.m. daily, but the number was never actually answered by anyone and we always had to leave a message and wait for a call back.
• Don’t expect the local ground agent to feel any greater sense of responsibility to the airline you traveled on. Our agent was helpful, but made it clear that Finnair wasn’t providing him with the digital information he really needed to help us, and wasn’t in a position to do anything about this.
• Don’t expect compensation for out-of-pocket expenses at the airport: I’m not sure what the regulations are about this, but when we asked in Toronto we were told that Catherine could get a voucher for expenses only if the bag didn’t show up in 48 hours.
• Don’t expect the bag to show up soon. In our case Finnair only flies from Helsinki to Toronto three times a week, and we were warned not to expect the bag to arrive before the next flight, and perhaps not even until the one after that.

As it happens, Catherine’s bag was ready for us to pick up at the airport on the Saturday after it went missing on Tuesday, just in time for us to pick it up and fly back to Charlottetown. Catherine had a devil of a time finding where to pick up the bag at Terminal 3 in Toronto — she ended up at an office that was neither Finnair nor Servisair. Oddly, she had to show neither identification nor the original baggage tag to be able to retrieve her bag.

As you might imagine, she’s very happy to be back in Charlottetown and wearing clean clothes again.

Last week in Sweden my old [[reboot]] friend Guy Dickinson popped over from Copenhagen for the afternoon and we spent an afternoon and evening drinking dreadful tea, eating Caribbean food and catching up on each others lives since the last reboot.

Several times Guy mentioned his ravenous consumption of reading material through Instapaper, to the extent that when I got back to Canada this week I suggested to Guy that he blog about this. And so he did. It’s an interesting and comprehensive post in the “how I use the tools I use” school, and if you consume web text you should read it.

I mentioned to Guy that it wouldn’t be such a bad life to “simply spend my time asking my friends to write about things I’m curious about.” He replied “Like a newspaper editor?” If I could run a newspaper like that, then yes. That would be a lot of fun, and probably the best and most interesting newspapers and magazines are exactly that.