FoxPro for Actuarial (1)

Recently when I helped my client to set up process to compile movement of certificate statistics (MOP) (i.e. new business, in force and termination/alteration), I advised them that Excel is not an ideal tool to do such actuarial exercise which requires a lot of data manipulation. "Then what is the better tool?" They asked me. Without any hesitation, I answered: "FoxPro".

Why do I recommend FoxPro as a good tool to be used for actuarial exercises that require a lot of data manipulation & matching (such as MOP reporting, experience studies and reinsurance administration)? What are Excel, MS Access or Data Conversion System (DCS) (a supplementary application comes together with Prophet) not considered good tools for actuarial exercise? Among the reasons are:

  • Ability to do data matching / joining - as FoxPro uses SQL (Structured Query Language) as the primary programming language, we can combine the selected fields from 2 different tables/data easily based on multiple matching criteria, by using "SELECT" and "JOIN" command (the ones I used the most are "LEFT OUTER JOIN" and "FULL JOIN").

    Doing such data matching / joining in Excel or DCS? You will definitely think that I must be kidding - it is pretty tedious to use "LOOKUP" function to do matching, especially if we need to refer to multiple fields / part of a field / combination of fields as matching criteria. Furthermore, our Excel / DCS will extremely "tired" to look up values if we have a huge set of data...
      
  • Highly automated process - We can set up highly automated process using coding the required steps in a "program" - from converting the raw data to FoxPro tables, perform necessary calculations and data manipulations to produce summaries for the intended results (such as in text file or Excel formats).

    Although Excel is able to summarize data like FoxPro by using Pivot Table function, it will be a bit complicated to set up coding for automated Pivot Table using VB in Excel (I just helped my client to set up one - I admit the relevant VB coding makes me a bit headache). FoxPro just needs a few lines of commands to do the required summaries.
      
  • Ability to handle huge volume of data - I still remember that when I used to use FoxPro to do matching for 2 tables with multiple millions records each. Sometimes I did felt sorry to my computer for "torture" it with sure heavy tasks... :p
      
  • Easy to debug - Although MS Access uses SQL as well, you may find out it may not be easy to debug. I still remember I used to help my staff to debug the MS Access template - which he totally had no idea where went wrong. The error message didn't give adequate information!
      
  • Inexpensive - Comparing to other actuarial applications (such as Prophet), FoxPro is considered cheap (just a few thousands riggit) - especially for a newly set up company with limited budget. By having FoxPro, you can even set up models to calculate reserves.
I will discuss my experience in learning FoxPro and the FoxPro training I provided to my staffs (with proper syllabus and training materials. Got exams too!)

Comments

Post a Comment

Popular posts from this blog

Other Ways to Prepare Prophet Model Point Files? Try FoxPro!

Image
If you are a frequent Prophet user, you must be definitely familiar with "Model Point Files" ("MPF"), i.e. the policy data that you compile in a specific format that Prophet can recognize as inputs for Prophet runs. If my guess is right, most probably you use Data Conversion System ("DCS") to convert the source data you download from your policy administration system (may be in various format, e.g. fixed ASCII / fixed width, comma delimited, tab delimited, etc.) into the required MPF, based on the definitions you specified in your DCS programs. In case your source data are divided into various files, you may need to carry out more steps to prepare MPF, for example: Open different source files in Excel Combine required fields from different source files by using commands (such as) VLOOKUP, SUMIF & MATCH. You may have automated this process by using VBA. Convert the required files into text files Use DCS to convert those text files into MPF  How
Read more

How Should I Do My UAT? (1): Program / Spreadsheet / Model

Image
If you are working in the actuarial department of an insurance company, it may not be uncommon to you involving in various testing exercises, which are known as User Acceptance Testing ("UAT") in general. Some of us may think that UAT is only applicable to the policy administration system implementation project (Oops... Do I remind on the non-stop follow-ups from your Project Manager?), but in fact UAT is also needed for the programs, spreadsheet templates or actuarial model we use to perform various actuarial studies - whether they are developed internally or by external consultant. It is particularly important for those tools which we are going to use to perform regular studies. In my view, there is no one right answer for the approach to be used in performing UAT on program / spreadsheet / model - most importantly, the approach that you adopt should allow the required testing to be carried out in a structured manner, and it should not create too much "processes"
Read more

How do You Setup Indicators in Your Prophet Model?

Image
In my previous post, I have discussed areas that you may consider in setting up input variables . Before I proceed my discussion on core variables, I would think that it is good for use to take a look on indicators - as indicators play an important role in setting core variables with various formulas (known as "variable definitions" in a Prophet model).  The funny thing about indicators is: it DOES NOT carry any formulas that you need to use in calculating a variable! Basically, they are just some texts that you can combine using different logical operators (AND, OR, NOT), such as "ORD_PAR AND SING_PREM" for a single premium ordinary participating product. 
Read more