Transforming an Excel Model into an API

Hello, I'm Brian O'Neill from Coherent. Today, I'll demonstrate how an Excel model, specifically a cyber insurance rater, can be transformed into an API. This API is accessible at a specific endpoint where you can input data and receive outputs in response. The key question is: how do you govern this model?

Governance of the Model

Governing the model involves making changes, versioning, debugging, testing, and promoting it from a lower to a higher environment. Essentially, you need to treat Excel as source code. Let's dive into the process.

Making Changes

1. Access the Coherent UI and locate the version overview section. Here, you can see the current version, for example, version 0.1.

2. Open the Excel model and make necessary changes. For instance, adjust the California base rate.

3. Save the changes and drag and drop the updated spreadsheet onto the existing service. Add a note, such as "bump up California base rate."

4. The system compiles this version separately, creating a semantic minor version (e.g., 0.2), allowing multiple rate sets in production without deprecating the old version.

This process empowers business users to make changes independently without involving the tech team.

Version Comparison

If a supervisor wants to review changes between versions, they can use the "compare versions" feature. Coherent generates a visual diff highlighting:

• New additions in green

• Deletions in red

• Changes in bright yellow

• Downstream impacts in lighter gold

This provides a clear audit trail of the model's evolution.

API Call History

Every API call is logged, showing details like calculation time and total time. Users can review inputs and outputs for each call and even rehydrate the Excel model with specific call data, aiding in audit and compliance.

Environment Management

Versions are iterated in a lower environment, with options for dev, UAT, and prod environments. Only thoroughly tested versions are promoted, integrating with your CICD pipeline.

Testing with Test Bed Feature

1. Create a test bed, a collection of test cases. For this demo, we'll use 1,500 random test cases.

2. Randomly select inputs like state, gross revenue, hazard group, and liability limit, defined in Excel data validation rules.

3. Run the test bed against the latest model version to identify any extreme outliers.

4. Download and review the results, which include inputs and outputs for each test case.

This feature helps ensure the model's reliability and performance.

In summary, Coherent provides a robust framework for managing Excel models as APIs, allowing for efficient governance, versioning, and testing.