Convert Scanned Schedule to Excel: Contract Schedules

To convert a scanned schedule to Excel, run the contract through OCR that detects tables, export a workbook, find the sheets that came from the schedule pages, and check every figure against the scan. Then add columns giving the schedule number, the page and the clause that relies on it, so each row can be traced.

A schedule is where a contract keeps its numbers: prices, fees, service levels, milestones, lists of sites and equipment. The clauses say that the customer pays "the Charges set out in Schedule 3". Schedule 3 says how much. That makes schedules the part of a scanned agreement most worth having in a spreadsheet, and also a part that needs the most checking after conversion.

Which contract schedules are worth putting in Excel?

A contract schedule is worth putting in Excel when someone will calculate with it, sort it, filter it or reconcile it against something else. Price lists, fee tables, milestone payments, service credits and asset lists qualify. A schedule you will only read once does not.

Ask three questions before starting:

1. Will anyone do arithmetic on it? Checking invoices against a rate card, totalling milestone payments, applying an annual uplift.
2. Will it be matched against other data? A list of 140 leased vehicles against the fleet register, or licensed sites against the company's actual locations.
3. Is it long enough that retyping is the slower route? Ten rows can be typed and proofread in the time it takes to check a conversion. Two hundred cannot.

One yes is enough to justify the conversion.

Prose and graphic attachments need different handling, and the post on OCR for contract exhibits covers them. Rent schedules in leases are worked through, with an arithmetic check, in the section on rent schedules and other tables inside a lease.

Converting a scanned price or fee schedule to a workbook

Convert a scanned price or fee schedule by uploading the whole agreement, exporting Excel, identifying which sheets came from the schedule's pages, and building one working sheet from them. Then prove the working sheet against the scan, row by row, before anyone uses a figure.

Steps

1. Find the schedule in the scan and note its pages. Write down the page of the PDF where it starts and ends, for example pages 34 to 37. Check the last page of the range: a schedule may end with notes that belong to the table.
2. Find the clauses that rely on it. Search the converted text for "Schedule 3". You need this for the sourcing columns later, and it tells you what the figures are for.
3. Convert the agreement whole. If you cut the schedule pages out into a separate file first, the page numbers in the output no longer match the signed PDF. Keeping the document intact means page 35 is page 35 everywhere.
4. Export Excel. In Schema Law the Excel export puts each table on its own sheet, with the rest of the text on another. Every plan includes it, the free one too, though the free plan takes one document a day of up to three pages. The pricing page has the page limits, and the guide to getting a scanned PDF into Excel describes the workbook and how tables are detected.
5. Pick out the schedule's sheets. Use the page range from step 1. Ignore tables from other parts of the contract for now.
6. Build a working sheet. Copy the schedule's rows into a new sheet in page order. Leave the exported sheets untouched as your record of what was read.
7. Bring in the qualifiers. Units, currency, "per user per month", "exclusive of tax" and footnotes may be printed above or below the grid and end up outside the table. Read the text around the table and add what applies as columns or notes.
8. Verify. See below.

A worked example

Pellam Logistics Ltd and Corvid Software, Inc. are fictional. Schedule 3 to their scanned services agreement is a rate card on pages 34 to 36. The printed schedule has 22 service lines, each with a unit, a unit price and a minimum monthly quantity.

The verification runs like this:

• Count rows. The working sheet has 21. Line 14 on the page wrapped onto two lines and was merged with line 15. Fixed from the image.
• Read every price against the image. Line 9 shows 4.50 on the page and 4.80 in the sheet. Corrected, and noted.
• Test any arithmetic the schedule itself contains. The page prints a minimum monthly charge for each line. Unit price times minimum quantity should equal it. Line 17 fails: 12.00 × 250 is 3,000 and the sheet says 8,000. The image shows 3,000.
• Check the total, if one is printed. The foot of page 36 gives a total minimum monthly charge, and the column sum in the sheet should match it. What a matching or a mismatched total tells you is covered under checking totals after extracting a table.
• Read the notes. A note under the table says prices rise each January by the percentage in clause 7.4. That goes into the working sheet as a note with its page.

Three corrections in 22 rows. Each would have survived a glance.

A disclosure schedule row may list a contract, a claim, or an exception the indemnification clause points to. Keep the schedule number, the row number, the name, and any amount together. Check each digit on the page image before you sort or total the sheet. A line that wraps is still one row.

If the schedule's own arithmetic fails on the image as well as in the sheet, the contract has an internal inconsistency. Record it and pass it to the lawyer. Do not fix it in the workbook.

Why do contract schedules convert worse than the clauses?

Contract schedules convert worse than the clauses because they are harder pages: smaller type, sideways layouts, grids with merged headings, and tables that run on across continuation sheets. Clauses are a single column of ordinary sentences, which is the easiest thing a page can ask of OCR.

