Author Archives: Gene Dan

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: , , , ,

No. 141: MIES – Premium and Claim Transactions

5 July, 2020 10:28 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

Up until now, I’ve been able to demonstrate basic consumer behavior and certain market phenomena seen in the insurance industry. However, there’s been a major problem with MIES in that no money has actually changed hands in any of the simulations we’ve seen so far. Consumers have simply switched carriers depending on price, which allowed me to demonstrate adverse selection, but not much else. Consumption decisions involving insurance depend on wealth, but since there was no way to calculate wealth in MIES, its ability to model these decisions was limited.

The next view chapters in Varian place a heavy emphasis on wealth and risk tolerance, so this week, I made the decision to work on incorporating transactions before diving deeper into consumer behavior.

At first glance, transactions might seem like a simple thing to implement, after all, why not just keep a running cash balance for each entity, and then add and subtract payments as needed? The problem with this method is the same problem that leads companies to use double-entry accounting. Transactions are more complicated than simply sending money from one place to another. Loans are generated and capital is invested, which creates liabilities that must be considered when trying to calculate the wealth of an entity. Even something as simple as a premium payment is effectively a loan to an insurance company that needs to have a liability recorded (the unearned premium reserve), in addition to an increase in cash to the insurer.

Therefore, I’ve had to draw on my basic knowledge of accounting, which made me uneasy since I’m not an accountant myself. However, in order to get MIES to model the phenomena I want to model, and to answer the questions I have about insurance, I need to implement double-entry accounting, and eventually, statutory accounting rules. I ran across a post on Hacker News titled, ‘It’s OK for your open source library to be a bit shitty,’ which encouraged me to keep moving forward with the project despite the amount of discomfort I have.

I have certainly found many errors in MIES from past versions and there will likely be many more, including in this post. However, there’s not a lot of open source actuarial stuff out there, or in particular, open source actuarial simulations incorporating both economics concepts and double-entry accounting. Or, if there are packages out there, they aren’t easy to find. Thus, I’ve taken the step to put something out there, awaiting any feedback for things that need to fixed, and then making improvements. If this ever proves to be something useful, younger generations will create even better tools in the future.

The Bank Class

Banks facilitate transactions between insurers, brokers, customers, and other banks. While it may eventually be possible to define more than one bank per simulation, all the examples in the near future will have a single bank at which entities can deposit money and send payments to each other.

Schema

As with all the other entities, each bank comes with its own database:

Here, a bank can accept three types of customers. The customer table represents the customer superclass and references the person, insurer, and bank subclasses. Each customer can have an optional number of accounts. Any of these customers can send transactions to any other customer, including themself. Notice that the transaction table has two relationships to the account table, since the debit and credit account fields for each transaction both point to the account table.

The SQLAlchemy mapping is defined below:

Bank Methods

Upon initialization, a bank will register itself as a customer, this allows it to have its own accounts, as well as to accept deposits from other customers. The reason why it needs to have its own accounts is because each transaction will need to have corresponding debit and credit accounts, and transactions between a bank and its customers will sometimes have one of the bank’s own accounts involved.

The method bank.get_customers() takes a list of IDs, which can be either those of people, insurers, or banks. For each of these entity IDs, the bank will create its own identifier for the customer, which can differ between that customer’s underlying ID. For example, a person with a person ID of 1, and an insurer with an insurer ID of 1, will have separate customer ids.

The method bank.assign_accounts() takes a list of customer IDs, a type of account (such as cash), and then creates that type of account for each customer.

The method bank.make_transactions() will take a list of transactions, each of which have a debit account, credit account, transaction date, and transaction volume defined, and then store them in the transactions table.

Now that we have our bank defined, I’ll walk through the insurance underwriting/claim cycle and show how the transactions that are currently available in MIES work. First, we’ll import the necessary modules, create an environment, make a population of 1000 people, and then create a bank called ‘blargo’ that has 4B in starting capital:

Starting Wealth

Before we can issue policies to customers, we need to have a starting amount of wealth for each person so that they can actually pay for their policies. Furthermore, we also need each person to have a bank account from which they can issue payments to their insurers. To take care of these two steps, we’ll first send the person IDs to the bank, which will then create corresponding customer IDs and then establish one cash account for each customer:

This action populates two tables. The person table contains a record of all 1000 people, and the customer table has 1001 records, since each person becomes a customer, but the bank itself is already its own customer:

