Analysing Transit Spending with Presto

ABOUT a year ago roughly I started using a new transit fare payment card called Presto. Presto is a top-up card–you pay as you spend–and it promised to unify fare payments for public transit systems throughout the Greater Toronto Area. By and large, it has lived up to its promise. But it does have its fair share of discontents.

One of the things that I was impressed by when I first got set up and registered on their website was they have a table of raw data on your transit usage–what service was used, where you travelled from, date and time, amount paid, etc. It was limited to only the past three months’ worth of data, though. No problem, I thought. I’ll just log in every three months, copy the accrued data into an Excel file, and save it that way. And for the most part it worked.

With this raw data, I imagined that I’d be able to do analysis on things like my travel patterns, bus timings (e.g. if some are regularly late), and of course how much I’m spending per week/month compared to how much I’d be spending with more conventional tickets or weekly/monthly passes.

I made a mistake, though. When copying and saving the data in a nicely formatted Excel file, I didn’t notice that Excel wasn’t properly understanding the date format (dd/mm/yyyy) used in the data and was converting it into bogus dates, for whatever reason. And so I deleted some (to me) extraneous columns in the data, like another column which contained the month in which the transaction took place. So I ended up not being able to do month-to-month-type analyses on a large subset of the original data.

Having learned my lesson, I’ve taken care to not delete any more columns in the fresh data I’m copying down now–just formatting things like transaction times and months when I’m absolutely sure Excel is understanding them properly.

With the new, better, data I managed to do a spending analysis like I originally wanted. And though I’d been a bit sceptical of the savings with Presto for me personally, I became a believer after seeing the figures below.

Before I get into the analysis though, a quick explanation of exactly how payment with Presto works: when you first buy the card at a transit kiosk, you pay some amount out of pocket for them to load the card with initially. When you board a bus/train, you tap the Presto card on a special reader and it deducts the amount of the fare from the card. Then you periodically top up the card to prevent going down to zero balance. You can set it up to automatically charge your credit card a certain amount when the Presto balance reaches a certain lower threshold that you set. This is really convenient and I’ve set up mine to auto-load $40 when the balances goes down to $20.

One more thing to keep in mind before the analysis: all of these months were more or less full working months for me, in which I worked at least 20 days and therefore took at least 40 transit rides throughout the month.

The Analysis Result

The above screenshot shows an Excel PivotTable with one line entry per month. So to go through the figures for September: the ‘E-Purse Fare Payment’ column says I spent $89.96 in transit fare in September; ‘E-Purse Load Value’ says my credit card was charged $80 total throughout September and that balance was added to my Presto card; and the ‘Grand Total’ of -$9.96, being negative, means I actually spent about $10 more than I loaded onto the card that month (since I had some balance left over from August). And so on for the following months.

How does this compare to what I would have spent with the more conventional weekly or monthly passes? Mississauga Transit, or MiWay weekly passes are $29, so for a month’s worth of them I’d have spent roughly $116, give or take a few days. For a monthly pass I’d have spent $120. Now these are basically the most affordable options if you need to ride 40 times throughout the month. You can’t get cheaper than that if you’re working full-time.

Given the above, September was really a very good month–lot of savings. My credit card was charged only $80. October was not so great, but not exactly horrible. Note that my credit card was still only charged $120–the extra $2.80 was from someone who borrowed the card and loaded some money into it by accident. This is no more than a monthly pass, and now let me explain why the spend was greater in October:

The above screenshot shows the same PivotTable as before, just broken down by transit system.

The person who borrowed my card spent about $11 on GO Transit, leaving $117 for my regular travel to/from work, and other places. Still competitive with the weekly and monthly passes.

November was competitive at a spend of $114.37–less than the MiWay weekly/monthly passes despite some extra travel on other transit systems. Again, my credit card was charged exactly $120. The remaining $5.63 was carried over as balance into December–something that is simply not possible with monthly/weekly passes.

December is so far so good. Presto has automatically reloaded early in the month, so for now the credit card has been charged a bit more than I’ve spent on transit this month. But that will have evened out by the time December ends.

All in all, a lot of value for money and a bunch of other benefits (check out the Presto website for details).

Process

