Skip to main content
Back to Blog
insuranceloss-runsexcel

Last reviewed:

How to Get a Loss Run Into Excel: A Column Layout and Checklist

By Fluxity Team

Getting a loss run into Excel means turning each carrier's report into rows of one table: one row per claim, per valuation date, with the same columns whatever the carrier. Once the data is in that shape, totals by policy year, line, or location are a pivot table away.

The hard part is not the copying. It is that every carrier lays out its loss runs differently, labels its columns differently, and sometimes defines "incurred" differently. This guide gives a column layout that works across carriers and a checklist for getting there.

Step 1: Ask for a Spreadsheet Before You Convert a PDF

Some carriers and TPAs already produce loss runs as spreadsheets. Beacon Mutual, for example, lets agents download loss runs as PDF, Word, or Excel, and large TPAs commonly provide loss run data in Excel on a schedule. Ask your broker or carrier for an Excel or CSV export before you start converting PDFs. It is faster and avoids transcription errors.

When you ask, request the same valuation date from every carrier, and ask for claim-level detail rather than a summary page.

Step 2: Get the Tables Out of the PDF

If only a PDF is available, you have three practical options:

  1. Excel's PDF import. In Excel on Windows, choose Data ‣ Get Data ‣ From File ‣ From PDF, pick the file, and select the tables in the Navigator; see Microsoft's instructions. Open them in Power Query to clean up before loading. The option is not available in every edition, including Excel for Mac.
  2. Copy and paste. Works for a short, text-based PDF. Check that columns did not shift, especially where a claim description wraps onto a second line.
  3. Retype. For scanned PDFs with no text layer, or very short loss runs, typing may be quicker than cleaning up a bad import.

Whichever you use, keep a column that records the source file and page for each row, so any number can be traced back.

Step 3: Use One Column Layout for Every Carrier

Map every carrier's report into the same columns. This layout covers workers' comp, auto, and general liability:

ColumnNotes
CarrierWho issued the loss run
LineWorkers' comp, auto, general liability, property, and so on
Policy numberAs printed
Policy start / Policy endTwo date columns, not one text column
Claim numberAs printed; this plus carrier identifies the claim
Claimant / DescriptionFree text
LocationIf the carrier reports it
Date of lossDate column
Date reportedDate column
StatusOpen, Closed, or Reopened, kept exactly as reported
Paid: medical / Paid: indemnity / Paid: expenseWorkers' comp splits; use one Paid column for other lines
Reserve (outstanding)Split the same way if the carrier does
IncurredAs printed by the carrier
RecoveriesSubrogation and salvage, as a positive number
Valuation dateThe "as of" date of the report
SourceFile name and page

Two rules make this table reliable:

  • Keep the carrier's printed incurred, and add your own calculated column (paid plus reserve) beside it. Where they disagree, the carrier is including something else, often claim expense. See what a loss run report is for how definitions differ.
  • Never merge Reopened into Open. A reopened claim is a signal on its own.

Step 4: Clean the Rows

Carrier PDFs carry layout that is not data. Before analysing, remove or fix:

  • Repeated header rows from each page of the PDF.
  • Subtotal and total rows by policy or year. Keep them aside for Step 5, but take them out of the claim table.
  • Wrapped descriptions that landed in a row of their own.
  • Dates stored as text, and two-digit years.
  • Amounts stored as text, with currency symbols, commas, or brackets for negatives.
  • Blank claim numbers on continuation lines.

Step 5: Check the Totals Against the Carrier's

Before trusting the table, reconcile it with the report. Group every check by carrier + policy number + policy term, not by term alone: two policies can cover the same term, and a total by term mixes them.

  1. Claim counts. For each carrier, policy, and term, count your rows and compare with the carrier's printed claim count.
  2. Paid and incurred totals. Sum Paid and Incurred for each carrier, policy, and term, and compare with the carrier's printed totals. A difference usually means a missed page, a duplicated row, or a negative read as positive.
  3. Paid plus reserve. Check that your calculated incurred matches the printed one claim by claim, and note the carriers where it does not.
  4. One row per claim per valuation. Count duplicates of carrier + claim number + valuation date. There should be none.

Step 6: Combine Valuations Without Double Counting

If you keep loss runs from several valuation dates, the same claim appears several times with different figures. That history is useful, but it breaks a simple sum.

  • For the current picture, keep the latest valuation date for each carrier + policy number + policy term, then total. Do not take the latest valuation per term alone: if Carrier A's report is valued June 30 and Carrier B's May 31 for the same term, filtering the term to June 30 silently drops every Carrier B claim.
  • To see development, pivot claims by valuation date and watch incurred move. Claims whose incurred keeps rising between valuations are the ones to ask the adjuster about.
  • Never sum incurred across valuation dates. It counts the same claim more than once.

Step 7: Analyse

With a clean table, the common views are quick pivots:

  • Incurred by policy year and line, at the latest valuation.
  • Claim count and average incurred by location or cause.
  • Open and reopened claims with the largest reserves.
  • Workers' comp medical versus indemnity by policy year.

Loss Run to Excel FAQs

Can I convert a loss run PDF to Excel for free?

Often, yes. Excel on Windows can import tables from text-based PDFs through Power Query, and copy and paste works for short reports. Scanned PDFs without a text layer need OCR first.

Why don't my totals match the carrier's loss run?

The usual causes are a missed page, a duplicated header or subtotal row left in the data, a bracketed negative read as positive, or a carrier that includes expense in incurred. Reconcile by carrier, policy number, and policy term to find which.

How should I combine loss runs from different carriers?

Map each carrier into the same columns, keep a carrier column and a valuation date column, and compare carriers only at similar valuation dates.

Should I keep old loss runs once I have a newer one?

Yes, if you want to see how claims develop. Keep each valuation as its own set of rows, and filter to the latest one for each carrier, policy, and term when you need current totals.


Further Reading