Category Archives: Actuarial

No. 147: The Chain Ladder Method with FASLR

27 March, 2022 4:25 PM / 1 Comment /

Today marks an exciting milestone – FASLR (Free Actuarial System for Loss Reserving) – has now implemented its first reserving technique, the chain ladder method. This makes it a good time to update the version number for the project, so I’ve bumped it from its inception v0.0.0 to v0.0.1. Feel free to check out the source code on the CAS GitHub.

New features added:

  • Ultimate Loss Column calculated via the chain ladder technique
  • Rows for selected LDFs and CDFs
  • The ability to select LDFs by double-clicking averages
  • Dialog box for creating and storing custom link ratio average types
  • Link ratio heatmap

These features have been added to the development factor view of FASLR. The images below show a comparison to how it looked last month (first image) vs. how it looks now, with the new features highlighted in red boxes (second image):

The FASLR development view, last month.

The FASLR development view, newest version.

Project Status

When examining the views of the repo, I’ve seen a lot of people taking a look at the releases section, the setup.py file, and the documentation. This leads me to believe that some people either think this is an installable program or are checking to see if it is. Right now, the project exists as a collection of Python source code files, so it’s not there yet. I do have plans to eventually release installable binaries where you can just double click a file and have it installed on your operating system. But first, I would have to learn how to do that and I have yet to decide on which tool I want to use to make that happen (GNU Make, Bazel, fbs, pyinstaller, etc.). This will be a new skill for me to acquire so it will take some time.

If you do want to run FASLR, you can execute the file main.py in the shell. This will give you access to the main GUI window and project pane. Currently, I’m focused on setting up views for the various reserving methodologies, once I’ve either exhausted those available in the chainladder package, or reached a point where it would be nice to integrate them into the main window, I will begin to focus more on the reserving project methodology – i.e., making it possible to start from data importation and end with a reserving estimate.

Versioning Methodology

The versioning system consists of a three-part format: v#.#.#. The rightmost digit represents unstable versions. Excluding v0.0.0, if your installation happens to have a rightmost digit other than 0, you can assume that you are using the software for the purpose of testing out the latest features and not relying on stability. The middle digit represents stable releases, meaning that the features have been tested to the best of the ability of the developer(s) and provide a reasonable level of reliability. So something like v0.1.0 would represent the first stable version and v0.1.1 would represent the most recent unstable version released after v0.1.0. The next stable version after v0.1.0 will be v0.2.0.

The leftmost digit represents major cultural milestones in the project. Right now it seems to be in vogue to reserve version 1.whatever for a special occasion, to mark the point where the software has become the de-facto open-source standard for performing work in the field. I will adopt this convention for this project, but while I have no plans to ever reach this point, as FASLR is mostly a learning exercise for me, it would be a nice point to reach if it ever gains traction.

Ultimate Losses

Since there are numerous sources on actuarial reserving methods which can do a much better job of explaining how they work than I can, I won’t spend much detail here on them and you can always refer back to CAS Exam 5 papers if you are not familiar with them. These next few sections will start with a blank factor view, and I will gradually demonstrate how link ratio averages can be used, in combination with the chain ladder technique to project ultimate loss.

One purpose of actuarial reserving is to estimate the liabilities per unit of time (such as accident year) and we call this estimate the ultimate loss. Therefore, one of my goals for this month was to add a column for ultimate loss. Below shows the (mostly) blank factor view with the link ratio triangle and ultimate loss column to the right. The LDFs have not been selected, and the chainladder package defaults non-selection to 1.000:

You can confirm that the starting LDFs are 1.000 by looking at the source triangle – the ultimate loss values are the same as the latest diagonal:

LDF Selection

Below the triangle, you will find a section that has various link ratio averages that you can select by double-clicking on them. The image in the previous section only had one option, the all-year volume-weighted average, but you can add more by selecting the “Available Averages” button in the upper-right hand corner. Doing so will open up a dialog box with averages that you can add to the factor view:

The starting averages are All, 5-, and 3- year volume-weighted averages. You can add these by clicking the checkboxes in the table. Alternatively, you can add a custom type if you want to use a different kind of average like straight or regression. You can do this by clicking the “Add Average” button and then selecting the options for the new average. Right now, only these three are supported by chainladder, but I have proposed that we add others like medial and geometric to the list:

