試算表 Spreadsheet Skill

I can't run code or write files in this session, so the script below is untested. Run it once and use the checks at the end.

build_budget.py

from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

OUT = "household_budget.xlsx"
MONTHS = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
          "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

INCOME = {
    "Salary":       [5200] * 12,
    "Side income":  [300, 250, 400, 350, 300, 200, 0, 300, 350, 250, 300, 200],
    "Other income": [0, 0, 0, 100, 0, 0, 0, 0, 0, 0, 0, 0],
}
EXPENSES = {
    "Rent / Mortgage": [1800] * 12,
    "Utilities":       [220, 210, 190, 160, 150, 170, 190, 185, 160, 170, 200, 230],
    "Groceries":       [620, 590, 640, 600, 610, 580, 650, 620, 600, 630, 640, 720],
    "Transport":       [180, 170, 190, 200, 210, 220, 260, 200, 190, 185, 180, 200],
    "Insurance":       [250] * 12,
    "Dining out":      [240, 200, 260, 230, 250, 280, 350, 260, 240, 270, 300, 420],
    "Entertainment":   [120, 100, 150, 130, 140, 160, 300, 120, 110, 140, 150, 350],
    "Health":          [60, 40, 80, 50, 45, 60, 70, 300, 50, 60, 40, 90],
    "Subscriptions":   [65] * 12,
    "Other":           [100, 80, 120, 90, 150, 110, 1500, 100, 90, 130, 110, 1400],
}

CURRENCY = '$#,##0;[Red]($#,##0);"-"'
PERCENT = '0.0%;[Red]-0.0%;"-"'

FIRST_COL, LAST_COL, TOTAL_COL = 2, 13, 14          # B..M months, N total
HEADER_FILL = PatternFill("solid", fgColor="1F3864")
SECTION_FILL = PatternFill("solid", fgColor="D9E1F2")
TOTAL_FILL = PatternFill("solid", fgColor="F2F2F2")
FLAG_FILL = PatternFill("solid", bgColor="F8CBAD", fgColor="F8CBAD")
INPUT_FONT = Font(color="0000FF")
BOLD = Font(bold=True)
TOP_BORDER = Border(top=Side(style="thin"))

wb = Workbook()
ws = wb.active
ws.title = "Budget"

ws["A1"] = "Household Budget"
ws["A1"].font = Font(bold=True, size=16)
ws["A2"] = "Blue = enter your numbers. Black = formulas. Months with expenses above income are highlighted."
ws["A2"].font = Font(italic=True, color="595959")

# Header row
HDR = 4
ws.cell(HDR, 1, "Category")
for i, m in enumerate(MONTHS):
    ws.cell(HDR, FIRST_COL + i, m)
ws.cell(HDR, TOTAL_COL, "Year Total")
for c in range(1, TOTAL_COL + 1):
    cell = ws.cell(HDR, c)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = HEADER_FILL
    cell.alignment = Alignment(horizontal="left" if c == 1 else "right")

first, last = get_column_letter(FIRST_COL), get_column_letter(LAST_COL)
tot = get_column_letter(TOTAL_COL)


def section(row, title):
    for c in range(1, TOTAL_COL + 1):
        ws.cell(row, c).fill = SECTION_FILL
    ws.cell(row, 1, title).font = BOLD


def data_rows(start, items):
    r = start
    for name, vals in items.items():
        ws.cell(r, 1, name).alignment = Alignment(indent=1)
        for i, v in enumerate(vals):
            cell = ws.cell(r, FIRST_COL + i, v)
            cell.font = INPUT_FONT
            cell.number_format = CURRENCY
        ws.cell(r, TOTAL_COL, f"=SUM({first}{r}:{last}{r})").number_format = CURRENCY
        ws.cell(r, TOTAL_COL).font = BOLD
        r += 1
    return start, r - 1


def total_row(row, label, a, b):
    ws.cell(row, 1, label)
    for c in range(FIRST_COL, TOTAL_COL + 1):
        L = get_column_letter(c)
        ws.cell(row, c, f"=SUM({L}{a}:{L}{b})").number_format = CURRENCY
    for c in range(1, TOTAL_COL + 1):
        cell = ws.cell(row, c)
        cell.font = BOLD
        cell.fill = TOTAL_FILL
        cell.border = TOP_BORDER


