ropimen/claude-skill-kpi-calculator

KPI Calculator

Turns one or more CSV/TSV files plus a short instruction into a verified set of KPIs, each with value, unit, equation, meaning and basis label. There is no fixed list of fields: KPIs are designed for whatever the data and the instruction describe. Papers, reports or earlier results attached next to the data contribute their KPIs, applied to the user's data with their provenance.

<skill_dir> below means the folder containing this file. Run scripts as python <skill_dir>/scripts/<name>.py from the work folder (see "Where outputs go"), passing data files by absolute path.

If python is not found (or opens the Microsoft Store), use py -3 on Windows or python3 elsewhere in every command below. Check once at the start with python --version.

Hard rules

1. Every number comes from code run on the files. Never estimate, round by eye, or state a value in prose that is not in results.json.
2. Excluded KPIs are never computed and never appear anywhere except the single "Excluded at your request" line.
3. Every assumption is stated in "What I understood" (pattern, roles, units, interpolation, time zone, day/month order, defaults used).
4. At most one question per run, and only when a wrong guess would change the results (see references/role-mapping.md, "When to ask"). Otherwise state the reading taken and proceed.
5. Every KPI is a spec in kpi_specs.json built from primitives. Each is labelled basis: established (standard in the field) or derived (built from first principles).
6. Ratios over periods are ratios of sums, not means of ratios, unless the KPI is defined otherwise.
7. Irregular time is real time. Never count rows as time steps; integrate and average over timestamps with each column's interpolation.
8. Validation failures are rechecked once (parse notes → mapping → spec), then reported with the warning, never hidden.
9. Field knowledge lives in Markdown only. Never add field-specific logic to scripts.
10. Values from references are quotes. Numbers reported in papers or earlier results go only into a spec's reference.reported; they are never inputs to a computation and never presented as the user's result.

Where outputs go

Decide this at step 1 and record it in request.json → output_dir.

