Xlsx

Requirements for Outputs

All Excel files

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

Industry-Standard Color Conventions

• 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

Required Format Rules

• 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

Assumptions Placement

• Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
• Use cell references instead of hardcoded values in formulas
• Example: Use =B5(1+$B$6) instead of =B51.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 unintended circular references

Documentation Requirements for Hardcodes

• Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
• Examples:
• "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
• "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
• "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
• "Source: FactSet, 8/20/2025, Consensus Estimates Screen"

XLSX creation, editing, and analysis

Overview

A user may ask you to create, edit, or analyze the contents of an .xlsx file. You have different tools and workflows available for different tasks.

Important Requirements

LibreOffice Required for Formula Recalculation: You can assume LibreOffice is installed for recalculating formula values using the recalc.py script. The script automatically configures LibreOffice on first run

Reading and analyzing data

Data analysis with pandas

For data analysis, visualization, and basic operations, use pandas which provides powerful data manipulation capabilities:

import pandas as pd

# Read Excel
df = pd.read_excel('file.xlsx')  # Default: first sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # All sheets as dict

# Analyze
df.head()      # Preview data
df.info()      # Column info
df.describe()  # Statistics

# Write Excel
df.to_excel('output.xlsx', index=False)

Excel File Workflows

CRITICAL: Use Formulas, Not Hardcoded Values

Always use Excel formulas instead of calculating values in Python and hardcoding them. This ensures the spreadsheet remains dynamic and updateable.

❌ WRONG - Hardcoding Calculated Values

# Bad: Calculating in Python and hardcoding result
total = df['Sales'].sum()
sheet['B10'] = total  # Hardcodes 5000

# Bad: Computing growth rate in Python
growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
sheet['C5'] = growth  # Hardcodes 0.15

# Bad: Python calculation for average
avg = sum(values) / len(values)
sheet['D20'] = avg  # Hardcodes 42.5

✅ CORRECT - Using Excel Formulas

# Good: Let Excel calculate the sum
sheet['B10'] = '=SUM(B2:B9)'

# Good: Growth rate as Excel formula
sheet['C5'] = '=(C4-C2)/C2'

# Good: Average using Excel function
sheet['D20'] = '=AVERAGE(D2:D19)'

This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes.

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): Use the recalc.py script

   python recalc.py output.xlsx

6. Verify and fix any errors:

• The script returns JSON with error details
• If status is errors_found, check error_summary for specific error types and locations
• Fix the identified errors and recalculate again
• Common errors to fix:
• #REF!: Invalid cell references
• #DIV/0!: Division by zero
• #VALUE!: Wrong data type in formula
• #NAME?: Unrecognized formula name

Creating new Excel files

# Using openpyxl for formulas and formatting
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active

# Add data
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
sheet.append(['Row', 'of', 'data'])

# Add formula
sheet['B2'] = '=SUM(A1:A10)'

# Formatting
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
sheet['A1'].alignment = Alignment(horizontal='center')

# Column width
sheet.column_dimensions['A'].width = 20

wb.save('output.xlsx')

Editing existing Excel files

# Using openpyxl to preserve formulas and formatting
from openpyxl import load_workbook

# Load existing file
wb = load_workbook('existing.xlsx')
sheet = wb.active  # or wb['SheetName'] for specific sheet

# Working with multiple sheets
for sheet_name in wb.sheetnames:
    sheet = wb[sheet_name]
    print(f"Sheet: {sheet_name}")

# Modify cells
sheet['A1'] = 'New Value'
sheet.insert_rows(2)  # Insert row at position 2
sheet.delete_cols(3)  # Delete column 3

# Add new sheet
new_sheet = wb.create_sheet('NewSheet')
new_sheet['A1'] = 'Data'

wb.save('modified.xlsx')

Recalculating formulas

Excel files created or modified by openpyxl contain formulas as strings but not calculated values. Use the provided recalc.py script to recalculate formulas:

python recalc.py <excel_file> [timeout_seconds]

Example:

python recalc.py output.xlsx 30

The script:

• Automatically sets up LibreOffice macro on first run
• Recalculates all formulas in all sheets
• Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.)
• Returns JSON with detailed error locations and counts
• Works on both Linux and macOS

Formula Verification Checklist

Quick checks to ensure formulas work correctly:

Essential Verification

• [ ] Test 2-3 sample references: Verify they pull correct values before building full model
• [ ] Column mapping: Confirm Excel columns match (e.g., column 64 = BL, not BK)
• [ ] Row offset: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)

Common Pitfalls

• [ ] NaN handling: Check for null values with pd.notna()
• [ ] Far-right columns: FY data often in columns 50+
• [ ] Multiple matches: Search all occurrences, not just first
• [ ] Division by zero: Check denominators before using / in formulas (#DIV/0!)
• [ ] Wrong references: Verify all cell references point to intended cells (#REF!)
• [ ] Cross-sheet references: Use correct format (Sheet1!A1) for linking sheets

Formula Testing Strategy

• [ ] Start small: Test formulas on 2-3 cells before applying broadly
• [ ] Verify dependencies: Check all cells referenced in formulas exist
• [ ] Test edge cases: Include zero, negative, and very large values

Interpreting recalc.py Output

The script returns JSON with error details:

{
  "status": "success", // or "errors_found"
  "total_errors": 0, // Total error count
  "total_formulas": 42, // Number of formulas in file
  "error_summary": {
    // Only present if errors found
    "#REF!": {
      "count": 2,
      "locations": ["Sheet1!B5", "Sheet1!C10"]
    }
  }
}

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 (row=1, column=1 refers to cell A1)
• Use data_only=True to read calculated values: load_workbook('file.xlsx', data_only=True)
• Warning: If opened with data_only=True and saved, formulas are replaced with values and permanently lost
• For large files: Use read_only=True for reading or write_only=True for writing
• Formulas are preserved but not evaluated - use recalc.py to update values

Working with pandas

• Specify data types to avoid inference issues: pd.read_excel('file.xlsx', dtype={'id': str})
• For large files, read specific columns: pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])
• Handle dates properly: pd.read_excel('file.xlsx', parse_dates=['date_column'])