r = HDR + 1
section(r, "INCOME")
inc_a, inc_b = data_rows(r + 1, INCOME)
INC_TOT = inc_b + 1
total_row(INC_TOT, "Total Income", inc_a, inc_b)

r = INC_TOT + 2
section(r, "EXPENSES")
exp_a, exp_b = data_rows(r + 1, EXPENSES)
EXP_TOT = exp_b + 1
total_row(EXP_TOT, "Total Expenses", exp_a, exp_b)

SAV = EXP_TOT + 2
RATE = SAV + 1
ws.cell(SAV, 1, "Savings (Income - Expenses)")
ws.cell(RATE, 1, "Savings Rate (Savings / Income)")
for c in range(FIRST_COL, TOTAL_COL + 1):
    L = get_column_letter(c)
    ws.cell(SAV, c, f"={L}{INC_TOT}-{L}{EXP_TOT}").number_format = CURRENCY
    ws.cell(RATE, c, f"=IF({L}{INC_TOT}=0,0,{L}{SAV}/{L}{INC_TOT})").number_format = PERCENT
for row in (SAV, RATE):
    for c in range(1, TOTAL_COL + 1):
        ws.cell(row, c).font = BOLD
for c in range(1, TOTAL_COL + 1):
    ws.cell(SAV, c).border = TOP_BORDER

# Highlight months where expenses > income (header + summary rows only,
# so the input cells stay clean).
for rng, top in ((f"{first}{HDR}:{last}{HDR}", HDR),
                 (f"{first}{INC_TOT}:{last}{INC_TOT}", INC_TOT),
                 (f"{first}{EXP_TOT}:{last}{EXP_TOT}", EXP_TOT),
                 (f"{first}{SAV}:{last}{RATE}", SAV)):
    ws.conditional_formatting.add(
        rng,
        FormulaRule(formula=[f"{first}${EXP_TOT}>{first}${INC_TOT}"],
                    fill=FLAG_FILL, font=Font(bold=True, color="9C0006")),
    )

ws.column_dimensions["A"].width = 34
for c in range(FIRST_COL, TOTAL_COL + 1):
    ws.column_dimensions[get_column_letter(c)].width = 11
ws.column_dimensions[tot].width = 13
ws.freeze_panes = ws.cell(HDR + 1, FIRST_COL)
ws.sheet_view.showGridLines = False

wb.save(OUT)
print("Saved", OUT)

Run it

python3 -m pip install openpyxl
python3 build_budget.py
open household_budget.xlsx

How to check it

1. Formulas calculate. Excel calculates on open. If you see blanks or zeros, press Ctrl+Alt+F9 (Cmd+= on Mac). No cell should show #DIV/0! or #NAME?.
2. Spot-check totals. January income should be $5,500 and January expenses $3,655, so savings are $1,845. That's a 33.5% savings rate. Year Total (column N) should equal the sum of the twelve months, both down the Total rows and across the category rows.
3. Highlight. The sample data overspends in Jul (income $5,200, expenses $6,435) and Dec (income $5,400, expenses $6,325). Those two columns should turn red in the header and summary rows, and no other months should. Change a Jul input, such as Salary to 9000, and the highlight should disappear.
4. Formats. Currency shows as $1,234, negatives are red in parentheses, and zeros show as -. Savings rate shows as a percentage, and it goes red when negative.
5. Optional automated check. If you have LibreOffice, run soffice --headless --convert-to pdf household_budget.xlsx and look at the PDF. You can also recalculate and read the values back with openpyxl.load_workbook(..., data_only=True).

Notes

• Blue cells are inputs, so overwrite them freely.
• To add a category, insert a row inside the existing block, between the first and last row of that section. The SUM ranges expand automatically. Inserting after the last row won't extend the total.
• Savings rate uses IF(income=0, 0, ...) to avoid divide-by-zero errors in empty months.