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!)