Collectible Stocks and Bonds from North American Railroads     by Terry Cox

A guidebook and catalog of prices
(I do NOT buy or sell certificates on this website)

Search this website for information about collecting stocks and bonds.

Search

Search the Coxrail database for descriptions of 23,700+ certificates from over 7,400 North American railroad companies.

Coxrail database explained

The purpose of this page. I have been approached several times to advise on the creation of a guide to certificates similar to mine. Since my guide depends completely on the compilation of information, I always explain how experience demanded that I keep information in a large database. Since the subject of database construction would make no sense without a purpose, I am going to assume that readers only come to this page when wanting to learn more about my experiences.

What is a "database?" In the broadest sense, a database is nothing more than a collection of electronic information that is organized in such a fashion that its data can be retrieved, managed and updated. A database can be likened to an electronic filing cabinet.

Microsoft Excel Logo

Does Microsoft Excel qualify a database? According to the broad definition above, it certainly can be classified as a database, especially now that the latest version of Excel can accommodate 1,048,576 rows! Excel holds information in columns and rows and its spreadsheet structure can be searched for information. However, it is crucial to understand that Excel was designed as a robust type of calculator that can display results in the form of spreadsheets. It is not designed for information retrieval. While Excel has grown greatly more powerful and flexible with time, it remains seriously limited by comparison to true database programs.

Name table in Coxrail database

A view of a small part of the Names table, the primary table that holds information about companies. Notice how it strongly resembles a spreadsheet.

Nutshell Logo

Flat databases share a strong resemblance to Excel's spreadsheet appearance. A flat database has only two dimensions. Whereas spreadsheets hold data in "rows" and "columns", databases hold data in "tables" composed of "fields" and "records". While this might sound like a distinction without a difference, the resemblance of database tables to spreadsheets is quite deceiving. While spreadsheets are highly organized, database tables can be imagined as large sacks of unorganized data. It is only when users retrieve information that form is given to that information. If a user asks for only ten records and five fields, the database program gives him that and nothing more. Conversely, spreadsheet programs must hold all information in memory and find specific records only through sorting. Spreadsheets make calculations and sorting easy, but information retrieval becomes increasingly difficult with larger and larger numbers of contained rows and columns.

Relational databases, however, are dramatically more powerful than either spreadsheet programs or flat databases. Relational databases store data in multiple tables that relate to each other through one or more common fields. While spreadsheet programs can access data across multiple spreadsheets, database programs are highly optimized for super-fast information retrieval. For instance, it is a trivial matter to discover where an image might be found for a specific serial number of a specific certificate variety, regardless of whether images of that variety have appeared in one reference or a thousand.

Lotus Nutshell and Access Logos

Microsoft Access. After some experimentation and research, I chose Microsoft Access as my database of choice. It is both highly flexible, powerful and well-suited for my needs of storing information about collectible certificates. Prior to my decision to learn Access, I had stored data in the Lotus 1-2-3 program, the #1 pioneer spreadsheet program released in 1983. Although I had used Lotus from its inception and was highly proficient, I realized its limitations. I ultimately moved my information to Nutshell, a flat file database sometime around 1989. A couple years later, I migrated to a relational version of Nutshell called Nutshell-Plus. Desiring even more flexibility and programming power, I moved within a few months to Microsoft Access, probably around 1992.

A taxonomic approach. I figure I have spent between 30,000 and 35,000 hours compiling information about certificates plus even more on a couple other specialties. Early in that period, it became obvious that information needed to be organized with a taxonomic approach similar to methods used for identifying and classifying animals and plants.

The basic idea of those systems is to start with one, plainly obvious feature and ask the user to choose between a few (ideally, only two or three) less obvious features. For instance, tree classification systems usually depend upon examining leaves. Those systems first ask users to decide whether leaves are needle-like, scale-like, broad or clustered.

From that point, classification systems offer choices between several progressively finer sub-divisions, each time offering users narrow ranges of choices. In well-defined systems, users should be able to identify exact species after making only eight or ten decisions. Biological classification systems are rarely perfect because of the natural variability of individuals. With living things, there is always the possibility of cross-breeding and the discovery of new species.

That is the exact circumstance we experience with collectible certificates.

Main search screen in Coxrail database

The main search screen in the Coxrail database. This is a regular form used in Microsoft Access, heavily modified with custom controls and colors.

Classifying certificates is a very organic activity. We can usually make the first distinction very quickly by using company names. After that, we can decide whether certificates can be defined as stocks, bonds or other security documents. From that point, many certificates offer obvious distinctions based on vignettes. After that, we might need to make a few more finer distinctions. And of course, it is apparent that certificates, like living things, often display individual variations such as serial numbers, types of issuances, cancellations and autographs.