In the next step, we’ll use a new method called grant_wealth() which the environment uses to give each person a starting amount of wealth. Since wealth is not evenly distributed in society, I’ve drawn these values from the Pareto distribution:

The method is defined as follows:

This action deposits wealth in each person’s cash account. This is marked as the debit side of the transaction, the credit side is the liability that the bank takes on by accepting the deposits, since deposits are loans to the bank:

Policy Inception

Before we issue policies, we need to create two more entities in the simulation. One, a broker called ‘rayon’ and an insurer called ‘company_1.’ Initializing a company now requires two new arguments, a bank with which it associates, and its inception date. These new arguments are used to create bank accounts for the insurer:

We’ll now use the broker rayon to place the customers with the insurer. This method also has a bank as a new argument, which will be used to facilitate the transactions:

This action creates a policy record for each insurer, which I won’t show here since I’ve already done so in a previous post. What has changed however, is that each person needs to pay the premium to the insurer up front. The broker is able to tell the bank to create a transaction for each premium payment. This time, the debit account is the insurer’s cash account, and the credit account is the person’s cash account. In the picture below, we see that there are 1000 additional transactions starting with transaction_id 1003 (1002 was used to seed insurer capital). The debit account on all these transactions is account ID 1004, which is the insurer’s cash account:

In reality, a corresponding liability should also be created in the insurer’s accounting system. This is called the unearned premium reserve which is what the insured is entitled to recieve if their policy gets canceled. This feature is not yet implemented in MIES, but it’s an important liability to consider in insurance.

Loss Occurrence

Now that we have our policies issued, we’re ready to simulate some losses:

This action produced 57 loss events, which can be found in the events table in the universe database:

Claim Reporting

These losses are not considered claims until they are reported to the insurer. Otherwise, the insurer has no knowledge that they occurred:

This method has changed from last week. The loss amounts are now reported as case reserves, which are estimates made by an insurer on how much they will need to pay for the claim. This is now distinguished from paid losses, which are the actual payments the insurer makes to the insured:

This action creates two transactions for each claim. One transaction, called ‘open claim’ signals that a claim has been created. Another transaction, called ‘set case reserve,’ sets a case reserve for each claim. Since there are 57 losses, there are 57 claims, and 114 transactions:

Notice that we have some fairly restrictive assumptions for the simulation. The case reserves are equal to the ground up losses, and all 57 losses are reported to and known by the insurer immediately. This is not the case in the real world, where an insurer does not know how much claims will cost until they are settled, and may not know about claims until many years after they have occurred. We’ll need to revisit this problem in the future, since estimating claim amounts, including those on unreported claims, is a core function of actuarial science.

Claim Settlement

Let’s close these claims by issuing payments:

Insurer.pay_claims() is a new method used to send checks to the insureds for indemnification:

There’s a lot going on here, so I’ll break it down. The payments to the insureds are handled first. We can see this by going to the transactions table in the bank database:

Notice that there are 114 additional transactions, two for each of the 57 claims. One transaction sends a payment from the insurer to the customer. You can see this since the debit account is the cash account of the customer, and the credit accont (1004) is the cash account of the insurer. The other transaction is a payment from the insured to whomever they owe money to due to the loss. The debit side of the transaction is now a reduction in the bank’s liability account, and the credit amount is a reduction in cash equal to the claim amount for the insured:

Next, three transactions are entered for each claim into the insurer’s database:

  1. Reduce case reserve
  2. Claim payment
  3. Close claim

‘Reduce case reserve’ is a reduction in the claims reserve to zero, signifying that the insurer no longer owes money to the insured. The ‘claim payment’ is a corresponding transaction representing the actual payment, and ‘close claim’ is a transaction that indicates that the claim is now closed. The insurer now has 5 claims transactions for each claim, 57 x 5 = 285 transactions in total. These five transactions are: 1) open claim, 2) set case reserve, 3) reduce case reserve 4) claim payment, and 5) close claim.

One source of confusion I had is that this way of recording transactions does not have the double-entry accounting that you’d see in a general ledger. Indeed, this form is more common for actuarial pricing modelers who do not usually need to get into the finer details of debits and credits. However, I’m leaning towards changing the claims transaction tables to also be double-entry, since doing so also makes things easier to program and avoids questions with negative values. For example, I had to think about whether to record case reserve reductions as a positive or negative value. This confusion is not present if I record them as credit and debit amounts.

Consumer Income

Each person now gets a paycheck during each period. This can be issued by the environment class:

There’s nothing too special about this method, it just debits each person’s cash account by their income amount:

