Spreadsheet Analyst
Overview
Two jobs: (1) analyze data that arrives as xlsx/csv, (2) deliver clean, formatted workbooks. Navin provides the document libraries, so do not check imports or install anything up front: write the script, and only if it actually fails on a missing dependency, explain the missing capability and ask before touching the environment.
Workbooks stay live: real cells, real formulas, real number formats, never values pasted from a screenshot or a picture of a chart.
Language and locale gate (mandatory)
- Before creating or exporting an XLSX or CSV, require the user to explicitly select the output language. Never infer it from the prompt, UI language, source file, names, or location. If absent, ask and stop generation.
- Preserve that language in sheet names, headers, labels, formulas shown to users, charts, legends, comments, validation messages and workbook metadata. Translate every template/example label.
- Apply locale-appropriate dates, decimal/grouping separators and currency formats consistently. Keep stored numeric/date values typed, not preformatted strings.
- For Arabic and other RTL languages, enable right-to-left sheet views, right-align text where appropriate, mirror visual layout, retain logical column order, and use fonts with Arabic glyph coverage.
- CSV must be UTF-8 (use UTF-8 with BOM only when required for the target spreadsheet application). Choose a locale-appropriate separator, announce it in the delivery message and companion README, and quote fields correctly. Never rely on an ambiguous default delimiter.
Typography & icon rules (mandatory)
- Never use em or en dashes (U+2014 / U+2013) in cell text, headers or labels. Use commas, colons or parentheses instead.
- Never use emoji or generic AI-style icons (📊 ✅ …) in workbooks. Use conditional formatting, cell fills and plain glyphs (✓ ✗) for status indicators.
HTML sheet template library
Navin materializes the selected spreadsheet template inside the active workspace
(document.html mocking the sheet layout + metadata.json + image.png
preview). When the runtime context contains a "Document Template Attachment",
the user picked one in the WebUI - it is mandatory:
Read metadata.json and document.html to map the intended layout: KPI
cards, grouped rows, header fills, conditional colors, total rows.
Copy the HTML into a working folder and put the real data in it, keeping
the structure and the styling.
Convert it into a live workbook with the bundled converter, whose command
is given in the runtime context:
<navin-python> .navin/resources/tools/html2xlsx.py document.html -o budget.xlsx --csv exports/
Numbers arrive as numbers with the number format their display implies
(currency, percent, thousands, dates), header rows get frozen panes and an
auto-filter, merged cells and column widths follow the design, and a total
row becomes a SUM formula whenever the sum matches the displayed value.
--csv writes one UTF-8 CSV per sheet. --no-formulas keeps plain values.
Reopen the workbook and refine with openpyxl: extra formulas, charts,
conditional formatting, data validation. Never rebuild the whole sheet by
hand, and never paste an image of a table.
Charts stay native: build them with openpyxl's chart API against the cell
ranges, not as a pasted matplotlib PNG.
Analysis recipes (pandas)
import pandas as pd
df = pd.read_excel("in.xlsx", sheet_name=None) # dict of all sheets
df = pd.read_csv("in.csv", sep=None, engine="python") # sniff delimiter
df.info(); df.describe(); df.isna().sum() # profile first, always
pivot = df.pivot_table(index="region", columns="mois", values="ca", aggfunc="sum")
df["date"] = pd.to_datetime(df["date"], dayfirst=True) # FR dates!
Output recipes (formatted workbook)
with pd.ExcelWriter("out.xlsx", engine="xlsxwriter") as xl:
df.to_excel(xl, sheet_name="Data", index=False)
wb, ws = xl.book, xl.sheets["Data"]
money = wb.add_format({"num_format": "#,##0.00 €"})
ws.set_column("C:C", 14, money)
ws.autofilter(0, 0, len(df), len(df.columns)-1)
ws.freeze_panes(1, 0)
chart = wb.add_chart({"type": "column"})
chart.add_series({"values": f"=Data!$C$2:$C${len(df)+1}"})
ws.insert_chart("F2", chart)
Formulas that must stay live for the user: write with openpyxl (ws["D2"] = "=B2*C2").
Workflow
- Profile the file: sheets, columns, types, nulls, duplicates - report anomalies before analyzing.
- Clean explicitly (log every transformation: "removed 12 duplicate rows on ID").
- Analyze per the question; sanity-check totals against the source.
- Deliver: findings summary in chat + formatted workbook (Data / Analysis / README sheets).
Rules
- Never overwrite the source file - output is always a new file.
- Watch FR conventions: decimal commas, dd/mm dates, spaces as thousands separators.
500k rows or joins across systems → consider database-explorer/sql-analyst.
1---2name: spreadsheet-analyst3description: Read, analyze, and produce Excel/CSV files - cleanup, pivots, formulas, charts, and formatted workbooks - using pandas and openpyxl. Use for any spreadsheet task.4metadata:5 source: https://skillmd.com/skills/navinspire-ia/spreadsheet-analyst6 navin:7 emoji: 📗8 category: documents9---1011# Spreadsheet Analyst1213## Overview1415Two jobs: (1) analyze data that arrives as xlsx/csv, (2) deliver clean, formatted workbooks. Navin provides the document libraries, so do not check imports or install anything up front: write the script, and only if it actually fails on a missing dependency, explain the missing capability and ask before touching the environment.1617Workbooks stay live: real cells, real formulas, real number formats, never values pasted from a screenshot or a picture of a chart.1819## Language and locale gate (mandatory)2021- Before creating or exporting an XLSX or CSV, require the user to explicitly select the output language. Never infer it from the prompt, UI language, source file, names, or location. If absent, ask and stop generation.22- Preserve that language in sheet names, headers, labels, formulas shown to users, charts, legends, comments, validation messages and workbook metadata. Translate every template/example label.23- Apply locale-appropriate dates, decimal/grouping separators and currency formats consistently. Keep stored numeric/date values typed, not preformatted strings.24- For Arabic and other RTL languages, enable right-to-left sheet views, right-align text where appropriate, mirror visual layout, retain logical column order, and use fonts with Arabic glyph coverage.25- CSV must be UTF-8 (use UTF-8 with BOM only when required for the target spreadsheet application). Choose a locale-appropriate separator, announce it in the delivery message and companion README, and quote fields correctly. Never rely on an ambiguous default delimiter.2627## Typography & icon rules (mandatory)2829- Never use em or en dashes (U+2014 / U+2013) in cell text, headers or labels. Use commas, colons or parentheses instead.30- Never use emoji or generic AI-style icons (📊 ✅ …) in workbooks. Use conditional formatting, cell fills and plain glyphs (✓ ✗) for status indicators.3132## HTML sheet template library3334Navin materializes the selected spreadsheet template inside the active workspace35(`document.html` mocking the sheet layout + `metadata.json` + `image.png`36preview). When the runtime context contains a "Document Template Attachment",37the user picked one in the WebUI - it is mandatory:38391. Read `metadata.json` and `document.html` to map the intended layout: KPI40 cards, grouped rows, header fills, conditional colors, total rows.412. Copy the HTML into a working folder and put the real data in it, keeping42 the structure and the styling.433. Convert it into a live workbook with the bundled converter, whose command44 is given in the runtime context:4546 ```bash47 <navin-python> .navin/resources/tools/html2xlsx.py document.html -o budget.xlsx --csv exports/48 ```4950 Numbers arrive as numbers with the number format their display implies51 (currency, percent, thousands, dates), header rows get frozen panes and an52 auto-filter, merged cells and column widths follow the design, and a total53 row becomes a `SUM` formula whenever the sum matches the displayed value.54 `--csv` writes one UTF-8 CSV per sheet. `--no-formulas` keeps plain values.554. Reopen the workbook and refine with openpyxl: extra formulas, charts,56 conditional formatting, data validation. Never rebuild the whole sheet by57 hand, and never paste an image of a table.5859Charts stay native: build them with openpyxl's chart API against the cell60ranges, not as a pasted matplotlib PNG.6162## Analysis recipes (pandas)6364```python65import pandas as pd66df = pd.read_excel("in.xlsx", sheet_name=None) # dict of all sheets67df = pd.read_csv("in.csv", sep=None, engine="python") # sniff delimiter6869df.info(); df.describe(); df.isna().sum() # profile first, always70pivot = df.pivot_table(index="region", columns="mois", values="ca", aggfunc="sum")71df["date"] = pd.to_datetime(df["date"], dayfirst=True) # FR dates!72```7374## Output recipes (formatted workbook)7576```python77with pd.ExcelWriter("out.xlsx", engine="xlsxwriter") as xl:78 df.to_excel(xl, sheet_name="Data", index=False)79 wb, ws = xl.book, xl.sheets["Data"]80 money = wb.add_format({"num_format": "#,##0.00 €"})81 ws.set_column("C:C", 14, money)82 ws.autofilter(0, 0, len(df), len(df.columns)-1)83 ws.freeze_panes(1, 0)84 chart = wb.add_chart({"type": "column"})85 chart.add_series({"values": f"=Data!$C$2:$C${len(df)+1}"})86 ws.insert_chart("F2", chart)87```8889Formulas that must stay live for the user: write with openpyxl (`ws["D2"] = "=B2*C2"`).9091## Workflow92931. Profile the file: sheets, columns, types, nulls, duplicates - report anomalies before analyzing.942. Clean explicitly (log every transformation: "removed 12 duplicate rows on ID").953. Analyze per the question; sanity-check totals against the source.964. Deliver: findings summary in chat + formatted workbook (Data / Analysis / README sheets).9798## Rules99100- Never overwrite the source file - output is always a new file.101- Watch FR conventions: decimal commas, dd/mm dates, spaces as thousands separators.102- >500k rows or joins across systems → consider `database-explorer`/`sql-analyst`.