Xlsx

XLSX Skill

Decision Tree

CRITICAL: Use Excel Formulas, Not Hardcoded Values

pythonCopy code
# ❌ WRONG — calculating in Python
total = df['Sales'].sum()
sheet['B10'] = total

# ✅ CORRECT — let Excel calculate
sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'

Always use Excel formulas so spreadsheets remain dynamic and updateable.

Reading & Analyzing

pandas (data analysis)

pythonCopy code
import pandas as pd

df = pd.read_excel('file.xlsx')                          # first sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # all sheets as dict

df.describe()                         # statistics
df.groupby('Category')['Amount'].sum()  # aggregation
df.to_excel('output.xlsx', index=False)

openpyxl (preserve formulas/formatting)

pythonCopy code
from openpyxl import load_workbook

wb = load_workbook('file.xlsx')
sheet = wb.active
for row in sheet.iter_rows(min_row=2, values_only=True):
    print(row)

Creating New Spreadsheets

openpyxl with formulas and formatting

pythonCopy code
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, numbers

wb = Workbook()
ws = wb.active
ws.title = "Revenue Model"

# Headers
headers = ["Quarter", "Revenue", "COGS", "Gross Profit", "Margin"]
for col, h in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=h)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="333333")
    cell.alignment = Alignment(horizontal="center")

# Data with formulas
quarters = ["Q1", "Q2", "Q3", "Q4"]
revenues = [120000, 145000, 168000, 192000]
cogs_pct = 0.35

for i, (q, rev) in enumerate(zip(quarters, revenues), 2):
    ws.cell(row=i, column=1, value=q)
    ws.cell(row=i, column=2, value=rev).number_format = '$#,##0'
    ws.cell(row=i, column=3).value = f'=B{i}*{cogs_pct}'
    ws.cell(row=i, column=3).number_format = '$#,##0'
    ws.cell(row=i, column=4).value = f'=B{i}-C{i}'
    ws.cell(row=i, column=4).number_format = '$#,##0'
    ws.cell(row=i, column=5).value = f'=D{i}/B{i}'
    ws.cell(row=i, column=5).number_format = '0.0%'

# Totals row
total_row = len(quarters) + 2
ws.cell(row=total_row, column=1, value="Total").font = Font(bold=True)
for col in [2, 3, 4]:
    cell = ws.cell(row=total_row, column=col)
    cell.value = f'=SUM({chr(64+col)}2:{chr(64+col)}{total_row-1})'
    cell.font = Font(bold=True)
    cell.number_format = '$#,##0'

# Column widths
for col_letter, width in [("A", 12), ("B", 15), ("C", 15), ("D", 15), ("E", 12)]:
    ws.column_dimensions[col_letter].width = width

wb.save("revenue_model.xlsx")

xlsxwriter (charts)

pythonCopy code
import xlsxwriter

wb = xlsxwriter.Workbook("chart_report.xlsx")
ws = wb.add_worksheet("Sales")

data = [["Month", "Sales"], ["Jan", 5000], ["Feb", 6200], ["Mar", 7100], ["Apr", 6800]]
for r, row in enumerate(data):
    for c, val in enumerate(row):
        ws.write(r, c, val)

chart = wb.add_chart({"type": "column"})
chart.add_series({
    "name": "Sales",
    "categories": ["Sales", 1, 0, len(data)-1, 0],
    "values": ["Sales", 1, 1, len(data)-1, 1],
    "fill": {"color": "#1E2761"},
})
chart.set_title({"name": "Monthly Sales"})
chart.set_size({"width": 600, "height": 400})
ws.insert_chart("D2", chart)

wb.close()

Editing Existing Files

pythonCopy code
from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
ws = wb['Sheet1']

ws['A1'] = 'Updated Value'
ws.insert_rows(2)               # insert row at position 2
ws.delete_cols(3)               # delete column C
new_ws = wb.create_sheet('Summary')
new_ws['A1'] = '=Sheet1!B10'    # cross-sheet reference

wb.save('modified.xlsx')

Warning: load_workbook('f.xlsx', data_only=True) reads calculated values but permanently loses formulas if saved.

Financial Model Standards

Color coding (industry standard)

Number formatting

Formula rules

• Place ALL assumptions in separate cells — never hardcode in formulas
• Use =B5*(1+$B$6) not =B5*1.05
• Document sources for hardcoded values: "Source: Company 10-K, FY2024, Page 45"
• Test formulas on 2–3 cells before applying broadly

Recalculating Formulas

openpyxl writes formulas as strings without calculated values. Use the bundled script:

bashCopy code
python scripts/recalc.py output.xlsx [timeout_seconds]

The script:

• Installs a LibreOffice macro on first run
• Recalculates all formulas in all sheets
• Scans every cell for Excel errors (#REF!, #DIV/0!, etc.)
• Returns JSON with error locations and counts

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

If status is "errors_found", fix the issues and re-run.

Common Pitfalls

• Cell indices are 1-based in openpyxl (row=1, column=1 = A1)
• data_only=True + save = formulas permanently lost
• For large files: read_only=True for reading, write_only=True for writing
• Specify dtypes to avoid pandas inference: pd.read_excel('f.xlsx', dtype={'id': str})
• Always verify cross-sheet references use correct format: Sheet1!A1