The steps from raw Presto usage data to finished PivotTable are fairly simple–if you’re an Excel user, you should be able to mostly figure it out. But the really quick summary: log in to the Presto website, go to the transactions page, show all transactions from the past three months, drag-select (with the mouse) the entire transactions table (including column headings) and paste into Excel. Excel should understand the tabular format and get it more or less right. Get rid of all formatting, then convert the range to an Excel table (select any cell in the data, press Ctrl-T). Format the ‘Loyalty Month’ column (should be column H) with the custom ‘number’ format yyyy-mm. This uniquely identifies the month and year. Finally, create a PivotTable from this raw data with the following specifications:

Happy analysis

Tweet from the Browser Address Bar

This will work on Firefox and should also work on Chrome with a little adjustment. You can start posting a tweet straight from the browser address bar instead of having to navigate to the Twitter website and click on the new tweet button.

In Firefox, create a new bookmark with the following properties:

Name: Tweet (or similar)

Location: https://twitter.com/intent/tweet?source=webclient&text=%s

Keyword: tw (or whatever you like)

Description: Tweet something

Now, with this bookmark saved, go to the address bar and type: tw Just testing. Then press Enter. A new tweet composition page should show up with the words Just testing. Finish off the tweet as wanted and click Tweet.

Is the tweet composition page unavoidable? Maybe not, but I don’t see an easy way to tweet directly from the address bar. And maybe that’s for the best–giving you a chance to finalise things before you publish for the world to see.

Marvel’s The Avengers

Warning: Minor spoilers. I do references specific scenes from the movie, but nothing major.

HARK, True Believers, and let me tell you a story. It’s a simple story, one of raw power and potential, where Good comes together against Evil and drives it back for another day. It’s the original story of the Avengers, straight from their first appearance in comics.

There came a day (unlike any other), when Loki, the Norse god of mischief, unleashed a series of machinations, starting with a clash between the Hulk and Thor. Other heroes got involved by chance or by fate, figured out what was going on, and finally stopped him. On that day they came together as a team, and the Avengers were born.

Since those original comics came out, there’ve been many iterations of the story. Significantly, there was Mark Millar and Bryan Hitch’s The Ultimates, the Ultimate Marvel version of The Avengers. It even provided the source material for the enemy alien armada in the movie, the Chitauri. In a way, The Ultimates was the kick in the pants that started the whole Marvel Avengers movie franchise rolling. So given all that history, where does this movie stand?

First of all, its credentials are impeccable. The story has Joss Whedon’s imprint all over it—the snarky humour, the really fun moments, the horror and the Big Bad (and the hints of the Bigger Bad). Oh, and let’s not forget the destruction of, and escape from, the SHIELD base in the beginning—a nod to the destruction of the Hellmouth in the series finale of Whedon’s Buffy the Vampire Slayer. That was awesome

The screenplay is by Zak Penn, who’s done really good work recently—to me, most notably in his new (and returning) show Alphas, which is like a modern, grounded-in-reality version of X-MEN.

The ensemble cast plays really well off of each other, in a way a lot of ensembles haven’t been able to; their egos and personalities, their senses of humour and honour, shine through; and under Whedon’s guiding hand, through all their neuroses and bickering, they keep a laser-like focus on driving the story forward.

The story: there were many moments that were laugh-out-loud funny—e.g. when Captain America shows a bunch of hardened NYC cops why they should take orders from him. There were moments when I cheered—e.g. when Iron Man shows up in Stuttgart to take down Loki. Robert Downey Jr’s Tony Stark provides the attitude and AC/DC’s Shoot to Thrill provides the soundtrack. Awesome!

On a side note, when they first announced the original Iron Man movie, I was rooting for Leonardo DiCaprio to play Tony Stark. The character was literally based off Howard Hughes (notice how Tony Stark father’s name is Howard) and DiCaprio had proven he could pull off Howard Hughes. But three movies later, I’m solidly in the RDJr camp. He’s proven himself, and not just because of the moments when he’s funny and eccentric, but especially in the moments when he’s dead serious.

As for the characters: I won’t do a headcount (I’ll let Tony Stark do that), but it’s being said that Mark Ruffalo’s Bruce Banner/Hulk provided the heart of the movie. Absolutely true. The wry humour and sarcasm, the world-weary cynic mixed with the eager scientist playing with new gadgets, Ruffalo’s Banner shows us all these sides of the character. As the Hulk, he shows us something almost elemental: rage personified, quick and unexpected as lightning, a force of nature, but ultimately a personality, a man struggling to do what’s right through the thick red haze of anger.

