jungho-git/xlsx

XLSX

• Shared examples and formatting reference: references/EXAMPLE.md.
• Keep new guidance, snippets, and edits aligned with that file.

---

Scope

Use this rule when:

• spreadsheet files are the primary input or output
• creating, editing, fixing, cleaning, converting, or restructuring .xlsx, .xlsm, .csv, or .tsv data
• validating formulas, formatting, workbook structure, or deliverable quality
• producing a spreadsheet deliverable rather than standalone code

The deliverable is the spreadsheet file, not only a script.

---

Tool Choice

• Use pandas for data analysis, cleaning, summarization, and CSV/TSV style operations.
• Use openpyxl for workbook structure, formulas, formatting, sheets, styles, and cell-level edits.
• Preserve existing templates with openpyxl when modifying workbooks.
• Use the provided scripts/recalc.py after formula edits or workbook creation.
• Use fs-mcp first for local file operations and metadata checks.
• Use scripts only as implementation helpers; deliver the resulting workbook.

Pick the tool by workbook contract, not convenience.

---

Template Preservation

• Study existing workbook structure, styles, formulas, named ranges, merged cells, filters, and sheet order before editing.
• Match existing format and conventions when updating templates.
• Do not impose standardized formatting on established templates.
• Preserve formulas unless the task requires changing them.
• Preserve hidden sheets, print settings, freeze panes, widths, and number formats when relevant.

Existing template conventions override generic styling rules.

---

Formula Rules

• Use Excel formulas for calculations instead of hardcoding Python-computed results.
• Put assumptions such as growth rates, margins, and multiples in separate cells.
• Use cell references in formulas.
• Verify references, ranges, row offsets, and column mappings.
• Check for off-by-one errors between DataFrame indexes and Excel rows.
• Ensure formulas are consistent across projection periods.
• Test edge cases such as zero values and negative values when the workbook model depends on them.
• Deliver zero formula errors: #REF!, #DIV/0!, #VALUE!, #N/A, and #NAME?.

Dynamic workbooks should recalculate when source data changes.

---

Financial Model Formatting

Unless the user or existing template says otherwise:

• Blue font: hardcoded inputs and scenario assumptions.
• Black font: formulas and calculations.
• Green font: links to other worksheets in the same workbook.
• Red font: external links to other files.
• Yellow fill: key assumptions or cells needing user attention.
• Years: text strings such as 2024.
• Currency: include units in headers, such as Revenue ($mm).
• Zeros: format as -.
• Percentages: one decimal place by default.
• Multiples: 0.0x.
• Negative numbers: parentheses, not leading minus.

Do not override a provided template's established financial model style.

---

Documentation In Workbook

• Document hardcoded assumptions with source, date, specific reference, and URL when available.
• Keep notes close to assumptions or in a clear assumptions section.
• Do not clutter calculation sheets with chat-style explanations.
• Use workbook comments or adjacent source cells only when they match local style.

Assumption provenance should be visible to workbook users.

---

Recalculation

• Recalculate formulas after creating or editing formulas.
• Use scripts/recalc.py when available.
• Review returned JSON for formula errors and locations.
• Fix all formula errors before handoff.
• If Excel or recalculation tooling is unavailable, state the blocker and residual risk.

Do not claim formula validity without recalculation or equivalent inspection.

---

Verification

• Confirm output file exists and opens logically.
• Confirm expected sheets are present.
• Confirm changed cells, formulas, and styles are in the intended locations.
• Confirm formulas recalculate or were checked.
• Confirm no known formula errors remain.
• For conversions, compare row counts, key columns, and representative values.
• For template edits, compare preserved style and sheet structure.

Verification must match the spreadsheet deliverable.

---

Boundaries

• Do not hardcode calculated values when formulas are required.
• Do not destroy template structure for convenience.
• Do not silently drop sheets, formulas, macros, named ranges, or styles.
• Do not deliver only a helper script when the user requested a workbook.
• Do not claim zero formula errors unless checked.