Reference spreadsheets

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
• Translink service datas

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

Notice

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 capacity.km) 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 capacity.km is a much more relevant one : That has increased by ~18% between 2007 and 2013:

Capacity.km 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 capacity.km 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.