Code Style Guidelines

IMPORTANT: When generating Python code for Excel operations:

• Write minimal, concise Python code without unnecessary comments
• Avoid verbose variable names and redundant operations
• Avoid unnecessary print statements

For Excel files themselves:

• Add comments to cells with complex formulas or important assumptions
• Document data sources for hardcoded values
• Include notes for key calculations and model sections

Dependencies for XLSX Skill

Overview

The XLSX skill requires Python packages for spreadsheet manipulation and LibreOffice for formula recalculation. All dependencies are optional in the sense that tests will skip gracefully if they are not installed, but full functionality requires all dependencies.

Python Packages

pandas >= 1.5.0

Purpose: Data analysis, manipulation, and basic Excel I/O

Features Used:

• Reading Excel files: pd.read_excel()
• Writing Excel files: df.to_excel()
• Data analysis and statistics
• CSV/TSV file handling

Installation:

pip install pandas

openpyxl >= 3.0.0

Purpose: Advanced Excel file manipulation with formula and formatting support

Features Used:

• Creating and loading workbooks
• Cell-level formula insertion
• Font, fill, and alignment styling
• Column/row dimension control
• Multiple worksheet management
• Preserving existing formulas when editing

Installation:

pip install openpyxl

Note: openpyxl is the default engine for pandas Excel operations on .xlsx files.

System Packages

LibreOffice (Version 6.0+)

Purpose: Formula recalculation engine for the recalc.py script

Why Required: Excel formulas inserted by openpyxl are stored as strings. LibreOffice's calculation engine evaluates these formulas and saves the computed values back to the file.

Commands Used:

• soffice - LibreOffice headless mode for automation
• StarBasic macro execution for calculateAll() and store()

Installation:

macOS

# Via Homebrew
brew install --cask libreoffice

# Manual download
# Download from https://www.libreoffice.org/download/download/
# Install the .dmg file

Linux (Ubuntu/Debian)

sudo apt-get update
sudo apt-get install libreoffice

Linux (Fedora/RHEL)

sudo dnf install libreoffice

Windows

# Via Chocolatey
choco install libreoffice

# Manual download
# Download from https://www.libreoffice.org/download/download/
# Run the installer

Size Note: LibreOffice is approximately 500MB download, 1.5GB installed.

Optional Dependencies

gtimeout (macOS only)

Purpose: Timeout support for macOS (Linux has timeout built-in)

Installation:

brew install coreutils

Note: The recalc.py script works without gtimeout on macOS, but timeout protection will be disabled.

Dependency Installation

Quick Install (All Dependencies)

# Install Python packages
pip install pandas openpyxl

# Install LibreOffice (choose your platform)

# macOS
brew install --cask libreoffice
brew install coreutils  # Optional: for timeout support

# Linux (Ubuntu/Debian)
sudo apt-get install libreoffice

# Linux (Fedora)
sudo dnf install libreoffice

# Windows (Chocolatey)
choco install libreoffice

Minimal Install (Data Analysis Only)

If you only need data analysis without formula recalculation:

pip install pandas openpyxl

This provides full functionality except formula recalculation via recalc.py.

Dependency Verification

Verify Python Packages

# Check pandas
python -c "import pandas; print(f'pandas {pandas.__version__}')"

# Check openpyxl
python -c "import openpyxl; print(f'openpyxl {openpyxl.__version__}')"

Expected output:

pandas 2.0.0
openpyxl 3.1.2

Verify LibreOffice

# Check LibreOffice is installed
soffice --version

# Test headless mode
soffice --headless --terminate_after_init

Expected output:

LibreOffice 7.5.3.2 10(Build:2)

Automated Verification

Use the provided verification script:

cd .claude/skills
python common/verification/verify_skill.py xlsx

Expected output if all dependencies installed:

Verifying xlsx skill dependencies...

Python packages:
  pandas: Installed
  openpyxl: Installed

System commands:
  soffice: Available

✓ xlsx skill is ready

Troubleshooting

LibreOffice Not Found

Symptom: soffice: command not found

Solution (macOS):

# Add LibreOffice to PATH
echo 'export PATH="/Applications/LibreOffice.app/Contents/MacOS:$PATH"' >> ~/.zshrc
source ~/.zshrc

# Or create symbolic link
sudo ln -s /Applications/LibreOffice.app/Contents/MacOS/soffice /usr/local/bin/soffice

Solution (Linux):

# LibreOffice should install to /usr/bin/soffice
# If not, reinstall
sudo apt-get install --reinstall libreoffice

pandas ImportError

Symptom: ImportError: No module named 'pandas'

Solution:

# Ensure pip is up to date
pip install --upgrade pip

# Install pandas
pip install pandas

# If using virtual environment, activate it first
source venv/bin/activate  # Unix
venv\Scripts\activate  # Windows

openpyxl ImportError

Symptom: ImportError: No module named 'openpyxl'

Solution:

pip install openpyxl

# If using pandas, ensure openpyxl is in same environment
pip install pandas openpyxl

LibreOffice Macro Not Configured

Symptom: recalc.py returns error about macro not configured

Solution: Run recalc.py once with any file. The script automatically sets up the required macro:

# Create a test file
python -c "from openpyxl import Workbook; wb = Workbook(); wb.save('test.xlsx')"

# Run recalc.py - this will set up the macro
python .claude/skills/xlsx/scripts/recalc.py test.xlsx

# Clean up
rm test.xlsx

Permission Denied on recalc.py

Symptom: Permission denied: recalc.py

Solution:

chmod +x .claude/skills/xlsx/scripts/recalc.py

Platform-Specific Notes

macOS

• LibreOffice installs to /Applications/LibreOffice.app
• May need to add soffice to PATH (see troubleshooting)
• gtimeout via coreutils recommended but optional

Linux

• LibreOffice typically pre-installed on many distributions
• timeout command built-in
• Headless mode works without display server

Windows

• recalc.py has limited timeout support on Windows
• Formula recalculation still works without timeout
• Consider WSL for full Unix-like experience

Docker Support

If you want to run the XLSX skill in a container:

FROM python:3.11-slim

# Install LibreOffice
RUN apt-get update && apt-get install -y \
    libreoffice \
    libreoffice-calc \
    && rm -rf /var/lib/apt/lists/*

# Install Python packages
RUN pip install pandas openpyxl

# Copy skill files
COPY .claude/skills/xlsx /app/xlsx

WORKDIR /app

Dependency Matrix

Minimum Requirements

For basic data analysis: pandas only For formula creation: pandas + openpyxl For complete functionality: pandas + openpyxl + LibreOffice

CI/CD Integration

For automated testing in CI environments:

# GitHub Actions example
- name: Install dependencies
  run: |
    pip install pandas openpyxl pytest
    sudo apt-get install -y libreoffice

- name: Test XLSX skill
  run: pytest .claude/skills/xlsx/tests/

Note: Tests skip gracefully if LibreOffice is not available.

Version Compatibility

Security Considerations

LibreOffice Macro Security: The recalc.py script creates a StarBasic macro for formula recalculation. This macro only calls calculateAll() and store() - no network access, no file system operations beyond the target file.

Untrusted Excel Files: When opening Excel files from untrusted sources, be aware that openpyxl does not execute VBA macros, but malicious formulas could still be present. Use the recalc.py script to verify zero formula errors.

Support and Resources

• pandas documentation: https://pandas.pydata.org/docs/
• openpyxl documentation: https://openpyxl.readthedocs.io/
• LibreOffice documentation: https://documentation.libreoffice.org/
• Verification script: ~/.amplihack/.claude/skills/common/verification/verify_skill.py

XLSX Skill Usage Examples

This document provides 10 comprehensive examples demonstrating the XLSX skill's capabilities, from basic spreadsheet creation to advanced financial modeling.

Example 1: Basic Spreadsheet Creation

Task: Create a simple expense tracker with totals.

User Request: "Create an Excel file tracking my monthly expenses with categories and totals."

Implementation:

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

wb = Workbook()
sheet = wb.active
sheet.title = "Expenses"

# Headers
sheet['A1'] = 'Category'
sheet['B1'] = 'Amount'
sheet['A1'].font = Font(bold=True)
sheet['B1'].font = Font(bold=True)

# Data
expenses = [
    ('Rent', 1200),
    ('Groceries', 400),
    ('Utilities', 150),
    ('Transportation', 200)
]

row = 2
for category, amount in expenses:
    sheet[f'A{row}'] = category
    sheet[f'B{row}'] = amount
    row += 1

# Total row with formula
sheet[f'A{row}'] = 'Total'
sheet[f'B{row}'] = f'=SUM(B2:B{row-1})'  # Formula, not hardcoded
sheet[f'A{row}'].font = Font(bold=True)
sheet[f'B{row}'].font = Font(bold=True)

wb.save('expenses.xlsx')

Recalculate formulas:

python .claude/skills/xlsx/scripts/recalc.py expenses.xlsx

Result: Excel file with expense categories and a SUM formula for total expenses.

---

Example 2: Revenue Projection Model

Task: Create a 5-year revenue projection with growth rates.

User Request: "Build a financial model showing revenue growth over 5 years with 15% annual growth."

Implementation:

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
sheet = wb.active
sheet.title = "Revenue Projection"

# Headers
sheet['A1'] = 'Metric'
years = ['2024', '2025', '2026', '2027', '2028']
for idx, year in enumerate(years, start=2):
    sheet.cell(1, idx, year)
    sheet.cell(1, idx).font = Font(bold=True)

# Assumptions section
sheet['A2'] = 'Assumptions'
sheet['A2'].font = Font(bold=True, color='0000FF')  # Blue for inputs

sheet['A3'] = 'Base Revenue'
sheet['B3'] = 1000000  # Base revenue
sheet['B3'].font = Font(color='0000FF')  # Blue for input

sheet['A4'] = 'Growth Rate'
sheet['B4'] = 0.15  # 15% growth
sheet['B4'].font = Font(color='0000FF')
sheet['B4'].number_format = '0.0%'

# Revenue projection with formulas
sheet['A6'] = 'Revenue'
sheet['A6'].font = Font(bold=True)

# Year 1 references assumption
sheet['B6'] = '=$B$3'  # Reference base revenue
sheet['B6'].font = Font(color='000000')  # Black for formula

# Years 2-5 with growth formula
for col in range(3, 7):  # Columns C through F
    prev_col = chr(ord('A') + col - 2)
    sheet.cell(6, col, f'={prev_col}6*(1+$B$4)')  # Growth formula
    sheet.cell(6, col).font = Font(color='000000')
    sheet.cell(6, col).number_format = '$#,##0'

# Format all revenue cells as currency
for col in range(2, 7):
    sheet.cell(6, col).number_format = '$#,##0'

wb.save('revenue_projection.xlsx')

Recalculate:

python .claude/skills/xlsx/scripts/recalc.py revenue_projection.xlsx

Result: Professional financial model with blue inputs, black formulas, and currency formatting.

---

Example 3: Data Analysis with pandas

Task: Analyze sales data and create summary statistics.

User Request: "Load sales data from CSV and create an Excel report with statistics."

Implementation:

import pandas as pd

# Sample data (in practice, load from CSV)
data = {
    'Product': ['Widget A', 'Widget B', 'Widget C'] * 12,
    'Month': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
              'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'] * 3,
    'Sales': [5000, 5200, 4800, 5500, 6000, 6200,
              6500, 6100, 5900, 6300, 6700, 7000,
              3000, 3100, 2900, 3200, 3400, 3500,
              3600, 3300, 3200, 3500, 3700, 3800]
}

df = pd.DataFrame(data)

# Calculate summary statistics
summary = df.groupby('Product')['Sales'].agg([
    ('Total Sales', 'sum'),
    ('Average', 'mean'),
    ('Min', 'min'),
    ('Max', 'max')
]).reset_index()

# Create Excel file with multiple sheets
with pd.ExcelWriter('sales_analysis.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Raw Data', index=False)
    summary.to_excel(writer, sheet_name='Summary', index=False)

print("Sales analysis created: sales_analysis.xlsx")

Result: Multi-sheet Excel workbook with raw data and summary statistics.

---

Example 4: Financial Model with Color Coding

Task: Create an income statement with proper color coding standards.

User Request: "Build an income statement following financial modeling best practices."

Implementation:

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

wb = Workbook()
sheet = wb.active
sheet.title = "Income Statement"

# Color definitions
BLUE = Font(color='0000FF')      # Inputs
BLACK = Font(color='000000')     # Formulas
GREEN = Font(color='008000')     # Internal links
YELLOW_BG = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# Headers
sheet['A1'] = 'Income Statement'
sheet['A1'].font = Font(bold=True, size=14)
sheet['A2'] = '($000s)'
sheet['B2'] = '2024'

# Assumptions (Blue inputs)
sheet['A4'] = 'Assumptions'
sheet['A4'].font = Font(bold=True)
sheet['A5'] = 'Revenue Growth'
sheet['B5'] = 0.12
sheet['B5'].font = BLUE
sheet['B5'].number_format = '0.0%'
sheet['B5'].fill = YELLOW_BG  # Key assumption

sheet['A6'] = 'COGS %'
sheet['B6'] = 0.40
sheet['B6'].font = BLUE
sheet['B6'].number_format = '0.0%'

sheet['A7'] = 'OpEx %'
sheet['B7'] = 0.25
sheet['B7'].font = BLUE
sheet['B7'].number_format = '0.0%'

# Income Statement (Black formulas)
sheet['A9'] = 'Revenue'
sheet['B9'] = 10000  # Base revenue (could be blue input)
sheet['B9'].font = BLUE
sheet['B9'].number_format = '$#,##0'

sheet['A10'] = 'COGS'
sheet['B10'] = '=-B9*$B$6'  # Formula
sheet['B10'].font = BLACK
sheet['B10'].number_format = '$#,##0;($#,##0);-'

sheet['A11'] = 'Gross Profit'
sheet['B11'] = '=B9+B10'  # COGS is negative
sheet['B11'].font = BLACK
sheet['B11'].number_format = '$#,##0'

sheet['A12'] = 'Operating Expenses'
sheet['B12'] = '=-B9*$B$7'
sheet['B12'].font = BLACK
sheet['B12'].number_format = '$#,##0;($#,##0);-'

sheet['A13'] = 'EBITDA'
sheet['B13'].font = Font(bold=True)
sheet['B13'] = '=B11+B12'
sheet['B13'].font = BLACK
sheet['B13'].number_format = '$#,##0'

# Column width
sheet.column_dimensions['A'].width = 25
sheet.column_dimensions['B'].width = 15

wb.save('income_statement.xlsx')

Recalculate:

python .claude/skills/xlsx/scripts/recalc.py income_statement.xlsx

Result: Professional income statement with proper color coding (blue inputs, black formulas, yellow highlighting for key assumptions).

---

Example 5: Multi-Year Budget Model

Task: Create a detailed budget with multiple categories over 3 years.

User Request: "Build a budget model for my business with quarterly breakdown for 3 years."

Implementation:

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

wb = Workbook()
sheet = wb.active
sheet.title = "Budget Model"

# Headers
sheet['A1'] = 'Budget Model 2024-2026'
sheet['A1'].font = Font(bold=True, size=14)

# Time periods
periods = ['Q1 2024', 'Q2 2024', 'Q3 2024', 'Q4 2024',
           'Q1 2025', 'Q2 2025', 'Q3 2025', 'Q4 2025',
           'Q1 2026', 'Q2 2026', 'Q3 2026', 'Q4 2026']

sheet['A3'] = 'Category'
for idx, period in enumerate(periods, start=2):
    cell = sheet.cell(3, idx, period)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal='center')

# Budget categories with formulas
categories = {
    'Revenue': 100000,
    'Cost of Sales': -40000,
    'Salaries': -30000,
    'Rent': -5000,
    'Marketing': -8000,
    'Utilities': -2000
}

row = 4
for category, base_amount in categories.items():
    sheet.cell(row, 1, category)
    sheet.cell(row, 1).font = Font(bold=category == 'Revenue')

    # Q1 2024 - base value
    sheet.cell(row, 2, base_amount)
    sheet.cell(row, 2).font = Font(color='0000FF')  # Blue input
    sheet.cell(row, 2).number_format = '$#,##0;($#,##0);-'

    # Subsequent quarters - growth formula (2% per quarter)
    for col in range(3, 14):
        prev_col = chr(ord('A') + col - 2)
        sheet.cell(row, col, f'={prev_col}{row}*1.02')  # 2% growth
        sheet.cell(row, col).font = Font(color='000000')  # Black formula
        sheet.cell(row, col).number_format = '$#,##0;($#,##0);-'

    row += 1

# Net Income row
sheet.cell(row, 1, 'Net Income')
sheet.cell(row, 1).font = Font(bold=True)
for col in range(2, 14):
    col_letter = chr(ord('A') + col - 1)
    sheet.cell(row, col, f'=SUM({col_letter}4:{col_letter}{row-1})')
    sheet.cell(row, col).font = Font(bold=True, color='000000')
    sheet.cell(row, col).number_format = '$#,##0'

# Set column widths
sheet.column_dimensions['A'].width = 20
for col in range(2, 14):
    sheet.column_dimensions[chr(ord('A') + col - 1)].width = 12

wb.save('budget_model.xlsx')

Recalculate:

python .claude/skills/xlsx/scripts/recalc.py budget_model.xlsx

Result: Comprehensive budget model with quarterly projections and automatic totals.

---

Example 6: DCF Valuation Model

Task: Build a discounted cash flow valuation model.

User Request: "Create a DCF model to value a company with 5-year projections."

Implementation:

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

wb = Workbook()
sheet = wb.active
sheet.title = "DCF Model"

BLUE = Font(color='0000FF')
BLACK = Font(color='000000')
YELLOW = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# Title
sheet['A1'] = 'DCF Valuation Model'
sheet['A1'].font = Font(bold=True, size=14)

# Years
years = ['2024', '2025', '2026', '2027', '2028']
sheet['A3'] = 'Year'
for idx, year in enumerate(years, start=2):
    sheet.cell(3, idx, year)
    sheet.cell(3, idx).font = Font(bold=True)

# Assumptions
sheet['A5'] = 'Assumptions'
sheet['A5'].font = Font(bold=True)

sheet['A6'] = 'Base FCF'
sheet['B6'] = 5000000
sheet['B6'].font = BLUE
sheet['B6'].fill = YELLOW
sheet['B6'].number_format = '$#,##0'

sheet['A7'] = 'Growth Rate'
sheet['B7'] = 0.10
sheet['B7'].font = BLUE
sheet['B7'].fill = YELLOW
sheet['B7'].number_format = '0.0%'

sheet['A8'] = 'Terminal Growth'
sheet['B8'] = 0.03
sheet['B8'].font = BLUE
sheet['B8'].fill = YELLOW
sheet['B8'].number_format = '0.0%'

sheet['A9'] = 'Discount Rate (WACC)'
sheet['B9'] = 0.12
sheet['B9'].font = BLUE
sheet['B9'].fill = YELLOW
sheet['B9'].number_format = '0.0%'

# Free Cash Flow Projections
sheet['A11'] = 'Free Cash Flow'
sheet['A11'].font = Font(bold=True)

# Year 1
sheet['B11'] = '=$B$6'
sheet['B11'].font = BLACK
sheet['B11'].number_format = '$#,##0'

# Years 2-5
for col in range(3, 7):
    prev_col = chr(ord('A') + col - 2)
    sheet.cell(11, col, f'={prev_col}11*(1+$B$7)')
    sheet.cell(11, col).font = BLACK
    sheet.cell(11, col).number_format = '$#,##0'

# Discount factors
sheet['A12'] = 'Discount Factor'
for col in range(2, 7):
    year_num = col - 1
    sheet.cell(12, col, f'=1/((1+$B$9)^{year_num})')
    sheet.cell(12, col).font = BLACK
    sheet.cell(12, col).number_format = '0.000'

# Present Value of FCF
sheet['A13'] = 'PV of FCF'
for col in range(2, 7):
    col_letter = chr(ord('A') + col - 1)
    sheet.cell(13, col, f'={col_letter}11*{col_letter}12')
    sheet.cell(13, col).font = BLACK
    sheet.cell(13, col).number_format = '$#,##0'

# Terminal Value
sheet['A15'] = 'Terminal Value'
sheet['F15'] = '=F11*(1+$B$8)/($B$9-$B$8)'
sheet['F15'].font = BLACK
sheet['F15'].number_format = '$#,##0'

sheet['A16'] = 'PV of Terminal Value'
sheet['F16'] = '=F15*F12'
sheet['F16'].font = BLACK
sheet['F16'].number_format = '$#,##0'

# Enterprise Value
sheet['A18'] = 'Enterprise Value'
sheet['A18'].font = Font(bold=True)
sheet['B18'] = '=SUM(B13:F13)+F16'
sheet['B18'].font = Font(bold=True, color='000000')
sheet['B18'].number_format = '$#,##0'

# Column widths
sheet.column_dimensions['A'].width = 25
for col in 'BCDEFG':
    sheet.column_dimensions[col].width = 15

wb.save('dcf_model.xlsx')

Recalculate:

python .claude/skills/xlsx/scripts/recalc.py dcf_model.xlsx

Result: Professional DCF valuation model with discounted cash flows and terminal value calculation.

---

Example 7: Dashboard with Multiple Metrics

Task: Create an executive dashboard with KPIs.

User Request: "Build a dashboard showing key business metrics with visual formatting."

Implementation:

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

wb = Workbook()
sheet = wb.active
sheet.title = "Executive Dashboard"

# Styling
TITLE_FONT = Font(bold=True, size=16, color='FFFFFF')
TITLE_FILL = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
HEADER_FONT = Font(bold=True, size=12)
METRIC_FONT = Font(size=20, bold=True, color='2F5597')
BORDER = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# Title
sheet.merge_cells('A1:F1')
sheet['A1'] = 'Executive Dashboard - Q4 2024'
sheet['A1'].font = TITLE_FONT
sheet['A1'].fill = TITLE_FILL
sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')
sheet.row_dimensions[1].height = 30

# KPI Section 1: Revenue
sheet.merge_cells('A3:B3')
sheet['A3'] = 'Total Revenue'
sheet['A3'].font = HEADER_FONT
sheet['A3'].border = BORDER

sheet.merge_cells('A4:B4')
sheet['A4'] = '=B10'  # Links to data below
sheet['A4'].font = METRIC_FONT
sheet['A4'].number_format = '$#,##0'
sheet['A4'].alignment = Alignment(horizontal='center')
sheet['A4'].border = BORDER

# KPI Section 2: Profit Margin
sheet.merge_cells('D3:E3')
sheet['D3'] = 'Profit Margin'
sheet['D3'].font = HEADER_FONT
sheet['D3'].border = BORDER

sheet.merge_cells('D4:E4')
sheet['D4'] = '=B12/B10'  # Profit / Revenue
sheet['D4'].font = METRIC_FONT
sheet['D4'].number_format = '0.0%'
sheet['D4'].alignment = Alignment(horizontal='center')
sheet['D4'].border = BORDER

# Data section (hidden below dashboard)
sheet['A9'] = 'Underlying Data'
sheet['A9'].font = Font(bold=True, italic=True)

sheet['A10'] = 'Revenue'
sheet['B10'] = 2500000
sheet['B10'].font = Font(color='0000FF')
sheet['B10'].number_format = '$#,##0'

sheet['A11'] = 'Expenses'
sheet['B11'] = 1800000
sheet['B11'].font = Font(color='0000FF')
sheet['B11'].number_format = '$#,##0'

sheet['A12'] = 'Profit'
sheet['B12'] = '=B10-B11'
sheet['B12'].font = Font(color='000000')
sheet['B12'].number_format = '$#,##0'

# Column widths
sheet.column_dimensions['A'].width = 15
sheet.column_dimensions['B'].width = 15
sheet.column_dimensions['D'].width = 15
sheet.column_dimensions['E'].width = 15

wb.save('dashboard.xlsx')

Recalculate:

python .claude/skills/xlsx/scripts/recalc.py dashboard.xlsx

Result: Professional executive dashboard with formatted KPI displays and underlying data.

---

Example 8: Data Import and Transformation

Task: Load messy data, clean it, and export to Excel.

User Request: "Clean this sales data and create a professional Excel report."

Implementation:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

# Sample messy data
raw_data = {
    'customer_name': ['  John Doe', 'Jane Smith  ', 'Bob Jones', None, 'Alice Brown'],
    'order_date': ['2024-01-15', '2024-01-20', 'invalid', '2024-02-01', '2024-02-15'],
    'amount': ['1000', '1500.50', 'invalid', '2000', '750.25']
}

df = pd.DataFrame(raw_data)

# Clean data
df['customer_name'] = df['customer_name'].str.strip()  # Remove whitespace
df = df.dropna(subset=['customer_name'])  # Remove null customers
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')  # Parse dates
df = df.dropna(subset=['order_date'])  # Remove invalid dates
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')  # Parse amounts
df = df.dropna(subset=['amount'])  # Remove invalid amounts

# Calculate summary
summary = {
    'Total Orders': len(df),
    'Total Revenue': df['amount'].sum(),
    'Average Order': df['amount'].mean()
}

# Export to Excel with pandas
with pd.ExcelWriter('sales_report.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Clean Data', index=False)

    # Summary sheet
    summary_df = pd.DataFrame(list(summary.items()), columns=['Metric', 'Value'])
    summary_df.to_excel(writer, sheet_name='Summary', index=False)

# Post-process with openpyxl for formatting
wb = load_workbook('sales_report.xlsx')

# Format Summary sheet
summary_sheet = wb['Summary']
summary_sheet['A1'].font = Font(bold=True)
summary_sheet['B1'].font = Font(bold=True)
summary_sheet['B2'].number_format = '0'
summary_sheet['B3'].number_format = '$#,##0.00'
summary_sheet['B4'].number_format = '$#,##0.00'

wb.save('sales_report.xlsx')

Result: Clean data and formatted summary report in Excel.

---

Example 9: Formula Error Detection

Task: Create a spreadsheet with formulas and verify zero errors.

User Request: "Build a spreadsheet and make sure all formulas calculate correctly."

Implementation:

from openpyxl import Workbook
import subprocess
import json

wb = Workbook()
sheet = wb.active

# Create some formulas
sheet['A1'] = 'Value 1'
sheet['B1'] = 100
sheet['A2'] = 'Value 2'
sheet['B2'] = 200
sheet['A3'] = 'Total'
sheet['B3'] = '=SUM(B1:B2)'

sheet['A5'] = 'Average'
sheet['B5'] = '=AVERAGE(B1:B2)'

sheet['A7'] = 'Percentage'
sheet['B7'] = '=B1/B3'
sheet['B7'].number_format = '0.0%'

wb.save('formulas_test.xlsx')

# Recalculate and verify
result = subprocess.run(
    ['python', '.claude/skills/xlsx/scripts/recalc.py', 'formulas_test.xlsx'],
    capture_output=True,
    text=True
)

# Parse result
verification = json.loads(result.stdout)

if verification['status'] == 'success':
    print(f"✓ Success! All {verification['total_formulas']} formulas calculated correctly.")
else:
    print(f"✗ Found {verification['total_errors']} errors:")
    for error_type, details in verification['error_summary'].items():
        print(f"  {error_type}: {details['count']} errors")
        for location in details['locations']:
            print(f"    - {location}")

Result: Spreadsheet with verified formulas and detailed error reporting if any issues found.

---

Example 10: Multi-Sheet Financial Model

Task: Create a comprehensive financial model with multiple linked sheets.

User Request: "Build a complete financial model with separate sheets for assumptions, income statement, balance sheet, and cash flow."

Implementation:

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

wb = Workbook()

# Remove default sheet
wb.remove(wb.active)

# Sheet 1: Assumptions
assumptions = wb.create_sheet("Assumptions")
assumptions['A1'] = 'Model Assumptions'
assumptions['A1'].font = Font(bold=True, size=14)

assumptions['A3'] = 'Revenue Growth'
assumptions['B3'] = 0.15
assumptions['B3'].font = Font(color='0000FF')
assumptions['B3'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
assumptions['B3'].number_format = '0.0%'

assumptions['A4'] = 'Gross Margin'
assumptions['B4'] = 0.65
assumptions['B4'].font = Font(color='0000FF')
assumptions['B4'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
assumptions['B4'].number_format = '0.0%'

assumptions['A5'] = 'Tax Rate'
assumptions['B5'] = 0.21
assumptions['B5'].font = Font(color='0000FF')
assumptions['B5'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
assumptions['B5'].number_format = '0.0%'

# Sheet 2: Income Statement
income = wb.create_sheet("Income Statement")
income['A1'] = 'Income Statement'
income['A1'].font = Font(bold=True, size=14)

years = ['2024', '2025', '2026']
income['A3'] = 'Year'
for idx, year in enumerate(years, start=2):
    income.cell(3, idx, year)
    income.cell(3, idx).font = Font(bold=True)

# Revenue
income['A5'] = 'Revenue'
income['B5'] = 1000000
income['B5'].font = Font(color='0000FF')
income['B5'].number_format = '$#,##0'

# Years 2-3 with growth from assumptions sheet
income['C5'] = '=B5*(1+Assumptions!$B$3)'  # Links to assumptions (green)
income['C5'].font = Font(color='008000')
income['C5'].number_format = '$#,##0'

income['D5'] = '=C5*(1+Assumptions!$B$3)'
income['D5'].font = Font(color='008000')
income['D5'].number_format = '$#,##0'

# COGS
income['A6'] = 'COGS'
for col in range(2, 5):
    col_letter = chr(ord('A') + col - 1)
    income.cell(6, col, f'=-{col_letter}5*(1-Assumptions!$B$4)')
    income.cell(6, col).font = Font(color='008000')  # Green for cross-sheet
    income.cell(6, col).number_format = '$#,##0;($#,##0);-'

# Gross Profit
income['A7'] = 'Gross Profit'
for col in range(2, 5):
    col_letter = chr(ord('A') + col - 1)
    income.cell(7, col, f'={col_letter}5+{col_letter}6')
    income.cell(7, col).font = Font(color='000000')
    income.cell(7, col).number_format = '$#,##0'

# Sheet 3: Balance Sheet
balance = wb.create_sheet("Balance Sheet")
balance['A1'] = 'Balance Sheet'
balance['A1'].font = Font(bold=True, size=14)

balance['A3'] = 'Year'
for idx, year in enumerate(years, start=2):
    balance.cell(3, idx, year)
    balance.cell(3, idx).font = Font(bold=True)

# Assets
balance['A5'] = 'Assets'
balance['A5'].font = Font(bold=True)

balance['A6'] = 'Cash'
for col in range(2, 5):
    col_letter = chr(ord('A') + col - 1)
    # Link to income statement profit * 0.3 (simplified)
    balance.cell(6, col, f"='Income Statement'!{col_letter}7*0.3")
    balance.cell(6, col).font = Font(color='008000')  # Green for cross-sheet
    balance.cell(6, col).number_format = '$#,##0'

# Sheet 4: Cash Flow
cashflow = wb.create_sheet("Cash Flow")
cashflow['A1'] = 'Cash Flow Statement'
cashflow['A1'].font = Font(bold=True, size=14)

cashflow['A3'] = 'Year'
for idx, year in enumerate(years, start=2):
    cashflow.cell(3, idx, year)
    cashflow.cell(3, idx).font = Font(bold=True)

# Operating Cash Flow (links to Income Statement)
cashflow['A5'] = 'Operating Cash Flow'
for col in range(2, 5):
    col_letter = chr(ord('A') + col - 1)
    cashflow.cell(5, col, f"='Income Statement'!{col_letter}7")
    cashflow.cell(5, col).font = Font(color='008000')
    cashflow.cell(5, col).number_format = '$#,##0'

wb.save('integrated_model.xlsx')

Recalculate:

python .claude/skills/xlsx/scripts/recalc.py integrated_model.xlsx

Result: Comprehensive multi-sheet financial model with proper linking (green color for cross-sheet references) and integration between statements.

---

Best Practices Demonstrated

These examples demonstrate:

1. Formula Usage: Always use formulas, never hardcoded calculations 2. Color Coding: Blue for inputs, black for formulas, green for links 3. Zero Errors: Always run recalc.py to verify 4. Professional Formatting: Currency, percentages, alignment 5. Multi-Sheet Integration: Linking data across worksheets 6. Documentation: Clear labels and assumption sections 7. Data Validation: Clean and verify data before export 8. Comprehensive Models: Build complete financial models 9. pandas Integration: Use pandas for data analysis 10. Error Detection: Automated verification of formula correctness

Running Examples

To run any example:

1. Copy the code to a Python file (e.g., example_1.py) 2. Run: python example_1.py 3. Recalculate formulas: python .claude/skills/xlsx/scripts/recalc.py output.xlsx 4. Verify zero errors in the JSON output 5. Open the Excel file to see results

Additional Resources

• SKILL.md: Complete skill documentation
• DEPENDENCIES.md: Installation instructions
• README.md: Integration overview
• tests/: Test examples for verification

XLSX Skill Integration

Overview

The XLSX skill provides comprehensive spreadsheet creation, editing, and analysis capabilities with support for formulas, formatting, data analysis, and visualization. This skill enables working with Excel files (.xlsx, .xlsm) and other spreadsheet formats (.csv, .tsv) using professional financial modeling standards.

Integration with Amplihack

This skill integrates seamlessly with the amplihack agentic coding framework, enabling AI agents to:

• Create sophisticated financial models with formulas
• Analyze and visualize data in spreadsheets
• Modify existing spreadsheets while preserving formulas and formatting
• Recalculate formulas with zero-error verification
• Follow industry-standard color coding and formatting conventions

The XLSX skill follows amplihack's brick philosophy: it is a self-contained, independently functional module with clear contracts and comprehensive dependency documentation.

Key Capabilities

Spreadsheet Creation

• Build Excel files from scratch using pandas or openpyxl
• Apply professional formatting (colors, fonts, alignment)
• Create dynamic formulas that recalculate automatically
• Support for multiple sheets and workbook organization

Data Analysis

• Load and analyze data with pandas
• Generate statistics and summaries
• Create visualizations and charts
• Export results to Excel format

Formula Management

• Insert Excel formulas (not hardcoded values)
• Recalculate formulas using LibreOffice engine
• Zero-error verification (#REF!, #DIV/0!, #VALUE!, etc.)
• Comprehensive error reporting with cell locations

Financial Modeling Standards

• Industry-standard color coding (blue inputs, black formulas, green links)
• Professional number formatting (currency, percentages, zeros as dashes)
• Assumption cell documentation
• Source attribution for hardcoded values

Dependencies

See DEPENDENCIES.md for complete dependency information including:

• Python packages (pandas, openpyxl)
• System requirements (LibreOffice)
• Installation instructions for macOS, Linux, and Windows
• Verification commands

Usage

Basic Example

from openpyxl import Workbook

# Create workbook
wb = Workbook()
sheet = wb.active

# Add data and formulas
sheet['A1'] = 'Revenue'
sheet['B1'] = 1000
sheet['B2'] = '=B1*1.1'  # Use formula, not hardcoded value

wb.save('model.xlsx')

Recalculate Formulas

python .claude/skills/xlsx/scripts/recalc.py model.xlsx

Returns JSON with error details:

{
  "status": "success",
  "total_errors": 0,
  "total_formulas": 15
}

Examples

See examples/example_usage.md for comprehensive examples including:

• Financial modeling (revenue projections, DCF models)
• Data analysis and visualization
• Budget tracking and forecasting
• Dashboard creation
• Multi-sheet workbook management

Testing

Run the test suite to verify the skill works correctly:

cd .claude/skills/xlsx
pytest tests/ -v

Tests will skip gracefully if dependencies are not installed.

Known Issues and Limitations

LibreOffice Required for Formula Recalculation

The recalc.py script requires LibreOffice to calculate formula values. Without LibreOffice:

• Formulas will be inserted correctly
• Formula values will not be calculated
• Excel will recalculate when opened

File Size Considerations

Very large Excel files (>100MB) may take longer to recalculate. Consider:

• Using write-only mode for large exports
• Breaking large models into multiple workbooks
• Using the timeout parameter: python recalc.py file.xlsx 60

Platform-Specific Notes

macOS: LibreOffice installs to /Applications/LibreOffice.app. The soffice command should be available in PATH.

Linux: LibreOffice is typically pre-installed. Use package manager if not available.

Windows: The recalc.py script has limited timeout support on Windows. Formula recalculation still works.

Zero-Error Requirement

All Excel files created with this skill MUST have zero formula errors. The recalc.py script verifies:

• #REF! - Invalid cell references
• #DIV/0! - Division by zero
• #VALUE! - Wrong data type in formula
• #NAME? - Unrecognized formula name
• #NULL! - Incorrect range operator
• #NUM! - Invalid numeric value
• #N/A - Value not available

If errors are found, the script reports their locations and counts for correction.

Best Practices

Always Use Formulas

Never hardcode calculated values. Use Excel formulas so spreadsheets remain dynamic:

# Wrong
sheet['B10'] = 5000  # Hardcoded sum

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

Follow Color Coding Standards

Unless the user specifies otherwise or an existing template has established conventions:

• Blue text: User inputs and scenario assumptions
• Black text: All formulas and calculations
• Green text: Internal worksheet links
• Red text: External file links
• Yellow background: Key assumptions requiring attention

Document Hardcoded Values

If you must hardcode a value, document the source:

sheet['B5'] = 42.5
sheet['C5'] = 'Source: Company 10-K, FY2024, Page 45, Revenue Note'

Verify Before Delivery

Always run recalc.py before considering the Excel file complete:

python recalc.py output.xlsx
# Check status is "success"
# If errors found, fix and recalculate

Amplihack Philosophy Alignment

This skill demonstrates amplihack's core principles:

Ruthless Simplicity: Uses standard libraries (pandas, openpyxl) without unnecessary abstractions.

Modular Design: Self-contained skill with clear boundaries. The recalc.py script is a focused, single-purpose tool.

Zero-BS Implementation: No placeholders or stubs. Every feature works completely or doesn't exist.

Regeneratable: Can be rebuilt from SKILL.md + recalc.py + this README.

Support

For issues or questions:

1. Check DEPENDENCIES.md for installation problems 2. Review examples/example_usage.md for usage patterns 3. Run tests to verify your environment: pytest tests/ 4. Check SKILL.md for complete skill documentation

Related Skills

• PDF Skill: For PDF manipulation and extraction (planned)
• DOCX Skill: For Word document creation (planned)
• PPTX Skill: For PowerPoint presentations (planned)

See ~/.amplihack/.claude/skills/INTEGRATION_STATUS.md for current integration status.