Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 06-29-2018, 09:16 AM
mattcarp mattcarp is offline
Member
CAS
 
Join Date: Sep 2016
Studying for Exam 5
College: UC Berkeley
Posts: 298
Default Parallelogram method in SQL or VBA?

I'm working on automating our premium on-leveling. I'm trying to decide if I want to do everything behind the scenes in SQL or pull the raw data into Excel and then use a VBA macro.

Thoughts?
__________________
P FM VEE MFE C S OC1 5 OC2 6 7 8 9
Reply With Quote
  #2  
Old 06-29-2018, 09:18 AM
PeppermintPatty's Avatar
PeppermintPatty PeppermintPatty is offline
Member
CAS
 
Join Date: Sep 2001
Posts: 37,060
Default

Well, I hate VBA, so I'd say either SQL or just do it with formulas. It's not a large calculation.
Reply With Quote
  #3  
Old 06-29-2018, 11:18 AM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 91,499
Default

How are you doing this?

1. By summing all past premiums at a premium-level definition and applying all rate increases after their date?

2. Or by taking all past policyholders and applying the current premiums to their past enrollment dates?
If your company has various discount options, for #2 you'd need every single variation of premium and then know which one applies to each policyholder at each time period.

I usually do #1, and in Excel, since I do it infrequently, and for a lot of data. No VBA required, just wait for it to finish recalculating, then Paste-Value the formulas over themselves for 20000 lines (leaving one formula line for the next time).

I used to do #2, but for very simple products with no discounts, in SQL. Had all (current and historical) premium rates loaded to the SQL Server, then summarized all past premiums by multiplying the past's restated exposure to current premium rates.
__________________
DTNF's Basic Philosophy Regarding Posting: There's no emoticon for what I'm feeling! -- Jeff Albertson (CBG)
DTNF's Trademarked Standard Career Advice: "pass some exams and get back to us."
DTNF's Major advice: "Doesn't matter. Choose major that helps you with goal of Career Advice."
DTNF's Résumé Advice: Have a good and interesting answer to every item on it for the interviews.
DTNF's Law of Job Offers: You not only have to qualify for the position, but you also have to be the best candidate available for the offer.
DTNF's Work Philosophy: I am actuary. Please insert data. -- Actuary Actuarying Rodriguez.
Twitches' Advice to Crazy Women: Please just go buy your 30 cats already.

Last edited by Dr T Non-Fan; 06-29-2018 at 11:22 AM..
Reply With Quote
  #4  
Old 06-29-2018, 02:00 PM
mattcarp mattcarp is offline
Member
CAS
 
Join Date: Sep 2016
Studying for Exam 5
College: UC Berkeley
Posts: 298
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
How are you doing this?

1. By summing all past premiums at a premium-level definition and applying all rate increases after their date?

2. Or by taking all past policyholders and applying the current premiums to their past enrollment dates?
If your company has various discount options, for #2 you'd need every single variation of premium and then know which one applies to each policyholder at each time period.

I usually do #1, and in Excel, since I do it infrequently, and for a lot of data. No VBA required, just wait for it to finish recalculating, then Paste-Value the formulas over themselves for 20000 lines (leaving one formula line for the next time).

I used to do #2, but for very simple products with no discounts, in SQL. Had all (current and historical) premium rates loaded to the SQL Server, then summarized all past premiums by multiplying the past's restated exposure to current premium rates.
1 is parallelogram method and 2 is extension of exposures, yes? I want to do 1.

The way we do it now, we have an excel file with all past rate changes. We have rate changes by product, by territory, and by peril.

Currently we have to locate the rate changes with effective dates in the last 6 years (bc on-leveling 5 years of premium), then pasting those rate changes into the on-leveling excel file. Then I have to calculate the weights to apply to each calendar year of earned premium. This step is partially automated, but needs to be adjusted and is usually the longest step. Once that is done I need to manually adjust a few other formulas because I likely added or deleted a few columns in the last step.

Finally, I need to update the footnotes with the correct filing numbers of the rate changes that were used in the first step.

I believe I can automate most of this. Just wondering if it would be best to have excel pull the data, then apply a macro to calculate the weights and put everything in it's proper place, or just write a sql procedure to do everything so all I need to do is pull the on-leveled premium.
__________________
P FM VEE MFE C S OC1 5 OC2 6 7 8 9
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 11:46 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, 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.22493 seconds with 9 queries