There was plenty of meat in all the characters; there were moments that showed that Joss Whedon gets them all. Take Captain America, Steve Rogers. A man who must feel like he’s suddenly time-travelled 70 years into the future, he’s looking for anything that’ll give him a connection to the past and the present. He doesn’t get most of the pop-culture references spouted off rapid-fire by the rest of the Avengers and SHIELD agents, but when he does, he has one of those ‘A-ha!’ moments that tell him this is still his own world.

Take Tony Stark. What’s the first thing this cynical technologist does when he comes aboard the SHIELD Helicarrier, Nick Fury’s flying fortress and repository of some of the best-kept secrets outside the Vatican? His actions are so quintessentially Tony Stark, they’re almost predictable.

Take the Black Widow. Much of her past is a mystery. In the comics, she’s a lot like a Russian version of Wolverine, given what was done to her by her government. She’s someone who’s trying to make amends; she’s a master spy—clever enough to outwit the god of mischief at his own game. All of these things are hinted at, and shown in her scenes. Every scene she’s given is used to the maximum.

And of course, take Loki. Disgruntled, and carrying a grievance the size of a kingdom, Loki comes to Earth with a plan to take away the world his brother loves and protects. He wants to ‘free’ people from the ‘tyranny’ of freedom—brilliant and twisted; something only Loki’s warped mind could conceive. The size of his ego, and his giant psychotic need for recognition, are such that one after the other, the Avengers and SHIELD big guns profile him and start figuring out his plays. Agent Coulson delivers what I think is the best line in the movie (and that’s saying something) when he explains to Loki why he won’t win.

If there was one character whose essence didn’t come out in the movie, I’d have to say it was Maria Hill. I know a lot of fans were excited to see this relatively new character (in the comics) make the transition to the big screen for the first time, but Maria Hill is literally supposed to have learned spycraft and attitude at the Nick Fury school of badass; she wasn’t really given an opportunity to show that here, maybe given the fact that Agent Coulson seemed to be performing the second-in-command duties in this movie. Oh well, maybe in the next one.

Speaking of Nick Fury’s second-in-command, one character who was rather conspicuously missing from this iteration of SHIELD was Dum-Dum Dugan; especially given that he’d been in the Captain America movie of last year. I guess they would have had a hard time explaining Dugan’s (and then Fury’s) seeming eternal youth (or at least their eternal non-retirement), something they still haven’t explained in the main Marvel comics storyline, as far as I’m aware.

I’ll skip the main plotline and results (the Avengers save the day, what’d you expect), and jump to the delicious little end-of-credits teaser. Let me take a moment here to cackle madly with glee while I tell you, True Believers, that if they carry through with what they’ve shown there (and history says they will), then the Avengers will be going intergalactic, interdimensional, inter-timeline and possibly exploring the boundaries between life and death! *cackle*

Verdict: Loved it. And Long Live Joss Whedon!

Bookmarking in Adobe Reader

I RECENTLY moved to Windows and started using Adobe Reader. The latest version is Adobe Reader X (that is, 10) and I started to keenly feel the need for a bookmarking feature like the one that's built in to Mac OS’s PDF reader, Preview. (Which is quite superbly done.)

A quick run through Reader's menus revealed nothing about bookmarking. Adobe's still calling the built-in navigation links in PDF documents ‘bookmarks’. So that’s a dead trail. A web search turned up a bunch of hacks, some of which work but not very well, and others which just don’t.

Fortunately, Adobe Reader X has a (no hacking required) feature that’s almost equivalent to bookmarking, albeit in a slightly unexpected place: the Comment panel on the right-hand side. If you click the Comment toolbar button on the right, the Comment panel pops up, divided into two sections: Annotations, and Comments List.

So to add a ‘bookmark’: click the Sticky Note button in the Annotations section of the panel, then click anywhere on the page where you want to position the sticky note. You can then minimise the sticky note by clicking the minimise button on its upper-right corner.

As soon as you place a sticky note on the page, it shows up on the Comments List section of the panel. This is awesome because this comments list can basically be used like a bookmarks list, with a click on each comment taking you to the exact page it’s on. And when you’re done with it as a bookmark, you can right-click on it and select Delete.

There is one drawback: every time you close the PDF file, you’ll have to save it, in place, again. The sticky notes, or for our purposes bookmarks, are saved inside the PDF files, and Adobe Reader doesn’t let you just Ctrl+S save a PDF file, it forces you to pick the file in a Save As dialog box each time. Still ... that’s a relatively small trade-off for a pretty convenient bookmarking feature.

