The goal
The agency prepared yearly renewal reports for its municipal clients by hand: export a policy cut from the CRM, then rebuild totals, next-year renewal tables, commissions and year-over-year differences in Excel. Claims-experience sheets for actuarial review were built the same way. The goal was to remove that manual work while producing files identical in structure and formulas to the ones the team already trusted.
The solution
A Python engine that reads any of the agency's policy-cut Excel layouts and writes a renewal workbook with live formulas: totals, a renewal table for the coming year, the difference from the previous year and a breakdown of that difference. On top of it, a Flask + Vue 3 web interface drives the whole process end to end: Playwright logs into the agency's web CRM, pulls the renewals report, extracts every authority, exports each policy cut and runs the engine on it. A second workflow takes the profitability report the insurer sends and adds a claims-experience sheet for actuarial review. The system runs as a Windows service on the agency's server with automatic deploys from GitHub.
Highlights
- Full renewal run for every authority from one click: CRM export to finished Excel
- Output files keep live formulas, so the team can keep editing them
- Handles multiple historical Excel layouts, including legacy .xls
- Cell-by-cell verification against the client's own manual reports
- Runs on the agency's own server with automatic deploys and rollback
The challenge
The policy-cut exports came in several different layouts: headers on row 3 or 4, the year in different cells, synonymous column names, duplicated annual and period columns, and old .xls files returning policy numbers as floats. Policy numbers also had typos, so grouping by exact match split real policies.
Header detection by fuzzy column matching instead of fixed positions, number normalization for legacy files, and policy grouping by line-of-business keyword plus the trailing digits with an edit-distance-1 merge. Every change is verified end to end against all sample layouts.
openpyxl writes formulas but not their computed values, so generated files looked empty in previews, and opening and saving a client workbook with it drops cached values, charts and pivots.
A Windows Excel COM step recalculates and saves every output, with retries when Excel does not release in time. New sheets are added to client workbooks through COM only, with a check that the sheet count grew and no original sheet disappeared.
In the CRM, leaving each date field triggers a postback that can reset other fields, so an export sometimes silently returned policies from the wrong years. It also allows only one session per user, so two parallel runs logged each other out.
Date fields are filled in a loop until all of them hold their values together, failing loudly otherwise. All runs go through a single-worker queue for CRM workflows and a separate lane for local Excel work, so CRM sessions never overlap.
Business rules came from how the client builds the reports by hand, and the client checks every number. Commission formulas, which rows count toward totals and what the original data block may touch had to match her files exactly.
The full business logic is documented, open questions are sent to the client in writing, and automated verification compares the output cell by cell, including number formats, against the sheets she built manually.
What was delivered
- Renewal report generator (Python CLI + drag-and-drop launcher)
- Flask + Vue 3 web interface with job queue and history
- Playwright CRM automation
- Actuarial claims report workflow
- Windows service setup scripts and GitHub Actions deploy with rollback
- Business logic documentation and verification scripts
Results
4
Workflows
End-to-end
Process
Hebrew RTL
Interface
What it taught me
- When the client checks every number, verify against her own manual files, including number formats, not only values
- A workflow that sometimes works is the dangerous one: fail loudly when the CRM state is not exactly what was requested
- Never assume an Excel layout - every new sample file revealed a structure the previous ones did not have