An additional 1000 transactions have been recorded, one for each customer. As with wealth, the debit side of the transaction is the person’s cash account, with a corresponding credit to the bank’s liability account:

Policy Pricing

Now that the claims have been reported and settled, the insurer can use this information to recalibrate the premium for each customer. However, unlike last week, claim amounts are not stored as a single column called ‘incurred_loss’, but now must be calculated from the transaction amounts. To handle this, I created a set of queries that can be used to return the case reserves, paid losses, and incurred amounts for each claim:

We mostly need to be concerned about the last one of these, query_pricing_model_data() which uses the first three to combine policy and claim information together, which can be priced with a GLM:

Since most people don’t have a claim, most incurred loss amounts are zero. This data set can then be used by the insurer to reprice with a GLM, and the new pricing algorithm is used by the broker to quote and place business:

Further Improvements

Now that I’ve got transactions modeled, I can calculate the wealth for each entity in the simulation. This is key piece required to make further changes to the way consumer preferences work in MIES, which will soon incorporate risk tolerance and wealth.

Posted in: Actuarial, Mathematics, MIES

No. 140: MIES – Rate Changes – Slutsky and Hicks Decomposition

28 June, 2020 6:24 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 revisited the MIES backend to split what was a single database into multiple databases – one per entity in the simulation. This enhances the level of realism and should make it easier to program transactions between the entities. This week, I’ll revisit consumer behavior by implementing the Slutsky and Hicks decomposition methods for analyzing price changes.

In the context of insurance, consumers frequently experience changes in price, typically during renewals when insurers apply updated rating algorithms against new information known about the insureds. For example, If you’ve made a claim or two during your policy period, and miss a rent payment, causing your credit score to go down, your premium is likely to increase upon renewal. This is because past claims and credit are frequently used as the underlying variables in a rating algorithm.

What happens when consumers face an increase in rates? Oftentimes, they reduce coverage, may try to shop around, or may do nothing at all and simply pay the new price. Today’s post discusses the first phenomenon, whereby an increase in rates causes a reduction in the amount of insurance that a consumer purchases. We can use the Slutsky and Hicks decomposition methods to break down this behavior into two components:

  1. The substitution effect
  2. The income effect

The substitution effect concerns the change in relative prices between two goods. Here, when a consumer faces a premium increase, the price of insurance increases relative to the price of all other goods, altering the rate at which the consumer would be willing to exchange a unit of insurance for a unit of all other goods.

The income effect concerns the change in the price of a good relative to consumer income. When a consumer faces a premium increase without a corresponding increase in income, their income affords them fewer units of insurance.

The content of this post roughly follows the content of chapter 8 in Varian. As usual, I focus on how this applies to my own project and will skip over much of the theoretical derivations that can be found in the text.

Slutsky Identity

The Slutsky identity has the following form:

    \[\frac{\Delta x_1}{\Delta p_1} = \frac{\Delta x_1^s}{\Delta p_1} - \frac{\Delta x_1^m}{\Delta m}x_1\]

Where the x_1 represents the quantity of good 1 (in this case insurance), p_1 represents the price of insurance (that is, the premium) and m represents the consumer’s income. The deltas are used to describe how the quantity of insurance purchased changes with premium, expressed on the left side of the identity. The first term after the equals sign represents the substitution effect, and the second term after the equals sign represents the income effect.

The Slutsky identity can also be expressed in terms of units instead of rates of change (\Delta x_1^m = -\Delta x_1^n):

    \[\Delta x_1 = \Delta x_1^s + \Delta x_1^n\]

While MIES can handle both forms, we’ll focus on the first form for the rest of the chapter.

Substitution Effect

Let’s examine the substitution effect. First, we’ll conduct all the steps manually, and then I’ll show how they are integrated into MIES. If we have some data already stored in a past simulation, we can extract a person from it:

Here, we have a person who makes 89k per year, with Cobb Douglas parameters c = 0.1 and d = 0.9. This means this person will spend 10% of their income on premium. For the sake of making the graphs in this post less cluttered and easier to read, let’s change c = d = .5 so the person spends 50% of their income on insurance:

From the above figure, we can see that the person consumes about 45k worth of insurance. That’s about 11 units of exposure at a 4k premium per exposure, which is unrealistic in real life, but makes life easy for me because I haven’t bothered to update the margins of my website to accommodate wider figures. We’ll denote this bundle as ‘Old Bundle’ because we’ll compare it to the new consumption bundle after a rate change (note – if you are trying to replicate this in MIES, your figures may look a bit different, in reality I manually adjust the style of the plots so they can fit on my blog).