In this example I have added a 2-year regression and selected all 4 average types in the table. This expands the number of rows in the LDF section of the factor view:

Next, you can select the LDFs by double-clicking on the LDF section. Double-clicking an entire row will select that whole row, and the CDFs are automatically calculated. The image below shows that I have selected the 2-year regression, and the ultimate loss values are automatically updated:

Alternatively, you can enter in your own custom values by typing or copy-pasting into the cells directly. And you can delete the selections by pressing the delete key over the cells or by double-clicking the row header of the selected LDF row. You can also remove LDF average types by clicking on the “available averages” button and unchecking the ones you want to remove.

Heatmap

This last feature is something that comes from the chainladder package. It was quite challenging to implement even though on the surface, all you have to do is tick the checkbox. This helps you identify outlier link ratios that you may want to exclude in your analysis:

There are some performance enhancements to be made on this feature, I’ll write up another post once that’s done. Below, you’ll see a gif of all of what I described above in action:

Posted in: Actuarial

No. 146: Development Factors with FASLR

21 February, 2022 5:42 PM / Leave a Comment /

A few things have happened since this last time I’ve posted about a technical subject – I have gotten into contact with Brian Fannin over at the CAS and now have two projects hosted on their GitHub page – PCDM, which I wrote about a couple years ago, and FASLR, a new project I started last year, which I’ll be talking about today.

FASLR (pronounced fæzlɹ̩), is a GUI wrapper built using the PyQt framework to accommodate open-source actuarial reserving engines, such as the chainladder packages written in Python and R.

OK. The buzzword-free version of that sentence is that FASLR is open-source software that is intended to help actuaries do reserving with buttons, windows, and mouse clicks. There are a few open-source packages that let actuaries do reserving by writing programs, and some commercial solutions that let actuaries do reserving with buttons and mouse clicks. But at least to my knowledge, there had yet to be an open-source interface-based software for doing reserving, so I decided to make one. What motivated me to start was I have been wanting to build graphical interfaces for my other projects, such as MIES, but hadn’t made a decision whether to use web-based technologies like Django (letting people use the software in the browser) or something desktop-based like PyQt. I have decided on using PyQt since that would require me to learn fewer languages or deal with browser stuff like JavaScript.

FASLR stands for Free Actuarial System for Loss Reserving, named after Fazlur Rahman Khan, an architect who designed a number of famous buildings in Chicago.

Other motivations include:

  • Giving me an excuse to learn PyQt
  • Increasing transparency on how actuarial computations are done
  • Giving students a window into how actuarial work is done in practice as opposed to exams
  • Increasing accessibility of actuarial software to the general public
  • Making a GUI compatible with existing open-source technologies
  • Imposing my worldview on how actuarial models should be built and implemented in the workplace
  • Bragging rights on the CAS GitHub page
FASLR basic interface

FASLR basic interface

The Chainladder Packages

Chainladder is a fancy word for one of the techniques that actuaries use to guess how much money insurance companies need to pay for claims. It’s also the name of a pair of open-source actuarial packages – one written in R, and another in Python. The R library was written many years ago by Markus Gesmann. It seems to have been written starting around 2007, which is the year of the earliest release I have been able to find on CRAN. The Python package is a port of the R library, written by John S Bogaardt starting around 2017 or so based on the commit history. These packages, by being open-source, have not only helped to improve transparency to how actuarial computations are done but have also improved accessibility to the field by being available to people who do not have the means to pay for commercial software, such as students looking to get into the field. However, since they are lightweight libraries, actuaries must write programs to do reserving – which, depending on personal preference, may or may not be the most productive way to get reserving done.

I think that last sentence is a fair criticism of using a programming language to get actuarial work done – especially when we consider the selection of development factors, the topic of today’s post. This isn’t to disparage these packages – both of which are major contributions to modernizing actuarial science. And thanks to John, pretty much 75% of the work is already done to get FASLR working – all I have to do is design the interface (unlike MIES, which will take forever to be ready). It is their work that makes something like FASLR possible. On the subject of development factors – this is a family of various averages of age-to-age factors used to develop losses to ultimate. When you use a package, you might need to write a line of code picking out which link ratios you want to exclude and then visualize the resulting averages by executing another line. If you didn’t like your selection, you may have to edit that line of code or write a new one and recompute – over and over again. That may be tedious, and hard to keep track of if you have several attempts. However, if you could simply double-click on a triangle of link ratios to exclude them and see the factors update in near-real time, you can get your work done a lot faster.

