Excel Toolkit

Setup (First Use)

python3 scripts/setup_deps.py

Workflow Selection

1. Inspect a file → Run

   scripts/inspect_excel.py

2. Analyze data / get insights → Run

   scripts/analyze_excel.py

3. Read data for processing → Use pandas in inline Python
4. Edit existing file → Use openpyxl (preserves formulas/formatting)
5. Create new file → Use openpyxl (formulas/formatting) or pandas (data export)
6. Recalculate formulas → Run

   scripts/recalc.py

Quick-Start Scripts

Inspect File Structure

python3 scripts/inspect_excel.py data.xlsx                    # Structure only
python3 scripts/inspect_excel.py data.xlsx --data              # With data preview
python3 scripts/inspect_excel.py data.xlsx --sheet "Sales"     # Specific sheet
python3 scripts/inspect_excel.py data.xlsx --data --rows 50    # More preview rows

Analyze Data

python3 scripts/analyze_excel.py data.xlsx                         # Basic stats
python3 scripts/analyze_excel.py data.xlsx --correlations          # With correlations
python3 scripts/analyze_excel.py data.xlsx --sheet "Revenue"       # Specific sheet

Recalculate Formulas

python3 scripts/recalc.py output.xlsx [timeout_seconds]

Reading Data

import pandas as pd

df = pd.read_excel('file.xlsx')                          # First sheet
df = pd.read_excel('file.xlsx', sheet_name='Sales')      # Named sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict
df = pd.read_excel('file.xlsx', dtype={'id': str})       # Force types

Creating / Editing

Create New

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

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws['A1'] = 'Category'
ws['A1'].font = Font(bold=True)
ws.append(['Sales', 1500])
ws['B3'] = '=SUM(B2:B2)'
ws.column_dimensions['A'].width = 18
wb.save('output.xlsx')

Edit Existing

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')  # preserves formulas
ws = wb['Sheet1']
ws['A1'] = 'Updated'
ws.insert_rows(2)
wb.save('modified.xlsx')

Critical Rules

1. Use Excel formulas, not hardcoded calculations
   • ❌

     ws['B10'] = df['Sales'].sum()

   • ✅

     ws['B10'] = '=SUM(B2:B9)'

2. Recalculate after writing formulas — openpyxl writes formula strings but doesn't evaluate:
   bash

   python3 scripts/recalc.py output.xlsx

3. Never save workbooks opened with

   data_only=True

   — destroys all formulas permanently.
4. Preserve existing formatting — use

   load_workbook()

   and match existing conventions.

Providing Insights

1. Run

   scripts/inspect_excel.py

   to understand structure
2. Run

   scripts/analyze_excel.py --correlations

   for numeric data
3. Present findings:
   • Overview: rows, columns, data types
   • Key Statistics: means, medians, ranges
   • Data Quality: missing values, duplicates, anomalies
   • Patterns: correlations, trends, distributions
   • Actionable Insights: what stands out, recommendations

Building Dashboards & Insights Sheets

references/advanced-patterns.md

1. Use a running ROW counter — never hardcode row positions for sections/charts
2. Reserve 17-20 rows after each chart anchor for chart height
3. Leave 2 blank rows between tables and charts
4. Set chart dimensions explicitly — use the sizing guide for each chart type
5. Apply consistent styling — title/section/header fonts, zebra striping, thin borders
6. Use the standard color palette — BLUE for primary, ORANGE for secondary, RED for warnings
7. Set column widths — use the defaults table for readable layouts

Advanced Features

references/advanced-patterns.md