A Model Railway depicting Thurstaston station on the Hooton to West Kirby branch on the Wirral Peninsula

Downloads

To assist me in my modelling, I've developed some spreadsheets to calculate key dimensions.  These can be downloaded from the links below.  Please send any feedback on their operation via my contact details on the Research and Links page.

The first two calculate gear ratios and frame spacer width for loco building.  The files are in Microsoft Excel 97-2003 format.   Take a copy of the speadsheet and put your figures in the turquoise highlighted cells.  I've included two worksheets in each file; one ready for use and a worked example.  It may help to set up and keep a separate worksheet for each loco.

Gear ratios

This spreadsheet takes the driver diameter, and works out the driver rpm to achieve a target mph for both for the prototype and model.  The next input is the top speed you want your model to achieve.

The no-load speed of your chosen motor is needed.  For Mashima motors, there doesn't seem to be a definitive value but some figures are available from the EM Society handbook and the Branchlines Motors & gears catalogue.

Inputting the no-load speed of your motor and a % of this to use as the maximum will return the gear ratio required to achieve your required top speed at this motor speed.  As a check, the columns on the right show the wheel and motor revolutions at a range of scale speeds based on the resulting gear ratio.  The resulting colours show:
 
Green - motor revs below 75% of no load speed Yellow - motor revs between 75% and 100% of no load speed Red - motor revs above no load speed, so not feasible.

Having got the derived ratio, you can select the most appropriate actual ratio from those provided by your preferred gearbox supplier.
<Download>

Frame spacing

The top part of this spreadsheet is set up for P4; for other gauges you will need to amend the initial wheel tyre back to back measurement accordingly.  It works by prompting for the dimensions of all the items I can think of that may exist around the axle between the back of the wheel and the inside of the frame.  If any aren't applicable, e.g. shorting strips, then put a zero in that row.  The dimensions are then applied to both sides with the resulting figure being the required distance between the frames.

The lower part of the worksheet can be used once the calulated or actual distance between the frames is known.  As above, the dimensions of items around the axle between the frames are entered, along with the overall width of the chosen gearbox over the axle bushes.  The space available between the gearbox and bearing is shown for each side.  Note that all dimensions are in mm.
<Download>
Go to Top

Pipework conversion

The General Arrangement (GA) or Pipe Run drawings that may be available from the NRM or similar, or included in books such as the Wild Swan Locomotive Profile series, include dimensions for much of the pipework specified for the locos as built.  Whilst this may not be exactly what the loco actually had in service, the only alternative is measuring a preserved loco, but this may have more modern pipework so can't be relied on either.

The terminology used in the diagrams is often specified in 2 parts; 1) the size of the pipe's bore / internal diameter in inches and 2) the pipewall thickness in Imperial Wire Gauge (IWG) or Standard Wire Gauge (SWG) which are the same.  I have only recently identified that the Wire Gauge could be used for both wire and pipewall size.  For our model, we are only interested in the external diameter of the prototype pipe, and then deriving the most appropriate wire diameter to represent it, which therefore needs to be looked up and calculated, which the spreadsheet does.

The GA Drawing Pipe worksheet in the Microsoft Excel 2007 spreadsheet takes in the prototype specification which can be read off the diagram.  All values are input using Excel drop down fields.  The pipe internal diameter is input as a) Whole inches in the range 0 to 4 and b) Fraction of inches from 0 to ⅞ in ⅛ steps.  The IWG or SWG value is input as a numberic value in the range 0 to 25.

From these values the spreadsheet calculates the prototype external diameter in inches and the 4mm scale equivalent in mm, to 3 decimal places.  This value is then used to identify the two nearest sizes, one smaller and one larger, of metal wire (straight or coiled) or rod based on what is available from the likes of Eileen's Emporium or eBay.  Note that only the size is given, as there may be multiple products of any size using different metals from Brass, Phosphor Bronze or Nickel Silver, and there may also be variation in hardness.  I have not included guitar string wire as this is unlikely to be suitable for modelling pipework.  Finally, the spreadsheet displays the % difference for the two suggested products against the calculated dimension.

The Known external dimension worksheet does the same but allows the prototype dimension to be entered in inches where this is known from measuerment etc.

<Download>
Go to Top

The Prototype | Train workings | Historical Photographs | The Prototype Now| The Layout | Model Photographs | On my workbench | Research and Links