Below is an example (taken from the Chainladder documentation) of how we can use Chainladder to load a sample triangle and see the link ratios:

And to view the volume-weighted LDFs for all years, we execute:

Now, to exclude certain periods, we can pass a list of excluded periods to the .Development() method:

We can see that this has altered the 12-24 and 24-36 LDFs.

However, actuaries typically want to experiment with several exclusions with trial and error, so a GUI would be helpful here.

FASLR Example

I will now give a demo of how FASLR uses the Chainladder methods above to speed up LDF selection via a GUI. Below is an example of a window I designed to display a triangle of link ratios with the volume-weighted LDFs right below the triangle:

What I’d like to do is double-click a factor to exclude it. Ideally this will get the LDFs at the bottom to update immediately so I can see the results – without having to do all the typing we did with the Chainladder example. I have written FASLR to update the formatting of the link ratio to be struck-out with a pink background to indicate exclusion. Below are the first three accident years of the 12-24 column excluded.

You can see that the formatting has now updated with the first ldf changed from 1.733 to 1.717.

The GIF below demonstrates how fast we can preview the LDF changes using this feature:

The demo can be run from the FASLR source code, available on the CAS GitHub page.

This is just one feature preview out of what I hope will be many, so keep an eye open for future updates.

Technical Appendix

This was all much easier said than done. Getting that factor elimination feature to work was tricky, especially with me being new to PyQt and all. This feature makes use of a concept called Model-View-Controller which you can read more about here. Below is some example code from the FASLR module that does most of the work that we see in today’s post. It depends on all the other modules in the repository, so I don’t expect a full understanding from the code listing alone. To find out more, refer to the entire source code:

Posted in: Actuarial

No. 144: MIES – Intertemporal Choice

26 July, 2020 10:11 PM / Leave a Comment /

This entry is part of a series dedicated to MIES – a miniature insurance economic simulator. The source code for the project is available on GitHub.

Current Status

This week, I’ve been continuing my work on incorporating risk into the consumer behavior component of MIES. The next step in this process involves the concept of intertemporal choice, an interpretation of the budget constraint problem whereby a consumer can shift consumption from one time period to another by means of savings and loans. The content of this post follows chapter 10 of Varian.

For example, a person can consume more in a future period by saving money. A person can also increase their consumption today by taking out a loan, which comes at a cost of future consumption because they have to pay interest. When making decisions between current and future consumption, we also have to think about time value of money. When I was reading through Varian, I was happy to see that many of the concepts I learned from the financial mathematics actuarial exam were also discussed by Varian – such as bonds, annuities, and perpetuities – albeit in much less detail.

This inspired me to create a new repo to handle time value of money computations, which is not yet ready for its own series of posts, but for which you can see the initial work here. I had intended to make this repo further out in the future, but I got excited and started early.

Also relevant, is the concept of actuarial communication. Now that I’m blogging more about actuarial work, I will need to be able to write the notation here. There are some \LaTeX packages that can render actuarial notation, such as actuarialsymbol. Actuaries are still in the stone age when it comes to sharing technical work over the Internet, not out of ignorance, since many actuaries are familiar with \LaTeX, but out of corporate inertia in getting the right tools at work (which I can suppose be due to failure to persuade the right people) and lack of momentum and willingness as many people simply just try to make do with using ASCII characters to express mathematical notation. I think this is a major impediment to adding rigor to practical actuarial work, which many young analysts complain about when they first start working, as they notice that spreadsheet models tend to be a lot more dull than what they see on the exams.

I was a bit anxious in trying to get the actuarialsymbol package working since, although I knew how to get it working on my desktop, I wasn’t sure if it would work with WordPress or Anki, a study tool that I use. Fortunately, it does work! For example, the famous annuity symbol can be rendered with the command \ax{x:\angln}:

Rendered by QuickLaTeX.com

That was easy. There’s no reason why intraoffice email can’t support this, so I hope that it encourages you to pick it up as well.

The Statics Module

Up until now, testing new features has been cumbersome since many of the previous demos I have written about required existing simulation data. That is, in order to test things like intertemporal choice, I would first need to set up a simulation, run it, and then use the results as inputs into the new functions, classes, or methods.

