A property submission arrives as one email with a spreadsheet attached. The insured's controller built it. It has 214 rows, a merged title block across the first four rows, a column called Bldg Val, another called Contents, a "TOTAL" row somewhere in the middle because someone subtotaled by region, and eleven locations where the construction class is written as "Masonry non-comb" instead of a code. The producer needs total insurable value by location, a clean location schedule for the carrier, and a list of what is missing before an underwriter asks.
We have covered pulling named fields off ACORD forms and parsing a carrier loss run. A schedule of values is the third document in every property submission and the one most teams key by hand. It is also the one where handing the whole file to a model goes wrong quietly: the totals come back plausible and off by one building.
This tutorial builds the SOV stage of a Quote-Intake Agent. Spreadsheet in, validated location rows out, TIV that reconciles, and a gap list for the producer. No rating, no carrier submission. The output lands as an attachment and an activity on the prospect in the AMS for a human to check.
You need Python 3.11, openpyxl, pandas, and at least five real SOVs from different insureds. Five, not one. The first file teaches you the happy path; files three through five are where the design gets decided.
Decide the target schema first
from dataclasses import dataclass
from decimal import Decimal
@dataclass
class LocationRow:
loc_number: str | None
bldg_number: str | None
street: str | None
city: str | None
state: str | None
zip_code: str | None
year_built: int | None
square_feet: int | None
construction_raw: str | None # insured's wording, kept verbatim
occupancy_raw: str | None
sprinklered: bool | None
building_value: Decimal | None
contents_value: Decimal | None
bi_value: Decimal | None
tiv: Decimal | None
source_sheet: str
source_row: int
confidence: float
Two decisions in there are worth defending.
construction_raw and occupancy_raw keep the insured's words. Mapping "Masonry non-comb" to ISO construction class 4 is the part that looks valuable and is the part a model will get confidently wrong on joisted masonry. Underwriters read the insured's wording every day. If you want a mapped code, emit it as a suggestion in a separate field with the raw string next to it, and let the producer accept it.
source_sheet and source_row are not metadata. They are how an account manager checks a disputed $4.1M building in ten seconds. Every row you emit points back at the cell it came from.
Step 1: find the header row, do not assume it
Most SOVs do not start on row 1. Score each of the first 25 rows on how many cells look like known headers, and take the best one.
import openpyxl, re
HEADER_HINTS = {
"loc", "location", "bldg", "building", "address", "street", "city",
"state", "zip", "year built", "yr blt", "sq ft", "square feet",
"construction", "constr", "occupancy", "sprinkler", "tiv",
"building value", "contents", "bpp", "business income", "bi",
}
def norm(s) -> str:
return re.sub(r"[^a-z0-9 ]", " ", str(s or "").lower()).strip()
def find_header_row(ws, max_scan: int = 25) -> int | None:
best, best_score = None, 0
for r in range(1, min(ws.max_row, max_scan) + 1):
cells = [norm(c.value) for c in ws[r] if c.value is not None]
score = sum(1 for c in cells if any(h in c for h in HEADER_HINTS))
if score > best_score:
best, best_score = r, score
return best if best_score >= 3 else None
Three hits is the threshold we use. Below that, stop and put the file on the gap list rather than guess. A wrong header row produces a full location schedule made of garbage, which is worse than no schedule.
Run this across every sheet in the workbook. Property SOVs regularly hide the real schedule on tab 3, behind "Instructions" and "Summary".
Step 2: map columns deterministically, ask the model only for leftovers
Write a synonym table. It is boring and it will cover roughly 85% of columns across a real book.
SYNONYMS = {
"loc_number": ["loc", "loc #", "location", "location number", "site"],
"bldg_number": ["bldg", "bldg #", "building", "building number"],
"street": ["address", "street", "street address", "location address"],
"year_built": ["year built", "yr blt", "yob", "construction year"],
"square_feet": ["sq ft", "sqft", "square feet", "area", "total sq ft"],
"construction_raw": ["construction", "constr", "const type", "iso construction"],
"occupancy_raw": ["occupancy", "occ", "use", "operations"],
"sprinklered": ["sprinkler", "sprinklered", "auto sprinkler", "as"],
"building_value": ["building", "bldg val", "building value", "real property"],
"contents_value": ["contents", "bpp", "personal property", "contents value"],
"bi_value": ["bi", "business income", "bi/ee", "loss of income", "rents"],
"tiv": ["tiv", "total", "total insured value", "total insurable value"],
}
def map_columns(headers: list[str]) -> tuple[dict[int, str], list[tuple[int, str]]]:
mapped, unknown = {}, []
for idx, raw in enumerate(headers):
h = norm(raw)
hit = next((f for f, alts in SYNONYMS.items() if h in alts), None)
if hit:
mapped[idx] = hit
elif h:
unknown.append((idx, raw))
return mapped, unknown
Only the unknown list goes to a model, and you send it as a small classification job: the header text, three sample values from that column, and the closed set of target fields plus ignore. Constrain the output to that set. A column of 1988, 1994, 2001 under a header of Const is a year, not a construction class, and the sample values are what tell you that.
Cache the answer keyed by (header text, insured). The same controller sends the same spreadsheet every year. Second renewal, the mapping is free and identical, which is worth more than accuracy on any single run.
Step 3: drop the rows that are not locations
TOTAL_MARKERS = ("total", "subtotal", "grand total", "sum", "all locations")
def is_data_row(values: dict) -> bool:
joined = " ".join(norm(v) for v in values.values())
if any(m in joined for m in TOTAL_MARKERS):
return False
if not any(values.get(f) for f in ("street", "city", "loc_number")):
return False
return True
Subtotal rows are the single most common source of an inflated TIV. Keep the ones you dropped: you are going to use them.
Step 4: reconcile, do not trust
Arithmetic is where you catch both the insured's errors and your own parsing errors, and it costs nothing.
def reconcile(rows, dropped_totals) -> list[str]:
issues = []
for r in rows:
parts = [r.building_value or 0, r.contents_value or 0, r.bi_value or 0]
if r.tiv is not None and sum(parts) and abs(r.tiv - sum(parts)) > 1:
issues.append(f"row {r.source_row}: TIV {r.tiv} != sum of values {sum(parts)}")
computed = sum((r.tiv or 0) for r in rows)
for t in dropped_totals:
if t.get("tiv") and abs(t["tiv"] - computed) > 1:
issues.append(
f"workbook total {t['tiv']} != sum of parsed rows {computed} "
f"(check row {t['source_row']})"
)
return issues
If the workbook's own total does not match the sum of the rows you parsed, you either dropped a location or double-counted a region. Do not resolve it in code. Surface it.
Step 5: build the gap list an underwriter would build
Missing data is the output, not an error condition. Carriers decline submissions for these fields, so name them:
REQUIRED = ["street", "city", "state", "zip_code", "year_built",
"construction_raw", "occupancy_raw", "building_value"]
def gaps(rows) -> list[str]:
out = []
for r in rows:
missing = [f for f in REQUIRED if getattr(r, f) in (None, "")]
if missing:
label = r.loc_number or r.street or f"row {r.source_row}"
out.append(f"{label}: missing {', '.join(missing)}")
roofs = [r for r in rows if r.year_built and r.year_built < 1990]
if roofs:
out.append(f"{len(roofs)} locations built before 1990: expect roof age questions")
return out
That last check is not extraction, it is the producer's experience written down once. Add the ones your team asks for every time: vacancy, flood zone, distance to coast, whether a location has no BI value on a manufacturing risk.
Step 6: write it back and stop
The agent produces three artifacts: a normalized location schedule (CSV or the carrier's own SOV template), the reconciliation issues, and the gap list. It attaches them to the prospect in the AMS and drafts one activity summarizing counts: 214 rows read, 211 locations, TIV $412,880,000, 9 gaps, 1 reconciliation issue.
Then it stops. It does not email the insured's controller for the missing years built, and it does not send anything to a carrier. A producer reads the activity, decides which gaps matter, and sends. Every draft carries the file name, sheet, and row numbers behind each figure, for the same reason we log every agent decision: when a building is missing from a bound schedule two years from now, the question is what the agency was told and when.
What to measure before you turn it on
Run it silently against the last 30 property submissions you handled and compare to what your team actually keyed. Track three numbers: percentage of locations extracted with correct TIV, percentage of workbooks where the header row was found, and how often the gap list caught something the team missed. In our experience the header-row number is the one that decides whether the thing is usable; column mapping failures are visible and get fixed, but a misread header produces confident nonsense.
Thirty files also tells you the real distribution of formats in your book, which no amount of model choice will substitute for.
If you want this scored against your own submissions before anything writes to your AMS, tell us your AMS and roughly how many property submissions you intake a month, and a senior engineer will reply within one business day.