Prepare Spreadsheet for Rate Upload
When a spreadsheet is the source for uploading rates, a very specific format must be used. All required metadata and rate table values must be provided in the spreadsheet and the file type must be XLS (*.xls). Multiple rate groups that have the same rate description can be entered using the same workbook.
The first worksheet in the workbook is called the Rate Legend. This is where all of the metadata is provided. The information in the legend worksheet and the individual rate sets will vary depending on the type of rate table that is uploaded: Aggregate, Select, or Ultimate. One worksheet is required to upload one rate set under a rate group.
Rate Legend
The legend worksheet contains the metadata of the rate table in the form of two columns. The data needs to be presented exactly as described below.
The Legend key will begin in cell A2, with the corresponding Legend values beginning in B2. The Legend key provides the user with the literal name of the expected value. The Legend worksheet should be structured as follows:
- A2 – "Group Name"
- B2 – The name of the rate group. This value provides the “RateDescription” for the rate table. There may be multiple rate sets with the same rate group name. If this is the case, the rate sets will be differentiated by the Rate Activation, Active From and Active To dates.
- A3 – "Table Type"
- B3 – “Select”, “ultimate” or “aggregate.” Some of the following legend information is dependent on the table type specified here.
- A4 – "Primary Index"
- B4 – The value here is the “IntegerCriteria” for the rate table.
- A5 – "Primary Index Orientation"
- B5 – "Column" or "row." This is the orientation of the integer criteria on the rate set worksheets.
- A6 – "Secondary Index"
- B6 – A value is required here for Select and Ultimate table types. The Secondary Index is one of the 10 optional criteria. It is used to determine when to stop loading.
- A7 – "Secondary Index Data Type"
- B7 – The value here is the data type of the secondary index mentioned above.
- A8 – "Select Period/Duration"
- B8 – A value is required here for the Ultimate table type. A value is required for the Select and Aggregate table types if a Maximum Age is not provided.
- A9 – "Maximum Age"
- B9 – A vaue is required here for the Ultimate table type. A value is required for the Select and Aggregate table types if a Select Period/Duration is not provided.
- A10 to A19 – Criteria 1 to Criteria 10.
- B10 to B19 – The criteria names for each of the criteria. These are not mandatory, but need to be in sync with the rate set worksheets. In the case of an existing rate description, the criteria cannot differ from the criteria in AsRateGroup table.
After the legend is complete, a worksheet must be created for each rate set. Refer to the Rate Set Worksheet page for additional information.
Sample Aggregate Rate Legend
Sample Select Rate Legend
Sample Ultimate Rate Legend