That really shouldn’t be necessary, especially since many of the concepts I have been making modules for apply to economics in general, and not just to insurance. To solve this problem, I created the statics module, which is named after the process of comparative statics, which examines how behavior changes when an exogenous variable in the model changes (aka all the charts I’ve been making about MIES).

The statics module currently has one class, Consumption, which can return attributes such as the optimal consumption of a person given a budget and utility function:

A lot of the code here is the same as that which can be found in the Person class. However, instead of needing to instantiate a person to do comparative statics, I can just use the Consumption class directly from the statics module. This should make creating and testing examples much easier.

Since much of the code in statics is the same as in the Person class, that gives me a hint that I can make things more maintainable by refactoring the code. I would think the right thing to do is to have the Person class use the Consumption class in the statics module, rather than the other way around.

The Intertemporal Class

The intertemporal budget constraint is:

    \[c_1 + c_2/(1+r) = m_1 + m_2/(1+r)\]

Note that this has the same form as the endowment budget constraint:

    \[p_1 x_1 + p_2 x_2 = p_1 m_1 + p_2 m_2 \]

With the difference being that the two endowment goods are now replaced by consumption in times 1 and 2, represented by the cs and the prices, the ps are now replaced by discounted unit prices. The subscript 1 represents the current time and the subscript 2 represents the future time, with the price of future consumption being discounted to present value via the interest rate, r.

The consumer can shift consumption between periods 1 and 2 via saving and lending, subject to the constraint that the amount saved during the first period cannot exceed their first period income, and the amount borrowed during the first period cannot exceed the present value of the income of the second period.

Since the intertemporal budget constraint is a form of the endowment constraint, we can modify the Endowment class in MIES to accommodate this type of consumption. I have created a subclass called Intertemporal that inherits from the Endowment class:

The main difference here is that the Intertemporal class can accept an interest rate and an inflation rate to adjust the present value of future consumption.

Example

As an example, suppose we have a person who makes 5 dollars in each of time periods 1 and 2. The market interest rate is 10% and their utility function takes the Cobb Douglas form of:

    \[u(x_1, x_2) = x_1^{.5} x_2^{.5}\]

which means they will spend half of the present value of the endowment as consumption in period 1:

The main thing that sticks out here is that the slope of the budget constraint has changed to reflect the adjustment of income to present value. The x-axis intercept is slightly less than 10 because the present value of income is slightly less than 10, and the y-axis intercept is slightly more than 10 because if a person saved all of their time 1 income, they would receive interest of 5 * .1 = .5, making maximum consumption in period 2 10.5.

Since the person allocates half of the present value of the endowment to time 1 consumption, this means they will spend (5 + 5/1.1) * .5 = 4.77 in period one, saving 5 – 4.77 = .23, which then grows to .23 * (1 + .1) = .25 in period 2, which allows for a time 2 consumption of 5 + .25 = 5.25. This is verified by calling the optimal_bundle() method of the Consumption class:

Further Improvements

The Varian chapter on intertemporal choice briefly explores present value calculations for various payment streams, such as bonds and perpetuities. I first made a small attempt at creating a tvm module, but quickly realized that the subject of time value of money is much more complex than what is introduced in Varian, since I know that other texts go further in depth, and hence it may be necessary to split a new repo off from MIES so that it can be distributed separately. This repo is called TmVal, the early stages of which I have uploaded here.

Neither of these are ready for demonstration, but you can click on the links if you are interested in seeing what I have done. The next chapter of Varian covers asset markets, which at first glance seems to just be some examples of economic models, so I’m not sure if it has any features I would like to add to MIES. There is still more work to be done on refactoring the code, so I may do that, or move further into risk aversion, or do some more work on TmVal.

Posted in: Actuarial, MIES / Tagged: , , ,

No. 143: MIES – Endowments

19 July, 2020 11:57 PM / Leave a Comment /

This entry is part of a series dedicated to MIES – a miniature insurance economic simulator. The source code for the project is available on GitHub.

Current Status

Last week, I took a break from MIES to focus on PCDM, a relational database specification for the P&C insurance industry. This week, I’m back to making progress on the consumer behavior portion of MIES, by shifting the focus from personal income to the personal endowment as the main financial constraint underlying purchasing decisions.

