excel-automation

Dieser Auszug wurde bei der Prüfung gespeichert. Die externe Quelle enthält die vollständige und aktuelle Version.

---
name: excel-automation
description: Create, parse, and control Excel files on macOS. Professional formatting with openpyxl, complex xlsm parsing with stdlib zipfile+xml for investment bank financial models, and Excel window control via AppleScript. Use when creating formatted Excel reports, parsing financial models that openpyxl cannot handle, or automating Excel on macOS.
---

# Excel Automation

Create professional Excel files, parse complex financial models, and control Excel on macOS.

## Quick Start

```bash
# Create a formatted Excel report
uv run --with openpyxl scripts/create_formatted_excel.py output.xlsx

# Parse a complex xlsm that openpyxl can't handle
uv run scripts/parse_complex_excel.py model.xlsm              # List sheets
uv run scripts/parse_complex_excel.py model.xlsm "DCF"        # Extract a sheet
uv run scripts/parse_complex_excel.py model.xlsm --fix        # Fix corrupted names

# Control Excel via AppleScript (with timeout to prevent hangs)
timeout 5 osascript -e 'tell application "Microsoft Excel" to activate'
```

## Overview

Three capabilities:

| Capability | Tool | When to Use |
|-----------|------|-------------|
| **Create** formatted Excel | `openpyxl` | Reports, mockups, dashboards |
| **Parse** complex xlsm/xlsx | `zipfile` + `xml.etree` | Financial models, VBA workbooks, >1MB files |
| **Control** Excel window | AppleScript (`osascript`) | Zoom, scroll, select cells programmatically |

## Tool Selection Decision Tree

```
Is the file simple (data export, no VBA, <1MB)?
├─ YES → openpyxl or pandas
└─ NO
   ├─ Is it .xlsm or from investment bank / >1MB?
   │   └─ YES → zipfile + xml.etree.ElementTree (stdlib)
   └─ Is it truly .xls (BIFF format)?
       └─ YES → xlrd
```

**Signals of "complex" Excel**: file >1MB, `.xlsm` extension, from investment bank/broker, contains VBA macros.

**IMPORTANT**: Always run `file <path>` first — extensions lie. A `.xls` file may actually be a ZIP-based xlsx.

## Creating Excel Files (openpyxl)

### Professional Color Convention (Investment Banking Standard)

| Color | RGB Code | Meaning |
|-------|----------|---------|
| Blue | `0000FF` | User input / assumption |
| Black | `000000` | Calculated value |
| Green | `008000` | Cross-sheet reference |
| White on dark blue | `FFFFFF` on `4472C4` | Section headers |
| Dark blue text | `1F4E79` | Title |

### Core Formatting Patterns

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

# Fonts
BLUE_FONT = Font(color="0000FF", size=10, name="Calibri")
BLACK_FONT_BOLD = Font(color="000000", size=10, name="Calibri", bold=True)
GREEN_FONT = Font(color="008000", size=10, name="Calibri")
HEADER_FONT = Font(color="FFFFFF", size=12, name="Calibri", bold=True)

# Fills
DARK_BLUE_FILL = PatternFill("solid", fgColor="4472C4")
LIGHT_BLUE_FILL = PatternFill("solid", fgColor="D9E1F2")
INPUT_GREEN_FILL = PatternFill("solid", fgColor="E2EFDA")
LIGHT_GRAY_FILL = PatternFill("solid", fgColor="F2F2F2")

# Borders
THIN_BORDER = Border(bottom=Side(style="thin", color="B2B2B2"))
BOTTOM_DOUBLE = Border(bottom=Side(style="double", color="000000"))
```

### Number Format Codes

| Format | Code | Example |
|--------|------|---------|
| Currency | `'$#,##0'` | $1,234 |
| Currency with decimals | `'$#,##0.00'` | $1,234.56 |
| Percentage | `'0.0%'` | 12.3% |
| Percentage (2 decimal) | `'0.00%'` | 12.34% |
| Number with commas | `'#,##0'` | 1,234 |
| Multiplier | `'0.0x'` | 1.5x |

### Conditional Formatting (Sensitivity Tables)

Red-to-green gradient for sensitivity analysis:

```python
from openpyxl.formatting.rule import ColorScaleRule

rule = ColorScaleRule(
    start_type="min", start_color="F8696B",   # Red (low)
    mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow (mid)
    end_type="max", end_color="63BE7B"         # Green (high)
)
ws.conditional_formatting.add(f"B2:F6", rule)
```

### Execution

```bash
uv run --with openpyxl scripts/create_formatted_excel.py
```

Full template script: S