Excel Spreadsheets

Excel Spreadsheets

When to Use

Workflow

1. Before modifying: call preview_excel_sheets to check current layout.
2. After creation/modification: call preview_excel_sheets to verify.
3. Run tools sequentially (never parallel) to prevent file race conditions.

Professional Formatting

• Font: Arial for all cells unless an existing template specifies otherwise.
• Negative numbers: parentheses format (1,234) — not minus -1,234.
• Currency: $#,##0 format. Specify units in headers (e.g., "Revenue ($mm)").
• Percentages: 0.0% (one decimal place).
• Years: format as text ("2024" not "2,024").
• Zeros: display as "-" using a custom number format.

# Example: professional number formatting
from openpyxl.styles import numbers
ws['B2'].number_format = '$#,##0'          # Currency
ws['C2'].number_format = '0.0%'            # Percentage
ws['D2'].number_format = '#,##0;(#,##0)'   # Negative in parentheses
ws['E2'].number_format = '@'               # Year as text
ws['F2'].number_format = '#,##0;(#,##0);"-"'  # Zeros as dash

Financial Model Color Coding

When building financial models, apply these conventions:

from openpyxl.styles import Font, PatternFill
ws['B2'].font = Font(name='Arial', color='0000FF')       # Blue: input
ws['B3'].font = Font(name='Arial', color='000000')       # Black: formula
ws['B4'].font = Font(name='Arial', color='008000')       # Green: cross-sheet link
ws['B5'].fill = PatternFill('solid', fgColor='FFFF00')   # Yellow bg: key assumption

Images

from openpyxl.drawing.image import Image
ws.add_image(Image('file.png'), 'E1')

Code Rules

• Workbook is pre-initialized as wb = Workbook(), active sheet as ws = wb.active (create), or loaded as wb (modify). Do NOT include Workbook() or wb.save().
• Available libraries: openpyxl, matplotlib, pandas, numpy (seaborn NOT available)
• ALWAYS use Excel formulas, NEVER hardcode calculated values.
  • Wrong: ws['B10'] = total
  • Right: ws['B10'] = '=SUM(B2:B9)'
• Place assumptions in separate cells, reference them in formulas (e.g., =B5*(1+$B$6) not =B5*1.05).
• Avoid circular references.
• Filenames: letters, numbers, hyphens only.

Tool Reference

create_excel_spreadsheet

Create a new Excel spreadsheet using openpyxl code.

Example tool_input:

{
  "python_code": "ws.title = 'Sales'\nws['A1'] = 'Quarter'\nws['B1'] = 'Revenue'\nfor i, (q, r) in enumerate(zip(['Q1','Q2','Q3','Q4'], [100,150,130,180]), start=2):\n    ws[f'A{i}'] = q\n    ws[f'B{i}'] = r",
  "spreadsheet_name": "quarterly-sales"
}

WARNING: Parameter is spreadsheet_name, NOT filename or name.

modify_excel_spreadsheet

Modify an existing spreadsheet and save with a new name.

Example tool_input:

{
  "source_name": "quarterly-sales",
  "output_name": "quarterly-sales-v2",
  "python_code": "ws = wb.active\nws['B6'] = '=SUM(B2:B5)'"
}

list_my_excel_spreadsheets

List all spreadsheets in workspace. No parameters needed.

read_excel_spreadsheet

Retrieve a specific spreadsheet for download.

preview_excel_sheets

Get sheet screenshots for visual inspection before editing.

UI Guidance (from tools-config)

Sequential Execution Required: Run modification tools sequentially, never in parallel (prevents race conditions on S3 file read/write).

When to Use:

• create_excel_spreadsheet: User asks to create/generate a NEW Excel spreadsheet
• modify_excel_spreadsheet: User asks to edit/modify/update an EXISTING spreadsheet
• list_my_excel_spreadsheets: User asks what spreadsheets are available
• read_excel_spreadsheet: User wants to download or read spreadsheet contents
• preview_excel_sheets: Check actual sheet appearance when editing

Before Modifying: Always use preview_excel_sheets first to see the current layout.

CRITICAL — Formulas, Not Hardcoded Values:

• ALWAYS use Excel formulas. NEVER calculate in Python and hardcode the result.
• Wrong: ws['B10'] = total. Right: ws['B10'] = '=SUM(B2:B9)'
• Place assumptions in separate cells, reference them in formulas (e.g., =B5*(1+$B$6) not =B5*1.05)
• Formulas are auto-recalculated via LibreOffice after save.
• Avoid circular references.

Professional Formatting:

• Font: Arial for all cells unless existing template specifies otherwise
• Negative numbers: parentheses format (1,234) not minus -1,234
• Currency: $#,##0 format; specify units in headers ("Revenue ($mm)")
• Percentages: 0.0% (one decimal)
• Years: format as text ("2024" not "2,024")
• Zeros: display as "-" using custom number format

Financial Model Color Coding (when applicable):

• Blue text (0,0,255): Hardcoded inputs and assumptions
• Black text (0,0,0): All formulas and calculations
• Green text (0,128,0): Links pulling from other worksheets
• Yellow background (255,255,0): Key assumptions needing attention

Filenames: Letters, numbers, hyphens only (e.g., "Q4-sales.xlsx")

Available Libraries: openpyxl, matplotlib, pandas, numpy (seaborn NOT available)

Images: from openpyxl.drawing.image import Image; ws.add_image(Image('file.png'), 'E1')

QA: Always use preview_excel_sheets after creation to verify appearance.