In short, an endowment is the consumer’s assets. When making consumption choices, people can use their income to purchase goods and services, but they can also draw from assets that they have accumulated over time, such as from savings and checking accounts, and by selling goods that they own. Furthermore, by taking the endowment into consideration, we will now be able to model situation when a person might not have a regular income, but can still make purchases using their assets (such as unemployed or retired persons who are not working).

In the context of insurance, the endowment is important because people purchase insurance to indemnify themselves against events that might damage or reduce the value of their assets. In the absence of the endowment, we would ignore an important determinant of insurance purchasing behavior. Incorporating wealth into MIES will take some time, and the textbook material I need to work on spans five chapters of Varian. Therefore, I estimate that this process will take me at least a month to do:

  1. Endowment
  2. Intertemporal Choice
  3. Asset Markets
  4. Uncertainty
  5. Risky Assets

These concepts will involve making some substantial changes to the utility functions as well. For now I’ll start with the endowment, which required me to modify the Budget, Slutsky, and Hicks classes of MIES.

The Endowment Class

An endowment is a bundle of goods or services that has a value based on the sum product of their prices and quantities:

    \[p_1 \omega_1 + p_2 \omega_2 = m \]

Where m represents income, each omega represents the quantity of each good, and each p represents the price. Rather than treat income as a flow quantity from an external source, in this interpretation of consumer choice theory we, we treat income as a stock quantity that includes the assets of of the consumer – that is, what the consumer has to spend at a certain point of time depends on the valuation of their assets.

This definition of income loosens the assumption of fixed income that I had made until now. This is because changes in asset values can now impact a person’s income. For example, if a person has a car and a house, their depreciation or appreciation changes the amount the person can sell them for on the market.

The good news is that MIES already has much of the machinery already coded up to allow us to work with endowments, so the new class definition is quite simple:

An endowment takes two goods, and their quantities. Upon initialization, Python will automatically calculate the Endowment’s value by multiplying the prices of the goods by the quantities supplied. I wrote this function as a property decorator, which was introduced to fix a bug I discovered when working with the Budget class. Earlier, changing the price of a good failed to change the budget constraint of a consumer, but the property decorator will now dynamically calculate certain attributes that depend on the price, such as income in the case of an endowment.

To illustrate, we can define two goods, each with a price of 1. We then initialize an endowment with a quantity of 5 for each of these goods:

Now we can check that the income was properly calculated by calling endowment.income. Since each good has a price of 1, and there are 5 of each good, the income should be 5 x 1 + 5 x 1 = 10:

Now that we have the Endowment class defined, we need to modify the other classes that used goods, such as the Budget class. Previously, the Budget class accepted two goods, an income amount, and a name to refer to the budget. Now I would like the Budget class to an accept an endowment as an alternative to specifying each good individually. The tricky part here is that in the former case, the class needs to be able to keep income fixed when the prices of goods change, but in the latter case, the income needs to change dynamically based on the prices of the goods.

To handle this, I created an alternative constructor called from_endowment() that lets you pass an endowment to the Budget class to initialize a budget object. I also created another constructor called from_bundle() that lets you define a Budget the old way more explicitly, to make it more obvious to anyone reading the code whether the budget was initialized with an endowment or individual goods:

And lastly, I added some consistency checks to make sure that the endowment value equals the sum product of the prices and quantities of the goods provided. The reason why these checks are here is because a person can still use the default constructor to specify each good individually along with an endowment, just based on how the arguments are defined. While this is possible, I would discourage doing this since 1) it’s less explicit than using the alternative constructors, 2) supplying the individual goods along with the endowment is redundant, and 3) it can lead to errors being thrown.

To initialize a budget by passing an endowment, simply use the alternative constructor:

Slutsky Decomposition

The loosening of assumptions brought about by the endowment introduces some changes to the Slutsky equation. In the examples I provided a few weeks ago, we assumed that income remained fixed when prices changed. Since changes in price now change the value of the endowment, we must now account for this change in the Slutsky equation. The derivation of this modified form can be found in Varian, so I’ll skip to the result:

    \[\frac{\Delta x_1}{\Delta p_1} = \frac{\Delta x_1^s}{\Delta p_1} + (\omega_1 - x_1)\frac{\Delta x_1^m}{\Delta m}\]

The Slutsky equation can now be explained by three effects: the substitution and ordinary income effects, which are the same as before, and an endowment effect, which models how consumer choice changes when the value of the endowment changes.