Now, let’s suppose the person faces a 100% increase in their rate so that their premium is 8k per unit of exposure, and construct a budget to reflect this:

Plotting this new budget, we see that the line tilts inward, since the person can only afford half as much insurance as before:

The first stage in Slutsky decomposition involves calculating the substitution effect. This is done by tilting the budget line to the slope of the new prices, but with income adjusted so that the person can still afford their old bundle. We then determine the new optimal bundle at this budget (denoted ‘substitution budget’) and call it the substitution bundle:

    \[\Delta m = x_1 \Delta p_1\]

You can see that if the person were given enough money to purchase the same amount of insurance even after the rate increase, they would still reduce their consumption from 11.1 to 8.3 units of insurance. 8.3 – 11.1 = -2.8 is the substitution effect.

Income Effect

Now, to calculate the income effect, we then shift the substitution budget to the position of the new budget:

The person purchases 5.5 units of insurance at the new budget, so the income effect is 5.5 – 8.3 = -2.8 for the substitution effect. The total effect is -2.8 -2.8 = -5.6 units of insurance, the sum of the substitution and income effect. This is the same as the new consumption minus the old consumption 5.5 – 11.1 = -5.6

MIES Integration

The Slutsky decomposition process has been integrated into MIES as a class in econtools/slutsky.py:

This has been added to the Person class, so we can use its methods to get the substitution and income effects. This conveniently packages all the manual steps we took above together:

Which returns -2.8 for each effect, the same as above.

Hicks Decomposition

A similar decomposition method is called Hicks decomposition. Instead of pivoting the budget constraint so that the person can afford the same bundle as before, the budget constraint is shifted so that they have the same utility as before. Using algebra, we can solve this by fixing utility:

    \[m^\prime = \frac{\bar{u}}{\left(\frac{c}{c +d}\frac{1}{p_1^\prime}\right)^c\left(\frac{d}{c+d}\frac{1}{p_2}\right)^d}\]

where m^\prime is the adjusted income, and p_1^\prime is the new premium, and \bar{u} is the utility fixed at the original level.

You’ll notice there’s a subtle difference, the new bundle is the same as in the Slutsky case, but the substitution bundle in this case is on the original utility curve. This gives different answers for the substitution and income effects, which are -3.3 and -2.3, which add up to a total effect of -5.6, as before.

The code for the Hicks class is much the same as that for the Slutsky class, so I won’t post most of it here, the relevant change is in calculating the substitution budget:

Further Improvements

Since Hicks substitution is mostly similar to Slutsky in terms of code, it makes sense for the Hicks class to inherit from the Slutsky class or for both of them to inherit from a superclass.

I’m currently working on implementing wealth into MIES, since as of today, none of the transactions actually impact wealth.

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

No. 139: MIES – Schema Changes and Claims Reporting

21 June, 2020 4:13 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

When programming MIES, I face a trade-off between rolling out enough features for a weekly blog post and spending more time trying to learn Python in order to code them in the most elegant way possible. If I implement things too quickly, I face the problem of accumulating bugs and technical debt, but if I spend too much time trying learn the latest libraries, I’d never get anything done. Therefore, I’ve decided to just write what I can most weeks, but revisiting the code to make it better every 3rd or 4th week, so I can implement any new technical knowledge I’d gained during that time.

Last month, I demonstrated the phenomenon of adverse selection using MIES. However, the underlying database was not a realistic depiction of the real world:

There was only one database, shared by all entities involved in the simulation – firms, people, and the environment. In business, it’s more realistic for companies to have their own internal databases, without the ability to access any other company’s database. I’ve reached point where I’ve decided that it would be difficult to add insurance operations, specifically the reporting of claims, if all entities used the same database. Therefore, I’ve decided to spend time this week setting up separate schemas for each company.

The reason why I didn’t do this earlier is because I hadn’t gone far enough in the SQLAlchemy documentation to figure out how to establish multiple metadata bases to allow each company in the simulation to access its own database. The decision I was facing last month was to either write the adverse selection demo with the knowledge I had, or to keep reading further to set things up the right way and then write about how I built nothing, but read some documentation in the hope that I’d build something later. I’d gotten tired of doing the latter one too many times, so I decided to just go for it.

This week, I was confident that I’d be able to fix the single-schema problem and add a claims reporting facility as well, so that’s what I’ll go over today.

