Timesheet to Payroll Automation for UK SMEs: Extraction Pipeline

Every other Tuesday at the 40-person management consultancy, the payroll administrator's morning began the same way. Print 40 timesheet forms. Cross-reference each line against 23 project cost codes recorded in a spreadsheet he had built when the firm had 12 people. Flag the missing entries, calculate overtime against the Working Time Regulations thresholds he carried in his head, and export the result to Xero Payroll. Eleven hours, every fortnight.

The process was built for a firm a third the current size. As headcount grew, the cognitive load of holding 23 project codes, 40 employment contract variations, and a changing holiday calendar in mind simultaneously grew with it — and that is not a problem you solve by being more careful. The extraction pipeline we built for them cut those 11 hours to 18 minutes. Here is how it works.

What UK SME timesheet-to-payroll reconciliation actually involves: the five data sources no one connects automatically

The five data sources that make payroll reconciliation hard for UK SMEs are rarely treated as a system, which is why most automation attempts only address one or two of them.

Timesheet submissions — handwritten forms, individual Excel templates, Harvest exports, Toggl CSVs, or entries in a project management tool. No firm we have worked with uses a single format.

Project cost codes — an internal reference list, often a spreadsheet maintained by one person, mapping project names to budget codes. Rarely the same format across two firms.

Employment contracts — each employee's hourly rate, contracted hours, and holiday entitlement. Stored in HR software, signed PDFs, or an Excel sheet last updated two years ago.

HMRC reference data — employee tax codes, National Insurance categories, and active student loan deductions. These change during the year and must be pulled from HMRC's own systems, not assumed to be current in the payroll software.

Payroll run history — the previous period's payroll export, used to detect duplicate entries, carry forward leave balances, and flag anomalous hours.

Connecting all five without manual re-keying is the core engineering problem. The pipeline does it in under 20 minutes, with two short human approval steps that are faster precisely because the cross-referencing is already done.

Timesheet format variance: extracting from handwritten forms, Excel, Harvest, and Toggl exports with one pipeline

The pipeline ingests four timesheet formats through a single normalisation layer. For handwritten forms, a vision model (GPT-4o Vision) extracts employee name, date range, project code as written, and hours per day. Excel templates are parsed with openpyxl after a header-detection pass that handles the five slightly different column orders we encountered at this client. Harvest submissions arrive via the Harvest v2 API /time_entries endpoint. Toggl exports arrive as CSV.

Everything gets mapped to this normalisation schema:

{
  "employee_id": "string | null",
  "employee_name": "string",
  "period_start": "2026-09-01",
  "period_end": "2026-09-14",
  "entries": [
    {
      "date": "2026-09-03",
      "hours_worked": 8.5,
      "project_code_raw": "Tate retainer – Q3 strategy",
      "project_code_resolved": null,
      "task_description": "Board prep review, slide deck",
      "confidence": 0.71
    }
  ],
  "total_hours": 76.5,
  "source_format": "handwritten",
  "extraction_warnings": ["date field low confidence on row 4"]
}

The confidence field matters. Any entry below 0.75 goes into a review queue rather than being auto-resolved. For handwritten forms, the most common low-confidence triggers are illegible handwriting on date fields and project codes that don't match anything in the reference list.

Per-format training is not required. The vision model is prompted with a schema description and a few examples, not with a template for each document type. When we onboarded a client using a bespoke Google Form export, the change was a system prompt update, not a new model. See the invoice OCR case study for how the same schema-first approach works across unstructured financial documents.

LLM extraction for project code mapping: matching free-text task descriptions to cost centre codes without a lookup table

Project code mapping is where LLM-based extraction earns its cost. Staff write task descriptions like "Accenture rework Q3", "Consulting – Tate retainer", or "TL project – strategy phase 2" when the actual cost code is CC-047 (Tate & Lyle — Phase 2 Advisory). A simple lookup table fails because the surface forms are too varied and the list changes as new projects start.

The approach: embed the full project code reference list once per payroll period and run cosine similarity against each raw task description. Descriptions scoring above 0.88 against a single code are auto-resolved. Descriptions scoring between 0.65 and 0.88 against two or more candidates surface in the review queue. Descriptions below 0.65 are treated as unrecognised projects and trigger an alert.

def resolve_project_code(raw_description: str, code_embeddings: dict) -> dict:
    query_vec = embed(raw_description)
    scores = {
        code: cosine_similarity(query_vec, vec)
        for code, vec in code_embeddings.items()
    }
    ranked = sorted(scores.items(), key=lambda x: x[1], reverse=True)[:3]
    best_code, best_score = ranked[0]

    if best_score >= 0.88:
        return {"resolved": best_code, "confidence": best_score, "review": False}
    elif best_score >= 0.65:
        return {"resolved": None, "candidates": ranked, "confidence": best_score, "review": True}
    else:
        return {"resolved": None, "confidence": best_score, "review": True, "alert": "no_match"}

In production, the auto-resolve rate is 91% of timesheet lines. The remaining 9% reach the review queue with their top three candidate codes and the reviewer's historical choices pre-loaded as context. The previous all-manual process resolved 100% of lines with human effort — but also produced an average of two transcription errors per run that only surfaced at month-end budget reporting.

HMRC payroll compliance checkpoints: what automated extraction must verify before writing to payroll

Before any data reaches the payroll system, the pipeline runs four compliance checks.

Tax code validation — each employee's tax code is checked against the current HMRC format (1257L, K codes, D0, NT) and compared with the previous payroll run. A code changed without a corresponding P6 or P9 notification gets flagged. The pipeline queries HMRC's PAYE Online service for current codes rather than assuming the payroll software is up to date.