Short Note on Blogging

SOME time ago I complained that there wasn’t any good, free blogging software on the Mac. Well, since then I’ve pruned my list of demands somewhat, and learned to settle for some typographical niceties like curly quotes (‘’, “”) and en-dashes (–).

And it turns out that the Mac’s built-in TextEdit editor does these nicely, with a little help from the system-wide Substitutions feature (in TextEdit’s main menu, click Edit > Substitutions > Show Substitutions, and check all the automatic substitutions offered there).

So now it’s just a matter of typing out my thoughts in TextEdit, which laods almost instantly; and dealing with the psychological baggage of navigating to the correct post page in Blogger comes later.

Thoughts on Lion

I UPGRADED to Mac OS X Lion, the latest and greatest version of Mac OS X, tonight. While the update was cheap ($30) and easy (click and run), it was also slow. Roughly speaking it must have taken at least a couple of hours to finish up. At the end of that, though, I found all my files and settings transferred seamlessly into the new OS.

The first question on my mind was whether Lion would slow down my system, given that I have a basic 2009 MacBook. The answer is no; it performs just as well as Snow Leopard in my perception.

On first boot there’s a change right from the start: the login screen is now a single page, not a window, of users and basic system info like battery status, time and Wi-Fi status. And on login the desktop is kind of sent hurtling forward towards you–it seems like Apple has grown fond of the kind of page-transition animations that started out on iOS.

Some of the biggest new features you see right after logging in are Mission Control and Launchpad. Mission Control (shortcut key Ctrl-UpArrow) is basically a new version of Exposé; and Launchpad is like the iOS app launcher–all your applications arranged in grids of icons, ready to be launched with a click. I like the former; but I don’t really intend to use the latter much because my most frequently-used apps are on the Dashboard, or I just launch them with Spotlight.

There are numerous new features scattered throughout the system; for a complete list see Apple’s page. I want to touch on a few of the UI changes.

The first is the look of basic UI elements like buttons, drop-down lists, checkboxes, etc. These are all now rounded rectangles instead of bubble-shaped. That’s definitely a break from the OS X Aqua design that was introduced ten years ago.

The second is the new overlay scroll bars that disappear when you’re not actually scrolling, thus giving you back some screen real estate. This is definitely influenced by iOS–designers had to figure out new space-saving tricks on mobile form factors and these tricks are now showing up on more traditional desktops. However, the new scroll bars aren’t actually enabled by default. In the System Preferences, General page, the ’Show scroll bars:’ option is originally set to ‘Automatically based on input device’, which basically means, ‘depends’. To enable the overlay scroll bars, choose the ‘When scrolling’ option here.

Also, another surprising (and slightly annoying) change is when scrolling with a wheel mouse, the traditional wheel down movement actually scrolls stuff up and the wheel up movement scrolls stuff down. To fix this, uncheck the ‘Move content in the direction of finger movement when scrolling or navigating’ option in the ‘Mouse’ page.

There are a couple of big changes in apps I use frequently. Mail has been redesigned to show all messages in the same conversation on a single page, like Gmail. For this I’m grateful. However, the ordering of the messages in the conversation view is most recent first–for me, that’s upside-down. This can be fixed in Mail Preferences, the Viewing page, in the ‘View conversations:’ section, by unchecking the ‘Show most recent message at the top’ option.

Another change is that the Address Book app has been redesigned to look like an actual open book with a list of contacts on the left and a detailed view of the contact on the right. I liked the old, normal Cocoa UI; to me the new design looks a bit cartoonish, and amateurish. The functionality is still the same, though. One thing I’ve always found puzzling that would be nice to get off my chest: all the operations on a card (a contact) are in the ‘Card’ menu, except for ‘Delete Card’, which is in the ‘Edit’ menu. Baffling.

Another couple of interesting (to me :-) tidbits: Lion has FaceTime calling built-in, and Google Chrome now takes advantage of Lion’s new support for full-screen mode instead of baking in its own.

So bottom line: Lion continues the evolution of Mac OS into something more iOS-like, but mostly familiar and comfortable for OS X users. Plus, it’s much easier to say ‘Lion’ than ‘Snow Leopard’, so that’s a win right there. :-)