Excel spreadsheets are heavily utilized across the insurance industry due to their capacity for coding complex formulas and business logic through worksheet formulas. One of the main use cases for insurance spreadsheets is to provide calculations for premiums that are tailored towards specific insurance products. Since calculations for insurance premiums can be highly dependent on up-to-date data will often incorporate actuarial formulas, most businesses will rely on Excel to build these raters. Most actuaries and underwriters are Excel experts, capable of building and maintaining complex business calculations in Excel spreadsheets.
One of the challenges that IT departments face is to embed those rating calculations into various platforms, such as policy administration and underwriting systems. Traditional recoding of such calculations can be very time consuming and costly.
In this article, we will cover an alternative approach for converting these rating spreadsheets into no-code APIs that can be consumed by other platforms.
Use Case: Homeowners Insurance Rating Calculations
We will start by taking a rating spreadsheet that was designed to calculate homeowners insurance for a fictitious insurance company. As with any rating spreadsheet, there are input cells, formula cells, data tables, and cells that display the final rates. The designers of most rating workbooks will often combine the inputs and outputs into one worksheet, while all intermediate calculations and data tables are spread in other worksheets.
There are 32 inputs in this rater, including various coverage amounts (e.g., dwelling, other structures, loss of use, personal property), policy credit inputs (e.g., fire alarm, storm shutters, claims free discount), and general policy information (e.g., construction type, policy type, deductible amounts). There is a single output in the rater called Premium. This premium value is calculated based on all the inputs, various calculations, and lookup tables.
The SpreadsheetWeb API can be used to set individual cell values using their named ranges. For example, cell B9 can be invoked using its named range if it exists or it can simply be invoked by its cell reference (i.e., Sheet1!B9). We always recommend using named ranges for better maintenance.
You can also set or retrieve multiple cells by a named range. For example, if cells B9:B14 are given a named range iCoverageAmounts, then you can use this name in the API request as a two-dimensional array of values.
Publishing Homeowners Rating Spreadsheet as API
The first step is to create a web service client through the SpreadsheetWeb Hub interface. Login to your account, go to Web Services and click the.
It is important to select the proper access rights during this process. Since we only want the client to execute calculations on the Homeowners rater, we will add Read-only access to Applications by Tag rule for the API tag. Upon creating the web service client, the system will provide you with an Identifier and a Secret. This set of credentials will allow you to access the calculation service for all applications with the API tag.
Next, we will update the Homeowners rater spreadsheet with the API tag.
Also, make sure that the application itself has been published. You can use a minimal user interface if you plan to solely work with the application via web service, but the state must be published in order to commit the current workbook as the active workbook.
Making API Calls to Homeowners Rating Spreadsheet
Before making the calculation call to the API, you must retrieve an access token. This token will be attached to any subsequent requests, acting as your authentication token. The client that was created in the prior step can be used to generate a token for a client credentials grant.
You can perform a POST request to the token endpoint in order to retrieve your access token. Ensure that the request body content type is x-www-form-urlencoded, and make sure to set all three required parameters:
- client_id: The client identifier from the web service client you created previously.
- client_secret: The client secret from the web service client you created previously.
- grant_type: Always set to ‘client_credentials’ since this is the type of grant you are requesting.
The target endpoint will be the SpreadsheetWeb Identity server /connect/token endpoint. In the example below, we are pointing to that endpoint on the public cloud server, though if you have a private server, the endpoint will vary depending on the setup.
In the response, you will receive your access token, which must be attached as a bearer in the header of all subsequent requests – otherwise, you will receive Unauthorized 401 errors.
The access token will last for one hour, as indicated in the expires_in parameter.
Next, we will make the calculation request call. Ensure that you have the Content-Type header set to application/json, and the Authorization header set to:
Bearer <insert_access_token>
This will be automatically handled if you are using Postman and enter the value into the authorization tab under the Bearer Token type.
The structure of the request is provided in the included Postman files, and also documented via our API’s Swagger UI page here under HubApiCalculations (/calculations/calculatesingle). To set up the request correctly, ensure that you are pointing to the correct workspace and application – if you are pointing to a workspace or application that your client does not have access to, then you will receive a Forbidden error. This is done through the workspaceId and applicationId parameters.
Another important aspect of the SpreadsheetWeb API is that the calculation consists of a single API call: the input values and the requested output values are provided in the same request. Thus, the response will include the calculated values for all of the requested outputs.
In this example, the value of only a single cell (oPremiumTotal) was requested in the response, which corresponds to cell G27 in the Excel file – the cell where the final premium calculation is made.
The response message will list the calculated value for the oPremiumTotal cell, as shown below.
You can download the Postman collection from this link. Simply follow the steps below to test the API:
- Import the collection into Postman.
- Create an application in your workspace using the attached Excel file. Once created, note the Application ID parameter (you can find this by clicking the Edit button for the application).
- Find the Workspace ID by using the Edit button for your workspace under your workspaces list.
- Follow the earlier steps to create a client in the system.
- Go to the POST Identity Token request sample and replace the values for client_id and client_secret with your values.
- Copy the access_token from the response.
- Go to POST API Calculation Request sample and paste the access_token into the Authorization tab where it says Token. This token is good for 1 hour. After 1 hour, you need to run the Identity Token method again to get a new token.
- Replace the workspaceId and applicationId members of the request with those found in Steps 2-3.
- Run the Post API Calculation Request
- You can change the values for iCoverageAmounts using the Input section of the request body and run it again to see the updated premium results.
Performance of SpreadsheetWeb Hub API
One majorly important facet of incorporating a new technology into a development project is the performance impact.
We prepared a test case of 1,000 scenarios with varying input combinations to ensure that each scenario triggers a recalculation in SpreadsheetWeb’s core calculation engine.
We used Postman to sequentially run these 1,000 individual rating scenarios against the API described above. The tests were executed from an Azure virtual machine with Intel Zeon Platinum 8272CL CPU @ 2.60GHz and 2 virtual processors.
All scenarios were run successfully with 100% accuracy. The entire test was completed in 63.7 seconds, with an average response time of 63 milliseconds.
If we are to extrapolate these performance figures, an insurance company would be able process approximately 60,000 insurance quotes per hour with SpreadsheetWeb, assuming a spreadsheet rater of similar complexity. Assuming a steady rate of traffic, this would equate to over 1 million quotes per day.
However, these tests were run sequentially, meaning that only one processing node was responding to these API calls on the SpreadsheetWeb Hub server. If the calls were coming concurrently, SpreadsheetWeb would have responded from multiple processing nodes to improve the scalability and substantially increase the number of quotes that the system would be able to process per unit time, far exceeding the 60,000 quotes per hour.
Abstracting Complex Business Logic using Business Driven Spreadsheet Formulas
The use case described in this article represents one way of eliminating the costs and resource requirements of coding actuarial calculations for an insurance company’s homeowners product. Considering the extent of spreadsheet usage across insurance organizations, especially in actuarial and underwriting departments, this methodology could transform the way that their IT departments handle and maintain business logic. Coding business logic is one of the most time-consuming and costly aspects of most development projects, so the idea of transforming existing business spreadsheets into no-code APIs has the capacity to transform their entire development life cycle and provide a strong competitive advantage.
