- 1 Introduction
- 2 Basic Concepts/prerequisites: Cases and Answers, rating files and lookups
- 3 Tools: Before you start
- 4 Inside a SchemeServe rating file
- 5 Flat Rating: Building your first Rating File
- 6 Modifiers: what they are and when to use them
- 7 Multi-step rating: using multiple sections
- 8 Improved rating with Lookups
- 9 Advanced rating: introducing formulae
- 10 Advanced rating: One file per risk-group
- 11 Master rating: using tables and row lookup loops
- 12 Examples for specific insurance types
- 13 Appendix A: Conditions
- 14 Appendix B: Actions
- 15 Appendix C: Maths helper functions
- 16 Appendix D: Tips & Tricks
At it's simplest, editing rates with SchemeServe means simply entering rates for calculation questions you create in Admin > Schemes. For more information on how to do this see Editing Questions/Proposals.
However, this article covers more advanced rating structures using the Conditional Matrix.
NOTE: ADM Network offer a paid service to create your rating files and schemes for you. If you would prefer to have full assistance in this area, please contact support (http://support.admnetwork.com) at and ask about our Managed Schemes service.
Basic Concepts/prerequisites: Cases and Answers, rating files and lookups
It is recommended before you start Conditional Rating with SchemeServe that you have a full grasp of the concepts covered in the Questions Editor. See Editing Questions/Proposals for more information. You should have a strong grasp of the concepts of Cases, Answers, Discounts & Loads before you begin.
In order to control rates and discounts, you will need to know the System IDs of the questions for which you intend to modify rates, cover values or discounts.
Rated questions must be set up as category "Calculation" so that rates can be applied - doing so also makes them appear on the Matrix page. If the item is also a Sum Insured and not an Indemnity Limit the 'This is Coverage box' should also be ticked.
We also recommend that when using Conditional Rating the Rate is left blank in the Question Editor; it will instead be set in your Conditional Rating file so setting it also in the Question Editor may lead to confusion later.
Tools: Before you start
To build SchemeServe rating files you may need to use a combination of tools. Below is a list of our recommendations:
- OpenOffice (Windows, Mac, Linux: http://www.openoffice.org/download) - The mainstay and most trusty companion for building rating files, we recommend OpenOffice as your primary tool. For our purposes, it produces more predictable results when opening and editing CSV files than Microsoft Excel, so is strongly recommended if you have the choice.
- GS-Calc (Windows: http://citadel5.com/gs-calc.htm) - GS-Calc is a paid program (US$19.95 at time of writing) but there is a free trial available. There are very few, if any other, spreadsheet programs that can handle more than a million rows (many don't scale as far as a million). This is not such a problem for rating but will cause users big problems if they need to edit lookups files (especially UK postcode lookups which often have 1.7m rows). Editing with a text editor is unlikely to help either. GS-Calc is a great solution.
- Notepad++ (Windows: http://notepad-plus-plus.org/download) - Notepad++ is a great alternative to Notepad on Windows. At times, you will need to open your CSV files with Notepad++ (or Textmate below) in order to debug problems. Spreadsheet programs like OpenOffice won't give you full visibility of how data in CSV files is actually being saved, whereas opening with a text editor will. If you suspect that a column is not being 'delimited' correctly with quotes around it, opening it with Notepad++ will make the real situation immediately apparent to you.
- Textmate (Mac: http://macromates.com/) - Recommended text editor for Mac.
Inside a SchemeServe rating file
You can upload new rate files, download or overwrite existing in Admin > Rates & Fees.
Your rating matrix files are created as standard 'comma separated value' files (.csv) and then imported into the SchemeServe database. CSV files can be opened with Microsoft Excel or OpenOffice (http://www.openoffice.org). We recommend however that you use a text editor like Notepad++ (see http://notepad-plus-plus.org/download) to edit your rating CSV files. It may be a little harder at first but will give you greater control and lead to more predictable results.
You will require a rating file for each insurer on each scheme combination. These may be identical if the same rating is applied for all insurers in which case the same file can be imported to each insurer. If they vary either slightly or significantly its normally easiest to take a copy of one and then make the necessary alterations to that rather than creating a whole new import file.
- To import a rating file that you have created click on the main Admin Tab and then Rates and Fees.
- Select the Scheme and Insurer and then Browse for the file that you want to import. Once this has been located click on the Upload button to import the file.
- You can download the current rating file SchemeServe holds for an insurer/scheme combination by clicking on the CSV/Excel icon (see screenshot above).
- If you want to make alterations to the Matrix update the .csv file that is downloaded, Save the changes and then Upload the new version as detailed above; the new rating will be available immediately.
Flat Rating: Building your first Rating File
Typically, your rating CSV files will have two types of column: Condition columns and Action columns. Conditions make up the leftmost columns in your file and define under what conditions you want to perform a specific action, e.g. to change a rate. Actions are found in the rightmost columns of your file, and define what you want to happen when the conditions are met.
- Tip: you can also leave out the condition columns, in which case any action columns will always be run.
To create a condition or an action, you must first know the System ID of the question which will affect your rating. To find this out, you can use the Question Editor, and Edit the question you are interested in (see Editing Questions/Proposals).
Once you know the System ID of the question, create a new column in your CSV file - at the start (i.e. column 1)! The column name should be set to "Answer:MyQuestion" where "MyQuestion" is the System ID of the question you would like to conditionally rate upon. In the subsequent rows for that column, enter all the possible answers for that question you would like to anticipate, and in the rows for the other columns in your file, enter the corresponding rates and fees. When you are done, upload your new rating file with the Rates & Fees Editor.
Modifiers: what they are and when to use them
For a complete list of modifiers that can be used in your rating files, see here.
Multi-step rating: using multiple sections
Very often you will want to build multiple operations into your rating file, e.g. you may like to set the PLLimit for one question and the ELLimit for another --both determined based on different Conditions.
In this case, you will likely need to create two tables and separate them with a blank line followed by three hash characters, i.e. ###.
In the above example, we are using two tables to determine first the PLLimit based on PL cover required and then ELLimit based on EL cover required.
Improved rating with Lookups
Advanced rating: introducing formulae
You can also use formula in your rating files, e.g.:
Yes,1000-999999,[Q4_Cover] * 0.2
To calculate the number of days between 2 dates ;
Conditional statements and functions
Please find below a list of conditional expressions that can be used in your rating files.
- MAX([Q1_Cover], [Q2_Cover]) This expression is used to take the maximum value from alternate expressions
- MIN([Q1_Cover], [Q2_Cover]) This expression is used to take the minimum value from alternate expressions
- SQRT(Q1_Cover]/2 * [Q2_Cover]^2) This expression provides the square root of the values in the statement
- CEILING([Q1_Rate]*0.123) – this expression rounds up the value of the statement
- FLOOR([Q1_Rate]*0.123) – this expression rounds down the value of the statement
- ROUND([Q1_Rate]*0.123) - this expression rounds the figure to the nearest number specified
IF(conditional_expression, value_if_true, value_if_false)
IF([Q1_Cover]+[Q2_Cover] >= 1000 AND [Q3_Value] = 'A little bit more', [Q4_Cover] * 0.2, [Q4_Cover])
In the main, the conditional stuff is easier to read if the old syntax is used.
Yes,1000-999999,[Q4_Cover] * 0.2
It's main use is when used in conjunction within a table rating loop
There are a number of ways to do this, though we recommend the use of the CEILING function. You can use CEILING() to always round up, you just need to get the decimal point in the right place!
101 round up to 200 = CEILING(101/100.0) * 100 = CEILING(1.01) * 100
You can also use it for rounding up thousands etc.
FLOOR() works in the same way for always rounding down.
You could alternatively use Math.Round(number) for this though there would be a caveat, since Math.Round(1.5) and Math.Round(2.5) both round to 2 (3.5 would round up to 4 and that 4.5 would round down to 4). This is because the default behaviour in .admScript is for this to use MidpointRounding.ToEven.
Most people are not familiar with "rounding to even" as the alternative, "rounding away from zero" is more commonly taught in school. .admScript defaults to "Rounding to even" as it is statistically superior because it doesn't share the tendency of "rounding away from zero" to round up slightly more often than it rounds down (assuming the numbers being rounded tend to be positive.)
You can improve it by doing Math.Round(number, MidpoindRounding.AwayFromZero) ??
The only way to really get the value to round properly (financially) is multiply, truncate and then divide (i.e. for 2 decimal places n = int(v * 100 +.5) / 100).
Advanced rating: One file per risk-group
Rating your main risk group before add-ons
Master rating: using tables and row lookup loops
- You must build a table into one of your question sets. Adding Tables to Your Questions
- You must have at least one Row Charge in your table. Table Row Charges
Tables are often used to rate a potentially unknown number of items, such as when the user inputting the quote is entering a list of insurables.
An example would be say, specific areas of surgery a surgeon operates within or types of work a builder wants cover for.
In the below example we will assume:
- You have a Table called "CoveredAreas", with two columns "AreaOfWork" and "PercentageOfTurnover"; and a charge called "AreaCharge".
- A question called "Turnover"
- A question called "Postcode"
- A File Lookup called "Postcode Banding" with Columns: "Postcode" and "RatingArea"
- A File Lookup called "Rating File Lookup" with
- "Area" being the areas you can cover
- "StdRate" being the rate to charge for that area of work and
- "HighRate" being an increased rate based on the result of the "Postcode Banding" Lookup.
You need to rate each area of work based on the Percentage Of Turnover it accounts for.
COL 1: Get the amount of turnover that the particular PercentageOfTurnover equates to.
COL 2: Get the Postcode Lookup Result
COL 3: If the result from the Postcode Lookup is "High" use the "HighRate" column from "Rating File Lookup", otherwise use the "StdRate" column.
To do this we'll use the function SetRateFromLookup with our table. When using tables, the systax of SetRateFromLookup is as follows:
ROW1: SetRateFromLookup:TABLESystemId_COLUMNSystemId_TODOLOOKUPWITH|Lookup File Name ROW2: TABLESystemId_CHARGERowSystemId|COLUMNName_INLOOKUP
NOTE: Notice how all Column SystemIds and Row Charge SystemIds must be prefixed with the SystemId of the table they belong to (followed by an underscore, i.e. TableId_ColumnOrChargeId.
So our file needs to look like this:
|COL 1||COL 2||COL 3|
|1.||SetCover:CoveredAreas_AreaCharge||AnswerLookup:Postcode|Postcode Banding||SetRateFromLookup:CoveredAreas_AreaOfWork|Rating File Lookup|
|2.||[Turnover]*[CoveredAreas_PercentageOfTurnover] / 100||Normal||CoveredAreas_AreaCharge|StdRate|
|3.||[Turnover]*[CoveredAreas_PercentageOfTurnover] / 100||High||CoveredAreas_AreaCharge|HighRate|
Rating based on the highest rate from a Table Question
If you're underwriters are feeling a little frugal you can always use the highest rate from the given areas:
To keep it broken down by AreaOfWork, use Example 3 above and just replace "SetRateFromLookup:" with "SetHighestRateFromLookup:"
That way you will have an individual charge for each area.
If you just want to set the rate on the Turnover question then you can omit the PercentageOfTurnover column if you wish and:
|1.||AnswerLookup:Postcode|Postcode Banding||SetHighestRateFromLookup:CoveredAreas_AreaOfWork|Rating File Lookup|
Examples for specific insurance types
Example 1: Life Rating
Tip: You can enter multiple columns for each combination of question-answers you would like to rate on, so for example, on a life insurance scheme your first column might be "Answer:SmokerYesNo" and your second column "Answer:Age". This would allow you to create higher rates for smokers above a certain age.
In the above case the conditional rating file could look something like:
Example 2: Public Liability
In another example you might want to determine a Public Liability Indemnity Limit based on the level of cover required and you want to set different premiums for each, £2,000,000 limit £250 and £5,000,000 limit £500. In this case, the entry in the matrix would be structured as below;
Appendix A: Conditions
Below is a list of all the available conditions for use in your rating files:
|Answer||Answer:QuestionName||Answer to a Question in the Question set|
|Cover||Cover:QuestionName||As 'Answer' but for Calculation Questions|
|Rate||Rate:QuestionName||Current Rate for the Question|
|Result||Result:QuestionName||Current Result for the Question - Cover x Rate / 100|
|Formula||Formula:arithmetic with QuestionName placeholders||Enables detailed formulas to be written with standard spreadsheet functions e.g "Formula:[StockSI] + Contents + [SpecificContents] / 100". The returned value could then be checked in a table to set a Rate|
|RecordType||RecordType (no other parameters)||Determines record types First Premium, Adjustment or Renewal|
|CLR||CLR:YearsToGoBack||Calculate the Claims Loss Ratio for specified number previous years|
|YearDiff||YearDiff:QuestionA|QuestionB||Calculate the number of whole years between Date QuestionA and date QuestionB. If QuestionB is omitted then the difference calculated will be between QuestionA and today's date.|
|MonthDiff||MonthDiff:QuestionA|QuestionB||Calculate the number of whole months between Date QuestionA and date QuestionB. If QuestionB is omitted then the difference calculated will be between QuestionA and today's date.|
|DayDiff||DayDiff:QuestionA|QuestionB||Calculate the number of whole days between Date QuestionA and date QuestionB. If QuestionB is omitted then the difference calculated will be between QuestionA and today's date.|
|BrokerPostcodeArea||BrokerPostcodeArea:File Lookup Name||Check the Broker Post Code in a specified File Look Up and return the Look Up value|
|ClientPostcodeArea||ClientPostcodeArea:File Lookup Name||Check the first 4 characters of the Post Code in a specified File Look Up and return the Look Up value|
|ClientPostcodeFull||ClientPostcodeArea:File Lookup Name||Check the full Post Code in a specified File Look Up and return the Look Up value|
|AnswerPostcodeArea||AnswerPostcodeArea:QuestionName|Lookup Name||Check the Answer Post Code from a Question in a specified File Look Up and return the Look Up Value|
|AnswerLookup||AnswerLookup:QuestionName|Lookup Name||Check the Answer from a Question in a specified File Look Up and return the Look Up Value|
|TableRowCount||TableRowCount:QuestionName||Adds up the number of rows in the table question|
|TableRowLookup||TableRowLookup:QuestionName|Column Index|Lookup Name||For all values in a specified table column check the specified File Look Up and return the Look Up Value|
Appendix B: Actions
Below is a list of all the available actions for use in your rating files:
|SetFee||SetFee:QuestionName DATA=Amount||Set a fixed charge on the specified Calculation question|
|SetRate||Setrate:QuestionName||Set a Rate on the specified Calculation Question|
|SetCover||SetCover:QuestionName DATA=Amount||Set the cover on the specified Calculation Question|
|SetFeeFromLookup||SetFeeFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set a fee from a value in a Lookup|
|SetResultFromLookup||SetResultFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set a result from a value in a Lookup|
|SetRateFromLookup||SetRateFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set a rate from a value in a Lookup|
|SetHighestRateFromLookup||SetHighestRateFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set all rates to the highest rate from all returned row values in a Lookup|
|SetLowestRateFromLookup||SetLowestRateFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set all rates to the lowest rate from all returned row values in a Lookup|
|SetCoverFromLookup||SetCoverFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set cover from a value in a Lookup|
|SetHighestCoverFromLookup||SetHighestRateFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set all rates to the highest cover from all returned row values in a Lookup|
|SetLowestCoverFromLookup||SetLowestRateFromLookup:QuestionToUseInLookup|LookupName DATA=Question ToSet|ColumnName||Set all rates to the lowest rate cover all returned row values in a Lookup|
|IncludeEndorsement||IncludeEndorsement DATA=CSV Identifier||Include the specified Endorsement(s) from the Endorsement Library|
|IncludeEndorsementsFromLookup:||IncludeEndorsementsFromLookup:QuestionToUseInLookup|LookupName DATA=ColumnName||Add Endorsement(s) from a look up|
|RemoveEndorsement||RemoveEndorsement DATA=CSV Identifier||Remove the specified Endorsement(s)|
|RemoveAllEndorsements||RemoveAllEndorsementsDATA=All||Remove all Endorsement(s)|
|SetExcess||SetExcess:ClaimTypeName DATA = Amount||Set a Claim excess for the specified amount|
|SetMinNet||SetMinNet DATA = Amount||Set the minimum overall Net Premium|
|SetRiskGroupMinNet||SetRiskGroupMinNet:RISKGROUP_ID DATA= Amount||Set the minimum Net Premium for the specified Risk Group|
NB. For SetMinNet you need to set up a load under Main called AutoLoad_Total. For SetRiskGroupMinNet you need to set up a load under the specific risk group called AutoLoad
Appendix C: Maths helper functions
Below is a list of quick mathematical functions which can be used in your rating files. They can be used anywhere: within data cells, column headers and even right within other functions like IF.
|cos()||Takes a single parameter of a radius angle and returns the "cos" of it.|
|sin()||Takes a single parameter of a radius angle and returns the "sin" of it.|
|tan()||Takes a single parameter of a radius angle and returns the "tan" of it.|
|cosh()||Takes a single parameter of a radius angle and returns the "cosh" of it.|
|sinh()||Takes a single parameter of a radius angle and returns the "sinh" of it.|
|tanh()||Takes a single parameter of a radius angle and returns the "tanh" of it.|
|ln()||Takes a single parameter of a number and returns the "ln" of it, which is the logarithm of base "e".|
|log()||Takes a single parameter of a number and returns the "log" of it, which is the logarithm of base "10".|
|logn()||Takes two parameters; the first is a number that we want to compute the logarithm of; the second parameter is the base of the logarithm.|
|sqrt()||Takes a single parameter number and returns its square root.|
|abs()||Takes a single parameter number and returns its absolute value.|
|acos()||Takes a single parameter number and returns its "arc cos".|
|asin()||Takes a single parameter number and returns its "arc sin".|
|atan()||Takes a single parameter number and returns its "arc tan".|
|exp()||Takes a single parameter number and returns its exponent.|
Appendix D: Tips & Tricks
Using the Agent reference to modify rates/fees or commissions
It's usually better to use Commission Chains for agent based commissions rather than putting them in your rating file but you may sometimes like to rate based on Agent. You can do this...
...in a formula:
### SetCover:AgentRefQuestion [Agent.AccountRef] ### SetRateFromLookup:AgentRefQuestion|LookupTable RatedCoverQuestion|RateColumn
...OR as a condition:
### AccountRef, SetRate:RatedCoverQuestion MOB1,0.1234