Trains.com

Subscriber & Member Login

Login, or register today to interact in our online community, comment on articles, receive our newsletter, manage your account online and more!

Ideas for Model Railroad Inventory - Excel Spreadsheet vs. Software?

41463 views
46 replies
1 rating 2 rating 3 rating 4 rating 5 rating
  • Member since
    February 2013
  • 151 posts
Ideas for Model Railroad Inventory - Excel Spreadsheet vs. Software?
Posted by trafficdesign on Thursday, March 27, 2014 10:32 PM

Looking for an easy way to start tracking what cars and #s I've got. Have way too many cars to keep them in my head and initially just want to have an easy way to know what road numbers I need (and have) when I go to flea markets and shows.

Am on a Mac so most of the Windows-based commercial programs are not an option for me.

Hesitant to go the Excel route as I have no idea what fields are important to add. Looking for ideas and best practices...

Ideally, (though it's probably too much to ask) does anyone have a source for a ready-made Excel spreadsheet or want to share their template????

Thanks!

 

Moderator
  • Member since
    June 2003
  • From: Northeast OH
  • 17,248 posts
Posted by tstage on Thursday, March 27, 2014 10:57 PM

Traffic,

I used Excel and it's works great.  I have my first column broken down into two categories (locomotive and rolling stock) then the following column headings:

  • Type - i.e. Diesel, Steam, boxcar, gondola, etc.
  • Builder - i.e. Lima, Balwin, Schenectady, etc.
  • Type/Length/Size/Capacity - i.e. HH600, 4-8-2, 6-panel wood, 10K gallon, etc.
  • Road - i.e. NYC, PRR, Erie, etc.
  • Road#/Name - i.e. 80515, Imperial Crown (passanger)
  • Sound - (Only for locomotives)
  • Manufacturer - Accurail, BLI, Stewart, etc.
  • Comments

That pretty much covers everything I need.

The sorting function of Excel comes in quiet handy, too.  For rolling stock I generally sort by length/capacity then by road number.  It makes it easier then to peruse my inventory list if I'm at a train show looking for something.

Hope that's a help...

Tom

https://tstage9.wixsite.com/nyc-modeling

Time...It marches on...without ever turning around to see if anyone is even keeping in step.

  • Member since
    September 2003
  • 10,582 posts
Posted by mlehman on Thursday, March 27, 2014 11:33 PM

When I first went back to school in 99, one of my first class projects was devoted to Excel, so I made an inventory template that is pretty simple. I sorted motive power separate from other rolling stock, had it total my "investment," etc. I've still got that same file working for me, despite different computers, OSs, 3.75 degrees, and years of additions and deletions.

One thing that might be useful to consider is JMRI. It's the Swiss Army knife of DCC and I'm pretty sure has an app to build a car inventory. That's something else to consider, as it's open source and has a large community of users, enough so that it's a de facto standard to use for all kinds of things in model railroading.

Mike Lehman

Urbana, IL

  • Member since
    June 2010
  • From: Victor Harbor, South Australia
  • 362 posts
Posted by alexstan on Thursday, March 27, 2014 11:47 PM
I use the freeware RRtrains software.

Modelling HO Scale with a focus on the West and Midwest USA

  • Member since
    February 2002
  • From: Reading, PA
  • 30,002 posts
Posted by rrinker on Friday, March 28, 2014 6:29 AM

I have an Access database written by Dave Husman, it generates car cards and such for operation but at this point it's just my inventory - it also inventories structures, track, books, and your electricla components as well as locos and rolling stock. Very comprehensive. There's a handy "train show" inventory report to carry with you to train shows so you check and see if you have a particular item. It's available via the Yahoo Group Car Cards.

                      --Randy

 


Modeling the Reading Railroad in the 1950's

 

Visit my web site at www.readingeastpenn.com for construction updates, DCC Info, and more.

  • Member since
    February 2013
  • 151 posts
Posted by trafficdesign on Friday, March 28, 2014 8:00 AM

Thanks Randy. Do you know if this will work on a MAC???? Is it cross platform or just PC only?

  • Member since
    August 2006
  • From: Franconia, NH
  • 3,130 posts
Posted by dstarr on Friday, March 28, 2014 10:53 AM

I have Excel, and am tolerably familiar with it, so I use it to keep my inventory.  Excel will sort on any or multiple columns so I can see things anyway I like.  I use separate wooksheets for different types of rolling stock, boxcars, hoppers, tankers, cabeese, locomotives.  For each item I have columns for road name, reporting marks, maker, date acquired, cost, maintenance history, scale length, and a few other things.  It will print out the whole inventory so I can take it to shows.  I haven't figured out how to make it print out car cards for operating sessions. 

  • Member since
    February 2012
  • 596 posts
Posted by charlie9 on Friday, March 28, 2014 12:13 PM

Can you use Google Drive?  Their spreadsheets worked fine for me.

Charlie

  • Member since
    February 2002
  • From: Reading, PA
  • 30,002 posts
Posted by rrinker on Friday, March 28, 2014 12:39 PM

MS Access is PC only. It will run on a Mac with Bootcamp, or Parallels or Fusion.

       --Randy


Modeling the Reading Railroad in the 1950's

 

Visit my web site at www.readingeastpenn.com for construction updates, DCC Info, and more.

  • Member since
    January 2011
  • From: Ft. Wayne, Indiana
  • 142 posts
Posted by Drew4950 on Friday, March 28, 2014 1:33 PM

I have been using Easy Model Railroad inventory software. Someone posted a link to it on these forums 2 or 3 years ago. It is very detailed in the information one can enter. Some info is required and some info is extra.

I do not use it this way but it can generate car cards and such. I use just to keep track of my stuff. It may have been a beta version but I am not sure of that. There is an address if you wish to make a contribution to the designers too.

It was a free download. I do not know if it is still free or not.

There are ways to design your own inquiries and turn those into spreadsheets. I am fairly certain the name is EMRI - Easy Model Railroad Inventory. 

Modeling a railroad hypothetically set in time.

  • Member since
    July 2006
  • From: Southern Quebec, Canada
  • 868 posts
Posted by Guy Papillon on Friday, March 28, 2014 2:01 PM

I used Excel to develop a file for my roster. The file has three sheets : cars, locomotives, vehicles.  With Excel, it is easy to add whichever data you need.

With Google Drive on my iPhone I have access to my roster wherever I am, LHS, Train show, etc.

Send me a PM and I'll send you my Excel file.

Guy

Modeling CNR in the 50's

  • Member since
    September 2003
  • From: Southeast Texas
  • 5,449 posts
Posted by mobilman44 on Friday, March 28, 2014 2:18 PM

If you have the software, go with Excel.   I actually used Lotus 1-2-3 many years ago to start, and then migrated to Excel.  The thing is, you can totally customize it to your own liking, and can "play" with it any way you like.  I have spreadsheets for my Lionel collection, as well as HO, and can only say "it works"!

ENJOY  !

 

Mobilman44

 

Living in southeast Texas, formerly modeling the "postwar" Santa Fe and Illinois Central 

  • Member since
    May 2005
  • From: Westcentral Pennsylvania (Johnstown)
  • 1,496 posts
Posted by tgindy on Friday, March 28, 2014 2:34 PM

Inventory should really have a database (like many file folders in a drawer) as opposed to a spreadsheet (primarily for math functions).  Spreadsheets can be used as an alternative to a database if the needs are not too in-depth.

Nolansoft's MDB Viewer Plus is a complimentary (and portable) database program that even includes mild relational database reporting (meaning a report using more than one database) with "one-to-many" and "many-to-many."  Do download the PDF instruction manual.  MDB Viewer Plus' native DB format is Microsoft Access' MDB or ACCDB (without the need to have MS Access installed).  There are many DB import/export conversion formats available to either databases, or spreadsheets.

Open-Source OpenOffice, or "its crisper" OpenOffice fork, LibreOffice, also has a database, as well as spreadsheet, and word processing.  Links here are to portable versions of each program.

Conemaugh Road & Traction circa 1956

  • Member since
    September 2003
  • From: Omaha, NE
  • 10,621 posts
Posted by dehusman on Friday, March 28, 2014 3:25 PM

Open Office can be downloaded for Mac.  A spreadsheet can be made with the rows the cars and the columns the various attributes.  Whats important is what['s important for you.  By using a custom spreadhseet you can include stuff that you want and leave off the stuff you don't really care about.  If your primary concern is operating you might not care about the manufacturer's model number or the condidtion of the box.  If you are a collector having a good original box might be important.

Databases take more technical knowledge to roll your own.  For most people who just want a list to take to shows and swap meets, a spreadsheet will provide all the power they need.

Dave H. Painted side goes up. My website : wnbranch.com

  • Member since
    February 2013
  • 151 posts
Posted by trafficdesign on Friday, March 28, 2014 3:39 PM

dehusman

For most people who just want a list to take to shows and swap meets, a spreadsheet will provide all the power they need.

For all of those who use spreadsheets for tracking their model railroad inventories, would there be an interest in sharing fields to create a master spreadsheet template that we can post online? I'd be happy to take a stab at programming one and sharing it - if everyone wants to provide ideas and input???

My thinking is that providing a free Excel template of the fields that we all feel is important we can create a 'best practice' and help newbies like me to start in the hobby.

 

tstage

tstage has suggested:

 

  • Type - i.e. Diesel, Steam, boxcar, gondola, etc.
  • Builder - i.e. Lima, Balwin, Schenectady, etc.
  • Type/Length/Size/Capacity - i.e. HH600, 4-8-2, 6-panel wood, 10K gallon, etc.
  • Road - i.e. NYC, PRR, Erie, etc.
  • Road#/Name - i.e. 80515, Imperial Crown (passanger)
  • Sound - (Only for locomotives)
  • Manufacturer - Accurail, BLI, Stewart, etc.
  • Comments

Any thoughts on this project?

  • Member since
    September 2003
  • From: Southeast Texas
  • 5,449 posts
Posted by mobilman44 on Friday, March 28, 2014 5:00 PM

Hi,

Here are some other fields that may not have been mentioned.......   Mfg number, car color, # of doors or bays or tanks, car build date (very important to me), cost, date purchased, maintenance done.

ENJOY  !

 

Mobilman44

 

Living in southeast Texas, formerly modeling the "postwar" Santa Fe and Illinois Central 

  • Member since
    December 2001
  • 1,932 posts
Posted by Stevert on Friday, March 28, 2014 5:08 PM

I'll go along with the suggestion for LibreOffice's "Base" (database).  It's free, constantly being updated, and works on Mac, Linux, and Windows.

Using a database gives you a lot of flexibilty that you don't get with just a spreadsheet.  And if you find there's something you DO want to use a spreadsheet for, it's a simple task to copy it in from your database:

Open Base, click on "Tables", right-click on your database, and click "copy".

Then from the Base menu bar, click File, New, Spreadsheet, and then paste.

Now you have your complete database, including column (field) headers in a spreadsheet.

And you can set up custom queries and reports, too.  For example, I have a bunch of "canned" reports for each manufacturer, with the data grouped by car type and sorted by road name, road number, etc.  I save them in PDF format (again, all within LibreOffice), and keep them on a small tablet I take with to train shows.

Oh, and the queries that the reports are based on can also be copied into a spreadsheet, the same as outlined above for the entire database.  Of course, you can also set up other queries that don't feed a report and copy them, too.

It's a very powerful and flexible cross-platform tool, and way cheaper than the "paid for" products.

  • Member since
    February 2013
  • 151 posts
Posted by trafficdesign on Friday, March 28, 2014 5:20 PM

Stevert

For example, I have a bunch of "canned" reports for each manufacturer, with the data grouped by car type and sorted by road name, road number, etc.  I save them in PDF format (again, all within LibreOffice), and keep them on a small tablet I take with to train shows.

Are you able to share or post a representative pdf so we can see what data you use?

  • Member since
    March 2004
  • From: San Diego
  • 954 posts
Posted by stokesda on Friday, March 28, 2014 5:41 PM

trafficdesign

 

 
dehusman

For most people who just want a list to take to shows and swap meets, a spreadsheet will provide all the power they need.

 

 

For all of those who use spreadsheets for tracking their model railroad inventories, would there be an interest in sharing fields to create a master spreadsheet template that we can post online? I'd be happy to take a stab at programming one and sharing it - if everyone wants to provide ideas and input???

My thinking is that providing a free Excel template of the fields that we all feel is important we can create a 'best practice' and help newbies like me to start in the hobby.

 

 

 
tstage

tstage has suggested:

 

  • Type - i.e. Diesel, Steam, boxcar, gondola, etc.
  • Builder - i.e. Lima, Balwin, Schenectady, etc.
  • Type/Length/Size/Capacity - i.e. HH600, 4-8-2, 6-panel wood, 10K gallon, etc.
  • Road - i.e. NYC, PRR, Erie, etc.
  • Road#/Name - i.e. 80515, Imperial Crown (passanger)
  • Sound - (Only for locomotives)
  • Manufacturer - Accurail, BLI, Stewart, etc.
  • Comments

 

 

Any thoughts on this project?

 

 

Your enthusiasm is appreciated, but a spreadsheet is so simple to create, I'm not sure it's really necessary to "crowd source" a generic template to post to the web for others to use. Tstage had a pretty good start on suggested fields, and others have chimed in with other helpful suggestions. Like someone else mentioned, what's "important" is what's "important" to you. You can very easily go back and add another column later if you decide you need to keep track of one more piece of information.... or delete a column if you think that particular bit of information doesn't turn out to be that useful.

If you get to the point where you want to keep track of more than a dozen or so fields, I'd recommend taking a look at EMRI, which was previously mentioned. It's a simple database program that has more fields than you can probably ever use, and you can create reports and extract data to a spreadsheet. A word of caution, as of a few months ago, it would only let you create a spreadsheet if you had Excel installed on your machine. It didn't work with LibreOffice. It's a very basic program, and kind of rough around the edges, but it's simple to learn and gets the job done. Here is a link.

Edit: I just remembered Dallas Model Works has some free web-based model rr management stuff on their website. You might want to check out what they have to offer. Look under the "My Railroad Apps" link on their website.

Dan Stokes

My other car is a tunnel motor

  • Member since
    September 2003
  • From: Omaha, NE
  • 10,621 posts
Posted by dehusman on Friday, March 28, 2014 5:45 PM

Part of the problem is that what you want to use the list for determines which fields and their order.  For example if I wanted a list to take to a show to decide what models I needed or had, I might want a list that was ordered:

Manufacturer

Model number

Type of equipment

Road name/initials

Number

Color

Slogan

Couplers

Special equipment (DCC, etc)

On the other hand if I wanted a list for use on my home railroad I might want a list  ordered like this:

Initial

Number

Type

Length

Capacity

Color

Slogan

Home road

Special assignment

For my own inventory I would want all the above plus:

Coupler brand

Wheel type and brand

Weight

Length

DCC decoder model

Sound equipped?

 

If I wanted a list for my insurance records I would have all the above fields, plus the following:

Purchase date

Purchase price

Current value

Added details

Dave H. Painted side goes up. My website : wnbranch.com

  • Member since
    February 2013
  • 151 posts
Posted by trafficdesign on Friday, March 28, 2014 6:40 PM

Great list...thanks.

  • Member since
    January 2013
  • 1,034 posts
Posted by PM Railfan on Sunday, March 30, 2014 3:13 AM

I have templates for this very exact reason. Do know they are in MS Works, not MS Excel. I highly recommend if you are able, use Works. It is much more suited to this very exacting task than Excel. I have used this method for decades. It is highly adaptable, changeable, and efficient. If your stuck with Apple, then disregard this post, I cant help you there.

I dont mind sharing my templates, but I think they may be more than what you need. Besides, only YOU know what YOU want to keep track of in your own files. I track almost 50 items of data per model. You might only want to keep a log of 10 items of info for your models. So that part is really up to you.

You mentioned yourself - 'road numbers'. That's one field of info right there. I bet you can think of others that would make each model uniquely identifiable to you. How about road number AND 'road name', car type, manufacturer, when and where and for how much did you buy it, and definately add a 'notes' column. That is an valuable field of info to track in inventory programs.

Once youve made your template, saved it, MADE A BACK UP COPY(!), you should copy it to your cell phone. When at a show, swap, or shop.... just pull it up and verify if you already have the car or not.

One more thing that most folks dont know..... your inventory sheet can be used by your home insurance provider for assessments should something happen to your collection. Pictures are a perfect item to combine with the inventory info. This should give you some thought towards the info you want to add to your spreadsheet.

 

  • Member since
    July 2003
  • From: Whitby, ON
  • 2,594 posts
Posted by CP5415 on Sunday, March 30, 2014 7:04 PM
Using Access here too. Set it up for how I needed it and have stayed with it for 14 years. Not as easy to make errors with it as with excel I find ( accidentally deleting info ) I can export the info I have to excel if I want to.

Brought to you by the letters C.P.R. as well as D&H!

 K1a - all the way

  • Member since
    September 2007
  • From: Charlotte, NC
  • 6,099 posts
Posted by Phoebe Vet on Sunday, March 30, 2014 7:52 PM

I, too, use MS Access, but if you don't already own it, it is too expensive to buy for just this one task.

Dave

Lackawanna Route of the Phoebe Snow

  • Member since
    August 2006
  • From: Nashville, TN area
  • 713 posts
Posted by hardcoalcase on Sunday, March 30, 2014 7:58 PM

I use Excel, and in addition to the fields mentioned, I also use:

Running (or operational), meaning - can the car be used in operations, or is it part of my unbuilt kit collection?  Enter 1 for running, 0 for not ready.

I calculated the approximate number of the different types of cars I need for operations, to identify what I should be looking to acquire, and those where the inventory is sufficient.

Jim

  • Member since
    February 2013
  • 151 posts
Posted by trafficdesign on Sunday, March 30, 2014 10:48 PM

stokesda

Edit: I just remembered Dallas Model Works has some free web-based model rr management stuff on their website. You might want to check out what they have to offer. Look under the "My Railroad Apps" link on their website.

Thanks for the Link to Dallas Model Works. Their online Railroad Apps look to be very useful and a source of great ideas for what I want to do!

  • Member since
    January 2004
  • From: Canada, eh?
  • 13,375 posts
Posted by doctorwayne on Monday, March 31, 2014 12:15 AM

I use an Excel template which my daughter created, and it contains folders for various types of equipment, such as Locomotives, Home road freight cars, Interchange freight cars, Non-revenue equipment, Passenger Equipment, etc., etc.

The number of categories is unlimited, as are the number and size of both the rows and the columns.  I include reporting marks and car numbers, car type, length, manufacturer, modified or not, value, and include whatever comments apply to each item.  Because I've sold a lot of home road (freelance equipment) over the years, I also keep track of those cars and locos - reporting marks and numbers - so that I don't inadvertently number a new item for a number already belonging to someone else (simply a matter of respect for anyone who liked my work enough to buy it).  Sold items within the list have a coloured background to differentiate them from what's still in my possession.
The programme will sort the items as you wish - mine are alphabetically by reporting marks and numerically within each similar group.  You can add items as they're acquired, in their proper order, and each folder can be customised as to the categories, number and size of columns, etc.  I'm sure that there are lots of other things it can do, too, but for me, the important thing is that it does what I need and it's simple to use - if it's simple for me, it should be simple for anybody. Smile, Wink & Grin


Wayne

  • Member since
    December 2004
  • From: Bedford, MA, USA
  • 21,481 posts
Posted by MisterBeasley on Monday, March 31, 2014 7:37 AM

Another vote for Excel.  My columns are car type, road name, road number and color.  Then, I've got a few extra columns that I put micellaneous stuff in, like whether it's a high-wheel boxcar, or whether it's an unbuilt kit still in the box.  I try to put kits in as soon as I get them, because a primary use of the spreadsheet is to sort by road number and then use it to avoid buying duplicates at train shows.

I use a separate sheet for locomotives, which contains mostly decoder information.  It's a convenient place to keep a record of CVs.

It takes an iron man to play with a toy iron horse. 

  • Member since
    July 2006
  • From: Phoenix, AZ
  • 1,835 posts
Posted by bearman on Tuesday, April 1, 2014 2:22 PM

I use Excel but I dont get into as much info as tstage.  Very basic w/car type including abbreviation, rad name and number.

Bear "It's all about having fun."

  • Member since
    March 2012
  • 1 posts
Posted by great life at76 on Monday, October 19, 2015 12:56 PM

trafficdesign

Looking for an easy way to start tracking what cars and #s I've got. Have way too many cars to keep them in my head and initially just want to have an easy way to know what road numbers I need (and have) when I go to flea markets and shows.

Am on a Mac so most of the Windows-based commercial programs are not an option for me.

Hesitant to go the Excel route as I have no idea what fields are important to add. Looking for ideas and best practices...

Ideally, (though it's probably too much to ask) does anyone have a source for a ready-made Excel spreadsheet or want to share their template????

Thanks!

 I USE EXCEL TO TRACK ALL MY MODEL TRAIN INVENTORY. I HAVE THE MANUFACTURE #,  ROAD #, THE COST ETC.  I KNOW HOW MUCH I BOUGHT FROM ALL MY SUPPLIERS, ALSO HAVE ALL ITEM IN A CATAGORY .  ENGINES, ROLLING STOCK  ETC. I ALSO WROTE A PICTURE PROGRAM USING EXCEL . IT HAS ALL THE PICTURES OF TRAIN SETS, ENGINES, ROLLING STOCK, TRANSFORMERS ETC.

FORMULAS CONTROL WHERE ALL DATA IS TO GO.

TRAIN GUY

 

 

 

 

Subscriber & Member Login

Login, or register today to interact in our online community, comment on articles, receive our newsletter, manage your account online and more!

Users Online

There are no community member online

Search the Community

ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT
Model Railroader Newsletter See all
Sign up for our FREE e-newsletter and get model railroad news in your inbox!