Situation	Work folder (all JSON files, scripts' cwd)	Deliverables
The user names a folder ("save the results in D:/reports/line3")	that folder (create it)	same folder
Claude apps (claude.ai / desktop chat, /mnt/user-data/outputs/ exists)	the current directory	copy into /mnt/user-data/outputs/: they appear as downloadable files
Claude Code, no folder named	kpi_output/ in the current directory	same folder

Deliverables are report.md, kpi_results.csv, kpi_run.py, and when produced kpi_chart.png, kpi_adhoc.py and an offered field pack. The working files (request.json, profile.json, mapping.json, kpi_specs.json, results.json) stay in the work folder for audit and are shared only if asked.

Hand the deliverables to the user so they can save them anywhere:

• Claude apps: besides the downloadable files, show the finished report as a Markdown document artifact, so it can be read, copied or downloaded on its own.
• Claude Code: list each deliverable as a link to its path. When the host offers a tool for sending files to the user (for example file cards in the desktop app), send the deliverables through it too.
• Never write outside the work folder and the deliverables location unless the user asks.

To rerun kpi_run.py later, put it in a folder together with the input files.

Scripts first, ad hoc only as a fallback

Always run the scripts below. Write your own pandas code only when:

• a script exits with an error you cannot fix by correcting profile.json, mapping.json or the specs (for example a file profile_csv.py cannot parse), or
• a KPI needs an operation no primitive or expression covers (check_spec.py reports unknown_op).

Then:

1. Put the code in kpi_adhoc.py in the working directory, load data with the parse settings in profile.json (or scripts/lib/io.py load), and keep it runnable on its own.
2. Add each ad-hoc KPI to results.json → kpis with every field of schemas/results.schema.json and "computed_by": "ad_hoc", after run_specs.py has written the file. Never change values the engine computed.
3. The report marks those KPIs with † and points to kpi_adhoc.py.

Never use eval/exec on user- or data-derived strings, even ad hoc.

Workflow

Run the eight steps in order. Read a reference file only when its step needs it.

Step 1 [Claude] Parse the request → request.json

Read references/request-parsing.md. Extract field, mode, focus, include, exclude, custom, params, max_kpis, output_dir.

• References (papers, reports, standards, earlier results) attached or named in the prompt: read references/reference-documents.md, tell them apart from data files, read each one in full and record its KPIs in request.json → references (id, name, definition, equation, location, reported values). Only data files go to step 2.

• Excel input: convert each sheet to its own CSV first (pandas.read_excel(sheet_name=None)), name them <book>__<sheet>.csv.

• A KPI both included and excluded → this is the run's one question. Compute nothing for that KPI until answered.

Step 2 [script] Load and profile → profile.json

python <skill_dir>/scripts/profile_csv.py FILE [FILE ...] --out profile.json

Check every column's type, unit hint and parse notes. If a column that should be numeric came out as text, look at its samples and fix the parse before going on.

Step 3 [script] Detect time and collection pattern → profile.json (time section)

python <skill_dir>/scripts/detect_time.py --profile profile.json [--tz <IANA tz>] --out profile.json

Read references/collection-patterns.md. The script proposes one pattern with evidence and other plausible candidates, plus entity_column (when timestamps repeat across a grouping column), frequency (pandas alias such as D, B, h, MS), gaps, duplicates and tz. Confirm or override the pattern and entity using the user's description. If ambiguous_dayfirst is true, state the assumption (or ask, if both readings change KPI values and no other question is pending). Pass --tz when the user names the time zone of naive local timestamps.

Step 4 [Claude] Map column roles → mapping.json

Read references/role-mapping.md. Assign each column a role, physical quantity, unit and interpolation; record entity, pairs, joins, reshape, resample and params. Headers in any language are read by meaning; keep original names.

Then write mapping.json → sensitivity: one variant for each judgement call that could reasonably have gone another way (interpolation, an ambiguous state class, day/month order, planned downtime, an assumed unit or parameter). The engine reruns every KPI under each variant and reports a low–high range, so the user sees which numbers depend on which assumptions. Rules and patch format are in references/role-mapping.md, "Sensitivity variants".

Step 5 [Claude] Design and select KPIs → kpi_specs.json

Read references/kpi-discovery.md (method) and references/kpi-spec.md (format, primitives, grammar). If references/fields/ has a pack matching the field, read it too; it informs but never limits.

Selection order: required (include) and custom first → KPIs from references (spec source: reference with a reference block, see references/reference-documents.md) → never exclude → by relevance to focus, established before derived, no near-duplicates → cap Claude's own picks at max_kpis (default 12). Required, custom and reference KPIs are never dropped; a reference KPI the data cannot support gets no spec and is listed under Not calculated automatically.

Then check the specs:

python <skill_dir>/scripts/check_spec.py --profile profile.json --mapping mapping.json --specs kpi_specs.json \
       --request request.json

Exit 0 = ok. Exit 1 = errors as JSON on stdout: fix the spec or mapping and rerun. Unknown units come back as warnings: if one changes results, it may be the run's question.

Step 6–7 [script] Compute and validate → results.json, kpi_run.py

python <skill_dir>/scripts/run_specs.py --profile profile.json --mapping mapping.json --specs kpi_specs.json \
       --request request.json --out results.json --script-out kpi_run.py

Each spec runs per group (entity) plus an overall value by its overall rule. A failing spec or group becomes not_computed with its reason; the run continues. The stdout summary lists every validation: warning.

The same run also:

• reruns all KPIs once per sensitivity variant and stores sensitivity: {low, high, variants} on each KPI;
• compares each KPI with the values its references report, converted to the KPI's unit (comparison: difference, relative difference, better/worse by direction, whether the reported value lies inside the sensitivity range);
• writes data_health: per mapped numeric column, missing share, longest constant run, robust outliers and impossible values (negative counts, % above 100, below absolute zero), plus time coverage for regular series.

If any KPI carries a validation warning: recheck parse notes (step 2), mapping (step 4) and spec (step 5) once, rerun from the earliest step you changed, then report with the warning if it still fails.

After the run, you may edit results.json only to add an enable text to not_computed entries (always for KPIs from references and include) (what data or parameter would make the KPI computable) and, per the fallback above, ad-hoc KPI records. kpi_run.py embeds the inputs' hashes, the profile, mapping, specs and the skill's code; it reproduces results.json (as results_rerun.json) without the skill.

Step 8 [script + Claude] Report → report.md, kpi_results.csv, optional chart

python <skill_dir>/scripts/report.py --results results.json --request request.json \
       --mapping mapping.json --profile profile.json \
       --out-md report.md --out-csv kpi_results.csv [--chart kpi_chart.png]

Pass --chart only when the focus involves change over time and a time column exists; it plots up to two KPIs with per_period values (give those KPIs a per_period scope). The script refuses to run if an excluded KPI is in results.json.

Read references/report-format.md, then replace the placeholders the script lists on stdout, editing report.md in place:

• {{FINDINGS}}: 2–5 sentences answering the focus, citing KPI values with units from results.json. No new numbers. Name KPIs whose sensitivity range is wide (and the reading that moves them), the comparison with references when there is one (using the computed difference and verdict, with the comparability limits), and any data health flag on a column a key KPI uses.
• {{READING:<kpi_id>}}: one sentence per KPI: good or bad relative to its direction and typical range if known.

Everything else in the report (names, equations, meanings, numbers) comes from results.json and is not rewritten. Show the finished report.md in chat and deliver the files as described in "Where outputs go".

Output checklist

Before answering, confirm all of these:

• Chat report with the eight sections in order: What I understood · KPI overview · KPI details · Findings · Not calculated · Excluded at your request · Data issues · Reproduce.
• Every KPI shows value, unit, direction, basis label, source, LaTeX equation and plain-text equation, symbols mapped to original column names with n, meaning, reading, warnings.
• Every required KPI is either computed or listed under "Not calculated" with what data or parameter would enable it.
• No excluded KPI appears anywhere except the one "Excluded" line (check the CSV too).
• Collection pattern and every assumption stated; every judgement call either asked, or stated and tested as a sensitivity variant.
• Data health table present; flags on inputs of key KPIs mentioned in the findings.
• Every KPI found in a reference is either computed (with citation, location, definition and adaptation shown) or listed under "Not calculated" with what would enable it; reported values are labelled as quoted.
• kpi_results.csv (UTF-8 with BOM) and kpi_run.py written, and every deliverable handed to the user per "Where outputs go" (downloads + report artifact in Claude apps; paths and file cards in Claude Code).
• Chart only if the focus is about change over time.
• Field with no pack in references/fields/: offer a field pack built from the KPIs used this run, as a separate Markdown file following references/fields/_template.md, for the user to review and add. Do not write it into the skill folder.

Non-goals

No forecasting or model building (the skill evaluates predictions it is given). No live feeds or databases. No investment, medical or engineering advice: report metrics and what they indicate. No currency conversion: mixed currencies are flagged and computed per currency.

Defaults (use without asking, state when used)

Setting	Default
Risk-free rate	0
Annualisation factor	weekdays-only daily 252, calendar daily 365, weekly 52, monthly 12, quarterly 4, hourly 8760
MASE seasonal period	hourly 24, daily 7, weekly 52, monthly 12, quarterly 4, otherwise 1
Irregular threshold	interval CV > 0.5
Gap threshold	3 × median interval
Time column parse rate	≥ 95%
Profile sampling	> 1,000,000 rows → 200,000-row sample (time column always read in full)
max_kpis	12 (Claude's own picks only)
Percent handling	0–100 values in a column whose header or cells carry % → divide by 100
Time zone	data offsets → params.tz → UTC
Interpolation	step for irregular_events and state_log, linear for regular_series
Std ddof	1
Annualised KPI on < 1 year of data	computed and flagged as extrapolated

Reference files

File	Read at
references/request-parsing.md	step 1
references/reference-documents.md	step 1 and 5, when papers, reports or earlier results are attached
references/collection-patterns.md	step 3
references/role-mapping.md	step 4
references/kpi-discovery.md	step 5
references/kpi-spec.md	step 5, and when writing a custom KPI
references/report-format.md	step 8
references/fields/	step 5 if a pack matches; end of run to offer a new pack
schemas/*.schema.json	whenever writing a JSON file by hand or ad hoc