Excel Master Skill for OpenClaw

• Use a consistent, professional font (e.g., Arial, Calibri) for all deliverables unless otherwise instructed by the user.

Zero Formula Errors

• Every Excel file MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)

Preserve Existing Templates (when updating files)

• 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 Model Standards

Color Coding (Industry Standard)

Unless otherwise stated by the user or an existing template:

Number Formatting

• Years: Format as text strings ("2024" not "2,024")
• Currency: Use $#,##0 format; specify units in headers ("Revenue ($mm)")
• Zeros: Show as "-" using format: $#,##0;($#,##0);"-"
• Percentages: Default 0.0% (one decimal)
• Multiples: 0.0x for valuation multiples
• Negative numbers: Use parentheses (123) not minus -123

Formula Construction Rules

• Place ALL assumptions in separate assumption cells.
• Use cell references instead of hardcoded values in formulas.
  • CORRECT: =B5*(1+$B$6)
  • WRONG: =B5*1.05
• Document data sources for hardcoded values in comments.

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

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.

Common Workflow

1. Choose tool: pandas for data analysis, 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):

   python3 {baseDir}/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:
     • #REF! — Invalid cell references
     • #DIV/0! — Division by zero
     • #VALUE! — Wrong data type in formula
     • #NAME? — Unrecognized formula name

Reading and Analyzing Data

Data Analysis with pandas

import pandas as pd

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

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

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

Specifying Data Types

df = pd.read_excel('file.xlsx', dtype={'id': str})
df = pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])
df = pd.read_excel('file.xlsx', parse_dates=['date_column'])

Creating New Excel Files

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

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

from openpyxl import load_workbook

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

# Working with multiple sheets
for sheet_name in wb.sheetnames:
    sheet = wb[sheet_name]

# Modify cells
sheet['A1'] = 'New Value'
sheet.insert_rows(2)
sheet.delete_cols(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 to recalculate:

python3 {baseDir}/scripts/recalc.py <excel_file> [timeout_seconds]

Example:

python3 {baseDir}/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.
• Handles sandboxed environments where Unix sockets are restricted.

Interpreting recalc.py Output

{
  "status": "success",
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {}
}

If errors are found:

{
  "status": "errors_found",
  "total_errors": 2,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {
      "count": 2,
      "locations": ["Sheet1!B5", "Sheet1!C10"]
    }
  }
}

Charts and Visualizations

from openpyxl.chart import BarChart, LineChart, PieChart, Reference

# Bar Chart
chart = BarChart()
chart.title = "Sales by Quarter"
chart.y_axis.title = "Revenue ($)"
chart.x_axis.title = "Quarter"

data = Reference(sheet, min_col=2, min_row=1, max_col=5, max_row=4)
cats = Reference(sheet, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
sheet.add_chart(chart, "G2")

# Line Chart
line = LineChart()
line.title = "Trend"
line.add_data(data, titles_from_data=True)
line.set_categories(cats)
sheet.add_chart(line, "G18")

Conditional Formatting

from openpyxl.formatting.rule import (
    CellIsRule, DataBarRule, ColorScaleRule, FormulaRule
)

# Highlight cells > 100
sheet.conditional_formatting.add(
    'B2:B20',
    CellIsRule(operator='greaterThan', formula=['100'],
              fill=PatternFill(start_color='00B050'))
)

# Data bars
sheet.conditional_formatting.add(
    'C2:C20',
    DataBarRule(start_type='min', end_type='max',
               color='638EC6', showValue=True)
)

# Color scale (red-yellow-green)
sheet.conditional_formatting.add(
    'D2:D20',
    ColorScaleRule(start_type='min', start_color='FF0000',
                   mid_type='percentile', mid_value=50, mid_color='FFFF00',
                   end_type='max', end_color='00B050')
)

Named Ranges and Data Validation

from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation

# Named Range
ref = DefinedName('SalesData', attr_text="Sheet1!$A$1:$D$100")
wb.defined_names.add(ref)

# Data Validation — dropdown list
dv = DataValidation(type="list", formula1='"Yes,No,Maybe"', allow_blank=True)
dv.error = "Please select from the dropdown"
dv.errorTitle = "Invalid Entry"
sheet.add_data_validation(dv)
dv.add('E2:E100')

Excel Tables

from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter

table = Table(
    displayName="SalesTable",
    ref=f"A1:{get_column_letter(last_col)}{last_row}"
)
table.tableStyleInfo = TableStyleInfo(
    name="TableStyleMedium9",
    showFirstColumn=False,
    showLastColumn=False,
    showRowStripes=True,
    showColumnStripes=False
)
sheet.add_table(table)

RTL Support (Arabic / Hebrew)

# Set Right-to-Left layout for Arabic sheets
def set_rtl(ws):
    ws.sheet_view.rightToLeft = True

for sheet in wb.worksheets:
    set_rtl(sheet)

Professional Color System

COLORS = {
    'input': '0000FF',          # Blue — manual inputs
    'formula': '000000',        # Black — formulas
    'crossref': '008000',       # Green — cross-sheet links
    'warning': 'FFFF00',        # Yellow background — attention
    'header_bg': '1F3864',      # Dark blue — table header background
    'header_fg': 'FFFFFF',      # White — header text
    'subheader': 'D6E4F0',      # Light blue — sub-header
    'total': 'E8F4FD',          # Pale blue — totals row
    'positive': '00B050',       # Green — positive values
    'negative': 'FF0000',       # Red — negative values / warnings
}

Formula Verification Checklist

Essential Verification

• Test 2-3 sample references before building full model
• Confirm Excel columns match (e.g., column 64 = BL, not BK)
• Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)

Common Pitfalls

• NaN handling: check with pd.notna()
• Far-right columns: FY data often in columns 50+
• Division by zero: check denominators before / in formulas
• Wrong references: verify all cell references point to intended cells
• Cross-sheet references: use correct format Sheet1!A1

Formula Testing Strategy

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

Best Practices

Library Selection

• pandas: Best for data analysis, bulk operations, simple data export.
• openpyxl: Best for complex formatting, formulas, Excel-specific features.

Working with openpyxl

• Cell indices are 1-based (row=1, column=1 = cell A1).
• Use data_only=True to read calculated values: load_workbook('f.xlsx', data_only=True)
• WARNING: If opened with data_only=True and saved, formulas are permanently replaced with cached values.
• For large files: read_only=True for reading, write_only=True for writing.
• Formulas are preserved but NOT evaluated — use scripts/recalc.py.

Code Style

• Write minimal, concise Python code without unnecessary comments.
• Avoid verbose variable names and redundant operations.
• Avoid unnecessary print statements.
• Add comments in Excel cells for complex formulas or important assumptions.