Splitting the Database

Splitting the database involved two separate challenges:

  1. Defining the schema for each entity
  2. Rewriting the code to accept the new backend structure

This second challenge was the main motivation for updating the schema now rather than later. The more features I added to MIES, the more of them I would have to rewrite when I inevitably split the database.

First, I envisioned the database structure I wanted for each firm:

Much of this was taken from the original schema, as seen at the top of this post. The policy table contains the same information as before, minus the company id. This is because a company does not need to have this field if all of its policies belong to itself.

The customer table is new. Here, I faced the challenge of whether I wanted to maintain data integrity between the information a company knows about its own customers, and their actual attributes as they exist outside the knowledge of the firm. For example, if a person switches insurers and then changes their profession, should the original insurer know about it?

In the real world, the answer is no. This also applies to things like credit scores which can change after the company does an initial credit check. Therefore, I’ve decided to discard data integrity between companies and the environment. I may want to rethink whether I want the all-knowing environment class to at least have a canonical record of all the information in the simulation (the answer is usually yes when I write these thoughts out in a blog post).

The customer table is analogous to the person table as before, but only contains information that the firm knows about each customer. This information typically costs money to acquire when quoting policies, and is considered an underwriting expense that I will need to add later.

Lastly, I’ve added a new table for claims. I mentioned last month that since we assumed each loss was fully covered, there was no need to distinguish between losses and claims. Now that we’re facing the challenge of assigning a losses to different companies as claims, I decided to lay the groundwork now so that we can loosen this assumption when we need to. Gradually, we will see the what the differences is between a loss and a claim. A loss is a fortuitous event that happens to a person, and an insurance claim is a financial claim that a person makes against their insurer for indemnification under their policy. There is also a distinction between the loss a person endures and the loss that a company needs to pay, due to things like coverage exclusions, limits, and deductibles. The claim table will take care of many of these distinctions.

Therefore, in a two-firm simulation, we will have a database for each firm. Below, we have two ER-diagrams side-by-side for each company:

This idea carries over to the case of an n-firm simulation, where we’ll have n databases. The code that defines the firm schema now resides in a new module:

There is also a schema for the environment, which is similar to before but minus the policy-specific information:

Query Encapsulation

MIES relies on querying its databases to run the simulations. Up until now, many of these queries resided within the environment, broker, and insurer classes. The tricky thing about querying databases is that you should close the connections to them once you no longer need them. However, establishing and closing a database requires a few lines of code, and the queries themselves can be verbose as well. This can get repetitive and can lead to bugs if I forget to close a connection or make modifications to reused queries.

Therefore, I thought it would be a good idea to encapsulate them into functions. In order to do this, I created two modules in a folder called utilities that are used to connect to and then query the databases, respectively. For example, the connections file contains code to connect to either the universe (environment) database or the database for a particular company:

You can see that you need to import some things, and also define an engine, session, and connection. That’s quite a bit of code that I don’t want to repeat every time I need to access a database.

Moving onto the queries, we likewise have code that we don’t want to repeat. For example, the query_population() function in the queries file returns the PersonTable from the environment schema as a dataframe:

Now, rather than calling all the code that we see within these functions, we can just get the information in one line, that is, query_population(). Other queries within the file access other parts of the database and return information we might want to ask about frequently:

Claims Reporting

If you looked carefully, you may have noticed an additional date field added since last month. In addition to the event date, synonymous with occurrence date, we now have the report date – that is, the date the claim is reported to the insurer. This distinction has important implications in actuarial science, since different types of policies exist that may only offer coverage on claims that either occur during or are reported during the period the policy is in effect.

For now, I have assumed that all policies are written on an occurrence basis, which means that a policy covers all claims that occur within the coverage period. This means that even if an insured reports a claim several years after is has occurred and can prove that its valid and occurred during the policy period, it is still covered under the terms of the policy.

I decided to add the claims reporting facility as a method in the broker class, which now resembles more of a place where transactions happen than an actual brokerage:

This function takes the report date as an argument, and then combs through both the event table from the environment and the policy table from each company to match the event to the appropriate company and policy, and then populate that company’s claim table.

The actual claims cycle is much more complex, involving adjusters, claimants, lawyers, agents, and other parties. These have been abstracted away for now, but I plan to add more detail later since claims modeling is itself an important part of an insurer’s strategy.

I’ve also made many changes to the other classes, but I wanted to highlight claims reporting since it’s a new feature. You can examine the other changes on GitHub.

