xlsx

name	xlsx
description	Use this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .csv, or .tsv file; create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path and wants something done to it or produced from it. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, or Google Sheets API integration.
license	Proprietary. LICENSE.txt has complete terms

Requirements for Outputs

All Excel files

Professional Font

• Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user

Zero Formula Errors

• Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)

Preserve Existing Templates (when updating templates)

• 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

Financial models

Color Coding Standards

Unless otherwise stated by the user or existing template

• Blue text (RGB: 0,0,255): Hardcoded inputs, and numbers users will change for scenarios
• Black text (RGB: 0,0,0): ALL formulas and calculations
• Green text (RGB: 0,128,0): Links pulling from other worksheets within same workbook
• Red text (RGB: 255,0,0): External links to other files
• Yellow background (RGB: 255,255,0): Key assumptions needing attention or cells that need to be updated

Number Formatting Standards

• Years: Format as text strings (e.g., "2024" not "2,024")
• Currency: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")
• Zeros: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")
• Percentages: Default to 0.0% format (one decimal)
• Multiples: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)
• Negative numbers: Use parentheses (123) not minus -123

Formula Construction Rules

• Place ALL assumptions in separate assumption cells
• Use cell references instead of hardcoded values in formulas
• Example: Use =B5*(1+$B$6) instead of =B5*1.05

XLSX creation, editing, and analysis

Overview

A user may ask you to create, edit, or analyze the contents of an .xlsx file.

Reading and analyzing data

import pandas as pd

df = pd.read_excel('file.xlsx')
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)

CRITICAL: Use Formulas, Not Hardcoded Values

Always use Excel formulas instead of calculating values in Python and hardcoding them.

❌ WRONG

total = df['Sales'].sum()
sheet['B10'] = total  # Hardcodes 5000

✅ CORRECT

sheet['B10'] = '=SUM(B2:B9)'

Common Workflow

1. Choose tool: pandas for data, openpyxl for formulas/formatting
2. Create/Load: Create new workbook or load existing file
3. Modify: Add/edit data, formulas, and formatting
4. Save: Write to file
5. Recalculate formulas (MANDATORY IF USING FORMULAS):

   python scripts/recalc.py output.xlsx
   

6. Verify and fix any errors

Creating new Excel files

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
sheet['B2'] = '=SUM(A1:A10)'
sheet['A1'].font = Font(bold=True, color='FF0000')
wb.save('output.xlsx')

Editing existing Excel files

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb.active
sheet['A1'] = 'New Value'
wb.save('modified.xlsx')

Recalculating formulas

python scripts/recalc.py <excel_file> [timeout_seconds]

The script:

• Automatically sets up LibreOffice macro on first run
• Recalculates all formulas in all sheets
• Scans ALL cells for Excel errors
• Returns JSON with detailed error locations and counts

Formula Verification Checklist

• Test 2-3 sample references: Verify they pull correct values
• Column mapping: Confirm Excel columns match (e.g., column 64 = BL)
• Row offset: Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)
• NaN handling: Check for null values with pd.notna()
• Division by zero: Check denominators before using /

Best Practices

Library Selection

• pandas: Best for data analysis, bulk operations, and simple data export
• openpyxl: Best for complex formatting, formulas, and Excel-specific features

Working with openpyxl

• Cell indices are 1-based
• Use data_only=True to read calculated values
• Warning: If opened with data_only=True and saved, formulas are permanently lost
• Formulas are preserved but not evaluated — use scripts/recalc.py to update values

Code Style Guidelines

• Write minimal, concise Python code without unnecessary comments
• Avoid verbose variable names and redundant operations
• For Excel files: add comments to cells with complex formulas, document data sources for hardcoded values

Dependencies

• pandas: Data analysis and manipulation
• openpyxl: Excel file creation and editing with formulas/formatting
• LibreOffice: Formula recalculation (via scripts/soffice.py)