Workers Compensation Records to Spreadsheet, Step by Step

To move workers' compensation records to a spreadsheet, pick out the pages that are already tables, which are the carrier's benefit payment history, the employer's wage statement and the medical payment ledger. Convert each with OCR, export to Excel, give every payment or pay period one row, and prove the totals against the printed page before you calculate anything.

The examples follow an invented claim. Lucille Ferreira, a line cook for Marlow Dining Group, injured her back at work. Pinecrest Indemnity has paid benefits on and off for five months, and her lawyer wants to know whether the payments were right.

Benefit checks and wage lines belong in one workbook with a page number on every row. Search the payment history for each compensation payment and the wage statement for the pay used to set the rate. If a medical ledger is in the file, keep those lines apart from the wage rows. Confirm both on the scan before you total what the carrier paid on the claim.

Which workers' compensation records belong in a spreadsheet?

The workers' compensation records that belong in a spreadsheet are the ones made of dated amounts: benefit payment histories, wage statements, payroll registers, medical payment ledgers and bill review summaries. Reports, forms and letters do not. They are read and searched, and breaking them into cells destroys them.

A sort of the claim's paper by what you will do with it:

Two neighbors of this post cover what it leaves out. The medical forms, examiner reports and treatment decisions in the same claim are handled in the post on OCR for workers' comp medical records. Bills that come from the providers themselves, as opposed to the carrier's record of paying them, follow the method in the post on converting medical records to a spreadsheet.

If the same injury also supports a claim against a third party, the wage figures get used a second time under different rules. The post on personal injury files covers wage loss, repair estimates and receipts as a specials spreadsheet. Keep the two workbooks separate, since a comp benefit and a tort wage loss claim measure different things.

Converting a benefit payment history printout to rows

Convert a benefit payment history by uploading the printout on its own, exporting it to Excel, removing the repeated page headings, and checking that the row count and the grand total match the page. The printout is a system report with ruled or evenly spaced columns, so it is usually among the cleaner tables in the claim.

A payment history, sometimes called a pay log, indemnity ledger or payment screen print, lists each payment the carrier issued on the claim. Typical columns are the issue date, a check or transaction number, the payee, a benefit type code, the period the payment covers and the amount. Some carriers print a running total. Many print the codes with no key.

The steps:

1. Isolate the printout. Cut it from a copy of the claim file so that it is one document. Leave the original untouched.
2. Note the control figures first. Before you convert, write down the grand total printed on the last page and count the payment lines on one page by eye. These are what you will test the export against.
3. Upload and export to Excel. Schema Law accepts PDF, PNG or JPEG uploads up to 20 MB. Excel and CSV exports are included on every plan, and the pricing page shows the page allowance of each.
4. Strip the page furniture. Delete repeated column headings, page numbers, "continued" lines and the claim header that prints at the top of every page.
5. Split combined cells. The period covered often prints as one string, such as "03/03/26-03/16/26". Break it into a "from" column and a "through" column, both in a true date format.
6. Fix wrapped rows. A long payee name can wrap to a second line and create a row with no amount. Merge it into the row above.
7. Convert amounts to numbers. Remove currency signs and stray spaces. Turn amounts printed in parentheses or with a trailing minus into negative numbers, since those are usually voids or recoveries.
8. Test the result. Count the rows and sum the amount column. Both must equal the printed figures.

How a tool finds the columns in the first place, and why they sometimes merge, is explained in the guide to converting a scanned PDF to Excel.

Voided and reissued checks

Payment histories often show a check, a void of that check and a replacement. In the Ferreira printout, a payment of $1,224.80 issued on 2 April is followed by a line for negative $1,224.80 and then a new payment of the same amount on 9 April. All three rows belong in the workbook. Delete the void and the total will be wrong by one payment. Add a "status" column and mark the pair, so that a filter can show net payments without losing the history.

When the printout has no totals

Some screen prints end without a total. In that case, ask the adjuster or opposing counsel for the total paid to date by benefit type, or find it in a recent notice or filing. An independent figure to test against is worth the request. Without one, proofread every amount against the image.

Wage statements and payroll records for an average weekly wage

Wage statements and payroll records supply the earnings that the compensation rate is built on, so convert them with more care than any other page in the claim. Every later figure is multiplied from these numbers. Export them to a spreadsheet with one row per pay period and check each amount against the page.

How earnings become an average weekly wage is a legal question with a different answer in each system. Under the federal Longshore Act, for example, 33 U.S.C. 910 on the determination of pay makes the average weekly wage at the time of injury the basis for computing compensation and defines it as one fifty-second part of the employee's average annual earnings, with several methods for arriving at those annual earnings. State statutes use their own look-back periods and their own rules about what counts as wages. Read the statute that governs your claim before you choose which rows to add up. This post covers getting the numbers into cells correctly.

The inputs arrive in three forms.

• An agency wage statement. Where the state agency prescribes a form, the employer reports earnings on it. Texas, for instance, lists an "Employer's wage statement" (DWC003) in its index of workers' compensation forms. Expect a grid of pay periods, filled in by hand or typed into small boxes.
• A payroll register. A report from the employer's payroll system with a row per pay date and columns for regular pay, overtime, other earnings and deductions.
• Pay stubs. One page per pay period, each with current and year-to-date columns, sometimes photographed by the worker.

Columns that keep the legal question open

Because the definition of wages varies, do not collapse earnings into one figure while converting. Keep the types apart.

With the types in separate columns, whoever applies the statute can include or exclude overtime, bonuses, tips or the value of meals without going back to the scans.

