Interhome tax workbook

Delegation model

Keep responsibilities narrow:

• Income child workflow: only extract normalized income rows from the payment-statement workbook
• Arrival child workflow: only extract normalized stay rows from the arrivals PDF
• Parent workflow: only merge the child outputs into the workbook

When the task tool is available, prefer spawning two subagents in parallel so extraction stays isolated:

• one subagent dedicated to the installed interhome-income-extraction workflow
• one subagent dedicated to the installed interhome-arrival-csv workflow

If those installed skills are not available in the current environment but local repo copies exist, read the local child SKILL.md files directly and run their bundled scripts yourself.

Expected child outputs

The income extraction must yield:

checkin_date,type,match_key,amount

The arrival extraction must yield:

arrival_date,leaving_date,invoice_no,guest,owner_booking

Target workbook rules

Populate the Income sheet with one row per arrival row, ordered by arrival_date.

Columns:

• Start Date = arrival_date
• Stop Date = leaving_date
• Number of Days
• Name of Tenant
• Booking ID
• Income
• Commission

Owner bookings

When owner_booking=true:

• Number of Days = 0
• Name of Tenant = Private
• Booking ID = invoice_no
• Income blank
• Commission blank

Non-private rows

When owner_booking=false:

• Number of Days should remain a formula: =DAYS(Bn,An)
• Name of Tenant = guest
• Booking ID = invoice_no
• Income = matched rental amount
• Commission = sum of matched non-rental amounts

Matching rules

Income matching for rental rows

Use the payment-statement rental rows to populate Income.

Priority:

1. same arrival date / checkin date
2. if multiple candidates exist, choose the best normalized guest-name match
3. if still ambiguous, record the ambiguity in Exceptions instead of guessing silently

Commission matching for non-rental rows

Use non-rental payment-statement rows (Charge, Adjustment) to populate Commission.

Priority:

1. direct numeric match_key equals the booking ID from arrivals
2. text match_key matches the guest name after normalization
3. blank match_key rows match by date proximity:
   • first rows inside the stay window
   • then rows shortly before the stay
   • then nearest non-private stay if still plausible

If the match is weak or ambiguous, keep the row in Exceptions.

Name normalization

For matching only:

• lowercase
• remove accents
• strip punctuation
• compare token sets without depending on token order
• allow short prefix matches for truncated names

Do not rewrite the visible tenant name in the workbook using the normalized form. Keep the PDF guest name as the visible tenant name.

Workbook preservation

• Preserve the template workbook structure and formulas
• Keep the summary formulas at the bottom of the Income sheet
• If a local workbook recalculation helper is available, use it after writing formulas. Otherwise rely on Excel / LibreOffice to recalculate when the user opens the workbook.

Workflow

1. Identify the tagged payment-statement .xlsx and arrivals .pdf.

2. If the user also tagged a workbook template override, use that as the source template. Otherwise use the bundled clean template.

3. Choose output paths for:

   • income.csv
   • arrivals.csv
   • populated workbook
   • merge_summary.json

4. Run the two child extraction workflows in parallel when possible.

5. Run the bundled merge script:

   PYENV_VERSION=3.10.14 python scripts/build_tax_workbook.py \
     --income-csv /path/to/income.csv \
     --arrivals-csv /path/to/arrivals.csv \
     --output /path/to/output.xlsx \
     --summary /path/to/merge_summary.json
   

   If the user supplied a template override, add:

   --template /path/to/user-template.xlsx
   

6. If soffice is available, recalculate the workbook. If it is not available locally, keep the formulas intact and say that recalculation could not be performed in this environment.

7. Return the workbook path and the summary path.

Notes for execution

• Do not let the extraction subagents edit the workbook.
• Do not require the user to supply a template unless they explicitly want to override the bundled one.
• Do not silently drop unmatched payment-statement rows.
• Prefer explicit exceptions over speculative matches.
• Create the workbook itself rather than returning a Markdown table.