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