April 2013

Tables Database Goes XtbML

By Stephen J. Strommen

The Society of Actuaries (SOA) has decided to dedicate staff to maintain and enhance the electronic database of actuarial tables that was originally created by our section about 15 years ago. At the same time they converted the table data format to XtbML, the extension of XML developed by our section in cooperation with ACORD. These changes, and others, pave the way toward making the database of actuarial tables more accessible and valuable to actuaries worldwide.

Before going into details of these changes, a brief review of some history might be in order. Back in 1995, the Computer Science Section (as the Technology Section was then called) initiated a project to create a database of actuarial tables in electronic form, along with software to enable use of the tables in that form. Through the efforts of several members, a database containing hundreds of standard actuarial tables was created. In addition, the Table Manager software was created, including an Excel add-in that provided a means to load the tables into Excel and provided Excel functions to access table values directly from the database.

That original work was downloaded and used by hundreds of actuaries, so questions started to arise as to how it could be managed in a sustainable long-term way. Over the next few years several more tables were added to the database through ad-hoc volunteer contributions, and the Table Manager software was upgraded to 32-bit Windows. Then, in 2001, Jim Toole led the International Section in an effort to significantly expand the database with tables from around the world. The Table Manager software was again upgraded to store additional data about each table and to enable easier search of the database by country and type of values in the table (insured mortality, population mortality, lapse rates, and so on).

For several years thereafter, the database continued in use, now by actuaries around the world. However, the issues concerning long-term sustainable management of the database remained unaddressed. No individual or organization stepped forward to take ownership of what was by then a very large collection and a valuable resource.

During 2000 to 2002, the Technology Section undertook a project to develop a new and more flexible file format for actuarial tables. Up to this point, all of the tables in the database were either a single column of values or were laid out as select and ultimate tables. All tables had to have values at every age or duration within their range. While this was sufficient for a great many commonly used tables, the emergence of XML as a data-coding language led to the idea of adapting XML to provide a very flexible standard for storing actuarial tables of virtually any layout. The Technology Section teamed up with ACORD to develop XtbML, a new standardized dialect of XML designed for digital encoding of tabular data.

The effort to develop XtbML met with success in that it was adopted by ACORD as part of its set of standards for insurance-related data. In addition, the Technology Section created software to translate the existing database into XtbML. However, no software like the Table Manager was made available to use the tables in that form. That, combined with lack of clear responsibility for maintaining the database, paved the way for the database to languish for a few years, during which the older version of the database and the old Table Manager software continued to be used.

That brings us up to 2010, when the Society of Actuaries decided to take on the responsibility for ongoing maintenance and enhancement of the database. The SOA has now dedicated staff resources to this effort, and has already added quite a few tables.

The SOA decided to maintain the database in XtbML form for two main reasons. First, XtbML is a standard that has been adopted not just by our section but also by ACORD, a widely respected standard-setting body. Second, XtbML provides the flexibility to encode many different table layouts, not just the single vector or select and ultimate layout.

Given this commitment by the SOA, the author of this article undertook the effort to adapt the Table Manager software to use XtbML rather than its legacy specialized binary file format. That effort involved more than simple file translation. In order to handle the flexibility inherent in XtbML, the software needed to be re-designed with that flexibility in mind. A later article in this newsletter will describe some of the technical considerations involved in that re-design.

That leads us to the present day. The SOA is actively validating all of the tables in the database and has added quite a few tables over the past year or so. In addition, the flexible nature of XtbML has allowed new table layouts such as continuance tables and generational tables to be added to the database. The Table Manager software was upgraded to version 4.5 last year to support these new types of tables, and recently a 64-bit version was released to support actuaries who use 64-bit Excel.

Much has happened in the 18 years since an electronic database of actuarial tables was first conceived by the Computer Science Section. Now, with the active commitment by the SOA and the use of XtbML, we can all expect to benefit from the easy availability of actuarial tables for many years to come.

Stephen J. Strommen, FSA, CERA, MAAA, is owner of Blufftop LLC. He can be reached at stevestrommen@blufftop.com.

Have You Tried ‘MORT’ Yet?

MORT, which stands for Mortality and Other Rate Tables, is a flexible user interface that provides easy access to the 1500+ tables in our Xtbml database. MORT allows you to pin-point tables through its sort and filter options. With MORT, you can download tables directly to your desktop in a variety of file formats such as csv, xls, and xlm, without having to download or install any software. And feel free to contact MORT@soa.org with your feedback.