| name | clean-data-xls |
| description | Clean up messy spreadsheet data — trim whitespace, fix inconsistent casing, convert numbers-stored-as-text, standardize dates, remove duplicates, and flag mixed-type columns. Use when data is messy, inconsistent, or needs prep before analysis. Triggers on "clean this data", "clean up this sheet", "normalize this data", "fix formatting", "dedupe", "standardize this column", "this data is messy". |
Clean Data
jdy Data Source Fallback Policy
When a skill needs external market/on-chain/macro data, use this order unless the user explicitly says otherwise:
- MCP first — use configured MCP tools when available and healthy.
- OpenCLI second — if MCP is unavailable, missing keys, rate-limited, or insufficient, use
opencli before lower-level fallbacks.
- Direct API / Chrome CDP / package fallback third — use direct HTTP APIs, the bundled
packages/chrome-cdp, or manual browser extraction only when OpenCLI has no adapter, cannot reach the page, or returns incomplete data.
- Web Search last — use search only as the final fallback or for qualitative context/news that requires multiple public sources.
Always cite which layer produced each important datapoint. If layers disagree, say so and prefer the more primary/structured source.
Clean messy data in a specified range or an entire sheet of a standalone .xlsx file.
Environment
Use Python with openpyxl to read and write .xlsx files. Read cell values via ws.cell(row, col).value, write helper-column formulas via ws.cell(row, col).value = '=TRIM(A2)'. The in-place vs helper-column decision still applies.
Data Sources
When the spreadsheet contains financial data that needs cross-referencing or enrichment during cleaning, use free data APIs:
- alpha-vantage MCP (Layer 1) — time-series data, technical indicators, company overviews
- Chrome CDP (Layer 2) —
finance.yahoo.com/quote/{ticker} for real-time prices, fundamentals, key statistics
- Web Search (Layer 3) — finance.yahoo.com, SEC EDGAR for financial statements, ratios
Use these to validate suspicious values (e.g. a stock price that looks like an outlier) or to fill missing data points when the user requests it.
Workflow
Step 1: Scope
- If a range is given (e.g.
A1:F200), use it
- Otherwise use the full used range of the active sheet
- Profile each column: detect its dominant type (text / number / date) and identify outliers
Step 2: Detect issues
| Issue | What to look for |
|---|
| Whitespace | leading/trailing spaces, double spaces |
| Casing | inconsistent casing in categorical columns (usa / USA / Usa) |
| Number-as-text | numeric values stored as text; stray $, ,, % in number cells |
| Dates | mixed formats in the same column (3/8/26, 2026-03-08, March 8 2026) |
| Duplicates | exact-duplicate rows and near-duplicates (case/whitespace differences) |
| Blanks | empty cells in otherwise-populated columns |
| Mixed types | a column that's 98% numbers but has 3 text entries |
| Encoding | mojibake (é, ’), non-printing characters |
| Errors | #REF!, #N/A, #VALUE!, #DIV/0! |
Step 3: Propose fixes
Show a summary table before changing anything:
| Column | Issue | Count | Proposed Fix |
|---|
Step 4: Apply
- Prefer formulas over hardcoded cleaned values — where the cleaned output can be expressed as a formula (e.g.
=TRIM(A2), =VALUE(SUBSTITUTE(B2,"$","")), =UPPER(C2), =DATEVALUE(D2)), write the formula in an adjacent helper column rather than computing the result in Python and overwriting the original. This keeps the transformation transparent and auditable.
- Only overwrite in place with computed values when the user explicitly asks for it, or when no sensible formula equivalent exists (e.g. encoding/mojibake repair)
- For destructive operations (removing duplicates, filling blanks, overwriting originals), confirm with the user first
- After each category of fix (whitespace → casing → number conversion → dates → dedup), show the user a sample of what changed and get confirmation before moving to the next category
- Report a before/after summary of what changed
