Re: Graphing timetables in excel

[G.Lambert@unsw.edu.au (Geoff Lambert)]

Michael Chinn <qamh-michael@powerup.com.au> wrote:

>I have just begun to chart some timetables in excel 4.0 to see if I
>could do it. It works well although the charting function leaves alot to
>be desired. I was wondering if anyone knew of any software out there
>that can create simple time/distance graphs (I dont know their correct
>name) as the spreadsheet I have created is getting to big and the one I
>want to create is beyond what I can do 

Yes.  Me.

I wrote a QBasic program that accepts timetables in ASCII form and
turns them into HPGL format.  These can be sent to a plotter or
printer that can handle HPGL.  Alternatively, they can be shunted
through a program called PRTGL which turns HPGL files into PRN files
for most types of printers, including Postscript.  HPGL also allows
screen display.  Colour too.

My program also does a number of statistical analyses on the traffic-
number of crosses, percentage line occupancy, etc.  Also the details
of which train meets which on single lines.  The program accepts an
entire timetable (e.g. a week's worth) and allows you to construct a
graph for any day of the week.  It can handle conditional trains.
Train numbers, etc are printed as text close to their relevant lines.

A separate QBASIC program constructs a machine-readable text timetable
from keyboard input.  I have long tried to adapt scanned timetables to
produce this input, but railway timetable typesetting is too
idiosyncratic.  No good generaly comes of it.  QR and some other
systems already produce their timetables as Excel spreadsheets.  This
would make things very easy.

Fo a copy of the output see the AATTC's "The Times" of (I think) about
October 1995.

I have been contemplating for some time using Excel to front-end the
above program- but only really as a place-holder for the data.  .
Excel is not really suited to graphing of data that has a value
X-axis, as opposed to a categorical axis.  There is only one type of
plot that Excel has (scatterplot) that can do this.  In addition, a
graphical timetable has a rather unusual feature that makes it hard to
use Excel: The controlled variable is essentially the Y-axis and the
contingent variable is the X-axis- this is the reverse of convention
and the reverse of what Excel expects.   Thus one has to plot the
graph at right angles to what one normally sees with a graphical
timetable and then rotate everything, including the labels to get a
graph that looks right.  I've done it, but the results are not that
good.  Trying to show train crosses at stations (e.g. by offsetting
the lines from the station axis) is a particularly vexatious problem
with Excel.  I'd be interested in hearing whether others have made a
better fist of it than I have.  Rod Smith? or Michael Chinn himself
might be the leader of the pack?

Other peole have written graphing programs for the PC, I have the
output of one that was written (I think) by the late Stephen MacLean-
Rod Smith might know about it.  It's certain NOT to be an Excel job
though- it existed long before Excel was dreamed up by Gates.
Possibly it was a CP/M system program (remember CP/M?- actually mine
was first written in CP/M Basic in about 1980).

The programs are available in executable form.  There are no help
files.  PRTGL is available from an ftp server like archie.au

Geoff Lambert