Checks specific to wage records

• Gross equals its parts. Add a formula that sums regular, overtime and other pay for each row and compare it with the gross figure from the page. A mismatch points to a misread digit in that row.
• Year-to-date rises by the current amount. On pay stubs, each stub's year-to-date gross should equal the previous stub's plus the current gross. A break in that chain means a misread or a missing stub.
• No pay period is missing or doubled. Sort by period start and confirm that each period begins the day after the last one ended.
• Handwritten forms are read by eye. If the employer filled in the wage statement by hand, treat the converted figures as a draft and enter each from the image.
• Weeks and pay periods are not the same thing. A biweekly register has 26 rows a year. If your statute counts weeks, record the number of weeks each row covers in its own column.

As a pure arithmetic illustration: if a governing rule called for a 52-week average and the register's 26 biweekly rows summed to $47,736.00 of includable gross pay, the weekly figure would be $47,736.00 divided by 52, or $918.00. Whether that is the right method, the right period and the right set of earnings is for the statute and the lawyer.

What should each row of a comp benefits workbook record?

Each row of a comp benefits workbook should record one payment: when it was issued, what kind of benefit it was, the period it covers, the amount, and the page it came from. Add calculated columns for days covered and the implied weekly rate, which are what expose errors.

A layout that has enough columns to answer most questions:

Three of these columns do most of the work.

Benefit type as printed. Carriers abbreviate benefit types in their own ways, and the same letters can mean different things at different companies. Keep the printed code and your decoded reading side by side. If the adjuster later tells you a code means something else, you change one column and lose nothing.

Days covered. Payment periods are easy to misread by a day or a month. A row that claims to cover 44 days when every other row covers 14 has a misread date.

Implied weekly rate. Divide each payment by the weeks it covers. For a steady benefit, the answer should be the same on every row. A row that implies a different rate is either a rate change that should have a notice behind it, a partial-week payment, or a recognition error. Each is worth knowing about.

A tab for notices

Carriers send notices when benefits start, change or stop. These are letters and forms, so they do not convert to rows. Log them by hand in a second tab: date of notice, what it says changed, the effective date and the page. Then every change in the implied weekly rate on the payment tab should have a matching line on the notices tab.

A tab for inputs

Put the average weekly wage, the compensation rate, the date of injury and any figures from an award or order in a third tab, each with its source. Formulas elsewhere should point to these cells. When a judge or an agreement changes the rate, you update one cell.

Medical payment ledgers and bill review printouts

A medical payment ledger is the carrier's list of what it paid providers on the claim, and a bill review printout shows how each bill was reduced before payment. Convert both to spreadsheets when the dispute involves unpaid bills, a lien or the total medical paid. They are the widest tables in the file.

These are the carrier's records. Their columns reflect what the payer did.

Wide tables are where recognition is most likely to merge or shift columns, because eight or nine narrow columns of small type leave little white space between them. After exporting, test each row: billed minus reduction should equal allowed, where the printout uses those three columns. Rows that fail the test show you where a column slipped.

Reason codes need a key. Ask the carrier or its bill review vendor for the list of codes used on the printout. Guessing at a reduction reason from two letters is not safe.

Matching the ledger to the bills

The ledger shows what the carrier says it paid. The provider's own statement shows what the provider says is owed. Put the two in one workbook on separate tabs, and match them on provider, date of service and procedure code.

1. Sort both tabs by provider, then date of service, then code.
2. Mark each ledger row that has a matching bill line.
3. List bill lines with no ledger row. Those are unpaid or were never received by the carrier.
4. List ledger rows with no bill line. Those are bills you do not have, and should request.

In the Ferreira claim, the physical therapy provider's statement lists 24 visits and the ledger shows payment for 18. The six unmatched visits all fall after a treatment request was refused, which ties the unpaid balance to a specific dispute.

How do you reconcile a comp payment ledger against the periods of disability?

Reconcile the ledger by laying the periods the carrier paid for beside the periods the medical evidence says the worker was disabled, then looking for days that appear in one list and not the other. Unpaid days, overlapping payments and rate changes show up as mismatches between the two lists.

The disability periods do not come from the payment records. They come from work status forms, doctors' reports and the employer's records of when the worker was actually on the job. Build that table by hand first. For Ms. Ferreira it reads:

That is 4 weeks, then 6 weeks back at work, then 10 weeks off: 14 weeks off work in all.

Now summarize the payment tab by period covered, netting out the voided check.

At the weekly rate of $612.40 shown in the carrier's first notice, 14 weeks would come to $8,573.60. The ledger totals $7,961.20. The difference is $612.40, exactly one week, and the period tables show which one: 12 May to 18 May, the first week of the second spell off work.

That finding is a question, not a conclusion. The week may have been paid on a check issued after the printout was run. A waiting period or another rule in the governing statute may account for it. Or it may simply have been missed. The workbook tells you precisely what to ask and gives you the pages to attach.

A reconciliation checklist

• Every day in a disability period falls inside exactly one payment period, or you can say why not.
• No day is covered by two payments, apart from a void and its reissue.
• The implied weekly rate is constant, or each change matches a notice.
• Payments stop when the status table says the worker returned, and restart when it says she went off again.
• The sum of the amount column equals the carrier's stated total paid for that benefit type.
• Every payee other than the worker is explained.
• Each figure you intend to put in a filing has been read from the page image, not from the export.

Keep the reconciliation in its own tab with formulas that point back to the payment rows, so that a corrected date or amount flows through. When a new payment history arrives three months later, convert it the same way, paste in the new rows and the comparison updates itself.