Train graphs in Excel
David Bromage
Excel is quite good for making tabular timetables, but turning the
tables into "string diagram" train graphs is a lot harder. I'm looking
for a (relatively) easy way to generate a train graph from the tabular
timetable. Part of the problem is I avoid anything by M$ like the plague
unless I absolutely have to use it, so I'm not familiar with all the ins
and outs of Excel.
Spacing the X axis by time is fine. One problem I've had is trying to
put the station names on the Y axis and space them by distance.
Another problem is intermediate stations which may not necessarily be
timetable points. "Express" trains don't have a time listed, so the line
on the chart is either broken or assumes a value of zero.
Cheers
David
Kevin Sewell
"David Bromage" <dbro...@omni.com.NOSPAMTHANKYOU.au> wrote in message
news:3EC9BD79...@omni.com.NOSPAMTHANKYOU.au...
I have tried for years to get XL to do that - even in a simple form where
every train stops at every station - but haven't mastered it. One of the
problems is translating the midnight hour turnover onto the graph. I
wouldn't call myself an "XL expert" but train timetabling is the only use I
have put excel to which I couldn't resolve.
So if there are any XLsperts out there who have never given this a whirl, I
echo David's call and encourage you to direct your superior expertise
towards solving. My only suggestion would be to start with one train and
only a few stations and build it up from there. When you do master it, could
you save it as a template and make it available for all of us to play with?
Trevor Hodges
I've forwarded your posting to a friend of mine who was making quite
beautiful train graphs on his computer for use with the Wyong and District
Model Railway Club's latge HO layout. My memory tells me they were in Excel
but I'll let him contact you at his discretion.
--
Trevor Hodges
Armidale
Australia
"Why settle for half when you can have the whole O?"
"David Bromage" <dbro...@omni.com.NOSPAMTHANKYOU.au> wrote in message
news:3EC9BD79...@omni.com.NOSPAMTHANKYOU.au...
David Bromage
> I have tried for years to get XL to do that - even in a simple form where
> every train stops at every station - but haven't mastered it. One of the
> problems is translating the midnight hour turnover onto the graph. I
> wouldn't call myself an "XL expert" but train timetabling is the only
use I
> have put excel to which I couldn't resolve.
> So if there are any XLsperts out there who have never given this a
whirl, I
> echo David's call and encourage you to direct your superior expertise
> towards solving. My only suggestion would be to start with one train and
> only a few stations and build it up from there. When you do master
it, could
> you save it as a template and make it available for all of us to play
with?
There are custom software packages out there that will do this, but they
cost anything from 4 to 9 figures! Mainly because they are also designed
to optimise pre-planned timetables for real railways.
If I was a half decent programmer I'd have a go at writing something in
C or VB and releasing it as freeware (or even open source). The idea
would be to write a package that would generate pre-planned tabular
timetables and train graphs, and generate graphs of actual movements.
With a bit of careful planning it's the sort of thing that could be used
on model railways, tourist railways and by timetable nuts in general.
Problem is I'm not even a half decent programmer. :)
Cheers
David
Jonathan Boles
"David Bromage" <dbro...@omni.com.NOSPAMTHANKYOU.au> wrote in message
news:3EC9F2D1...@omni.com.NOSPAMTHANKYOU.au...
Kevin Sewell
>
> There are custom software packages out there that will do this, but they
> cost anything from 4 to 9 figures! Mainly because they are also designed
> to optimise pre-planned timetables for real railways.
>
> If I was a half decent programmer I'd have a go at writing something in
> C or VB and releasing it as freeware (or even open source). The idea
> would be to write a package that would generate pre-planned tabular
> timetables and train graphs, and generate graphs of actual movements.
> With a bit of careful planning it's the sort of thing that could be used
> on model railways, tourist railways and by timetable nuts in general.
>
> Problem is I'm not even a half decent programmer. :)
Half decent is better than not-at-all. At least you have some idea where to
start.
Kim Hazelgrove
a good price), which used a pre-determined timetable. The chart was less
than spectacular, but actually was passable. We used it for several years.
Unfortunately I've formatted my hard drive since then. I've been actually
cheating - using the grid from Excel (resized) and drawn the lines using the
draw tools. Looks good, but ...
Kimbo
"Kevin Sewell" <kevin...@spamfilteroptusnet.com.au> wrote in message
news:3eca166c$0$28279$afc3...@news.optusnet.com.au...
Kim Hazelgrove
http://home.earthlink.net/~dpssys/ and choose the download. It will be okay
for a few, maybe.
Kimbo
"Kim Hazelgrove" <azl...@iinet.net.au> wrote in message
news:3eca508d$0$22...@echo-01.iinet.net.au...
Keith Norgrove
<jab...@ihug.com.au> wrote:
>What's the input format? I could give it a try :-P
>
Look at this from the other side,
Input is to draw a matrix with stations/junctions named in the left
column and represented by horizontal lines, lines spaced proportional
to distance between stations.
Then with time along top or bottom with vertical lines for time
divisions. To cover 24hrs.
So first input is to enter stations and mileages.
Second input is to draw diagonal lines for the train paths, drawing
tool should allow average speed to be selected and then draw at the
correct angle. Horizontal lines allowed for station dwell time.
Lines, once drawn should be editable by drag and drop.
When you finished drawing it the program should be able to convert it
to tabular timetable format. Even better allow input in both formats
and conversion either way.
Keith
Make friends in the hobby.
Keith
Visit <http://www.grovenor.dsl.pipex.com/>
Garratt photos for the big steam lovers.
Geoff Lambert
<dbro...@omni.com.NOSPAMTHANKYOU.au> wrote:
>Has anybody tried using something like Excel to generate train graphs?
Yes, in two ways:
In one method I simply use the worksheet as the source for an X-Y
scatterplot, but the difficulties you mention mean that the results
are not so good. The fundamental handicap is that Excel plots a
variable (usually the Y axis) against a set of fixed points (usually
the X-axis). In a train graph, what you want to do is plot a fixed
point (the station distance) against a variable (the train time). When
you think about it, this is not the same thing as a simple swap of the
X and Y axes. It CAN avtually be done by swapping the axes and then
rotating the resultant graph 90 degrees (or swivelling the labels 90
degrees and printing sudeways), but one still needs to somehow make
the station distance the variable in the array of numbers used to
produce the scatterplot. At the very least this involves a tedious
editing of the X and Y data in the Chart set-up.
There are other problems in making Excel produce a "conventional"
train graph, including the difficulty of making it produce the
"offsets" at ecah station where trains stop to cross or pass. Another
is the need for an extensive table just to produce the background
station/time grid. Excel scatterplots can be configured to interpolate
missing data, so the absence of a timing point (mentioned in one of
the posts) shouldn't really be a problem.
The other way I use Excel is as a data entry to front-end another
program that does the drawing. Excel, with its row and column
mentality is ideally suited to data entry of timetables in
conventional format. The data entry form ends up looking very much
like the Excel-based timetables that are on the ARTC's Working
Timetables on their web site*. What I do then is use Excel's TRANSPOSE
function to swivel the whole shebang, and output the result to a .CSV
file which in turn is fed to a QBasic progran that produces HPGL
plotter commands ouput. This can be sent direct to a plotter or to an
HPGL interpreter program such as PrintGL that reformats HPGL to the
printer language of your choice- I turn it into Postscript and send it
to a 600 DPI laser printer (or the screen). The QBasic software does
pretty much all of the usual drawing stuff for a train graph:- can be
configured to accept a full timetable but print only part of it (i.e.
by day of the week, or by length of line), has various options like
including or not including the annotated time at each timing point. It
can be used to print a "tiled"output- for instance, I used it to draw
a graph of the NSW Main North line (busiest single line in Australia)
and to tile the result to 16 A4 sheets which I pasted together to make
an A0 sized train graph, about the size of the one the train
controller uses. In addition, it can produce a whole lot of train
running statistics- line occupancy, crossing "busy-ness", etc. etc.
I think the output looks good, but recently I used it to produce some
train graphs for Jack McLean's article on the Maldo line in the
AATTC's Times but, in the end, Jack said he preferred the look of the
ones he'd done by hand. One problem my program has is that it balks at
plotting a timetable for Tuesday on the same output media that it uses
for Wednesday- which is something Jack wanted to show in his article.
It's free.
* Note on the ARTC website's WTTs. As well as a tabular WTT, ARTC have
lodged an Excel-based train graph on their website- a condensed
week-long schedule for Brisbane-Perth. It IS actually in Excel, but
when you download and edit it in Excel, you find that it is really a
very clever Excel Drawing Object and not an Excel Chart. As such, I
doubt that it could have been generated easily from the data, it must
have been laboriously "hand-drawn" using Excel's drawing tools.
Geoff Lambert
*
look at one to see if I can help... ta.
*
look at one to see if I can help... ta.
PčéJäy
>
> If I was a half decent programmer I'd have a go at writing something in
> C or VB and releasing it as freeware (or even open source). The idea
> would be to write a package that would generate pre-planned tabular
> timetables and train graphs, and generate graphs of actual movements.
> With a bit of careful planning it's the sort of thing that could be used
> on model railways, tourist railways and by timetable nuts in general.
>
> Problem is I'm not even a half decent programmer. :)
Give it a go, and then those of us that have some ability can finish it
off/make it work
Chris Brownbill
I have an Excel spreadsheet that does a moderate job of it.
The technique I have used is to use a data-entry worksheet, similar to
the way Geoff Lambert described, although requiring separate columns for
the hours and the minutes.
This data is then processed through a series of intermediate sheets that
basically use the vlookup function to do the transposition so that the
distance in km becomes the y variable against the time being the x
variable. The km value for each Each timepoint is plotted against the
minutes into the day that distance point is reached. The intermediate
worksheet has one row for every minute of the day, and one column for
each train on the data entry sheet. Each intersection cell then
searches the entered data using vlookup to determine at what distance
that train was at that time.
Finally, a graph sheet is produced by plotting each train from the
transposed table. The graph enables the use to distinguish services by
using two different colours, and also identifies stopping points with a
circle.
Because its all in Excel it is quite easy to use, you get immediate
onscreen results and you dont need any extra software to print it.
It doesn't however handle the subtleties such as geoff describes like
offsets for crossing trains, trains travelling over midnight and station
names on the axis. but I am working on these.
I would offer to post it online, but because the solution required brute
force, the file is 22Mb in size. I could cut a CD and snailmail it to
anyone who would be genuinely interested.
Regards,
Chris Brownbill.
Derick Wuen
> Has anybody tried using something like Excel to generate train graphs?
I have managed a "80%" solution in lotus 123 which should work in XL.
Fundamentally the solution involves ...
(1) using X-Y scatterplot graph type (mainly used in
scientific/statistical analysis), with option of connecting the dots
turned on. This tool then behaves like a drawing program and just
about any shape can be created from coordinates recorded in the work
sheet. For example I have used this to draw maps from coordinates
loaded from external files (I found a set of coordinates for every
country in the world in a pcusergroup freeware file, for example).
None of the "business graph" types provides enough control to do a
traditional train graph.
(2) with this general purpose tool linked to speadsheet, the
traingraph solution "simply" involves creating a file of mileage-time
coordinates for every train you wish to graph... time on the X axis
and mileage on the Y axis. Set up 2 columns of data and record all
train coordinates in only those columns. The minimum info needed is
the arrival and departure times (or passing time) for each train at
each station, and the mileage of each station... usually sufficient
data is available from public timetables and mileages of stations. Of
course a WTT will give you more than enough. A station stop will be
plotted as a horizontal line; train paths between stations are plotted
as sloping lines.
(3) One "trick" learned from my mapping exploits is that a blank row
separates one graphed object from the next (country in a map, train in
a traingraph). The drawing program plots the coordinates in each
object to scale, reaches a blank row, pauses drawing, then starts
again, thus not connecting "Australia" and "New Zealand" or the
"Melbourne Express" with the "Camden mixed" plots.
(4) The next "trick" also learned from mapping, is that the XY plot
handles coordinates in a different system used for latitude and
longitude, and indeed train graphs. The "raw" coordinate data may need
processing via spreadsheet formula into something that converts the
natural coordinates to plottable XY data. For example a point in
Australia is referenced as say 130 degrees East, 30 degrees South. To
plot this point in its logical (southern hemisphere hegemonists
excepted!) relationship to UK (50 degrees North 0 degrees E) requires
adjusting latitudes and longitudes into a coordinate system to fit
into an XY system where either southern hemisphere latitudes are
expressed as negative in relation to the 0 axis representing the
equator, or to fit in with a new 0,0 reference point where all
coordinte values to be plotted are positive (GMT/South pole is such a
point). I favour the "all positve" recode, as I find it difficult to
think in negatives, especially in a train graph where you may have to
cope with the concept of negative time, depending on what you want to
present. So you need to think about your coordinate system the way the
XY graph does.
(5) Most "real" traingraphs I have seen plot time horizontally and
mileage vertically, but the mileage is plotted in "descending" order
the way the XY graph looks at it.... if Sydney is zero mileage,
Parramatta appears BELOW Sydney, not above it. This can be done, but
it involves more transformation of the coordinte system. In my view,
the conventional traingraph presentation is just that... a
convention... and other presentations are equally logical. I have not
bothered to reproduce the "descending" order of mileage in the
traingraphs I have drawn, and used a more natural (to the software)
ascending order, which is one reason why I call my solution an "80%"
solution.
(6) Spreadsheets have the ability to do "date and time" arithmetic. In
expressing your time (X axis) data take advantage of this. With a
little bit of practice you can process times at various points by
adding "minutes" to the previous entry for a train. The spreadsheet
processes dates and times as number but presents the data as date and
time. So I format the time column as "time" not numbers and let the
spreadsheet do the work.
(7) Selecting all rows in the two coordinate columns and graphing them
will give you plots of all the trains. But what about grids to
identify stations and times? Continuing in the coordinate columns you
can plot horizontal (the mileage axis) lines for each station by
creating a graphical object with only two coordintes: mileage,
earliest time; mileage, latest time. Similarly you can plot vertical
(time) grids at whatever convenient increment, say hourly: lowest
mileage, time(h); highest mileage, time(h). To minimise clutter,
experiment with turning off tick marks and grids "helpfully" supplied
by the software.
(8) You should have a lovely set plots, but no labels. Spreadsheet
software allows you to define additional ranges associated with the
graphed data which puts labels at various points linked to your
coordinates. Station names, train numbers/names, time scales, mileages
etc. You have a huge amount of control over presentation once you get
the hang of how the labels facilities work. For example you can show
labels for a graphical object (outside the coordinate range) and "turn
off" the plot for this dummy range, leaving the labels only to
display.
(9) Annotation and axis labelling puts a final polish on the plot. You
may want to "forge" the corporate look and feel of the typefaces used
by your railway here.
This general technique can be used for other railway oriented graphs:
curve and gradient diagrams, cylinder indicator card plots, horsepwer
curves etc.
As with many things in sreadsheets, the initial idea is
straightforward enough, and work goes quickly at first. But finishing
the product PRECISELY how you want it takes time, perseverance with
the mindsets of the software developers, and possibly lots of time and
effort. As usual with spreadsheets, document what you are doing by
writing commentary labels as you enter data, otherwise you'll never
remember which block of coordinates relates to which graphical
objects. I'm sure there are more wrinkles than I have jotted down
here, and anyone who wants to pursue this further can contact me off
group where we can exchange binaries.
An example of the sorts of results obtainable is posted in an article
called "Hotspot" in the files section of the yahoo group EarlyRail..
EarlyRail...@yahoogroups.com
This article deals with the splitting and combining of "long distance"
southern and western trains at Parramatta Junction (now Granville) in
the 1863 timetable.
regards
jbsche...@gmail.com
I am interested in acquiring this excel sheet. Can you post or send me a screen.
email: jbsch...@vodamail.co.za
