Vehicle Time Table into Movement Graph - MS Excel

[PAC820109]

Good Day,


I work in Public Transport planning, we compile our time tables (origin + origin time and destination + destination time on a specific route / path) in Ms. Excel. Once the timetable is completed it looks something like this:

https://ibb.co/ccNzSNB

You will notice that the timetable is for a specific route (105) and it is for trips in a Forward Direction (F) and return Direction (R) in separate rows etc.


We then "manually move the trips (forward and return) onto a "vehicle movement graph" which looks like this:

https://ibb.co/k9Rc1kw

The vehicle movement graph is a 24 hour day seperated in minutes from left to right and we then "block" out the time on the graph with the details of the specific trip on the row for a vehicle (each row represents a vehicle). We continue allocating the trips to time blocks, if there is a conflict we add a vehicle to do the trip

For example vehicle 1's time is "blocked" for trip 1 between 5:52 and 6:34 on the forward trip, therefore vehicle 1 cannot do trip 2 between 6:03 and 6:50 on a return trip, therefore vehicle 2 is added.
For trip 3 on the return journey we need a time "block" between 6:21 and 7:07 to operate the trip, both vehicle 1 and vehicle 2 is committed still on their first trips, therefore vehicle 3 has to be added. This continues all the way through.

Another example is trip 6, we need a time "block" between 6:37 and 7:28 on a return journey to operate this trip. The first check is if one of the vehicles already added to the movement graph has the "open time" and secondly it needs to be at "Destination A" at the time to operate the trip back to "Origin A", in the example above, vehicle 1 arrived at the "Destination A" at 6:34 therefore it is available to operate trip 6 back from "Destination A" to "Origin A".

This complete process is currently "manual" on our side. It is a critical process since we need to know how many vehicles are required to operate the time table that has been drawn up - this is the end result, given the time table, X number of vehicles will be required to operate the time table.

The result does not have to be visually as in the example (although it would be nice), it also does not have to be colors etc, the "time blocks" can be indicated as XXXXX etc., it does not matter. I would however run some query / macro or formula to have a "number of vehicles result at the end.

What am I requesting?

I need to have some form of query / macro or formula to have a number of vehicles result at the end to try and remove the "manual" process from moving trips from the timetable to a vehicle graph. We have a large number of route timetables and will have to do all the vehicle determinations "manually". In short:

