Reference spreadsheets

April 16, 2014

For the purpose to document our different posts, such as this one, we use to maintain a couple of spreadsheet related to Translink, which I have updated and put under Google doc

Translink ridership, operations and capital datas

This spreadsheet gathers information usually found in the “Translink Statutory Annual Reports, and other relevant reports, like the APTA ridership reports.

ridership 1986-2013

The numbers are not necessarily always matching the Translink reports:
To enable better comparison across different Transit system, the below assumptions are done:

  • Operating costs include the consolidated operating costs, but not the depreciation and debt service costs, neither Translink Police cost (a Translink specificity)
  • Farebox revenues include all ridership related revenues. In addition of the farebox itself: it includes also advertising revenues (and should include fine recovery revenue too)
  • Capital costs include all Capital cost supported by both the taxpayers and Translink
    • Number could be sparsely collected, especially for the Expo line


Due to the Canada Line P3 financing arrangement, it is not possible to separate the operating cost of the debt servicing cost: for this reason the farebox recovery (farebox revenue/Operating cost) is computed excluding the Canada line (both cost and revenue).
A 62.5% farebox recovery ratio is good:

farebox recovery ratio (excluding the Canada line, both ‘operating cots’, and related revenues)

Translink service datas

This spreadsheet gathers information coming from the Google GTFS (Translink schedules) and are appended with some other datas such as the Bus service Performance review. They represent an image of the service on first Friday following the Labor day of every year.

  • Data are extracted of the Translink gtfs feed (most of the precedent years are available here here
  • The Perl script to populate the spreadsheet from the GTFS datas is provided here
    • vehicle type used per route (data used to compute the is as seen of our vantage point: we welcome correction

Some fun facts

  • Schedule bus service average speed is of 22.65km/h
  • Not surprisingly: the slowest service is the #6 (closely followed by #5), at 9.56km/h, the fastest is the #555 at 62.33km/h
  • …but the 99B service is schedule at an average speed of 21.56km/h, still much faster than the bus 9 at 14.5km/h

Notice the mileage per route is computed from waypoint as provided by Translink into the GTFS files – and the quality of them varied greatly – the later years being much better than the earlier (but still need some corrections): The mileage is eventually corrected accordingly in the spreadsheet (assuming the average bus service speed is practically constant at 22.65km/h over years).

The numbers are not necessarily always matching Translink report:

The average speed computed from “Translink Statutory Annual Reports is at ~19.5km (see above spreadsheet). The average speed computed from the GTFS is of 22.65km/h. Other numbers could also mismatch significantly (like poor correlation between service hour /km computed from GTFS and the one provided by the annual report. Here is the proposed explanation:

  • The “annual service” included in the annual reports, include dead-end trip and lay-over in addition of the customer service: that explain also the discrepancy in the average speed
  • The “annual service” is provided on a car basis by Translink: one hour of a 2 cars train service is computed as 2 hour car service by Translink) when it is computed for one hour train service from the GTFS datas
  • Not all trips are included in the GTFS: that is especially for the Skytrain, where special event can trigger additional unscheduled service (e.g. the shuttle train operating between Commercial and Waterfront is not rported in the GTFS)

That said, the service hour variation year over year roughly followed what can be obtained form the annual report (minus the 2010 year)

service hour on first friday following labour day

Thought the previous metric is often used by both Translink and local transit advocates, it is basically an irrelevant one when it is time to evaluate the level of Transit service. the provided transit is a much more relevant one : That has increased by ~18% between 2007 and 2013: of Transit service has increased by ~18% between 2007 and 2013 included

Some facts worth to note

As a rule of thumb, multiplying a weekday service datas by ~330 provide a good approximation of its annual data (that can be verified on the comparison of a daily route service and the annual operating hour per route as provided by the Translink’s BSPR), but we have a rather significant discrepancy -hard to explain by layover and dead-end trip- when come route #96B and #555

route Annual hour service
from GTFS schedule From Annual report [2] difference in %
All 3,841,860 4,950,000 29%
555 13,500 21,400 60%
96B 42,900 62,400 44%

[1] Translink has started to track the metric in 2011. but this metric is not provided in a straightforward way in its reports, and number doesn’t seems very consistent either: the maximum car capacity, 167, is used for the Canada Line service, but the car caapcity is estimated at ~50pax on the Skytrain (and at ~55 on the bus system). We have used the maximum capacity (not necessarily a realistic one, but a consistent one across the board, see vehicles.txt into the Perl package).

[1] 2013 Statutory annual report, Translink April 2014.


5 Responses to “Reference spreadsheets”

  1. Rico Says:

    Any thoughts as to what is causing the discrepancy on the 555 and 96B?

    • Voony Says:

      On the #555, you could consider
      (1) The location of the Port Coquitlam Transit Centre (~15km of the CarVolth.
      (2) The budgeted hour are for a terminus at Lougheed Station instead of Braid Station as actually scheduled

      All that doesn’t apply to the 96B (the Surrey Transit Center is 2km away of Newton)

      For matter of comparison, I have added a row in the last table of this post: the discrepancy if of 30% on average: lay-over and dead-end trip can reasonably explain that.

  2. […] See our reference spreadsheet (which has been updated with the 2014 data) for further detail. [5] We use here the hourly […]

  3. […] have already pointed, in a previous post, the relatively high discrepancy between the total hour of service (including layover, […]

  4. […] [2] Difference between the GTFS data (revenue hr) and the Translink 2013 Annual report (Total service hr). see more in this post […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: