xlsx

What this skill does

# Kortix XLSX — Spreadsheet Skill

You are loading the spreadsheet skill. Follow these instructions for ALL spreadsheet work.

---

## Autonomy Doctrine

**Act, don't ask.** Receive the task, build the spreadsheet, verify it, deliver it. No permission requests. No presenting options. Pick the best approach and execute.

- Write the Python script, run it, verify the output, clean up.
- If it fails, debug and retry. Only surface blockers after exhausting options.
- Every spreadsheet gets professional formatting by default — headers, borders, number formats, frozen panes, auto-width columns.
- Verify your own work: read the file back, check structure, run `recalc.py`, confirm zero errors.

---

## Communication Rules

**The user is non-technical. NEVER expose implementation details.**

**DO say:**
- "I'll create that spreadsheet for you"
- "Here's your budget spreadsheet with the calculations"
- "I've organized the data and the totals calculate automatically"
- "I've added a new sheet for Q2 data"

**NEVER say:**
- "I'll use openpyxl to create an .xlsx file"
- "I'm executing a Python script"
- "I'll load_workbook and update cells"
- "I'll use PatternFill and Font classes"
- "Running recalc.py to evaluate formulas"

**Tone:** Friendly, conversational. Describe WHAT the spreadsheet does, not HOW you built it. Make it feel effortless.

---

# Requirements for Outputs

## All Excel Files

### Professional Font
- Use a consistent, professional font (e.g., Arial, Calibri) for all deliverables unless otherwise instructed

### Zero Formula Errors
- Every Excel file MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)
- Run `scripts/recalc.py` on every file that contains formulas before delivering
- If errors are found, fix them and recalculate until clean

### Preserve Existing Templates (when updating)
- Study and EXACTLY match existing format, style, and conventions when modifying files
- Never impose standardized formatting on files with established patterns
- Existing template conventions ALWAYS override these guidelines

### Professional Styling (new files)
- Styled headers (dark fill, white bold text)
- Borders on all data cells
- Number formatting (currency, percentages, dates)
- Frozen header row (`ws.freeze_panes = "A2"`)
- Auto-fit column widths
- Alternating row fills for large datasets

## Financial Models

### Color Coding Standards
Unless otherwise stated by the user or existing template:

| Color | RGB | Use |
|---|---|---|
| Blue text | 0,0,255 | Hardcoded inputs, scenario-changeable numbers |
| Black text | 0,0,0 | ALL formulas and calculations |
| Green text | 0,128,0 | Links pulling from other worksheets |
| Red text | 255,0,0 | External links to other files |
| Yellow background | 255,255,0 | Key assumptions needing attention |

### Number Formatting Standards

| Type | Format | Example |
|---|---|---|
| Years | Text string | "2024" not "2,024" |
| Currency | `$#,##0` | Specify units in headers: "Revenue ($mm)" |
| Zeros | Dash format | `$#,##0;($#,##0);-` |
| Percentages | `0.0%` | One decimal default |
| Multiples | `0.0x` | EV/EBITDA, P/E ratios |
| Negative numbers | Parentheses | (123) not -123 |

### Formula Construction Rules

**Assumptions Placement:**
- Place ALL assumptions (growth rates, margins, multiples) in separate assumption cells
- Use cell references, not hardcoded values: `=B5*(1+$B$6)` not `=B5*1.05`

**Formula Error Prevention:**
- Verify all cell references are correct
- Check for off-by-one errors in ranges
- Ensure consistent formulas across all projection periods
- Test with edge cases (zero values, negative numbers)
- Verify no circular references

**Documentation Requirements for Hardcodes:**
- Add cell comments with source info: `"Source: [System/Document], [Date], [Reference], [URL]"`
- Examples:
  - "Source: Company 10-K, FY2024, Page 45, Revenue Note"
  - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"

---

# XLSX Creation, Editing, and Analysis

## CRITICAL: Use Formulas, Not Hardcoded Values

**Always use Excel formulas instead of calculating values in Python and hardcoding them.** The spreadsheet must remain dynamic and updateable.

```python
# WRONG — hardcoding calculated values
total = df['Sales'].sum()
sheet['B10'] = total  # Hardcodes 5000

# CORRECT — Excel formulas
sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'
```

This applies to ALL calculations — totals, percentages, ratios, differences. The spreadsheet should recalculate when source data changes.

## Execution Workflow

1. **Choose tool**: pandas for data analysis/bulk ops, openpyxl for formulas/formatting
2. **Create/Load**: New workbook or load existing
3. **Modify**: Add data, formulas, formatting
4. **Save**: Write to file
5. **Recalculate (MANDATORY for formulas)**: `python scripts/recalc.py output.xlsx`
6. **Verify**: Check recalc output JSON — if `errors_found`, fix and recalculate again
7. **Clean up**: Remove temp Python scripts
8. **Report**: Describe result in user-friendly language with file path

### Script Path Resolution

The `scripts/` directory lives alongside this SKILL.md file. When running recalc:
```bash
python <skill_dir>/scripts/recalc.py output.xlsx
```

Where `<skill_dir>` is the directory containing this SKILL.md (e.g., `skills/xlsx/` or `.opencode/skills/xlsx/`).

---

## Reading and Analyzing Data

### pandas (data analysis)
```python
import pandas as pd

df = pd.read_excel('file.xlsx')                          # First sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict

df.head()       # Preview
df.info()       # Column types
df.describe()   # Statistics

df.to_excel('output.xlsx', index=False)
```

### openpyxl (read with formulas preserved)
```python
from openpyxl import load_workbook

wb = load_workbook('file.xlsx')                    # Preserves formulas
wb_values = load_workbook('file.xlsx', data_only=True)  # Reads calculated values (WARNING: saving loses formulas)
```

---

## Creating New Excel Files

```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Sheet Name"

# Headers
headers = ["Product", "Revenue", "Cost", "Profit", "Margin %"]
header_fill = PatternFill('solid', start_color='1F4E79')
header_font = Font(bold=True, color='FFFFFF', name='Calibri', size=11)
header_align = Alignment(horizontal='center', vertical='center')

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = header_align

# Data with formulas
data = [
    ["Product A", 50000, 35000, "=B2-C2", "=IFERROR(D2/B2*100,0)"],
    ["Product B", 75000, 45000, "=B3-C3", "=IFERROR(D3/B3*100,0)"],
]

for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# Summary row (dynamic — never hardcode row numbers)
summary_row = len(data) + 2
last_data_row = summary_row - 1
ws.cell(row=summary_row, column=1, value="Total").font = Font(bold=True)
ws.cell(row=summary_row, column=2, value=f"=SUM(B2:B{last_data_row})")
ws.cell(row=summary_row, column=3, value=f"=SUM(C2:C{last_data_row})")
ws.cell(row=summary_row, column=4, value=f"=SUM(D2:D{last_data_row})")
ws.cell(row=summary_row, column=5, value=f"=IFERROR(D{summary_row}/B{summary_row}*100,0)")

# Borders
thin_border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)
for row in ws.iter_rows(min_row=1, max_row=summary_row, max_col=len(headers)):
    for cell in row:
        cell.border = thin_border

# Number formatting
for row in range(2, summary_row + 1):
    for col in [2, 3, 4]:
        ws.cell(row=row, column=col).number_format = '#,##0'
    ws.cell(row=row, column=