Like the Budget class, the Slutsky class has been modified to take budgets that were constructed from individual goods or an endowment. Plotting the Slutsky class is now quite bit messier, since a new budget line, bundle, and utility curve are now added to an already crowded plot.

I have not yet gotten endowments to work within the context of insurance, so the image below comes from a modified version of an example provided in Varian where a milk producer faces a $1 increase in the price of milk – his endowment increases in value, and hence income. However with the graph as cluttered as it is, it can be hard to visually isolate the effects:

It does look better with a larger plotting area if you try it with MIES, but not so much when I have to shrink the image to fit it within the margins here.

Posted in: Actuarial, MIES

No. 142: The Property Casualty Data Model Specification

12 July, 2020 10:31 PM / 4 Comments /

Introduction

A few weeks ago, I stumbled across something neat – the Property Casualty Data Model (PCDM). PCDM is a relational database specification that covers all major parts of an insurance company’s operations. At first glance, the web page on which it is located seemed mundane to me, so I almost overlooked it, but when I opened the accompanying documentation, I realized I had stumbled upon a goldmine of useful information. This document contains enough information to implement an entire data warehouse and then tweak it to an organization’s specific needs.

Although we actuaries have a reputation for being able to handle data, most of us have not received any kind of formal training in handling relational database systems, and have had little interaction with standards organizations outside of our own governing organizations like the CAS, SOA, and AAA. When I tried searching for PCDM in the CAS library, I was astonished to find just a handful of references to the specification, and flabbergasted that there had been a document floating around on another profession’s website for the last seven years that almost no actuaries had ever heard about, but contained the exact type of information that many actuaries wanted but thought had never existed in the public domain (and in my case, it contains more than what I need for parts of the MIES backend).

One benefit that I’ve had from working several jobs, and also as a consultant, is that I’ve been able to witness widely varying levels of maturity of data warehouse systems at commercial insurance companies of different sizes and functions (specialty, commercial, and reinsurance) as well as those of a major stock exchange to get some perspective of how insurance database implementations compare to those of other industries. I have seen many actuaries at small and midsize carriers struggle with how to create OLAP data warehouses, not knowing what tables, relationships, and fields to define, how to do it in a way that conforms to commonly accepted data management practices (if they even knew what they were), or even where to look or whom to talk to to find out what data are stored at the company, and in what form.

Earlier in my career, at a small insurer, a few years before this document existed, I would sometimes encounter databases with hundreds of undocumented tables, not knowing what any of the tables stored, if they were the right tables I needed, or if they were designed appropriately. I was one of the few actuaries in my department who knew SQL (and even then, I wasn’t good at it). So, with my introductory textbook on database management systems at my side and a landline I used to contact people in the IT deparment, I embarked upon the long journey of understanding insurance information systems.

In those days, the game kind of went like this. I would ask my boss if they knew anyone in IT, then I’d pick up the phone and call them to ask if they maintained the database I was looking at or knew of anyone who did or anyone who might know anyone who did. That chain of phone calls typically went 5 people deep until I finally reached someone who actually worked on the relevant database, and if I was lucky, they’d have some documentation, and I’d slowly figure out what kind of data I was working with.

It might have looked like this. What the hell? Boss, this is gonna take me a while to understand. (Actually this is PCDM, but the good news about PCDM is that it’s documented – but imagine what it would be like if it weren’t and the last person who touched it left 3 years ago).

When I moved to a larger organization, I came to realize how large of a knowledge/talent gap there was between companies when I actually got to use a well-documented data warehouse that had all the entities, relationships, and fields defined as well a data dictionary detailing what all the values were – a rare practice that even many established insurers don’t follow. Unfortunately, when I left that organization, I also lost access to that documentation, and with it, a wealth of knowledge on what a proper data warehouse should look like.

I had thought to myself if only the CAS had a standard that actuaries and students could access, we’d have something that would actually resemble what a database at a real insurer actually looked like, instead of just having just theoretical papers from which to learn that might have some code copy-pasted in. If we had a standard, knowledge of proper database design and implementation would then not be employer dependent, and new research papers could reference the standard rather than just imagining what a data warehouse would look like or omitting proprietary parts of a company’s database.

