scott-workbook-builder

Scott's Add-ins Workbook Builder

Build a finished Excel workbook populated with Scott's Add-ins formulas, driven off a single Inputs sheet so the user can re-point it at a new period or org without touching any formulas.

The companion skill scott-formulas documents the formula syntax. This skill is about producing the actual .xlsx file. Lean on scott-formulas/references/formula-reference.md for parameter order — don't guess at it.

When this skill applies

The trigger is the combination of two things: the user wants a file as output (.xlsx, "workbook", "spreadsheet", "build me a..."), AND they mention Scott's Add-ins, Scott's functions, or =SCOTT.* formulas. If only one of those is present, the other skill is probably the right one.

Workflow

1. Identify the report. P&L, Balance Sheet, Budget vs. Actual, or custom. If the user wasn't specific (e.g., "build me something using Scott's"), ask before building. For custom reports, sketch the structure in plain language and confirm with the user before writing any code.

2. Ask the user for their chart of accounts. This is required before building any report. Ask them with this exact phrasing:

   Please supply an Excel workbook that contains your organization's chart of accounts generated by using the =SCOTT.CHART custom function. Be sure the workbook is saved in a connected / authorized state to your accounting system.

   The chart workbook is the source of truth for which accounts exist, what they're called, and what type each one is (asset, liability, equity, revenue, expense, etc.). Every report this skill builds lists individual account codes from the chart — never SCOTT.RANGE(...) against guessed code ranges. See references/chart-of-accounts.md for how to parse the file the user supplies and how to map it onto report sections.

   If the user pushes back ("I don't have one ready," "can't you just figure it out"), explain that range-based reports miss accounts that fall outside the assumed range, mis-section accounts that don't follow the convention, and produce numbers that don't tie to the org's own books. The chart is a 30-second export; it's worth the round-trip.

3. Ask which accounting system the org is in — Xero or QuickBooks. This comes before the Org ID and chart-of-accounts asks. The answer controls system-specific behavior in the build (account-type names, default budget version, expected chart format, and which dimensional-tracking function to use). Always ask, even if the user dropped a hint about which system they use. Carry the answer through the conversation and write it as a row on the Inputs sheet (e.g., Accounting system: Xero) so the saved workbook records which system it was built against.

   Known Xero/QuickBooks divergences in function choice:

   Use case	Xero function(s)	QuickBooks function
   Single-account balance filtered by a dimension	SCOTT.XTRACK(org, account, category, option, start, end)	SCOTT.QCLASS(org, account, class_name, start, end, [include_sub_classes])
   Range of accounts filtered by a dimension	SCOTT.XTRACKR(...)	(use multiple QCLASS calls or aggregate manually)
   Single account, two dimensions	SCOTT.XTRACKM(...)	(QB doesn't have a two-dim equivalent — classes are flat with sub-classes)
   Discovering available dimensions for dropdowns	=SCOTT.LIST(org, "track") — returns Category / Option pairs	=SCOTT.LIST(org, "class") — returns flat list of classes. Also: "department", "location", "customer", "vendor" for those QB-specific list types.

   The conceptual difference: Xero tracking has a (category, option) two-level structure. QuickBooks classes are flat (with optional sub-classes that roll up via a flag on QCLASS). A "P&L by tracking" report for QB has one column per class; for Xero it's one column per option within a chosen category. Branch the skill's UI accordingly — don't ask Xero-style "pick a category, then pick options" if the org is QuickBooks. For QB, the user picks classes directly (one dropdown level, sourced from SCOTT.LIST(_, "class")).

4. Always ask the user for the Org ID — never default it. Even if you've worked with the user before in the conversation, ask for every new workbook. Users frequently work across multiple orgs and silently pulling the wrong one produces a workbook full of wrong numbers (or #VALUE, if the org isn't authorized) with no obvious sign anything's wrong. Ask explicitly with AskUserQuestion; don't infer it from context.