1. A formula or query to move the trips from the time table to "fit" (in time blocks") onto a line graph indicating which vehicle can fit in which of the timetable trips in its operational movement time would be the best result.
2. If that is not possible, perhaps some formula or query that still follows the process as described and yields an end result for the number of vehicles would be the second best result, HOWEVER we would still have to display the visual graph.

Could someone please assist?

 

[baitmaster]

Hi, I'm not able to look at your files at the moment as I'm on a restricted network but I'll look again later. I have some questions though, since what you're trying to do is a fairly specialist area where mistakes could be quite costly for you

Can you elaborate a little more on the type of work, and number of journeys you're looking at? Is this e.g. a standard weekly bus timetable, or something a little more bespoke? And what country are you in?

For info I've extensive experience working on real-time scheduling, calculating the most efficient means of performing >750k movements across the UK and hence identifying vehicle sizes & quantities, staff requirements, base locations for minimising stem mileage, staff shift patterns etc. Depending how much work you're looking at you may well be better off looking at specialist software e.g. Oracle Real-Time Scheduler than just trying to botch something in a spreadsheet - RTS will take into account GPS locations, Google Map routes, average traffic flows and then optimise your solution for you. Taking a couple of vehicles off the road along with the associated fuel and staffing could be worth hundreds of thousands of pounds to you - hence my questions

If you think it won't be worthwhile then that's fine, it's still possible to do something in Excel. I'll look in on this thread again this evening when I can do more for you

 

[PAC820109]

Thank you for the reply @baitmaster!

We do public transport consulting work, we are in the process of talking to Omnibus (UK, awaiting final proposal), Optibus (Israel, received quotation etc.) , Goal Systems (Spain, received initial quotation, many questions )etc. for a full scheduling solution all the way through to drivers duty scheduling etc.

For my current project I only have to compile the time table (60+ routes, 10 000 + journeys per day) for a number of different bus types (seating capacity) and then determine the number of buses per bus type required to operate the time table - I can do all this manually, and probably will still have to. The one major problem with the "manual way" is if we change for example the commercial speed / running time in the time table for any of the routes or trips on routes it would mean a complete restart of the manual process to re-allocate the buses on the line graph, I thought there might be a programming / macro / formula way of doing this quicker in excel - just for a scenario, not for finaliation etc.

Hope this helps??

 

[baitmaster]

I think you're right, there will be a way to programme this, so long as you're clear on the algorithm that you need to follow. One downside will be that it doesn't help you with the efficiencies, optimising the vehicle mix / timetable, nor how to manage when there are too many customers

On that note, how do you decide which size vehicle you will use on any route? Do you simply say up front that Route X will be a 40 seat bus regardless of time of day, or do you have historic data of hop on / hop off rates by stop so you can see expected number of passengers on each leg of the journey? Are you concerned about managing peak passenger demand, what do you do if 100 people want to be on a 40 seat bus at the same time? Perhaps these types of question will be clearer from your example file, so I'll see them later. And do you have a fixed depot that already exists? Do you have locations for all these places, or at least stem mileage from depot to start/end point? You can't schedule Bus A to be on Route B if they are 100 miles apart at the time... Then there's how to manage resilience (breakdowns, staff shortages) and staff welfare / breaks. You'll probably need to factor all of these into a realistic timetable, for example by adding 20 minutes onto every bus route

You'll also want to know vehicle prices (are these already owned? leased? to be purchased? are there several options new vs old etc.), running costs (servicing, fuel MPG and cost, repairs, insurance by vehicle size, any specialist kit), staff costs, agency staff costs, stuff like that, because they can all guide your thinking on scheduling work... but I digress. I'll come back to you IDC

 

[PAC820109]

Thank you again, you are right with all your comments. All of the business improvements and efficiencies has already been taken into consideration in the time table for now. With regards to the bus sizes etc.

To simplify the way we are doing this now we used the demand (a comparison between peak demand and maximum demand) from a survey conducted and allocated the trips on the time table accordingly.

In the example I did not include various vehicle types (because I believe that this will overcomplicate the scenarios at the moment), therefore just assume a vehicle needed for the time between Departure time and Arrival time on the time table for every trip. Also, if the vehicle operated on a Forward Trip, it will wait at the Destination to operate back to the Origin on the exact same route etc. (obviously in reverse). What I will be doing is splitting the route time table per vehicle type and then "running" (HOPEFULLY) some algorithm / formulas on each of the vehicle types....

You are 100% about the depot locations etc., BUT that has also been sorted out in the Business Modeling (a parallel process for our project), so I literally just need to be able to put 100 odd forward trips on a route and 100 odd reverse trips on the same route, at specific times, (as in the example) and be able to see how many vehicles will be required to do the specific route and the Forward and Return Trips.

I can also mention that the services will be scheduled as a route almost being a "contract allotment", in other words, in a perfect scheduling world a contract might have 8 routes with 8 different time tables to operate the 8 routes. You can then use a vehicle (depending on its location after completing a trip) to operate any of the 8 route time table trips - that is the efficiency you are also indicating - IN THIS CASE THOUGH, it will be a route by route vehicle allocation, therefore each route will have its time table and each route will have its vehicle requirements.

Hope to further hear from you!

 

[baitmaster]

questions:

- is there any logic in your starting times? I notice they aren't say every 20 minutes, so is there method here, or will you be manually playing around with these?
- is there any logic in your route durations? the same route varies by 20 minutes, is this due to traffic, or are you factoring in other things like scheduled driver breaks?

So in both cases are these both subject to manual adjustment or is it possible to define a matrix of rules that can be easily manipulated in order to generate many data sets quickly? You're current plan is to have a set of times pre-written that can be quickly graphed, but if you can state the rules in a table then that element could be automated too...

For example, rules might be:
- between 6 and 7 am, journey times are 5% less than average
- between 8 and 9, journey times are 10% above average
- between 10 and 11, ensure buses incur 30 minutes downtime between journeys
things like that...

finally, I sent you a PM for better understanding but now I can't find it. Did it come through OK? It would help me to have a larger data file to play with, could you send through your data file for me to look at?

 

[PAC820109]

Good Morning, let me answer your questions:

1. The timetable was compiled based on the demand requirement (we did an extensive on-board survey) to move the passengers, it is not a "headway" or "frequency" based time table - all 60 + routes will be like this. Therefore the "logic" is that the demand at the specific time will determine the trip time.
2.The route / trip durations were observed during the survey process, yes, it is linked to the traffic issues, traffic management issues etc. - the trip running time (durations) could be standardized, the times in the example timetable was the exact observations for the specific services at the time.
3. I understand the matrix and "automation" with rules etc., at the moment the timetable and running times is what is observed and needed for the services - I would need to be able to use the example as is and determine the vehicle movements and requirements.
4. I did not receive the more detailed PM, the example is an actual route with it's time table (I just removed the details since I did not think it is necessary to have). I could send a route with more trips if this will give you more to work with, however, if you can make the example work, the route/s with more trips should also work? BUT if you need a route with more trips, please let me know.

Thanking you in advance for your assistance on this!

 

[baitmaster]

Messaging: I'm getting a notice that "PAC820109 has exceeded their stored private messages quota and cannot accept further messages until they clear some space" which is strange since you're new here... anyway if you could try to PM me instead that would be helpful

I've looked at your files and used them to generate a basic concept, which will act as a starting point for you. You'll need to recreate a file containing this code, it will need 2 worksheets, sheet1 = a data table and sheet2 = a timeline with one header row

Sheet 1 data table = 2 rows, the first is named "rngJourneys" (Using Formulas > Name Manager, only name the used section, not the whole row). The second and third rows contain start/end times, in time format. There is another cell on this sheet named "downTime" which is also in time format and ensures a suitable amount is blanked out after each journey to allow for driver rest period, flexibility in timetable, anything you want. If not wanted, set it to 0

Sheet2
A timeline that looks like yours - very thin columns representing minute blocks, with groups of 60 being hours. A single header row (if more required, code will need amending) containing an integer in the first column of each block, which is the hour that block relates to. You can merge these cells if wanted

The following code is added to a new code module, which is hopefully self explanatory

Option Explicit


Const iOutputHeaderRows As Integer = 1  ' number of header rows on Sheet2, the outputs sheet


Sub processVehicles()


' declare local variables
Dim cl As Range
Dim iJourney As Integer, dStart As Date, dEnd As Date   ' times are declared as dates
Dim dDown As Date: dDown = Range("downTime").Value      ' a fixed value to be added to the end of every journey. Can be 0 if not wanted


Dim iColStart As Integer, iColEnd As Integer
Dim iRow As Integer, rngBus As Range


For Each cl In Range("rngJourneys")                     ' rngJourneys is row 1 on a 3 row input table of journey numbers, start times and end times
    
    ' get parameters from data table
    iJourney = cl.Value
    dStart = cl.Offset(1, 0).Value
    dEnd = cl.Offset(2, 0).Value + dDown
    
    ' find hour columns. If columns not found then 0 will be returned, and system should reject these
    iColStart = getHourColumn(Hour(dStart))
    iColEnd = getHourColumn(Hour(dEnd))
    
    If iColStart = 0 Or iColEnd = 0 Then
        MsgBox "ERROR: one or more times could not be found in the timeline", vbCritical
        Exit Sub
    End If
    
    ' add minutes to columns
    iColStart = iColStart + Minute(dStart)
    iColEnd = iColEnd + Minute(dEnd)


    ' start at top of data and move down sheet until an entirely valid time slot is found
    iRow = iOutputHeaderRows
    Do
        
        ' create range object for this journey
        iRow = iRow + 1
        Set rngBus = Range(Sheet2.Cells(iRow, iColStart), Sheet2.Cells(iRow, iColEnd))
        
        ' check if whole range is empty
        If WorksheetFunction.Count(rngBus) = 0 Then
            
            ' report variables to immediate window, in case issues encountered
            Debug.Print iJourney, iRow, iColStart, iColEnd
            
            ' pass bus number into the results table and add colour
            With rngBus
                .Cells.Value = iJourney         ' may not be visible due to thin coloumns
                .Interior.ColorIndex = iJourney ' change colours as required
            End With
            
            ' success - jump out of loop
            Exit Do
            
        End If
        
    ' keep looping until range is empty
    Loop
    
' move to next journey
Next cl




End Sub




Sub resetJourneys()
' clear all data from output sheet by deleting rows up to row 1000 - removes data & colours but preserves whole-column borders
Range(Sheet2.Rows(iOutputHeaderRows + 1), Sheet2.Rows(1000)).Delete xlUp
End Sub




Function getHourColumn(h As Integer) As Integer


' search hourly headers for hour band. Headers MUST be integer values, but can be formatted to look like time values
With Rows(1)
    Dim cl As Range: Set cl = .Find(What:=h, After:=.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
End With


' if not found then outside of timeline - return zero value (else would generate error)
If cl Is Nothing Then Exit Function


' return column in which hour band was found
getHourColumn = cl.Column


End Function

 

[baitmaster]

Once you've got the file set up as described, then to implement the code perform the following actions, starting from the Excel window:

- Alt + F11 to open the Visual Basic Editor (VBE)

- find the relevant workbook's VBA Project within the VBE Project Explorer window

- right-click anywhere on that project and select Insert > Module

You should now see a white space appear in the main part of the screen, this is the code window, in here paste the code I wrote above. You can now close the code window, the code is embedded within the spreadsheet and so you won't be asked to save anything because you aren't yet closing anything, only the view of the code

Back in Excel, check you're on Sheet2 then go to View > Macros, and from the pop-up you can run the piece of code processVehicles