Balancing usability and data organization. Like other taxonomic classification systems, I wanted users to be able to identify varieties as quickly as possible. Unfortunately, companies are living, breathing, evolving and dying entities, just like plants and animals. That means that as much as we might desire, companies rarely chose the same approach when they created security documents. Consequently, features of certificates never fit cleanly into inflexible categories. My organization, therefore, had to embrace flexibility.

The basic structure. As mentioned above, I use a relational database system. Information with similar purpose is grouped into several separate tables and the tables are linked, one to another, by single fields. In the Coxrail database, I use these eight tables to describe and organize certificates:

  1. CompanyNames - contains company numbers used throughout the catalog (e.g. MIS-716), abbreviated country and state names, dates of incorporation and dissolution and miscellaneous public and private notes about companies.
  2. Tier1 - contains letters used to indicate certificate types (B, S, O), descriptions of primary and secondary vignettes and any other primary features that will help users discriminate between collectible certificates.
  3. Tier2 - contains descriptions of secondary features to help users further discriminate between similar certificates (only needed for about 23% of certificates)
  4. Tier3 - contains descriptions of tertiary features (only needed for 6% of certificates).
  5. Certs - contains specific details about certificate varieties including colors, denominations, printer names, par values, capitalizations, bond percentages, bond issue dates, redemption dates and printed portions of dates.
  6. IssueCondition - contains codes for issuance condition (issued, unissued, specimen, proof), codes for cancellation condition (cancelled, uncancelled), current price estimates, dates prices were last checked, abbreviated references to autographs used online and in the printed catalog and all-time maximum and minimum observed prices.
  7. SerialNumbers - contains dates, prefixes, serial numbers, suffixes, shares, celebrity autographs and roles (up to five names recorded) plus private notes about individual certificates.
  8. CertificateReference - contains codes indicating sources of listings for each serial number (auction catalogs, contributor names, books, websites, etc), listing dates, page numbers, lot numbers, offer prices, sale prices references to high-res, mid-res and low-res images on file plus private notes about contributions.

These eight tables constitute the backbone of all information offered to readers of the catalog and users of the online database. However, behind the scenes are numerous additional tables that record tremendous amounts of ancillary information. Those tables are used to record a host of other subjects surrounding railroads and certificate collecting in general.

Table relationships in Coxrail database

A pictorial view of how major tables relate to one another in the Coxrail database. Each rectangle represents one table and lines between rectangles represent links between tables.

Website users will encounter some of that additional information in the form of special note pages that accompany the majority of company listings. Other tables are used for private purposes and research in conjunction with the main purpose of listing and evaluating certificates. I advise anyone using my project as a model for their own cataloging efforts to consider using at least some of these concepts in their databases. Note that all these tables are linked to the crucial certificate tables in some manner. Additional tables of supporting information include:

  • 1995Edition - Listing of certificates that appeared in the 1995 edition.
  • 2003Edition - Listing of certificates that appeared in the 2003 edition.
  • ArticleContributors - Listing of individuals who have written company summaries used in online company notes.
  • ArticleText - Text of articles about companies that appear in online notes.
  • Celebrities - Listing of short biographies of celebrities including birth and death dates.
  • SecondTierCelebrities - Listing of celebrity names that have been touted somewhere as having value, but which fail to meet my listing criteria.
  • AutographImages - Links to autograph images and names of contributors who sent those images.
  • Email - Descriptions of certificates sent by contributors and my responses inserted into emails and letters.
  • HelpScreens - Text used in many custom help screens used throughout my Access database; strictly intended for whoever might take over my project in the future.
  • Hessler - Listing of rail-related vignette images that appear in Gene Hessler's book, 'The Engraver's Line'. Used as a source when researching popular vignettes.
  • PreNumbering - Certificate numbers that might be used in the future if certificates appear for selected super-crowded companies.
  • PriceIndex - Listing of and links to certificates comprising my custom price index.
  • References - List of all references used in this project including contributors, dealers, price lists, auction catalogs, books, libraries and companies.
  • WebAddresses - Web addresses for dealer contributors (used online with certificate images.)
  • CountryAbbreviations - Official and semi-official country abbreviations used in certificate listings.
  • TimelineDates - Descriptions of events and dates used in the online timeline.
  • Nicknames - Official and unofficial railroad company nicknames.
  • Officers - Names and terms of company presidents and CEOs, primarily in the largest companies.
  • PeripheralDocuments - Descriptions and links to images used for peripheral (non-stock and bond) documents used in the Coxrail website.
  • Printers - Names and abbreviations of all printers and engravers known to have produced stocks and bonds.
  • ScamBonds - Listing of bonds known to have been used in gold bond scams.
  • StateAbbreviations - Official and non-official state abbreviations used in certificate listings.