The page's history can add to this. A schedule printed from a spreadsheet to fit the page, then photocopied before it was scanned, has lost sharpness at each generation, and small type suffers first.

Nothing here predicts how any tool will handle your particular pages. Run the schedule, look at the result beside the scan, and judge from that. When a page is too poor to trust, a better scan of the same page helps more than any amount of repair in the workbook: ask whoever holds the signed original to rescan the schedule pages alone, upright and at a higher resolution.

Why tables in general shift and merge is explained in the scanned PDF to Excel guide, under why columns shift or merge in the spreadsheet. The table in this section is limited to what is characteristic of schedules.

Keeping the schedule number and clause reference on every row

Put the schedule number, the item number, the scan page and the relying clause in columns on every row of the working sheet. A heading above the table is not enough, because rows get sorted, filtered and pasted into other workbooks, and the heading does not travel with them.

The columns to add, to the left of the data:

Then keep two versions of each figure: the cell as read, and the cleaned value you calculate with. If a price was read as 4.80 and you corrected it to 4.50 from the image, the as-read column still shows what the conversion produced and a note says why the working value differs. Anyone auditing the sheet can see every place a person intervened.

A row built this way reads as a complete statement by itself:

The relying clause earns its column when a figure is disputed. A unit price means little until you know whether clause 7 treats it as fixed, as a cap, or as a starting point for indexation. With the clause number on the row, the person reading the sheet knows where to look. What the clause means is for the lawyer.

Disclosure schedules that are lists, not tables

A disclosure schedule to a purchase or loan agreement may be a set of numbered lists and paragraphs keyed to sections of the agreement, with no grid. It comes out of OCR as text. To use one in Excel you build the rows yourself: one disclosed item per row.

A disclosure schedule might read: "Schedule 4.12 (Material Contracts)", followed by forty numbered agreements, then "Schedule 4.13 (Litigation): None." There are no columns on the page. The structure is the numbering.

The general technique for turning recognized lines into columns is set out in the post on OCR into Excel, in its section on turning recognized lines of text into spreadsheet columns. Disclosure schedules add a few points of their own.

Use the schedule number as the key. In this example Schedule 4.12 is keyed to section 4.12 of the agreement. Record both, even though they match, because a schedule may also refer to other sections.

One item per row, whole. An item that wraps over three printed lines is one row. Join the lines before splitting anything into columns.

Record "None". A schedule that says "None" is part of what was delivered. Give it a row.

Keep cross-references as written. "See item 3 of Schedule 4.7" stays in the row as text, with a column noting the schedule it points to.

Split out the obvious fields only. For a list of contracts: counterparty, title, date. Leave the full item text in its own column so nothing is lost in the splitting.

A layout:

The "Matched to" column is the reason to do this at all. A disclosure schedule in rows can be reconciled against what was actually provided: you can check whether every listed contract is in the data room, and whether a contract in the data room is missing from the list. What a gap means is for the lawyer. The post on OCR for data room documents covers making the room's files searchable and indexed for that purpose.

Proofread names and dates against the image as you would figures. A misread counterparty name will fail to match anything.

What happens to the workbook when an amendment replaces the schedule?

When an amendment replaces a schedule, the workbook gains a new version and keeps the old one. Never type new prices over old ones. Someone may need to check an invoice from before the amendment against the earlier figures, so both sets have to remain available and clearly labeled.

Start by reading what the amendment does, because there are two patterns:

• Whole replacement. "Schedule 3 is deleted in its entirety and replaced with the Schedule 3 attached to this Amendment." Convert the new schedule as a fresh set of rows.
• Partial change. "Lines 4 to 6 of Schedule 3 are amended as follows." Only those rows get new versions. The rest carry on.

Then record versions in the rows themselves. Three columns do it:

After Amendment No. 2 changes line 9, the sheet holds two rows for it:

Filter on Status to see the schedule as it stands. Filter on Effective from to see it as it stood on the date of a disputed invoice.

Some practical rules:

• Convert the amendment's schedule from the amendment's scan, with its own page numbers. Do not edit old rows to match a summary someone emailed.
• Check that the effective date comes from the amendment's text. It may differ from the signing date.
• Look for amendments you do not have. If you hold Amendment No. 1 and Amendment No. 3, ask whether there is a No. 2, since it may have touched the schedule.
• Rerun the checks. A replacement schedule gets the same row count, price-by-price reading and total test as the original.
• Date the workbook. Put "reflects documents through Amendment No. 2" on a cover sheet, with the name of the person who confirmed it.

Working out which of several amendments controls a given line, and in what order they apply, is its own task. The post on building a conformed copy of a contract deals with that. The workbook follows whatever that exercise concludes. It is a working tool built from the signed pages, and the signed pages remain the contract.