Making Train Graphs from Excel spreadsheets

 
  Maikha Moderator Not a gunzel

Hi all

I frequently use Excel to work out train times by using the Time Formula (Which is for those in the know, =TIME(hour,minute,second) and filling the hour, minute and second accordingly) and this has worked well for me thus far.

However, does anybody know if there's a way to use this data for Excel to automatically draw up a Train Graph, similar to what the Train Controllers use? I'm certain there was a way to do so (And recall a thread Bromage made many years ago, but can't seem to find it), however I've come to a standstill in finding the answer, so I thought I'd post it out there to see if anyone else may know.


Many thanks for any help that can be provided! Smile

Sponsored advertisement

  wongm GEEWONG

Location: Geelong, Victoria
There is a member here who has done as you describe, will shoot him a reminder email. Wink
  mnel Assistant Commissioner

did you mean this thread

http://www.railpage.com.au/f-t1012-0-asc-s15.htm


had a go in excel using x-y scatter plot with the data from that thread, the results are below....



To get the station names, create columns that have data in two rows, the km and the two time extremes that you are graphing over, with the series name as the station name (in a cell somewhere), then label these data by "data label.

The data in the old thread


 Dist                      467
from                      Exp
Sydney                     Frt

485.670  Junee        arr  4 31
                     dep  4 41
497.612  Harefield    arr  4 54
                     dep  5 04
504.614  Shepherd's
513.691  Bomen        arr  
                     dep  5 17
521.160  Wagga Wagga  arr  
                     dep  5 23


                 468
                 Exp
                 Frt

Wagga Wagga  arr  
            dep  4 40
Bomen        arr  
            dep  4 48
Shepherd's
Harefield    arr
            dep  5 01
Junee        arr  5 14
            dep  5 24


"DavidB"



This is how I loaded the data into excel


  thelastname Photo Overlord

Location: Somewhere
I've done it  a different way, I can't remember how I did  it. The files are on my other computer, which I wont be able to use till the weekend.
  YM-Mundrabilla Minister for Railways

Location: Mundrabilla but I'd rather be in Narvik
As the original request was to produce what I shall call a 'proper train graph' my comments are towards achieving precisely that.

No doubt it may be possible to achieve electronically but some computer whizzes I work with have not been able to produce a 'proper train graph' - to my satisfaction anyway.

The first decision is whether you want a NSW, Victorian or AN style graph where DOWN trains read down the graph and UP trains upwards or a WA graph which, of course, does the opposite.

Anyway take an Excel spreadie and put the stations/locations you want in column A.
Pick a scale to represent the various sectional distances and adjust the row height accordingly.
Put the time intervals you want across the top having chosen a suitable column width to suit the result you need.
Using the Excel draw function simply join the locations at the appropriate times.

It's not electronic but it's simple and effective and you know what you are getting unlike a simulation dished up by a consultant recently which was  not only hard to read (rubbish scales and directions etc) but the four trains depicted all ended up in a computer generated collision around Thursday lunchtime.

Simple, clear and effective even if somewhat manual.
  TE2815 Minister for Railways

Location: Mission control Minto or Thirlmere
That looks plausible except that the times go on the horizontal axises with the locations being on the vertical axises.
  YM-Mundrabilla Minister for Railways

Location: Mundrabilla but I'd rather be in Narvik
That looks plausible except that the times go on the horizontal axises with the locations being on the vertical axises.
"TE2815"


True, that is what I was trying to say. It may be tedious, but at least you know what you are getting and if you get garbage out it is because you have put garbage in (as distinct from computer generated garbage).

You can also adjust the scales to suit daily, weekly and even longer periods to cater for eccentric schedules.

Regards
  flying_donkey Chief Commissioner

Location: Well, at the moment, right here!
More a piece of useless info than anything, but [i]this mob[/i] seems to supply nearly all the Australian train control companies with train planning software.

If you click on the 'Train Control Diagrams' tab, and then scoll down, you'll see a large portion of an ARTC graph for the Upper Hunter 2 board.

Still probably doesn't help Maikha with his Excel problem though Smile.
  TW10 Junior Train Controller

Location: Anywhere in Scandi
Hi all

I frequently use Excel to work out train times by using the Time Formula (Which is for those in the know, =TIME(hour,minute,second) and filling the hour, minute and second accordingly) and this has worked well for me thus far.

However, does anybody know if there's a way to use this data for Excel to automatically draw up a Train Graph, similar to what the Train Controllers use? I'm certain there was a way to do so (And recall a thread Bromage made many years ago, but can't seem to find it), however I've come to a standstill in finding the answer, so I thought I'd post it out there to see if anyone else may know.


Many thanks for any help that can be provided! Smile
"Maikha"


Do a Google search and you will find some. Among these will find:

* stringline.xls and stringline2.xls;
* Trainschedule120
* scheduleit; and
* MRTT
  thelastname Photo Overlord

Location: Somewhere
I've done this one in excel, it's not as flash as the others. But it gives a general area of when and where trains should cross, as you know trains don't always run on time.

[bigimg]http://img5.imageshack.us/img5/7923/westernsgf.jpg[/bigimg]
  ljwu716 Beginner

I've done this one in excel, it's not as flash as the others. But it gives a general area of when and where trains should cross, as you know trains don't always run on time.
"thelastname"


Hi,
I was searching online to try to find a way of doing Train graph by using Excel. Your train graph looks very impressive. How did you do it Excel?
Thanks.
  BBR Train Operations Beginner

Hi All

I recently needed to do a Train graph for an up-coming event at Bennett Brook Railway, and a search lead me to this thread.

I found "mnel"'s post above most useful in getting me going in the right direction, but it took quite a bit of fiddling around to get to a result that fitted what I needed. The 2 screen shots below show where I am up to currently.

[img]http://www.mediafire.com/conv/9c80826fcd53e95a55f55902e178c91a88edfd35b5b7438eba5d9e9873e4febd4g.jpg[/img]

[img]http://www.mediafire.com/conv/a1ea51689e7bf617848016df402280a5c4fbfe6d900cd3ec150c3db398a34ffc4g.jpg[/img]

As the operations get quite intensive, I needed to be able to look at track occupance at each station, and out to station limits, hence, for instance, the 5 lines for Whiteman Village Junction (WVJ). These represent platforms 1, 2, 3, siding track 4, and WVJ North Jct. The line departing WVJ north forms a 5km circle, returning to WVJ from the south, hence WVJ South Jct at the top of the graph, with the vertical "jump" from there to one of the WVJ station tracks.

On this graph I have used blue for passenger trains, green for freight, and red for a mixed train, but you can select almost any colour you like.

Once set up, you can delete all the train data (may have to leave one point - haven't tried this yet)and as new data is added, a new graph is drawn.

If you hover the cursor over a train path on the graph, a dialogue box appears showing the train number, and time and location of the neares node. If you click on a train path, the data for that path is highlighted.

What I had not expected was that you can click on a node on the graph and drag it to a new location (say a different time) and the input data is adjusted accordingly. It can be a bit tricky getting on to the right node though if the graph is a bit congested at that location though.

The extra column for "location" in the input data is with the thought that it should be possible to enter a text location and get Excel to look up the corresponding distance.

To show where a train occupies both the main and loop at a station while the loco runs around, a loop is drawn in the train path by switching across to the loop at the end time for the shunt move and then entering an earlier time next corresponding to the start time for the shunt, back across to the main, and then the next time is departure time.

Hope this is of interest/use.
  zion12 Beginner

Hey.. So this whole system graphs are generating using excel sheet data right ?? I have to do a one like this too.. Can i know more details about the system.. Did you implement on .NET or Java ?

Thank you
  nambiar10 Beginner

[quote=BBR Train Operations]Hi there - I cam upon your posting via a google search for Train GRaphs. I am trying to create something similar. Would you be able to share the spreadsheet that you had posted on your forum. My email address is nambiar10@gmail.com.

Thanks,

R. Nambiar


Hi All

I recently needed to do a Train graph for an up-coming event at Bennett Brook Railway, and a search lead me to this thread.

I found "mnel"'s post above most useful in getting me going in the right direction, but it took quite a bit of fiddling around to get to a result that fitted what I needed. The 2 screen shots below show where I am up to currently.

[url=
  4BJ Chief Commissioner

Location: Backside almost trackside at Hawthorn near Mitcham
I know it has been some time since the previous post on this thread.

I have found a solution that works for me.

Instead of using the "hh:mm" format for train times, I have found "=(hh*60)+mm" works better.  Converting 24 hour time to minutes after midnight time works with Excel.


4BJ

Sponsored advertisement

Display from:   

Quick Reply

We've disabled Quick Reply for this thread as it was last updated more than six months ago.