XLSX Processing

Overview

Work with Excel spreadsheets for creation, editing, data analysis, and financial modeling.

Key Requirements

Zero Formula Errors

All Excel deliverables must have no errors:

• #REF! - Invalid reference
• #DIV/0! - Division by zero
• #VALUE! - Wrong value type
• #N/A - Value not available
• #NAME? - Unrecognized name

Template Preservation

When updating existing files, study and exactly match existing format, style, and conventions.

Financial Model Standards

Color Coding Convention

Color	Usage
Blue text	Hardcoded inputs users will modify
Black text	All formulas and calculations
Green text	Links from other worksheets
Red text	External file links
Yellow background	Key assumptions requiring attention

Number Formatting

• Years as text strings ("2024" not "2,024")
• Currency: $#,##0 with units in headers
• Zeros displayed as "-"
• Percentages: 0.0% format
• Negative numbers in parentheses, not minus signs

Python Libraries

pandas - Data Analysis

import pandas as pd

# Read Excel
df = pd.read_excel('input.xlsx', sheet_name='Sheet1')

# Process data
df['Total'] = df['Price'] * df['Quantity']

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

openpyxl - Complex Formatting

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

wb = Workbook()
ws = wb.active

# Add data with formatting
ws['A1'] = 'Revenue'
ws['A1'].font = Font(bold=True)

# Add formula
ws['B10'] = '=SUM(B1:B9)'

wb.save('output.xlsx')

Tool Selection

Task	Tool
Data analysis	pandas
Bulk operations	pandas
Simple exports	pandas
Complex formatting	openpyxl
Formulas	openpyxl
Excel-specific features	openpyxl

Critical Rules

Use Formulas, Not Hardcoded Values

Always employ Excel formulas instead of calculating in Python and embedding results. This maintains spreadsheet dynamism.

# Good - uses formula
ws['C1'] = '=A1+B1'

# Bad - hardcoded result
ws['C1'] = 15  # Don't do this

Documentation Requirements

Hardcoded values require comments citing:

• Source
• Date
• Location

Example: "Source: Company 10-K, FY2024, Page 45"

Common Operations

Reading Multiple Sheets

xlsx = pd.ExcelFile('workbook.xlsx')
for sheet_name in xlsx.sheet_names:
    df = pd.read_excel(xlsx, sheet_name=sheet_name)

Conditional Formatting

from openpyxl.formatting.rule import ColorScaleRule

rule = ColorScaleRule(
    start_type='min', start_color='FF0000',
    end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('A1:A10', rule)

Pivot Tables with pandas

pivot = df.pivot_table(
    values='Sales',
    index='Region',
    columns='Product',
    aggfunc='sum'
)