NI category check — employees approaching state pension age can move between NI categories mid-year. The pipeline flags employees within 12 months of their state pension birthday for manual verification.

RTI Full Payment Submission completeness — every employee who should receive payment must appear in the FPS, including those with zero variable hours. The pipeline validates the full employee list before the FPS is generated, not after.

Statutory payment triggers — hours below contracted threshold are cross-checked against leave records. A shortfall can mean authorised annual leave, SSP-triggering sickness, or a data entry gap. Each case routes to the appropriate flag rather than all short-hours entries being treated identically.

Overtime and holiday pay calculation rules: UK Working Time Regulations and the extraction logic that catches edge cases

The Working Time Regulations 1998 set a 48-hour average weekly limit across a 17-week reference period and specific daily rest requirements. For payroll extraction, three overtime scenarios need distinct handling:

Employees with WTR opt-out agreements under Regulation 5 are tracked as a flag in HR data. The 48-hour alert fires only for employees without an active opt-out, avoiding the false-positive problem that hits payroll tools applying WTR limits uniformly.

Bank holidays use separate calendars for England & Wales and Scotland. Employees based in Scotland get a different statutory holiday set. The calendar lookup is updated once per year from a JSON file that an ops team member refreshes each January.

Writing to payroll systems: Xero Payroll, Sage, Brightpay, and FreeAgent via API and file export

The four payroll systems we have integrated differ significantly in what they accept programmatically.

Brightpay's lack of a public API is the constraint we encounter most often. For Brightpay clients, the pipeline generates a CSV in the exact column format Brightpay's import wizard expects and deposits it in a watched folder. The import step still requires a human click — Brightpay does not support headless import as of September 2026 — but that is 90 seconds rather than 11 hours.

Xero Payroll is the most complete integration. The Xero Payroll API supports creating and updating pay runs, writing employee payslip lines, and triggering RTI submissions, all via authenticated REST calls. We use a service account OAuth token with payroll scope only — not full accounting access — which limits the blast radius if the token is ever compromised.

Human-in-the-loop design: the two approval steps you cannot automate and the interface that makes them fast

Two approval steps remain manual, and keeping them manual is a deliberate design choice rather than a limitation.

Step 1: Flagged line review. Any timesheet line with confidence below 0.75, or a project code in the 0.65–0.88 ambiguous zone, surfaces in a review queue. The interface shows the raw entry text, the top three candidate codes with similarity scores, and that employee's three most recent accepted assignments as context. A reviewer clicks a code or types a correction. At the consultancy, 7–9 lines surface per payroll run; average review time is 4 minutes.

Step 2: Totals sign-off. A summary screen shows total hours by cost centre, total gross pay by employee category, and anomalies — an employee whose hours are more than 25% above their prior-period average, or a cost centre that received no hours when it normally does. The payroll administrator signs off electronically. Sign-off state persists for 30 days for audit purposes.

This is not a rubber stamp. It is where business context catches errors no confidence score can surface: "Yes, Sarah worked 70 hours — the project overran." The OCR and human-in-the-loop design patterns post covers the review interface in more detail.

What changed in 2025–2026: HMRC Making Tax Digital payroll phase-in and real-time information requirements

HMRC's Making Tax Digital programme, which began with VAT returns, has extended into payroll real-time reporting. The practical change for automated pipelines: a stricter interpretation of the paymentDate field in Full Payment Submissions. Pipelines that defaulted paymentDate to the period end date produce RTI accuracy failures under the updated rules, because HMRC now cross-references the declared payment date against BACS settlement data.

From Q1 2026, HMRC's RTI system performs real-time NI category verification rather than deferring discrepancies to year-end. A pipeline that loads NI categories once at period start and holds them static can now produce incorrect FPS submissions if a category changes mid-period. The fix — refresh NI categories from HMRC on each payroll run — requires holding a valid PAYE credentials object rather than relying on a stale export.

The ICO's guidance on employment records updated its data minimisation expectations in 2025: automated pipelines should not retain raw timesheet images beyond the extraction stage without a specific audit justification. Store the structured output; delete the source document once extraction is confirmed.

Good / Bad / Ugly: three timesheet extraction designs and their payroll accuracy at month-end

Good: schema-first, confidence-gated, two-step approval

The design described above. All formats normalise to a single schema. Project codes resolve via embedding similarity with a confidence gate. Low-confidence entries surface for human review before writing. A totals sign-off catches business-level anomalies. At this consultancy after 12 months: 98.4% of payroll lines correct without human correction at the write stage. Two payroll errors total, both caught at sign-off before submission.

Bad: LLM end-to-end with no confidence gating

We audited a client's pipeline built by a previous contractor: GPT-4o was prompted to "read the timesheet and output payroll entries". No schema. No confidence scores. No rejection path. Measured accuracy was 87% at line level — which sounds acceptable until 13% of 40 employees across 26 entries is roughly 5 incorrect payroll lines per fortnight. Several involved wrong project codes that fed bad cost data into management accounts for three months before anyone noticed. Without confidence gating, there is no reliable signal about which lines to check.

Ugly: format-specific templates with manual fallback

The most common SME approach: build an Excel parser for your standard template, handle Harvest separately, tell everyone using something different to switch. Maintenance cost is high, templates break on column header changes, and the manual fallback for handwritten or non-standard formats means the original problem persists for part of your workforce. We have measured 70–80% automation coverage with this approach, with 20–30% still requiring full manual processing.

The AI invoice data extraction pipeline post shows how the same confidence-gating logic performs on unstructured financial documents.