I suggest anyone considering undertaking a catalog project spend quite a bit of time in the planning process. Understand that as soon as you start your project, you will encounter people willing to contribute information. Or more accurately, those people will find you. That's a good thing. Just make sure that you set up methods of recording all the information they will send. The more information you record up front, the less you will need to go back and re-visit your original sources.

Access form used to record data in Coxrail database

This is another custom form used to record information about certificates. It looks simple, but this form pulls information from five different tables.

Unless absolutely necessary, avoid abbreviating information. If you do abbreviate, make sure you are consistent and record your acceptable abbreviations somewhere.

Make sure you plan on expansion. I almost guarantee that many of you will consider a spreadsheet an acceptable method of recording information in the beginning. The problem is that regardless of your initial planning, your project will grow in ways you never imagined. The more information you record, and the more directions you are pulled, the sooner you will realize that spreadsheets are woefully inadequate for the task.

You will notice from my introduction that I started with a Lotus spreadsheet. In my consulting business, I have encountered the same thinking (that spreadsheets are adequate for data storage) time and again. Please, use my experience. Start with a database in the beginning and you will be happier overall. Yes, databases are somewhat hard to understand and there is a steep learning curve. No doubt about it. But, nonetheless, the effort is worth it in the long term. Remember, you are going to be doing this for years to come and, ideally, your project will outlast you.

What if you choose a specialty that is too small? In a very small cataloging project, whether involving certificates or something else, a spreadsheet system might very well prove to be the appropriate method of data storage. However, the smaller the size of the project, the more important it is to consider whether the effort is worth the trouble. Why not use your drive and precious time for a larger specialty? For something that will help more people?

I faced that exact problem with coal company certificates. There may have been thousands upon thousands of coal companies, but relatively few certificates exist. And the number of dedicated collectors in that specialty is very limited. However, because I was once a coal geologist, I was drawn to the collecting specialty. Being a pragmatist, I accept that the specialty is too small for a separate effort.

However, with the large railroad certificate database already in progress for twenty-plus years, I realized a simple solution. All I needed to do was add one new field to the CompanyNames table mentioned above. It merely needed to hold eight characters. In that field, I put a code for coal companies ("COA") or rail companies ("RRY".) In only a few cases (50 so far), I must use the code "RRY, COA". Those few overlap companies use the same company numbers for both specialties (for example, DAY-190, Dayton Coal Iron & Railway Co.) Because I am using the "specialty" code field to discriminate between the two company types, I don't need to worry about potential duplication of company numbers. In fact, the "specialty " code freely allows duplicate certificate variety numbers with no problems whatsoever.

Will I ever add another specialty? I am afraid I have too few years left. At the moment, I see no reason to increase my effort further. However, it begs the question, "Could a database like mine handle multiple specialties in the detail I currently use for railroad and coal certificates?"

With the warning that more thought would need to go into initial database design, I don't see any valid reason that a database could not handle tens or even hundreds of specialties. Of course, at some point, even the Access database would probably need to be replaced with a more robust database program such as SQLServer or Oracle. I have already built two Access databases holding over a couple million records each and have experienced no performance problems at those sizes. Nonetheless, I suspect the Oracle/SQLServer options would be a better choice if considering enlarging my project multi-fold.

Access form used to record data about serial numbers in Coxrail database

View of yet another custom form used to record information about serial numbers.

What about the online database? The online version of the Coxrail database is a subset of my main database. While I upload a new version of my Access database to my web host every two weeks, web users actually use SQLServer to retrieve information. SQLServer is a program that works well in a multi-user environment, whereas Access stumbles under the load of only a few concurrent users. Because I am coding my web pages with Classic ASP, I don't need to worry about what is really happening between web users and my Coxrail database.

Will you need to learn programming if you want to offer your database online? Short answer: "Yes."

Still, I wouldn't worry about that right now. By the time you build your own database, create custom data entry forms and structure your own database queries, you will know enough to build a database-driven website. When you reach that point, it won't matter whether you choose to construct your website with Classic ASP, ASP.NET, PHP or something else; you will be able to tackle online databases with minimal new effort.

Send an email message with corrections or comments about this page.

(Last updated Jan 16, 2016)

 

I strongly recommend buying the Cox Catalog from your favorite SCRIPOPHILY DEALERS. Catalog cover
If they do not yet carry, or are out of stock, you may buy directly from the author.
$49.95+post


 

 


Papermental logoHelp support this free site! Please visit my eBay store called Papermental by Terry Cox. My inventory includes railroad passes, railroad ephemera, newspapers, magazines, engravings, and all sorts of paper collectibles.

Please contact me if you have certificates not yet listed. (See How You Can Help.)

I suggest using WeTransfer or similar file transfer sites when sending large files or large numbers of files.

PLEASE contact the many fine dealers listed on my dealers page to buy certificates.