When I came across PCDM, I realized the significance of a document I randomly found while eating breakfast on holiday. Therefore, I immediately got to work in writing an implementation of it in Python so that other actuaries could finally have what I wish I had when I was younger. I completed the first release at the end of the July 4th weekend, and made it available on GitHub. My hope is that somebody out there finds this post and clones my repository – and understands how important it is to spread word of this specification.

Subject Area Models

PCDM details 13 subject area models (SAMs), each of which comes with an entity-relationship (ER) diagram. Each subject area model represents a major portion of an insurance company’s operations:

  1. Party
  2. Account and Agreement
  3. Policy
  4. Claim
  5. Assessment
  6. Agreement Role
  7. Claim Role
  8. Staffing Role
  9. Party Subtype
  10. Insurable
  11. Money
  12. Event
  13. Product Coverage

For example, the Party SAM contains information on all parties that are involved in insurance transactions, such as households, employees, vendors, etc. Other SAMs contain information on underwriting, claims, and accounting operations. The rest of this post will show you what those SAMs look like – the images are crowded and contain quite a bit of information, so it’s best to click on them to see them in full resolution. If you want more details, the PCDM document is available on the object management group website.

Party

As stated before, the Party SAM contains information about households, firms, and staff:

Account and Agreement

The Account and Agreement SAM contains information on legal agreements, such as policies, reinsurance contracts, etc.

Policy

The Policy SAM contains information on…policies. Policy number, effective date, expiration date, limits, deductibles, coverage, etc.

Claim

The Claim SAM contains information on claims, relevant dates, adjusters, lawyers, damage amounts, etc.

Assessment

The Assessment SAM contains information on how the insurer goes about gathering information, such as credit scores, appraisals, and investigations carried out during the claim adjustment and underwriting processes.

Agreement

The Agreement Role SAM contains information on the different types of parties that might be involved in insurance – mostly an expansion of the Party Role superclass found in the Party SAM.

Claim Role

The Claim Role SAM contains information on the parties involved in the adjustment process, such as adjusters, claimants, lawyers, etc.

Staffing Role

The Staffing Role SAM contains information on insurance company staff and contractors.

Party Subtype

The Party Subtype SAM contains information on company subdivisions.

Insurable Object

The Insurable Object SAM contains information on things that can be insured, like cars and buildings.

Money

The Money SAM contains information on transactions, like premium, loss payments, and case reserves.

Event

The Event SAM contains information on policy and claim events, like policy inception, cancelation, and claim occurrence.

Product Coverage

The Product Coverage SAM contains inforamtion on product types and coverages.

Python/SQLAlchemy Implementation

I figured if I want to claim credit for anything besides just copying and pasting the diagrams above from the specification, I should actually make some kind of contribution. The good news is I haven’t found any kind of implementation on GitHub – what I mean by that is the PDF is nice to have and all, but you can’t deploy a datawarehouse with the PDF, it needs to be translated into code which people can then use to deploy the warehouse. Therefore, I’ve written my own implemntation of PCDM in Python using the SQLAlchemy ORM.

Below is an example of how I translated the Party SAM into code. I did this for the 12 other SAMs as well, and then wrote another module to combine them all together into a single deployable data warehouse:

I have taken a few liberties in my own implementation, such as creating surrogate keys whenever I came across a composite identifier, which should make joining easier. I’ve also chosen my own data types for ambiguous fields (such as integers for identifiers and strings for descriptions) based on my own interpretation, and chose to represent superclass/subclass relationships by making one table for each superclass and subclass, which can be joined with primary/foreign key relationships.

Installation and Deployment

To use PCDM, you need to install SQLAlchemy if you don’t already have it:

You can install and deploy PCDM by cloning the repo from my GitHub. The deployment script is displayed below:

You can either run this script, or use the terminal to clone and then deploy:

If the deployment succeeds, you should see a SQLite database appear with 256 tables in it:

Although SQLite is the default, you can use this repo to make your own deployments for other RDBMSs (Postgres, SQL Sever, etc.). I have not made these ports yet, but I’m assumming if you have read this far, you’re a technically savvy person who can help me out by writing your own.

Documentation

I am working on making my own documentation, which is not yet available. But for now you can refer to the official OMG PCDM document.

Bugs

Warning – this repo is open source, and hence contains no warranty and may contain bugs. You can help contribute to the effort by making an issue or pull request should you encounter a bug.

Posted in: Actuarial / Tagged: , , , ,