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

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

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'
)