Spreadsheet data achilles heel or critical link?

Over the years, CATEX has been involved in many projects involving data upload. These projects have included reinsurance brokers, reinsurers, delegated authority capacity providers and coverholders. Usually, the projects are focused on providing clients the ability to examine and analyse data better after it is loaded into the CATEX Pivot Point System.

As it turns out we have learned a thing or two about ‘data upload’ over the years. The first thing we learned was self-evident (one would have thought) and that is that one could have the finest system in the world for either bordereau or programme business management but that if the data being loaded into the system were corrupt or inaccurate then, inevitably, inaccurate results followed.

Since our systems are predicated on the intake of granulated risk level information, for both compliance purposes and better risk management, we have insatiable and omnivorous eating tendencies. The more data the better, because we believe the more the client knows the better off the client is.

We also learned that despite advances in technology, and the good intentions of promoters of system interface standards, the hard truth is that most data continues to come in via spreadsheets.

Like most lessons we learned this one the hard way. After responding to numerous complaints from clients about inaccurate bordereau or programme reports being produced by the system we finally focused on the incoming data itself. It turned out that our systems performed perfectly but, boy, the quality of data being loaded into them was uneven, to put it mildly.

Digging for detail

Whenever one of our systems that are dependent upon uploaded data presents an inaccurate response we invariably discover that the error lies with the data that was uploaded.

The incoming data is usually in the form of Excel spreadsheets. Typically, a number of these spreadsheets can include as many as 60 or so vertical columns representing risk characteristics. Spreadsheets are wonderful tools but they can, and do, become complex.

Over time we learned that it was rare for any two cover holders to create their spreadsheets in exactly the same format. We learned that often, spreadsheets coming from the same cover holder were formatted differently. We learned that even spreadsheets coming from the same cover holder for the same binding authority could be formatted differently, depending on which employee created the spreadsheet.

We began to feel like old-time telegraph key operators trying to identify a sender by his or her particular individual key touch.

We worked around these difficulties and began to become quite successful in transforming spreadsheet data—no matter how incoherent—into structured data that could then enter our system database. Part of this process involved imposing an endless series of validations on the incoming data to ensure that anomalies were spotted and corrected before they reached the system database.

Luckily for us our focus in this area coincided with a push from Lloyd’s to demand a better understanding of the delegated authority business being underwritten by Lloyd’s syndicates. By some accounts the delegated authority business accounts for nearly 28 percent of all Lloyd’s business. A better knowledge of risk exposures comprising such a large portion of the Lloyd’s business was an objective that no-one could quarrel with. And, even a cursory review of Lloyd’s Vision 2025 business goals paper signals that the corporation believes that delegated business opportunities abound in rapidly developing areas such as China, India and the Middle East.

Stop the press

Getting a handle on what exactly was being written and what the potential exposure was made sense.

So far there has been no ‘news bulletin’ in this story, but hold on. CATEX had been examining the data issue from its own insular perspective of ensuring that spreadsheet data of all disparate types could be uploaded into its own systems. Since the CATEX system is a 100 percent software-as-a-service system the data, once uploaded, moves around at 186,000 miles per second which, unless the discovery of the Higgs boson particle changes anything, is still the speed of light.

Once the data is in our structured data base it can be ‘mined’ to produce reports, analysis, claim review, etc, and be exported out to other internal systems used by our clients. CATEX can export the data for multiple uses such as ratings, modelling, analytics and interfaces in many formats (CSV, XML, spreadsheets, etc).

Here is the surprise to us. It turns out that systems such as CATEX are not the only systems grappling with the need to intake spreadsheet data and turn it into structured data. In fact we have learned from clients that a host of systems which they currently use demand similar data transformations just to make use of the licences they have already purchased.

Modelling companies have their own structured data templates that require the same data scrubbing and data anomaly spotting that CATEX requires for its own systems. We’ve demonstrated our bordereau/programme management system to syndicates and insurers who are not delegated authority capacity providers. They want to see our system because they want to learn how we manage to upload the spreadsheet data and learn whether they can then export that structured data out to modelling templates.

Our response is simple and effective: yes, we can manage your inbound spreadsheet data in any format and convert it to structured data in our Pivot Point System. Once it’s in our system you can export it out (at a speed of 186,000 miles per second) to any destination or template you desire.

A unique offering

We hear tales of outsourcing and internal resource expenditures that are literally hair-raising as clients struggle to convert spreadsheets into data that’s structured and accurate enough to send to a modeller. We’ve heard tales of analysts with graduate degrees, who have been retained to review modelled data, who are mired in dealing with spreadsheet formatting and uploading. We’ve heard tales of on-site ‘job shops’ of employees set up to do manual data entry from spreadsheets. And we’ve heard of costly and ineffective business process outsourcing efforts that grind to a halt if a spreadsheet comes in with a new or different format, forcing consultation with London, Bermuda or the US.

We offer: