Extracting Year-to-Date Totals From a Payslip

The current-period column and the year-to-date column contain the same set of fields, sit next to each other, and are identical in value on the first payslip of the year. That combination makes a column mis-assignment both easy to make and impossible to notice for months.

The same numbers, printed twice

Nearly every payslip prints each earnings and deduction row at least twice: once for this pay period and once cumulatively for the year. In the first period of a tax year those two numbers are equal, so an extractor that swaps them produces output that validates perfectly against every identity in the payslip schema page. By December the year-to-date figure is twenty-six times larger on a fortnightly payroll, at which point the swap is glaring — and has been in your data since January.

The consequence is that this is not a field-accuracy problem to be managed with confidence scores. It is a systematic error that is either right for every row on the sheet or wrong for every row, and it needs a structural check rather than a statistical one.

Tell them apart by geometry, not by label

The label is unreliable in three ways. It varies — “YTD”, “Year to Date”, “Y-T-D”, “This Year”, “Cumulative”, “Total to Date”. It is often printed once in a column header several rows above the data and never repeated, so a row-by-row extraction never sees it. And on compact templates the columns are headed only by “Current” and “YTD” in small type that is the first thing to go on a scanned copy.

What is reliable is that a column is a column. Every year-to-date value on the sheet shares an x-coordinate range, and that range overlaps the header cell that names it. So the robust procedure is: collect the horizontal extent of every numeric cell, cluster the extents into columns, order the columns left to right, and match that ordering against the ordered header cells. Assign by position within the ordering, and use the label only to confirm.

This matters most on the templates with more than two numeric columns. A payslip with Rate, Hours, Current and YTD has four, and a nearest-label match will attach the YTD header to whichever number happens to sit closest to it on a row where the hours field is blank. Some templates also print four columns as Current amount, Current YTD, Prior YTD and Difference on a corrected slip, where three of the four are cumulative figures. The general problem of recovering columns from coordinates rather than from text order is the same one described in PDF parsing.

A further complication is that many payslips carry cumulative figures in two places at different granularities: beside each line in the table, and again in a summary box that reports only gross, taxable gross, total tax and net. The two are not redundant — the summary box is often the only place a cumulative taxable figure appears at all, because the line table has no row for it. Extract both, and assert that the summary agrees with the sum of the corresponding lines; where a template prints only one of the two, record which, since a downstream reconciliation that expects the summary will otherwise silently receive nothing.

The check that actually proves it

With one payslip you can only test a weak property: for every row, the year-to-date value is greater than or equal to the current value. That catches a swap in November and passes it in January.

With two consecutive payslips from the same employer you get the real check:

for each field f:
    assert ytd[n][f] >= ytd[n-1][f]                  # monotonic
    assert ytd[n][f] - ytd[n-1][f] == current[n][f]  # exact

# and across the sheet, once:
    assert not all(ytd[n][f] == current[n][f] for f in fields)
        unless period_number == 1

The second assertion is the strong one. It ties three extracted numbers from two documents together, and there is no way to satisfy it with a swapped column assignment except in the first period of the year. The third guards that exception: if every field has an identical current and year-to-date value on a payslip that is not the first of the year, you have almost certainly read the same column twice.

This is worth doing even when you only need one payslip’s data, because it is the difference between an extraction you can defend and one you hope is right. If the previous payslip is not available, record that the strong check could not be run, rather than reporting the same confidence as a verified sheet.

When year-to-date legitimately drops

Monotonicity is a good assumption and it is not a law. The cases where it legitimately fails are the cases worth handling explicitly.

• The year boundary. Obvious, and less obvious than it looks: the year in question is a tax year, and a tax year is not always the calendar year. In the United Kingdom it begins on 6 April, so a payslip dated 10 April shows a near-zero cumulative figure and a full month’s pay.
• A change of employer or payroll provider. Cumulative figures usually restart at zero. Sometimes they are seeded with the prior employer’s totals where the jurisdiction requires continuity, which produces a large jump rather than a reset — the opposite signature.
• A correction run. An over-payment recovered, or a benefit reversed, can reduce a cumulative figure genuinely. These are rare enough to warrant review rather than silent acceptance.
• An off-cycle payment. A separate bonus payslip between two regular ones has its own current values and advances the cumulative figures, so consecutive regular payslips will not satisfy the exact-difference assertion unless the off-cycle slip is included in the sequence.
• Multiple employments with one employer. Separate assignments can carry separate cumulative figures that are also summarised in a combined block, and comparing across the two produces nonsense.

Reconciling against the W-2

The final payslip of the year is a preview of the annual tax form, and the comparison is a genuinely useful check — provided you expect the right differences rather than equality. Year-to-date taxable gross should correspond to the wages figure in the first box of a W-2, while the cumulative social insurance wages correspond to a different box that is subject to an annual cap, and cumulative Medicare-type wages to a third that is not capped.

Those three boxes differ from each other legitimately, for exactly the reasons set out in extracting box values from a W-2, so a reconciliation that expects them to match will report failures on correct data. Build the comparison as three separate checks against three separate cumulative fields, and treat a mismatch as a question to ask rather than as an error to correct automatically — the annual form is the filed document and the payslip is not.