Other Ways to Prepare Prophet Model Point Files? Try FoxPro!
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:
However, as Excel is not an ideal data manipulation tool (Unfortunately MS Access doesn't seem to be a good choice too!), you may find out the run time becomes longer & longer once the policy data are getting bigger - furthermore you would need to pray hard that the Excel won't hangup!
Understand MPF Structure
If you open a MPF using Notepad, you can easily find out that a MPF consists of 3 parts, namely header, contents & footer - as shown in the following sample MPF below:
"Contents" is the most important part of your MPF, which consists of the policy data that you would like to input into Prophet run (the "header" is the MPF description and "footer" consists of some codes that only Prophet understand). In case you would like to use other tools to create MPF (such as Excel or FoxPro), basically you only need to create the "contents" part.
How to Create MPF Using FoxPro?
Before you start creating MPF using FoxPro, I would suggest you perform all necessary matching and consolidated all/most required policy data fields in ONE master table (file format: DBF) - so that FoxPro only needs to refer to 1 table when generating MPF, as well as making your checking process easier when you need to find out how a particular value is produced.
To better manage your FoxPro program, you may setup a separate PROCEDURE in the FoxPro program for generating MPF.
STEP 1: Generate MPF Using FoxPro
In the MPF generation procedure of your FoxPro program, I would suggest you split the procedure into 2 parts: (1) define values for all necessary variables first, and followed by (2) writing policy data.
If you open the file generated by Foxpro, it will look like the following screen capture - without header & footer:
STEP 2: Open & Update MPF in Prophet
Open every MPF in Prophet - click "Import" button for "Open" dialog. If your MPF consists of any text fields (i.e. non-numeric except "!" field), you would need to select all records in a particular field and add "Quote" to the values (right-click, select "Quote" in the menu). Otherwise, no modification is necessary unless you would like to include a description in the MPF (Correct, basically you only need to open & save the MPF if you do not have any text fields).
STEP 3: Save MPF
Save MPF after you have made the necessary modifications in STEP 2. By saving the MPF, Prophet will automatically add the missing "header" & "footer" in the MPF and now the MPF are ready to be used for Prophet runs.
A Reminder...
Apart from FoxPro, you may use Excel to prepare MPF for Prophet runs as well. However, please always note that the policy data should be sorted by SPCODE (Sub Product Code), in ascending order - otherwise, your Prophet run will fail. I would suggest you sort the policy data: (1) first by SPCODE in ascending order; (2) followed by policy number in ascending order.
I would recommend that you always include policy number in your MPF and Individual model point result files (i.e. RPT files) - in case you require Prophet results by policy level in your analysis.
- 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
However, as Excel is not an ideal data manipulation tool (Unfortunately MS Access doesn't seem to be a good choice too!), you may find out the run time becomes longer & longer once the policy data are getting bigger - furthermore you would need to pray hard that the Excel won't hangup!
Understand MPF Structure
If you open a MPF using Notepad, you can easily find out that a MPF consists of 3 parts, namely header, contents & footer - as shown in the following sample MPF below:
- Consist of field names (variable names) and policy data.
- Delimited with comma (","), similar to CSV format you are familiar with.
- 1st field should always start with "MPF identifier" field, which the field name is defined as "!" (or "!1" as shown in the above figure) and the values are defined as "*".
"Contents" is the most important part of your MPF, which consists of the policy data that you would like to input into Prophet run (the "header" is the MPF description and "footer" consists of some codes that only Prophet understand). In case you would like to use other tools to create MPF (such as Excel or FoxPro), basically you only need to create the "contents" part.
How to Create MPF Using FoxPro?
Before you start creating MPF using FoxPro, I would suggest you perform all necessary matching and consolidated all/most required policy data fields in ONE master table (file format: DBF) - so that FoxPro only needs to refer to 1 table when generating MPF, as well as making your checking process easier when you need to find out how a particular value is produced.
To better manage your FoxPro program, you may setup a separate PROCEDURE in the FoxPro program for generating MPF.
STEP 1: Generate MPF Using FoxPro
In the MPF generation procedure of your FoxPro program, I would suggest you split the procedure into 2 parts: (1) define values for all necessary variables first, and followed by (2) writing policy data.
- Define values for Prophet variables that you need to include in MPF, especially those fields containing values deriving from multiple fields.
- By using TEXTMERGE method, write the required field names to a text file. Please remember to include a comma (",") between 2 fields.
- Similarly, write the values into the target text file using TEXTMERGE method. In case you would like to convert a numerical value to text value, please remember to set the length & no. of decimal places for the value and remove redundant spaces, such as ALLTRIM(STR(ANN_PREM,15,2)). You need to ensure the string length should cater for all numerical fields that require conversion - use ONE max length will do, otherwise you will be confused by having many different max length.
If you open the file generated by Foxpro, it will look like the following screen capture - without header & footer:
Open every MPF in Prophet - click "Import" button for "Open" dialog. If your MPF consists of any text fields (i.e. non-numeric except "!" field), you would need to select all records in a particular field and add "Quote" to the values (right-click, select "Quote" in the menu). Otherwise, no modification is necessary unless you would like to include a description in the MPF (Correct, basically you only need to open & save the MPF if you do not have any text fields).
STEP 3: Save MPF
Save MPF after you have made the necessary modifications in STEP 2. By saving the MPF, Prophet will automatically add the missing "header" & "footer" in the MPF and now the MPF are ready to be used for Prophet runs.
A Reminder...
Apart from FoxPro, you may use Excel to prepare MPF for Prophet runs as well. However, please always note that the policy data should be sorted by SPCODE (Sub Product Code), in ascending order - otherwise, your Prophet run will fail. I would suggest you sort the policy data: (1) first by SPCODE in ascending order; (2) followed by policy number in ascending order.
I would recommend that you always include policy number in your MPF and Individual model point result files (i.e. RPT files) - in case you require Prophet results by policy level in your analysis.
Why don't you use DCS instead of Fox PRO to manipulate the data if you have only one master table? I think that data administrative system such as Access, Fox Bro's power is manipulate in many table. In my company, currently, we use Access to manipulate data and DCS to convert into model point format.However, data is so big that Access is very slow and even can not run sometime.
ReplyDeleteInstead of using 2 different tools in policy data manipulation, I would think it is better to use only a single tool for the entire process - since the master table is compiled using FoxPro (although you can use FoxPro table directly as DCS input files). So that you have less programs to maintain and manage.
ReplyDeleteI'm afraid that Access is not a good choice in policy data manipulation - normally I will advise my clients to use either DCS or FoxPro, and not to consider Access. Perhaps you would like to consider using only DCS (discard Access) for the entire policy data manipulation process.
Actually, if you only process in one master table, we could use only DCS to produce model point for Prophet running. Unfortunately, sometimes, we need group by data, store in temp table bla bla...Access is very flexible for these kinds of task while DCS is very hard to do.
DeleteIf there is only ONE input file downloaded from the policy admin system and no extensive manipulation needed (such as various types of matching), yes it is good to use DCS directly. The example I mentioned in the article refers to those require extensive data manipulation. Simple and straightforward policy data should use DCS directly.
DeleteBoth Access and FoxPro use SQL as the programming language - although there are some differences in syntax. FoxPro is a more powerful data manipulation tool, which there was one time I use it to handle a data file with 10 millions records. Yes I agree with you that there are limitations in DCS in term if data manipulation.