5. Gather period inputs. Period start and end, and any comparison periods. If the user didn't specify a period, default to the current month, with prior-year same month and YTD comparison columns. Tell the user what defaults you used so they can override.

6. Read the relevant report template. Each standard report has a reference file laying out the sheet structure, columns, and formulas to use:

   • P&L → references/pnl.md
   • Balance Sheet → references/balance-sheet.md
   • Budget vs. Actual → references/budget-vs-actual.md

   Read the file before building. It captures choices (sign convention, section structure, total formulas) that aren't worth reinventing each time.

7. Build the Inputs sheet first. Architecture is in references/inputs-sheet.md. Every formula on every other sheet must reference Inputs cells, never hardcoded dates or org IDs. This is the whole point — it's what makes the workbook reusable. Include a row recording the accounting system from step 3 (e.g., Accounting system: Xero).

8. Build the report sheet(s) with xlsxwriter, then run the _xldudf_ post-processor. This is non-negotiable — see "Critical: SCOTT.* needs two things or it doesn't work" below. For any cell containing a SCOTT.* formula, use worksheet.write_dynamic_array_formula() (not write_formula, not openpyxl). After wb.close(), call xldudf_prefix_scott_calls(out_path) to rewrite the formula text to the _xldudf_SCOTT_<NAME> form Excel expects. The complete working pattern — both pieces wired together — is in scripts/build_pattern.py. Use the xlsx skill for general file-construction guidance (/var/folders/_t/jx84c0f10jl1fty9jm4qwclr0000gn/T/claude-hostloop-plugins/c7a3a993eb61ab7c/skills/xlsx/SKILL.md). Pull SCOTT formula syntax from scott-formulas/references/formula-reference.md.

9. Format it. See references/formatting-standards.md. Bold headers, currency formatting on amounts, frozen header rows, sensible column widths, totals styled distinctly from line items. The output should look like a finished deliverable, not a draft.

10. Save and share. Save to the user's outputs folder. Share with a computer:// link. Use the handoff template in references/user-messages.md so the message is structured the same way every time and includes the first-open instructions.

User-facing copy

All clarifying questions, opening messages, handoff messages, and pushback responses follow the templates in references/user-messages.md. Read that file before your first interaction with the user in any conversation that triggers this skill — consistency in the user-facing voice matters more than ad-libbing.

Charts

Only build charts when the user explicitly asks. The reports look complete without them; adding one unprompted forces a layout decision the user didn't request. When the user does want a chart, place it on a dedicated sheet or in clearly delineated whitespace below the data — never overlapping cells with formulas.

Critical: SCOTT.* needs two things or it doesn't work

There are two distinct, both-required steps. Skipping either one produces a workbook that looks fine on save but is broken on open. Confirmed against Mac Excel + Scott's Add-ins.

Step 1 — Write cells as dynamic-array formulas

If you don't, Excel injects implicit-intersection (@) in front of every custom-function call: the user opens the file and sees [email protected](...) in the formula bar.

Use xlsxwriter. Its worksheet.write_dynamic_array_formula(cell, formula, fmt) method writes the proper cm="1" cell metadata reference and registers the xl/metadata.xml part automatically. This is the only reliable way.

Do not use:

• openpyxl — even with post-processing, getting the metadata exactly right is fragile.
• worksheet.write_array_formula() (xlsxwriter) — that's a CSE array, produces {=...} braces in the formula bar.
• worksheet.write_formula() for SCOTT.* cells — Excel will inject @.

Use write_formula() for ordinary formulas (=SUM(C7:C8), =C16-C21, =IFERROR(...)) — those don't need DA marking. Reserve write_dynamic_array_formula() for any cell whose formula contains SCOTT..

Step 2 — Rewrite the formula text to _xldudf_SCOTT_<NAME> after saving

Excel stores JavaScript custom-function calls in saved XML with an _xldudf_ prefix, with dots replaced by underscores: =SCOTT.GL(...) is stored as _xldudf_SCOTT_GL(...). When the user types the formula by hand, Excel's formula bar does this translation silently. xlsxwriter doesn't — it writes the literal SCOTT.GL.

