Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Property - Casualty / General Insurance
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old 04-06-2020, 03:42 PM
ykpyoung ykpyoung is offline
CAS
 
Join Date: Apr 2020
Posts: 10
Default

Quote:
Originally Posted by ExamDanger View Post
I am also curious to developing an extension of exposures approach for all my policies as opposed to the current parallelogram method we use in SQL.

I only have R and SQL at my disposal to rerate the policies. I am curious for those of you working in larger personal lines insurance where the rating is standardized, what program do you use to rerate policies and how long does it take to run say 100,000 policies
I would like to know as well! What application do you guys use to re-rate policies (extension of exposures method)? SQL? R? Or even EXCEL?
Reply With Quote
  #12  
Old 05-06-2020, 06:02 PM
exekias exekias is offline
CAS
 
Join Date: May 2020
College: UC Berkeley
Posts: 11
Default

I used to work for a decently size personal auto insurer where we implemented extension of exposures in Python.

There are really two problems that need to be solved:

First you have to have a rate engine that is fast enough to handle the volume of data you are rating in a reasonable amount of time. I think our raters would rate about 10k policies per second. We did this in python but it could also be done in R. If I had to do it again I would probably use the data.table package in R. Excel or Access would not have worked.

Second, and this is probably the hardest part, you need to be able to collect the data for every version of every policy for the group of policies and time period that you are interested in. Luckily for us, our database was completely version controlled so we could pull the relevant rating facts as of each point in time that the policy premium was rated by the production system. This gave us the level of granularity that we needed to calculate the on level earned premium. All you have to do is pass the facts to the rate engine, get the written premium, then pro-rate it by the number of days that particular version of the policy was valid for to get the on-leveled earned premium.

We were able to validate the totals using a sort of parallelogram type method, but the nice part about extension of exposure was that it was accurate at a detailed level. For example you could look at on-leveled loss ratios by territory and any correlations between territory and other rating factors that had changed would be automatically taken into account. It was also much easier than making manual adjustments. The whole process could be done in about 30 minutes granted you didn't run into any bugs. Sometimes the data was dirty and required a little extra wrangling.
Reply With Quote
  #13  
Old 05-09-2020, 08:31 AM
Tacoactuary's Avatar
Tacoactuary Tacoactuary is offline
Member
CAS
 
Join Date: Nov 2014
Location: Des Moines, IA
College: Vanderbilt, UIUC
Favorite beer: Yazoo Sue
Posts: 1,596
Default

Depending on the line of business, coverage, and whether we are current levelling experience or just rerating point-in-time data, we have used massive sql jobs that take a few days to run; supplemental servers which can feed data from sql into a copy of our rating software; Access implementations of the rating engine; or, if its simple enough, R/excel.
__________________
ACAS 7 8 9 FCAS
Reply With Quote
  #14  
Old 07-13-2020, 10:17 PM
underactuary212 underactuary212 is offline
Member
CAS
 
Join Date: Dec 2017
Posts: 79
Default

Quote:
Originally Posted by exekias View Post
I used to work for a decently size personal auto insurer where we implemented extension of exposures in Python.

There are really two problems that need to be solved:

First you have to have a rate engine that is fast enough to handle the volume of data you are rating in a reasonable amount of time. I think our raters would rate about 10k policies per second. We did this in python but it could also be done in R. If I had to do it again I would probably use the data.table package in R. Excel or Access would not have worked.

Second, and this is probably the hardest part, you need to be able to collect the data for every version of every policy for the group of policies and time period that you are interested in. Luckily for us, our database was completely version controlled so we could pull the relevant rating facts as of each point in time that the policy premium was rated by the production system. This gave us the level of granularity that we needed to calculate the on level earned premium. All you have to do is pass the facts to the rate engine, get the written premium, then pro-rate it by the number of days that particular version of the policy was valid for to get the on-leveled earned premium.

We were able to validate the totals using a sort of parallelogram type method, but the nice part about extension of exposure was that it was accurate at a detailed level. For example you could look at on-leveled loss ratios by territory and any correlations between territory and other rating factors that had changed would be automatically taken into account. It was also much easier than making manual adjustments. The whole process could be done in about 30 minutes granted you didn't run into any bugs. Sometimes the data was dirty and required a little extra wrangling.

How did you handle historical policies that didn't have new rating variables implemented for more recent policies
Reply With Quote
  #15  
Old 07-14-2020, 08:47 AM
John S. Mill's Avatar
John S. Mill John S. Mill is offline
Member
CAS
 
Join Date: May 2020
Posts: 492
Default

Quote:
Originally Posted by underactuary212 View Post
How did you handle historical policies that didn't have new rating variables implemented for more recent policies
a couple ways to do this.

1. use mean/median(if skewed) as proxy for missing values

2. create a glm with those variables as response variables
Reply With Quote
  #16  
Old 07-14-2020, 09:00 AM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for CSPA
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 68,884
Default

In addition to JSM's suggestion . . .

Create an indicator variable to indicate "prior" policy vs. "newer" policy as a means to control for this absence (if needed).

Then for the "missing" rating variable, you simply include a level for these policies that is then set at the base level. (Note that you'll need to do something along these lines if you want to assess premium impacts between "before" and "after" the new rating variable was introduced; which might also be useful to see how best to approach the overall modeling.)


Overall, you might create a couple of different models using a combination of these options and compare results to see just how sensitive the models are to the "new" rating variable(s).
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 07:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.20919 seconds with 11 queries