XLSX Workbench — Models, Recalc & Automation

When to Use

• Building or modifying financial/operational models that require strict formula hygiene.
• Auditing workbooks for formula errors or metadata before reviews.
• Batch recalculating formulas after programmatic modifications.
• Preparing scenario/assumption sheets with consistent color coding and formatting.

How to Apply

1. Duplicate the workbook into your task workspace and catalog existing formatting/assumptions.
2. Implement changes using pandas/openpyxl while keeping formulas in Excel (no hardcoded outputs).
3. Run python resources/scripts/recalc.py <workbook> to trigger LibreOffice recalculation.
4. Review the JSON output for errors; fix issues (refs, types, names) and rerun until clean.
5. Capture evidence (logs, diffs, summary notes) and store Local Memory records.

Success Criteria

• recalc.py reports status: success with no formula errors.
• Workbook retains original structure, formatting, and templates unless updates explicitly requested.
• Assumptions documented (comments/adjacent cells) referencing data sources.
• Evidence bundle includes recalculation log, diff summary, and manual validation notes.
• Local Memory entry records outcome with effectiveness ≥0.8 and artifact IDs.

0) Mission Snapshot — What / Why / Where / How / Result

• What: Provide a disciplined process for Excel workbook creation and editing with zero formula errors.
• Why: Ensures models remain dynamic, auditable, and compliant with finance standards.
• Where: Financial planning, forecasting, operations analytics, and reporting tasks across Cortex-OS.
• How: Use pandas/openpyxl for structure, recalc.py for validation, and documented standards for formatting.
• Result: Review-ready workbook with supporting evidence and no spreadsheet integrity issues.

1) Contract — Inputs → Outputs

Inputs: source workbook, change requests, assumption data, formatting rules. Outputs: updated workbook, recalculation log, summary report, Local Memory references.

2) Preconditions & Safeguards

• Confirm workbook version and baseline; snapshot before edits.
• Identify macros or external links; plan compatibility steps.
• Document color coding and number formats to preserve template conventions.

3) Implementation Playbook (RED→GREEN→REFACTOR)

1. Plan (RED): Map required changes, data sources, and scenario structure.
2. Implement (GREEN): Edit workbook using openpyxl/pandas while referencing formatting standards.
3. Validate (REFACTOR): Run recalc.py, inspect JSON output, fix issues, and repeat until clean; assemble evidence bundle.

4) Observability & Telemetry Hooks

• Log each recalculation run with timestamp, user, and workbook hash.
• Track number/type of formula errors across iterations.
• Store summary markdown with key metrics and assumptions.

5) Safety, Compliance & Governance

• Do not expose sensitive financial data in logs; redact before sharing.
• Keep track of manual overrides and schedule follow-up automation if needed.
• Align with RULES_OF_AI logging and documentation requirements.

6) Success Criteria & Acceptance Tests

• recalc.py returns success; manual spot checks confirm formulas intact.
• Scenario inputs and outputs behave as expected when altering assumptions.
• Reviewer checklist completed with no outstanding major/minor issues.
• Evidence Triplet stored: failing recalculation log, passing log, audit summary.

7) Failure Modes & Recovery

• LibreOffice not found: Install via Homebrew (brew install --cask libreoffice) and rerun script.
• Persistent formula errors: Use recalc JSON to pinpoint cell addresses; fix references or data types.
• Template drift: Compare with baseline workbook, copy formatting, rerun validations.
• Macro-heavy models: Document limitations; coordinate manual testing or use Windows Excel automation if required.

8) Worked Examples & Snippets

• Example pandas/openpyxl scripts for table updates and formula insertion.
• recalc.py usage with sample JSON output for quick triage.
• Pattern for logging changes and writing audit summaries.

9) Memory & Knowledge Integration

• Record each workbook cycle in Local Memory with key metrics (error count, assumptions changed).
• Link to related docx/pdf workbench entries for cross-document deliverables.
• Reference memory IDs in PRs and governance manifests.

10) Lifecycle & Versioning Notes

• Update skill when templates or color standards change; track versions in changelog.
• Extend to cover macros/VBA once governance approves automation.
• Evaluate integrating with cloud-based Excel APIs for remote recalculation.

11) References & Evidence

• resources/scripts/recalc.py — LibreOffice recalculation helper.
• LibreOffice documentation for headless recalculation.
• Task artefacts: recalculation logs, diff workbooks, audit summaries.

12) Schema Gap Checklist

• Add script to diff workbooks and summarize changes automatically.
• Integrate scenario generator scaffolding for common financial models.
• Provide template repository references for color/format standards.