Excel-on-load doesn't recognize the dot-form as a UDF call (the dot reads as a structured-reference operator). Result: every SCOTT cell shows #VALUE or #NAME on first open and only resolves when the user manually edits the cell.

The fix is a one-pass post-process after wb.close():

xldudf_prefix_scott_calls(out_path)

That function is defined in scripts/build_pattern.py. Always call it. The formula bar still shows the friendly SCOTT.GL form because Excel translates back at display time.

Some functions canonicalize under a different name than what users type. Most functions store unchanged (SCOTT.GL → _xldudf_SCOTT_GL), but a few don't. Known exceptions, confirmed by inspecting workbooks saved after typing the formula natively in the formula bar:

Friendly name (what users type)	Canonical name (what Excel stores)
SCOTT.XTRACK	_xldudf_SCOTT_TRACK (X stripped)
SCOTT.XTRACKM	_xldudf_SCOTT_TRACKM (X stripped)
SCOTT.XTRACKR	_xldudf_SCOTT_TRACKR (X stripped)
SCOTT.RANGE	_xldudf_SCOTT_COMRANGE (renamed entirely)

All other X-prefixed functions (XINVOICE, XBILL, XGLTRX, XPAYMENT, XNI, XINVOICE_HEADER, etc.) keep their friendly names verbatim. The post-processor in scripts/build_pattern.py handles all known mappings via a FUNC_NAME_MAP dict.

When you hit a fresh #NAME error despite the _xldudf_ prefix being applied, suspect a friendly-vs-canonical name mismatch: have the user type the formula manually in a blank cell, save the workbook, and inspect the canonical form in the saved sheet XML. Then add the new mapping to FUNC_NAME_MAP.

A complete working example combining both steps is in scripts/build_pattern.py. Copy that pattern.

Recurring gotchas

These come up on nearly every workbook. Internalize them so you don't have to look them up.

Org ID is a cell reference, not a named range. Use Inputs!$B$3 directly in SCOTT.* formulas (the cell where you put the Org ID on the Inputs sheet). Do not define a named range called OrgID. Other inputs (PeriodStart, PeriodEnd, BudgetVersion, etc.) are still named ranges — only Org ID uses the direct cell reference. The Org ID itself must always come from an explicit user answer (see workflow step 3) — never hardcode a value like 112353.

Spilling arrays. SCOTT.CHART, SCOTT.XINVOICE, SCOTT.XBILL, SCOTT.XGLTRX, SCOTT.XPAYMENT, and SCOTT.LIST return arrays that spill across cells. Leave empty space below and to the right of where you place them. Never put a spilling formula adjacent to other formulas — the spill will collide and Excel will throw #SPILL!.

Dates must be real Excel dates. On the Inputs sheet, period dates need to be Excel date serial values (formatted as a date), not text strings. In xlsxwriter, use worksheet.write_datetime(row, col, datetime_obj, date_format). If you write a string like "2026-04-01", the SCOTT formulas won't accept it.

Unknown account codes. When the user hasn't given you their specific chart of accounts, use SCOTT.RANGE for section totals (e.g., revenue 4000–4999, COGS 5000–5999, expenses 6000+ — these are common but not universal). Leave a clearly labeled placeholder section in the report where they can drop in specific account codes, and call this out in your handoff message. If precision matters more than speed, ask the user for their account structure first or use SCOTT.CHART to pull it.

No sign flipping. Don't wrap SCOTT.* calls in unary minus (=-SCOTT.GL(...)) and don't recommend SCOTT.NGL over SCOTT.GL as a reflex. Whatever Xero returns is what the user wants — leave the sign alone unless they specifically ask for revenue to read as positive.

Output requirements

The deliverable is always a .xlsx file saved to the outputs folder, shared back to the user via a computer:// link. Don't return the contents inline — the user wants the file.