Your rating file is where all your calculations live and will use answers from your question set to drive your rating file to populate your matrix tab. You can upload new rating files, download or overwrite existing ones in ‘Admin‘, and ‘Rates & Fees’.
The basics | |||
Step 1 |
Step 2 |
Step 3 |
Step 4 |
| Grab an empty Excel sheet. (.CSV) | Calculate your base premium/fee/rate | Add your loads and discounts | Add you commission, policy fee and IPT & upload |
Overview:
Your rating files are created as standard ‘Comma Separated Value’ files (.csv) and then imported into the SchemeServe database. This area of SchemeServe is slightly more complex than others so it´s worth taking the time to read through our guides.
.png)
Rating files interact with our question set with the goal of populating our matrix tab. Watch our demo below on the matrix tab to understand how the rating file operates.
Uploading a Rating File
- To import a rating file, go to ‘Admin’ tab and then ‘Rates and Fees’.
- Select the scheme and insurer, and then ‘Browse‘ for the file that you want to import. 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 most recently uploaded rating file.
- If you want to make alterations to the Matrix calculations, update the CSV file that is downloaded, save the changes and then upload the new version as detailed above.
- You can have separate rating files for every record type or you can upload the same rating file to all three sections in the ‘Rates and Fees’ area.
Watch our quick overview here:
There are 3 main things to consider when building a rating file:
1. What actions do you need to happen?
2. What data are you trying to use?
3. What formula is needed to allow you to do all your calculations?
Actions - you will use these in each columns header row:
| Answer: | Conditional column - if the answer to this question is true, then move to the next column e.g. Answer:Destination |
| SetFee: | Set the amount to a calculation question e.g. SetFee:BasePremium |
| SetRate: | Set the rate to an amount e.g. SetRate:MP_BuildingSICalc |
| SetCover: | Populate a question with a number e.g. SetCover:MP_BuildingSICalc |
| SetValue: | Populate a question with a value e.g. SetValue:CompanyName |
Suffixes: Add these to the end of system IDs.
| _Cover | Numeric/Money - e.g. the amount entered in a Calculation question |
| _Value | Text - e.g. the answer Yes/No |
| _Fee | Numeric/Money - e.g. the Fee you've set some rows above in your rating file |
| _Result |
Numeric/Money - e.g. the result of your total Premium 'TotalPremium_Result' i.e the obtained result of a % applied to £XX Sum Insured |
| _DateValue | Date |
| _CoverBefore | Pull through the previous record´s answer on a calculation question |
| _Rate | Numeric/Money - e.g. the Rate you've set some rows above in your rating file |
Formula: Examples of different formula that can be used in rating with the most commonly used highlighted.
| Action | Description |
|
SetAgentId IF([Agent.AccountId].ToString()='ACCOUNT_ID_35471',35463,[Agent.AccountId]) |
Change agent. If you cannot find the Agent ID, in Formula testing enter [Agent.AccountId] to find it |
|
[ContentsDescription_ContentsItemValue_Row#0_Cover] - 1st row [ContentsDescription_ContentsItemValue_Row#1_Cover] - 2nd row [ContentsDescription_ContentsItemValue_Row#2_Cover] - 3rd row |
Return value from specific table row |
|
HeaderBusinessEquipment HeaderBEAtPremisesRate HeaderBEAwayFromPremisesRate OrderBy Business Equipment 0.005 0.006 1 Computers, Laptops 0.0075 0.015 2 Diesel Tank 0.0075 0.015 3 Other 0.005 0.006 4 |
Customise the order of a file lookup. By default, file lookup will arrange alphabetically but this will enable you to change the order. Adding OrderBy as the last column will ensure it does not clash with rates |
| IF([Claims_Table_DateofLoss_DateValue] > NOW.AddYears(-5) AND [Claims_Table_TypeofLoss_Value]="Employer\'s",1,0) | For values that contain apostrophe, add \ |
|
Answer:MP_SystemID Null |
To check if question is blank |
|
Answer:MP_HiddenInterestedPartiesLock Null Answer:MP_HiddenInterestedPartiesLock checked SetCover:MP_HiddenInterestedPartiesLock Null SetCover:MP_HiddenInterestedPartiesLock checked |
To check if Tickbox is ticked or unticked |
|
SetCover:PostcodeNoSpace [PHHomeAddress_Postcode_Value].Replace(' ','') SetCover:PostcodeMinusTwo [PostcodeNoSpace_Value].ToUpper().Substring(0,[PostcodeNoSpace_Value].Length-2) SetCover:PostcodeRated [PostcodeMinusTwo_Value].Insert([PostcodeMinusTwo_Value].Length-1, " ") |
Insert space into postcode to get AB1 2 or AB12 2 Create first question (text box) with formula to grab postcode and remove space (works with or without space) SetCover:PostcodeNoSpace [PHHomeAddress_Postcode_Value].Replace(' ','') Create second question (text box) with formula to remove last 2 digits SetCover:PostcodeMinusTwo [PostcodeNoSpace_Value].ToUpper().Substring(0,[PostcodeNoSpace_Value].Length-2) Create third question (text box) with formula to insert space SetCover:PostcodeRated [PostcodeMinusTwo_Value].Insert([PostcodeMinusTwo_Value].Length-1, " ") |
|
SetCover:MostRecentDate|OVERWRITE IF([XXX_Date_DateValue] > [MostRecentDate_DateValue], [CCJ_Date_DateValue], [MostRecentDate_DateValue]) |
Finding the most recent date in a table. ( works on all fields in table.) |
| ([Q12_Postcode_Value].Replace(' ','') | Remove space in postcode |
| IF([QC6F_Value]= "Yes" AND [QC6C_Geo_Value]= "Worldwide", "<div>" + [QC6C_Desc_Value] + " / " + [QC6C_Geo_Value] + " / " + [QC6C_Sum_Value] + "</div>", "") | Useful when trying to pull data out of a table for a document. The destination needs to be a large text box. |
| [Policy.InceptionDate].AddDays(90).ToString("dd MMMM yyyy HH:mm:ss") |
When use SetExpiryDate: this formula will add the required days or months depending on how you adapt it. .ToString("ddMMMMyyyy") will display 01 January 2018 .ToString("ddMMMyyyy") will display 01 Jan 2018 .ToString("ddMMMyy") will display 01 Jan 18 .ToString("dd.MM.yy") will display 01.01.18 .ToString("yyyy-MM-dd") will display 2018-01-01 .ToString("dd/MM/yyyy") will display 01/01/2018 |
| CEILING |
To round up decimals - wrap the entire formula in round brackets, then add CEILING to the start of the formula For example, 1.2 will be rounded up to 2 |
| FLOOR |
To round down decimals - wrap the entire formula in round brackets, then add FLOOR to the start of the formula For example, 1.2 will be rounded down to 1 |
| "IF (CEILING((NOW-IF([ConvictionsTable_Identifier_Cover]=1, [ConvictionsTable_Date_DateValue], [ConvictionsTable_OffenceDate_DateValue])).Days / 365.242199) = 1, [ConvictionsTable_Year1_Cover], [ConvictionsTable_Score_Cover])" | Formula used on Acorn site to use one of two dates. |
| NOW or TODAY | Gives you today's date |
| 365.242199 | |
| [PostCodeRisk_Value].Substring(0,2) | Substring can take certain letters from a text string. |
| [Pri_Postcode_Value].Replace(' ','').ToUpper().Substring(0,3) | First three letters of postcode. |
|
SetCover:HiddenAge FLOOR((([StartDate_DateValue]-[ProposerDOB_DateValue]).Days/365.242199)) |
How to set the age of a policyholder based on the start date of the policy and the policyholders date of birth |
|
SetCover:NumberOfDays ([PIInceptionDate_DateValue]-[CurrentPIRetroactive_DateValue]).Days |
To calculate the difference in days between two dates |
|
SetCover:RetroDate [Policy.InceptionDate].AddYears(-3) |
When doing a retroactive date where it needs to set a date 3 years prior the inception date. |
| #yyyy-MM-dd# | How dates should be written when used in an IF statement. |
| [RiskAddress_Postcode_Value].ToUpper().Substring(0,[RiskAddress_Postcode_Value].Length-3).Replace(' ','') | Use this formula to always pull through the first part of the postcode |
|
RemoveEndorsement:1 AA11223344 |
Use this formula if you want to remove specific endorsements (in this example the endorsement being deleted is AA11223344) Other endorsements can be listed below if deleting more than one |
|
SetCover:AnyField "first line text" + "\r\n" + "second line text" |
Enables you to put text into a large text box in multiple lines |
|
SetCover:RoundedExcess ([HighestExcess_Cover] - ([HighestExcess_Cover] % 250)) |
If you are trying to round down a cover amount use this formula where the 250 is what you are rounding it down to. |
|
SetCover:ExcessDifference ([Excess_Cover] % 50) ################################################################################ Answer:ExcessDifference SetCover:RoundedExcess 0-24.9 ([Excess_Cover] - ([Excess_Cover] % 25)) 25-50 ([Excess_Cover] + ([Excess_Cover] % 25)) |
This is a different version where you want to round to the nearest 50 (or 100 ect) Create a hidden field to to find out the difference in the %. then you take away the difference if it is below 25 or add if it is above. |
|
SetExpiryDate [StartDate_DateValue].AddMonths(1) [StartDate_DateValue].AddMonths(6) [StartDate_DateValue].AddDays(40) |
To set a specific Expiry Date |
|
SetExpiryDate [StartDate_DateValue].AddMonths(1).AddSeconds(-1).AddSeconds(-1) [StartDate_DateValue].AddMonths(6).AddSeconds(-1) [StartDate_DateValue].AddDays(40) |
The format in the above row adds on the number of months but uses the same day i.e. if the inception date is 02/02/2023 and a three month term is selected the expiry is set to 02/05/2023 The option on this row sets the expiry date to one second before the inception date plus the selected term i.e. if the inception date is 02/02/2023 and a three month term is selected the expiry is set to 01/05/2023 |
|
SetCover:Named_Driver_Age [Now].Year - [Named_Driver_Date_Of_Birth_DateValue].Year |
Formula to find out a persons age from a DoB question |
|
[Now].Month - [Purchase_Date_DateValue].Month + (([Now].Year - [Purchase_Date_DateValue].Year )*12) |
Formula to use to find out number of months between two dates |
|
SetCover:Years_Trading [Years_Trading]+1 |
Adds one year onto the previous years answer at renewal (use in renewal matrix) For _Cover to work it needs to be a calculation question; if it is a textbox then use [PreviousAnswer_Years_Trading_Cover] |
|
SetRate:CommissionFromInsurer_793 if([Policy.InceptionDate]>= #2017-12-01#,23.5,26) |
Set a new 'Commission from Insurer' rate from a specified inception date for a Risk Group |
|
SetRate:Commission_XXXX_IndexLevel0 If([Policy.InceptionDate]>= #2019-06-01#,25,20) |
To set the commission rate on a risk group for a broker from a particular date |
|
SetCover:Claims_2yr_Count IF([ClaimsTable_DateOfClaimLoss_DateValue] > NOW.AddYears(-2) AND [ClaimsTable_DateOfClaimLoss_DateValue] < NOW.AddYears(-1), 1, 0) |
Pulls through the total number of claims from a table, only if date of claim is over 1 year ago but under 2 years ago |
|
SetCover:Claims_2yr_Amount IF([ClaimsTable_DateOfClaimLoss_DateValue] > NOW.AddYears(-2) AND [ClaimsTable_DateOfClaimLoss_DateValue] < NOW.AddYears(-1), [ClaimsTable_CostOfClaim], 0) |
Pulls through the total cost of any claims from a table, only if date of claim is over 1 year but under 2 years ago |
|
IF([ConvictionCode_Value].CONTAINS("DR"), TRUE, FALSE) or IF([ConvictionCode_Value].StartsWith("DR"), TRUE, FALSE) |
Use if you want to find out if a field contains or starts with a value. |
|
SetCover:QuestionID IF([Policy.InceptionDate]<#2017-12-01#,'Reference A','Reference B') |
To set an answer to a question that is dependant on a particular date - used in particular for DOA schemes that have a documents page with all insurer references on them |
|
SetFee:QuestionID IF([Policy.InceptionDate]<#2017-08-01#,66.98,70.329) |
To set a rate that needs to be used after a particular date - for example when an insurer increases there rates on renewal of the binder |
|
RemoveAllEndorsements All |
To remove all endorsements in the renewal rating file |
|
RemoveEndorsement IF([Record.InceptionDate] >= #2018-01-01#, "REMOVE ALL", "") |
To remove all endorsements on renewal subject to a particular date |
|
Answer:RequestedBI | SetCover:ActualBI 0-250000 | 250000 |
To set a minimum value in a field that a customer can populate i.e. the package in question has a fixed minimum value of £250k for BI but a higher limit can be requested and will rate accordingly. However, if the customer enters £100k the premium must still rate on the minimum value of £250k |
|
[Policy_Renewal_Count_Cover] = 0 | Set Action [Policy_Renewal_Count_Cover] > 0 | Set Action |
Create a hidden Question [Policy_Renewal_Count] and set the initial value to 0. In your renewal rating file, set this value to increase by 1 year using SetCover:Policy_Renewal_Count [Policy_Renewal_Count]+1 You can then use this hidden question to set any adjustment rules conditioned to whether the policy is in it's first year, 2nd year etc. The first condition to the left will set the rule if policy in it's first year, the second condition says set the rule if policy has completed 1 or more years |
SetCover:HiddenCalcQuestion IF([TableName_ColumnName_Value]='Column Value' AND [TableName_PercentageColumnName_Cover]>0, ([SumInsured]*[TableName_PercentageVariableAmount]/100), 0) |
Use this in a calculation question to set as the percentage of the sum insured, depending on the value selected from a table dropdown by the percentage amount entered in that row. Example: If trade is equal to builder and this is 50% of trade activity, set the hidden question to be 50% of the sum insured. You can then set a rate against this hidden calc question. |
|
SetCover:HiddenField_Id MIN([Q1_Id_Cover],MIN([Q2_Id_Cover],MIN([Q3_Id_Cover],MIN([Q4_Id_Cover],[Q4_Id_Cover])))) |
Use this to set cover to the lowest amount, where the are more than 2 variables. For only 2 variables just use MIN([Q1_Id_Cover], [Q2_Id_Cover]) |
|
SetAgentId 1234 or SetAgentId [AgentTest_Value] |
To set the Agent in the rating file where you can use either the actual AccountId (as in the first example) or an (hidden) question name that holds the AccountId (as in the second example) NB it has to be the numeric one (not the alpha code) |
|
AccountRef SetFee:PolicyFee DIR3 20 DIR1 50 SIG1 35 |
Using the agent's account reference to set the answer to a fee (NB you do not need to use "Answer:" when using AccountRef in this format) |
|
SetFee:PolicyFee IF([Agent.AccountRef]='DIR3',(([Net_3730_Cover] + [Net_3731_Cover])*0.025),20) |
Using the agent's account reference within an IF formula |
|
SetFee:PolicyFee CEILING([PolicyFee_Result]/5.0)*5 |
Rounding up a value to the next £5 |
|
SetCover:RoundedExcess CEILING([Excess_Cover]/50.0)*50 |
Rounding up a value to the next £50 |
|
SetCover:HiddenMTADays|OVERWRITE ([Policy.RenewalDate]-[Record.InceptionDate]).Days |
Add a hidden standard number question and use this formula in rating to check how many days are remaining in the MTA policy term |
|
SetCover:HiddenAnnualDays|OVERWRITE ([Policy.RenewalDate]-[Policy.InceptionDate]).Days |
Add a hidden standard number question and use this formula in rating to check how many days are in the annual policy term. This is useful if you need to use a formula to pro rata a cover field on the matrix for an MTA but need to establish whether it's a leap year. |
|
SetCover:Example [MtaDiff_Net_XXXX_Result] |
If you want to set cover on an adjustment to the risk group net total premium, this is what you need to use to look at the current record |
| SetMinNet |
To set a minimum premium for the record as a whole i.e. all cover groups The Main cover group must have a fixed value load field named Autoload_Total_Main |
| SetRiskGroupMinNet:xxxx |
To set a minimum premium for a particular cover group The cover group in question must have a fixed value load field named Autoload |
|
Formula:[RiskGroup_XXXX].Insurer.Name Insurer Name Value |
You can use this as a condition (usually for an add-on) in rating to set different rates depending on who the insurer is for a specific risk group |
|
SetCover:VOIPHackingInsurance If([Record.CreatedDate]>=#2020-05-20#,"No",[VOIPHackingInsurance_Value]) |
Using the record created date in a formula - do not use _DateValue as it will error out |
|
Previously: IF([ProfWorkSplitsDC_WorkType_Value]="Civil Engineering" OR [ProfWorkSplitsDC_WorkType_Value]="Flooring" OR [ProfWorkSplitsDC_WorkType_Value]="Cladding","True, "False") Can now use: IF([ProfWorkSplitsDC_WorkType] IN ("Civil Engineering","Flooring", "Cladding") ,"True","False") |
If you have an expression that uses multiple answers to populate a value you can instead use the IN expression which will do the same amount of work without needing to type so much |
|
ApiRequest:MP_FoodRatingAPI {"request": {"body": "","method": "GET","headers": [{"Key": "x-api-version","Value": "2"}],"url": "[GroupBy Field=\"MP\"]https://api.ratings.food.gov.uk/establishments?name=##[CompanyName_Value]##&address=##[MP_RiskAddress_Postcode_Value]##[/GroupBy]"},"responseDataLocation": ["establishments","RatingValue"]} |
FOOD RATING API REQUEST in rating file - on MP Scheme Where the field 'MP_FoodRatingAPI' is hidden field created as 'GetApiResponse' question type on the MP and the fields 'CompanyName' and 'MP_RiskAddress_Postcode' are respectively the trading/restaurant name on the General Details tab and the postcode of the risk address we want to check. Please note: the API will return a value only if there is the perfect match of 'Restaurant name + Postcode'. |
|
Formula:[Agent.Name] Test |
How to use the agent name as a condition in your rating file |
|
SetPolicyTermMonths 12 |
Force policy length to annual _ |
|
Answer:TableName_RowCount 1-1000 |
If a formula is reliant on an answer in a table question, but if that table question has no data entered and is therefore blank, the case may error out when running the rating file Avoid this issue by adding a condition to check if there are rows in the table before running the formula _RowCount can also be used with MP questions |
|
Answer:Insured_Addresses_Table_RowCount 1-1000 |
Use this in the first column before any formula that use table system IDs - it will qualify that there is at least one row in the table and run the formula. If there are no rows present it should just skip it, but it can sometimes cause an error if it tries to run the formula but there are no rows In this example the table name is [Insured_Addresses_Table] |
|
Answer:PreviousClaimsDetails_ActualClaimDate SetCover:PreviousClaimsDetails_ActualClaimDate null [Now].AddYears(-1) |
A date field has been added to a table question - if a record is renewed using the auto renewal function this date will remain blank - broker wanted the rating file to be able to identify records such as this and overwrite a blank date field with a date set as this time one year ago |
| SetCover:TotBuildingsRateFactorMinusUnoccupiedCover | |
|
SetRate:Commission_5279_IndexLevel0 IF([Agent.AccountId].ToString()='ACCOUNT_ID_20000',0,20) |
Using the agent account ID (i.e. 20000) ather than their account ref (i.e. DIR3) |
| [Policy.OriginalInceptionDate] | To use the very first date that a policy started i.e. the third renewal has been processed but the original inception date was three years ago |
|
Answer:QuestionID 1-Year 3-Month 6-Month 9-Month |
When you have a 'Policy Length Period' question type on your question set you must use '3-Month', '6-Month' etc for the Months options, even if they are displayed as '3 months' '6 months' on the dropdown during the Get Quote. Same for the '1 year' option -> needs to be '1-Year'. This is to be done in both rules and rating files. |
| [PreviousAnswer_TotalPremiumNet_Result] |
Used on Adjustment - Cancelled rules as when you cancel a policy using the Cancel Policy button the status 'Cancelled' doesn't 'read' any XXXXX_ResultBefore or XXXX_CoverBefore. You then have to use [PreviousAnswer_XXXX_Result] or [PreviousAnswer_XXXX_Cover] or [PreviousAnswer_XXXX_Rate] if you need to use/check a result or a cover or rate from the previous record. It can be used in rules, rating and docs - the only place where it can't be used is reports. PreviousAnswer must be spelled with upper and lower case characters - if not the system ID wont work |
|
Answer:MP_HiddenInterestedPartiesLock Null |
To look for the answer of an unchecked tick box |
|
|OVERRIDE for example: SetFee:PolicyFee|OVERRIDE 75 |
Use when lock rates are enabled on a scheme but there are some rates / fees that must not be locked. For example, last year's policy fee was 50; all other rates / fees should be locked, except the policy fee which now needs to be set at 75. Creating a rule to set this fee wont work, even though rules run after the rating files; if SS sees the rates are locked, the rule will fail. |OVERRIDE is only used with SetRate and SetFee - it is not necessary for SetCover. |
| [CaseRecord.QuotationTemplateId.Value] | Pulls the Question Set reference number |
| [Basic_xxxx_Cover] | If the 'This is Coverage' box is checked within a calculation question the sum insured on that question will be populated to the bottom of that cover group on the matrix page of the record to show the total sum insured of that question and all others that are checked. This is the system ID for that field that can then be used in rating file or report formula |
| IncludeEndorsement | Use this to include endorsements from the rating file - list them by ID in rows below. Note that this applies endorsements at policy level, even for MP |
|
This formula errors out because a record didnt have a contents sum insured value entered: (RoundUp(([SI_Landlords_Contents_ResultBefore]/([SI_Landlords_Contents_CoverBefore]/100))*(1+([RenewalRateLoad]/100)),3)) Changed it to include an action of setting a value of zero if either of the two system IDs used in the division formula had values values of zero: IF([SI_Landlords_Contents_ResultBefore]=0 OR [SI_Landlords_Contents_CoverBefore]=0,0,(RoundUp(([SI_Landlords_Contents_ResultBefore]/([SI_Landlords_Contents_CoverBefore]/100))*(1+([RenewalRateLoad]/100)),3))) |
If a formula is structured to divide one value by another you must take into account that one of those values may sometimes be zero - if this is not factored into the formula an error will occur when one or both values are zero so you need to add an IF condition to the formula to account for this possible occurrence and carry out another action. You would use the same format in both reports and rating files if you have any column formula that use the divide function. |
| To work out what an amount should be to take off a percentage to end up with the figure we are given for rating (ie before commission is removed): Pre-calculation figure - 0.350% - we are told that example 10% commission is on top of this figure and we need to work out the gross (so that taking 10% off the gross will end up with the figure we are given). In this instance, to calculate a 10% commission off gross, multiply the net figure by 11.1112%: ((0.35*11.1112)/100)+0.35=0.3888892 - Now subtract this amount by 10% to ensure it results in 0.35: 0.3888892-(0.3888892*10/100)=0.35 | |
|
Formula:IF([Policy.DoNotAutoRenew]=True,1,0) SetCover:MP_RatingCycles 0 0 |
To be used in a renewal rating file to identify if a record is being renewed via batch, and then trigger an action in a subsequent field We now know that the 'reset if hidden' option within a question doesnt work when a case is batch renewed - in the example seen to the left I wanted to reset the question [RatingCycles] back to the default value of 0 when the case is batch renewed WIth this particular system ID the value of True or False does not need to be wrapped in apostrophes - this is not normally the case, but it is with this system ID |
|
SetFee:PolicyFee IF([TotalPremiumNet_Result]>=400 AND [TotalPremiumNet_Result]<500,40,IF([TotalPremiumNet_Result]>=500 AND [TotalPremiumNet_Result]<600,50,0)) |
How to set a policy fee based on the Total Net Premium within premium bandings What this formula is checking/doing: If net total is equal to or great than 400 but less than 500, the fee sets to £40 If net total is equal to or great than 500 but less than 600, the fee sets to £50 If none of the above formula are true, i.e. for any other net total, the fee sets to £0 |
|
SetExcess:Excess Name 50 |
How to set a standard excess which has been created under Claim Types Excess Name being the name of the excess given when you created it in Claim Types In this example, the excess will be set to £50 in the rating file This will override any amount which has been set for this excess in Claim Types |
| [Policy.CreatedDate] | The date the policy was original created at First Premium, regardless of how many renewals it has gone through since |
Best practices:
- Build your rating file following the 4 steps at the top of this page. Keep every rating file in this order.
- Use lookup files to do your rating.
- Use suffixes to ensure the correct data is being used.
- Set fields to 0 before rating on them. This avoids picking up previous data when requoting.
- Test your rating file after building each rating section.
📅If you´d like some help building your rating file, book in a session here with our trainers.
.png)
