spreadsheets_mastery v1.0.0

name: "spreadsheets_mastery" description: "Build, edit, and analyze spreadsheets like a pro: formulas that are correct and auditable, real pivot tables, and solid financial models (NPV/IRR, amortization, mini-DCF). Triggers when a task involves Excel, Google Sheets, .xlsx files, formulas, pivot tables, or financial modeling." metadata: { "includeInPrompt": true }

Spreadsheets Mastery

Purpose

Build spreadsheets an agent or human can trust: formulas written correctly the first time, real pivot tables instead of faked summaries, and financial models that use the right functions for the cash-flow pattern. Covers Excel (.xlsx via openpyxl), Google Sheets (API v4), and the financial-modeling basics that make a model defensible.

Workflow

1. Decide the tool for the job

• Excel file, no Google account needed: use Python + openpyxl. Write formulas as strings, never computed constants.
• Google Sheet shared with humans: use Sheets API v4 with valueInputOption=USER_ENTERED so formulas parse as formulas.
• Analyze data, no spreadsheet needed: compute in pandas, write only the result to a sheet.

2. Write formulas, not values

Formulas must always be formula strings ("=SUM(A1:A10)"), never precomputed numbers. Rules:

• openpyxl: English function names, comma separators. openpyxl never evaluates; Excel calculates on open. To read results programmatically, load with data_only=True after a real Excel save.
• Sheets API: write with valueInputOption=USER_ENTERED or the =FORMULA lands as literal text.
• Keep one formula per row pattern consistent: same relative-reference structure down the column, headers that say what the number is, units in a label cell.
• The most common bug is the denominator/referencing one: SUM a column, divide by COUNT, and lock lookup keys with absolute refs ($A$1). See the cheat sheet.

3. Build pivot tables the honest way

• A real pivot table is a live aggregation anchored at one cell, with a declared source range, row/column fields, and value aggregations. Faking it with a SUMIF table is fine only if you label it a summary table, not a pivot.
• openpyxl cannot create pivot tables (read/preserve only). Options: (a) precompute the aggregation in pandas and write it out with SUMIFS-style drilldown formulas beneath, labeled clearly; (b) hand the user a sheet with clean data and documented pivot steps; (c) in Google Sheets, create a real one via API.
• Google Sheets API: batchUpdate with an updateCells request carrying a pivotTable definition on the top-left cell (fields: "pivotTable"). Editing = fetch the definition, modify, send back whole.
• Full patterns: references/pivot-tables.md.

4. Model money with the right function

• Regular equal-interval cash flows, each at end of period: NPV(rate, values) / IRR(values, [guess]).
• Irregular-dated cash flows: XNPV(rate, values, dates) / XIRR(values, dates, [guess]).
• Loan schedules: PMT(rate, nper, pv, [fv], [type]) plus an explicit amortization row per period, not one magic number.
• Cash-flow-timing gotcha: NPV assumes the first value is at the END of period 1. A time-zero investment goes OUTSIDE the NPV call and gets added separately.
• Full modeling playbook: references/financial-modeling.md.

5. Sanity-check before handing over

• Recompute one row by hand and compare. If a formula column has mixed patterns, find the outlier cell.
• Check: no hardcoded numbers inside formulas that should be inputs; inputs live in one clearly labeled input block.
• If writing with openpyxl, open the file with the demo builder (bin/build_demo.py) pattern to smoke-test formulas, tables, and formatting render before shipping.

Output Contract

Every deliverable includes: the .xlsx file or Sheet link, a one-paragraph description of what it computes and which inputs drive it, and the names of the sheets and key formula columns. Financial models additionally state the assumptions (rates, periods, timing) and which function handles irregularity.

Operating Rules

1. Formulas in the file must be formula strings, never baked numbers (unless the cell is a literal input).
2. Never claim a pivot table exists when the file holds a static summary. Call it what it is.
3. Verify every function name and argument order against references/formulas-cheatsheet.md or the linked docs. No invented functions.
4. Keep SKILL.md tight: details live in references/, not in chat.
5. Style: plain and direct language, no em dashes, no marketing phrasing anywhere in output.