Simulation

By encapsulating the queries, I’ve removed the number of arguments and lines of code needed to run the simulations. For example, we can now define an Insurer with just two arguments:

Whereas previously, it took five aguments:

The need to specify the connection, engine, and table was cumbersome and is now handled automatically by the Insurer class.

Finally, I tested out how the data flows from policy issuance to claims occurrence, and then to renewals, and MIES is still able to demonstrate the phenomenon of adverse selection:

Further Improvements

I’d like to get back to the subject of economics next week, so now I’m working on implementing the Slutsky Equation, which decomposes the motivations a consumer might have for changing their consumption as prices change.

Posted in: Actuarial, Mathematics, MIES

No. 138: MIES – Offer, Engel, and Personal Demand Curves

14 June, 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

Last week, I specified a Cobb Douglas utility curve for each person in MIES. I also demonstrated a situation in which a person might choose to not fully insure. However, I’ve gone a few chapters ahead in my readings and found out that under certain assumptions, a risk-averse person who is offered a fair premium will choose to fully insure. In MIES, since each company charges the pure premium without loading for profit or expenses, each person is getting a fair premium – so there’s something missing from my current model that makes it inconsistent with economic theory.

Risk aversion is not yet implemented in MIES, and will have to wait a few weeks before I get to it, since there’s quite a bit of work to do. But I’m mentioning the issue here, just in case someone reading this knows more about the subject than I do.

This week, I’m going to demonstrate a set of tools to examine consumer choice – the offer, Engel, and personal demand curves. I don’t recall using the first two curves very much in my economics courses, but the latter will be very important and will serve as a bridge between personal demand and market demand. Surprisingly, these curves were very quick to implement, since they all rely on the same method I wrote last week for the Cobb Douglas class.

The topic of this post roughly corresponds to chapter 6 of Varian.

Offer Curve

The offer curve for a consumer depicts their optimal consumption bundle at each level of income. Since the offer curve is unique to a particular consumer, I decided to define the methods that generate and plot the offer curve within the Person class. Luckily, the CobbDouglas class that I defined last week has a method called optimal_bundle, which returns the optimal consumption bundle given a set of prices and income. Since this is exactly what we need given the definition of the offer curve, we can simply use this method to generate each person’s offer curve:

Note that while I only have one utility curve defined in MIES at the moment (Cobb Douglas), the definition of the offer curve doesn’t need to have anything specific to the Cobb Douglas utility function. This means in the future, I should be able to abstract this method to accept other utility functions without too much modification.

I’ve also added a method to plot a person’s offer curve:

This method takes a preset range of income values, and uses the get_offer method to plot the optimal consumption bundle for each income value in the range. For example if we’ve already run a few iterations of a market simulation, we can examine what combinations of insurance and non-insurance a person can afford at different income levels. Let’s do this for the person with id=1:

Imagine what would happen if you were to shift the blue budget line inward and outward. The optimal consumption bundle would the the point of tangency with the corresponding utility function. We can see that the orange offer curve is the set of all these points.

Engel Curve

The Engel curve is similar to the offer curve, but plots the optimal choice of a good at various levels of income. Its definition within the Person class is also similar, except we only need to return the first good of the optimal bundle:

Let’s see what the Engel curve looks like for person 1:

Demand Curve

The demand function depicts how much of a good a person would buy if it were at a certain price. This one’s important since we’ll need it to derive industry demand, which will then be used to answer many fundamental questions about the insurance market. Like the other curves, defining this one was simple, we just get the optimal bundle at each price and return the quantity demanded of the first good:

Let’s see what the demand curve looks like for person 1:

Note that the demand curve slopes downward as it should, since we’d expect a person to buy more insurance the cheaper it is. However, note that there is no price such that the demand equals zero. The demand curve asymptotically approaches zero as the premium increases, but this particular person will never go uninsured. This is due the property of the Cobb Douglas utility function that the exponent of the good equals the percent of income spent on that good, which is hard coded as 10% at the moment. However, in the real world people do go uninsured, and this is a subject of great interest to me, so we’ll need to revisit this later.

Further Improvements

I’ve added quite a few features to the person class, but I haven’t integrated them to the point where I can perform more than two market simulations. I’m also several chapters ahead in my readings than what I’ve posted about, and I’ve encountered an interesting demonstration on risk aversion and intertemporal choice concerning assets, which will take quite an effort to both implement and reconcile with what I’ve written so far.

Posted in: Actuarial, Mathematics, MIES