Excel Submission Data Extraction: Clean Cedent Spreadsheets
On this page
- Key Takeaways
- What Is Excel Submission Data Extraction?
- Why Are Cedent Spreadsheets So Hard to Use?
- What Data Do Reinsurers Extract From Submission Spreadsheets?
- What Are the Most Common Spreadsheet Traps?
- How Can One Unit Error Change the Price?
- How Does Excel Submission Data Extraction Work?
- Should Reinsurers Ask Cedents for a Standard Template?
- What Should Stay With the Underwriter?
- Where Does AI Help With Spreadsheet Extraction?
- How Do You Measure Spreadsheet Extraction Quality?
- What Should Leaders Do Before the Next Renewal?
- Key Terms
- Sources
- Frequently Asked Questions
Last reviewed: November 2026
Every treaty submission arrives with an Excel file, and no two cedents lay theirs out the same way.
Underwriters lose hours retyping figures before a single price is tested.
Excel submission data extraction reads every tab of a cedent spreadsheet and maps its figures into one standard structure.
Units, currencies and dates are normalised, and totals are checked before pricing starts.
Key Takeaways
- Layout is not the real problem. Hidden units, mixed currencies and unclear bases cause the costly errors.
- Map everything to one data dictionary. Every cedent format should land in the same internal fields.
- Reconcile before you price. Totals across tabs, the slip and the email should agree.
- Keep a trail to the source cell. Underwriters and auditors must see where each number came from.
What Is Excel Submission Data Extraction?
Excel submission data extraction is the process of moving figures from the Excel and CSV files in a reinsurance submission into a standard underwriting structure.
It covers premium, losses, risk profiles and exposure.
The goal is pricing-ready data. If an analyst still has to reformat a file before pricing, extraction is not finished.
Why Are Cedent Spreadsheets So Hard to Use?
Cedent spreadsheets are hard to use because each one is built for the cedent's own reporting. They are rarely built for a reinsurer's pricing model.
Why does every cedent send a different layout?
Every cedent sends a different layout because it exports from its own policy and claims systems. Its actuarial team then adds its own tabs, labels and notes.
Even after a broker tidies them, one renewal season can bring dozens of layouts for the same class.
Why do errors hide in submission spreadsheets?
Errors hide in submission spreadsheets because meaning often sits outside the cells. A note saying "USD 000s" or "net of QS" in a header is easy to miss.
Merged cells, hidden rows and subtotal lines add more risk. Nothing looks broken, yet the totals are wrong.
What Data Do Reinsurers Extract From Submission Spreadsheets?
Reinsurers extract five main data sets from submission spreadsheets. Each one needs its own fields and checks.
| Spreadsheet tab | Key fields to extract | Must also capture |
|---|---|---|
| Premium history | Subject premium by year, rate changes, policy count | Currency, gross or net basis |
| Loss listing | Claim reference, date of loss, paid, outstanding, incurred | Valuation date, unit, currency |
| Loss triangle | Paid or incurred by origin year and development period | Basis, cumulative or incremental |
| Risk or limit profile | Band, risk count, sum insured or limit, premium | Band definition, currency |
| Exposure schedule | Location, construction, occupancy, insured values | Geocoding quality, value basis |
The third column matters most. A number without its unit, currency or basis cannot be priced safely.
What Are the Most Common Spreadsheet Traps?
The most common spreadsheet traps fall into three groups: unit and currency, structure, and basis and date. They rarely look like obvious errors.
What are unit and currency traps?
Unit and currency traps happen when the scale or currency of a figure is unclear. They cause the largest pricing errors.
| Trap | What it looks like | How to handle it |
|---|---|---|
| Units in thousands | "USD 000s" appears only in a header note | Read header notes and flag unusual magnitudes |
| Mixed currencies | Some years in EUR, others in USD | Capture currency per row and convert at agreed rates |
What are structure traps?
Structure traps happen when the spreadsheet layout distorts the data. Totals and headers are the usual causes.
| Trap | What it looks like | How to handle it |
|---|---|---|
| Subtotal rows | Class totals sit inside the claim list | Detect and exclude total rows before summing |
| Multi-row headers | "Incurred" is split across two merged rows | Rebuild one clean header per column |
What are basis and date traps?
Basis and date traps happen when a figure is correct but measured on an unclear basis or date. They are the hardest traps to spot.
| Trap | What it looks like | How to handle it |
|---|---|---|
| Gross and net mixed | Some losses are net of inuring cover | Record the basis per figure and query if unclear |
| Ambiguous dates | 03/04/2025 could be 3 April or 4 March | Confirm the format from unambiguous dates in the file |
| No valuation date | Losses have no "as at" date | Treat it as a blocking query for loss data |
None of these traps needs advanced technology to spot. They need a fixed check that runs on every file, every time.
How Can One Unit Error Change the Price?
One unit error can remove real losses from an excess of loss price. Here is a simple illustration using a USD 4m xs USD 1m per risk layer.
What happens when the unit is read correctly?
When the unit is read correctly, two claims listed as 1,250 and 3,800 in "USD 000s" become USD 1.25m and USD 3.8m.
Both exceed the USD 1m attachment, so the layer pays USD 0.25m and USD 2.8m.
What happens when the unit is misread?
When the unit is misread as whole dollars, both claims sit far below the USD 1m attachment.
The layer looks loss-free, and USD 3.05m of experience disappears from the burning cost.
That is why magnitude checks matter. Our guide to burning cost reinsurance pricing shows how each large loss feeds the rate.
How Does Excel Submission Data Extraction Work?
Excel submission data extraction works through a repeatable six-step process that turns cedent spreadsheets into clean underwriting data.
The same steps apply to every cedent and every format.
- Capture the full pack. Link each spreadsheet to its broker email, slip and other attachments.
- Identify each tab. Classify tabs as premium, losses, triangle, profile or exposure, and skip pivots and charts.
- Map to a data dictionary. Match each column to a standard internal field, whatever the cedent calls it.
- Normalise the figures. Convert units, currencies and dates, and record the basis of every figure.
- Reconcile across sources. Check premium against the slip, the loss listing against the triangle, and totals across tabs.
- Flag and review. Send exceptions to an analyst or underwriter, and keep a link to the source cell.
Why does reconciliation matter after mapping?
Reconciliation matters after mapping because a correctly mapped column can still hold the wrong figures.
Mapping checks where data goes, while reconciliation checks whether it is right.
Should Reinsurers Ask Cedents for a Standard Template?
Reinsurers should share a preferred template, but they should not depend on it. Large cedents and brokers usually keep their own formats.
What does the OED standard cover?
The Open Exposure Data (OED) standard covers exposure data for catastrophe modelling, and it is curated by Oasis LMF.
Aon adopted it for property reinsurance placements in 2023.
Clean exposure fields matter for every model view. Our guide to catastrophe model blending explains why.
What does the Core Data Record cover?
The Core Data Record covers the contract data needed to bind and process a risk in the London market. It was extended to treaty reinsurance in May 2026.
Neither standard covers the loss and premium data in a pricing pack. That is why mapping any layout to your own data dictionary remains essential.
What Should Stay With the Underwriter?
Judgement on what the data means should stay with the underwriter. Extraction can show that a basis is unclear, but it cannot decide how to price around it.
Underwriters should confirm every figure that drives the price, including the largest losses, the latest year and any as-if adjustments.
Where Does AI Help With Spreadsheet Extraction?
AI and automation help with reading, mapping and comparing, which is the slow part of spreadsheet extraction. They do not replace underwriting review.
How does automated extraction compare with manual rekeying?
Automated extraction differs from manual rekeying mainly in consistency and traceability, not in who makes the final call.
| Factor | Manual rekeying | Automated extraction with review |
|---|---|---|
| Renewal peaks | Slows as volume rises | Keeps pace with volume |
| New cedent layouts | Each file is reworked by hand | Mapped to the same dictionary |
| Unit and total checks | Depends on who opens the file | Run on every file |
| Audit trail | Often lost in copied cells | Each figure linked to its source |
| Pricing judgement | Underwriter | Underwriter |
How does Insurnest support spreadsheet extraction?
Reinsurance Email Intelligence reads the broker email together with its PDFs and spreadsheets.
It extracts structure, premium, limits, commissions, claims history, loss ratios and exposure into one structured submission view.
It also surfaces missing information early, which our guide to incomplete reinsurance submissions covers.
For slips, see turning a PDF slip into structured risk data.
How Do You Measure Spreadsheet Extraction Quality?
Reinsurers measure spreadsheet extraction quality with five metrics tracked every month. Comparing them across renewal seasons shows real progress.
| Metric | What it tells you |
|---|---|
| Time from receipt to pricing-ready data | How long extraction really takes |
| Fields extracted without manual edits | How well mapping handles new layouts |
| Reconciliation breaks per submission | How often tabs and slips disagree |
| Unit or currency corrections | How often magnitude errors nearly slipped through |
| Broker queries about spreadsheet data | Which cedents need a data-quality conversation |
What Should Leaders Do Before the Next Renewal?
Leaders should fix the data dictionary and the intake checks before the next renewal peak. Both can start without a new system.
- Write one data dictionary for your three busiest classes.
- Make unit, currency, basis and valuation date mandatory for every figure.
- Add magnitude and reconciliation checks to intake.
- Log every correction, so you know which cedents need support.
Key Terms
- Data dictionary: The fixed list of internal fields that every submission is mapped into.
- Loss listing: A claim-by-claim schedule of losses, usually above a reporting threshold.
- Valuation date: The "as at" date on which loss figures were measured.
- Basis: Whether a figure is ground-up, gross, or net of inuring reinsurance.
- Data lineage: A record linking each extracted figure back to its source file and cell. See reinsurance data lineage.
Sources
- Oasis LMF (GitHub): Open Data Standards – Open Exposure Data (OED)
- Artemis: Aon embraces Open Exposure Data Standard (23 January 2023)
- Lloyd's: Core Data Record
- Reinsurance News: LMG's Data Council updates Core Data Record to support treaty reinsurance (18 May 2026)
- Insurnest: Reinsurance Email Intelligence
Frequently Asked Questions
What is Excel submission data extraction in reinsurance?
Excel submission data extraction is the process of reading the spreadsheets in a broker submission and moving their figures into a standard structure. It covers premium history, loss listings, triangles, risk profiles and exposure schedules, so underwriters and actuaries can price without retyping or reformatting.
How do you extract data from Excel submissions?
To extract data from Excel submissions, capture the full broker pack, classify each tab, map every column to a standard data dictionary, and normalise units, currencies and dates. Then reconcile totals against the slip and across tabs, and send flagged items to an underwriter for review.
What data should be extracted from a reinsurance submission spreadsheet?
Reinsurers should extract subject premium by year, rate changes, loss listings with paid, outstanding and incurred amounts, loss triangles, risk or limit profiles, and exposure schedules. Each figure also needs its currency, unit, valuation date and basis, such as gross or net of inuring cover.
What is the most damaging error in spreadsheet submission data?
The most damaging error in spreadsheet submission data is usually a unit or basis mismatch. A loss listing in thousands read as whole currency, or gross losses mixed with net losses, can move large losses above or below a layer and distort the experience price.
Can AI extract data from Excel submissions accurately?
AI can read varied spreadsheet layouts, recognise tab types and map columns to a standard data dictionary far faster than manual rekeying. Accuracy still depends on rule-based checks, such as reconciling totals across tabs, and on an underwriter reviewing flagged items before pricing.
Should reinsurers make cedents use a standard submission template?
Reinsurers should share a preferred submission template, but they should not rely on it. Large cedents and brokers often keep their own formats, so a process that maps any layout to one internal data dictionary is more practical than rejecting non-standard files.
How does spreadsheet extraction connect to catastrophe modelling?
Spreadsheet extraction feeds catastrophe modelling because exposure schedules in submissions become model inputs. Location, construction, occupancy and value fields must be clean first. Open standards such as Oasis Open Exposure Data (OED) give a common format for passing property exposure into models.
How can reinsurers measure spreadsheet extraction quality?
Reinsurers can measure spreadsheet extraction quality by tracking time from receipt to pricing-ready data, fields extracted without manual edits, reconciliation breaks per submission, unit or currency corrections, and broker queries about data. Comparing these across renewal seasons shows real progress.

Hitul Mistry
CEO, Insurnest
An InsurTech leader with more than a decade of experience across insurance and technology, focused on solving business problems with the help of technology. Has worked with brokers, insurance carriers, and reinsurance firms across the India, UAE, and US markets.
View LinkedIn profile →