excel-plus

# Excel Plus

スプレッドシート作業全般と、更新時のロック・IRM・形式不一致対応をまとめたスキル。

## Excel Plus Extensions

### When to Use (Troubleshooting)

- `PermissionError`, `BadZipFile`, `file is open by another application` が出る
- OneDrive/SharePoint 配下のブックで保存競合が出る
- IRM/保護付きファイルで自動更新スクリプトが失敗する
- キーワード: `excel update plan`, `1on1 excel`, `IRM`, `BadZipFile`, `openpyxl`, `lock`

### Procedure (Troubleshooting)

1. 対象の更新スクリプトと必須引数を確認する
2. 日付、対象パス、出力先などの引数を workbook open / COM 起動より前に検証する
3. ブックが開いている場合でも、まず `--dry-run` を実行する
4. エラー種別で分岐する（下の Branches）
5. dry-run 成功後に本更新を実行する
6. 更新結果（作成シート名、変更件数、保存成功）を確認する
7. 一時ファイル/一時スクリプトを削除して終了する

### Branches

- PermissionError: まず snapshot export や open-workbook COM 更新を試す。開いたブックの未保存変更も snapshot で読む。閉じるのは最終手段
- BadZipFile: 先頭バイトで形式確認（`PK`=xlsx, `D0 CF`=旧形式/OLE/IRM の可能性）。必要なら `.xlsx` で保存し直す。開いているブックなら COM で snapshot を作って継続
- IRM/保護: hidden Excel で勝手に open しない。ユーザーが Excel Desktop で対話的に開き、保護/IRM ダイアログを解決済みの workbook にだけ attach する。hidden open を許す場合は明示フラグ、短い timeout、fail-fast、cleanup を必須にする
- ハング: 長い1行コマンドを避け、短いコマンドか一時スクリプトに分割する。COM helper は `Workbooks.Open` / `SaveAs` / `Save` が戻らない前提で timeout を置き、finally だけに依存しない
- hidden Excel 残留: `EXCEL.EXE` の window title/handle が空のプロセスを dry-run で列挙し、ユーザーが Excel を閉じてよいと確認できた場合だけ PID を絞って終了する。`taskkill /IM excel.exe /F` のような全 kill は避ける

### COM Guardrails (Troubleshooting)

- 保護付き・IRM・形式不一致が疑われる workbook は、`PK` でない時点で非対話ライブラリ処理を fail-fast させる
- `RequireOpenWorkbook` 相当のガードを持たせ、対象 workbook が既に開いていない場合は hidden Excel を新規作成しない
- `--dry-run` / `--plan-output` のような機械可読プレビューを優先し、本更新前に変更行・未解決行・追加候補を確認する
- URL 列では、表示文字列と実 hyperlink metadata を分けて扱う。`RMOT...` `SCOP...` `ROSS...` の ID が見えていてもクリック不能なことがあるため、推定可能なリンク先は repair pass で再付与し、表示値だけで完了判定しない
- 一時 snapshot / temp copy は成功・失敗・timeout の全経路で削除する
- 回帰テストでは、実 workbook や Excel COM 更新を使わず、OpenXML の一時 workbook で dry-run / 実更新 / 引数検証 / 非 OpenXML fail-fast / hidden Excel 非生成を確認する
- **COM 接続が `AttributeError: Excel.Application.Workbooks` や `GetActiveObject` 失敗で連続拒否される場合**、Python/PS 側のリトライより先に **Excel 側の状態**を疑う: セル編集中 / モーダルダイアログ表示中（「他のプロセスで変更されました。再読み込みしますか？」「保存しますか？」等）/ Excel 応答停止。対処順: Esc でセル編集解除 → ダイアログを閉じる → 再試行。それでも復旧しないなら Excel を一度終了 → 再起動 → 再試行
- **PowerShell 7 (`pwsh`) では `[System.Runtime.InteropServices.Marshal]::GetActiveObject` が削除済み**。Excel COM に接続する PS スクリプトは `powershell.exe`（PowerShell 5.1）を明示指定する
- **PowerShell 5.1 の `.ps1` ファイル UTF-8 読み込みは壊れる**（日本語文字列が `EDE COMPLETE (縲・026-05-31)` のように化けて構文エラー）。日本語を含む payload は Python 側で `base64.b64encode(json.dumps(payload, ensure_ascii=False).encode("utf-8"))` して環境変数経由で渡し、PS 側で `[Text.Encoding]::UTF8.GetString([Convert]::FromBase64String($env:VAR)) | ConvertFrom-Json` でデコードする

### Quality Gates (Troubleshooting)

- [ ] dry-run が成功している
- [ ] 本更新が成功し、保存完了メッセージを確認した
- [ ] 期待シート（例: `YYYYMMDD`）が作成/更新されている
- [ ] 変更件数を確認した（0件でも理由を説明できる）
- [ ] URL / ID 列を補修した場合、代表セルで hyperlink の有無と target を spot-check した
- [ ] 一時ファイルを削除した
- [ ] 開いていたブックが閉じられていないこと、または意図せず別名保存されていないことを確認した
- [ ] `EXCEL.EXE` の hidden 残留と workbook lock file がないことを確認した

# 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

#### 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 =B5*1.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 `scripts/recalc.py` script. The script automatically configures LibreOffice on first run, including in sandboxed environments where Unix sockets are restricted (handled by `scripts/office/soffice.py`)

## Reading and analyzing data

### Data analysis with pandas

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

```python
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

```python
# 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

```python
# 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 scripts/recalc.py script
   ```bash
   python scripts/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

```python
# 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

```python
# 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 `scripts/recalc.py` script to recalculate formulas:

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

Example:

```bash
python scripts/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 scripts/recalc.py Output

The script returns JSON with error details:

```json
{
  "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 scripts/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