Spreadsheet Auditing skill for Codex

Gives the Codex app a display name, a one-line description and a starter prompt for this skill.

interface:
  display_name: "Spreadsheet Auditing"
  short_description: "Find the broken formula before the board does"
  default_prompt: "$spreadsheet-auditing Audit pricing-model.xlsx for formula mistakes before it goes to the CFO"

#!/usr/bin/env python3
"""Static audit of an .xlsx/.xlsm workbook using only the standard library.

Usage:
  python3 xlsx_audit.py model.xlsx               # text report
  python3 xlsx_audit.py --json model.xlsx        # JSON report
  python3 xlsx_audit.py --strict model.xlsx      # warnings also fail (exit 1)
  python3 xlsx_audit.py --limit 50 model.xlsx    # list up to 50 cells per finding

Reads the file's XML directly (no Excel, no openpyxl) and reports:
  errors   : cached error values (#REF!, #DIV/0!, #N/A, ...), #REF! inside formulas or
             defined names, external workbook links
  warnings : formulas inconsistent with both neighbours in their row or column,
             hard-coded constants in formula arithmetic, approximate-match lookups,
             numbers stored as text, formulas without cached values, manual calc mode
  info     : hidden / very hidden sheets, volatile functions
It does not recalculate; cached values are whatever the last saving application wrote.

Exit codes: 0 = no errors, 1 = errors (or warnings with --strict), 2 = cannot read file.
"""
import argparse
import json
import posixpath
import re
import sys
import zipfile
from collections import defaultdict
import xml.etree.ElementTree as ET

M = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
R = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}"
PR = "{http://schemas.openxmlformats.org/package/2006/relationships}"

STRING_LIT = re.compile(r'"(?:[^"]|"")*"')
QUOTED_SHEET = re.compile(r"'(?:[^']|'')*'!")
CELL_REF = re.compile(r"(?<![A-Za-z0-9_.\[])(\$?)([A-Z]{1,3})(\$?)([0-9]{1,7})(?![A-Za-z0-9_(\[])")
NUM_IN_ARITH = re.compile(
    r"(?:(?<=[-+*/^])\s*(\d+(?:\.\d+)?)(?![\d.:A-Za-z_(!]))"      # operator then number
    r"|(?:(?<![A-Za-z0-9_.$:])(\d+(?:\.\d+)?)\s*(?=[-+*/^]))"      # number then operator
)
VOLATILE = re.compile(r"\b(INDIRECT|OFFSET|NOW|TODAY|RAND|RANDBETWEEN|RANDARRAY)\s*\(")
# numeric-looking text; leading-zero codes such as 00123 are treated as deliberate text
NUMERIC_TEXT = re.compile(r"^\s*[-+]?([1-9]\d{0,2}(,\d{3})+|[1-9]\d*|0)(\.\d+)?\s*%?\s*$")


def col_to_num(col):
    n = 0
    for ch in col:
        n = n * 26 + ord(ch) - 64
    return n


def split_ref(ref):
    m = re.match(r"([A-Z]+)(\d+)$", ref)
    return col_to_num(m.group(1)), int(m.group(2))


def clean(formula):
    f = formula.replace("_xlfn.", "").replace("_xlws.", "")
    f = STRING_LIT.sub('""', f)
    return QUOTED_SHEET.sub("'S'!", f)


def to_r1c1(formula, col, row):
    def sub(m):
        cabs, c, rabs, r = m.groups()
        cn, rn = col_to_num(c), int(r)
        if cn > 16384:
            return m.group(0)
        rpart = f"R{rn}" if rabs else f"R[{rn - row}]"
        cpart = f"C{cn}" if cabs else f"C[{cn - col}]"
        return rpart + cpart
    return CELL_REF.sub(sub, clean(formula))


def lookup_issue(formula):
    """Return True if a VLOOKUP/HLOOKUP uses approximate match (3 args, or TRUE/1)."""
    f = clean(formula).upper()
    for m in re.finditer(r"\b[VH]LOOKUP\s*\(", f):
        depth, args, cur, i = 1, [], "", m.end()
        while i < len(f) and depth:
            ch = f[i]
            if ch in "({":
                depth += 1
            elif ch in ")}":
                depth -= 1
                if depth == 0:
                    break
            if ch == "," and depth == 1:
                args.append(cur)
                cur = ""
            else:
                cur += ch
            i += 1
        args.append(cur)
        if len(args) == 3 or (len(args) >= 4 and args[3].strip() in ("TRUE", "1")):
            return True
    return False


def read_xml(z, name):
    return ET.fromstring(z.read(name))


def rels_for(z, part):
    base, fname = posixpath.split(part)
    rel = posixpath.join(base, "_rels", fname + ".rels")
    out = {}
    if rel in z.namelist():
        for r in read_xml(z, rel).iter(PR + "Relationship"):
            target = r.get("Target", "")
            if r.get("TargetMode") != "External":
                target = posixpath.normpath(posixpath.join(base, target)) if not target.startswith("/") else target.lstrip("/")
            out[r.get("Id")] = (target, r.get("TargetMode"))
    return out


def audit(path, limit):
    z = zipfile.ZipFile(path)
    names = set(z.namelist())
    findings = defaultdict(list)  # (severity, category) -> [location strings]

    def add(sev, cat, loc):
        findings[(sev, cat)].append(loc)

    wb = read_xml(z, "xl/workbook.xml")
    wb_rels = rels_for(z, "xl/workbook.xml")
    calc = wb.find(M + "calcPr")
    if calc is not None and calc.get("calcMode") == "manual":
        add("warning", "workbook saved in manual calculation mode", "workbook")

    for dn in wb.iter(M + "definedName"):
        text = dn.text or ""
        if "#REF!" in text:
            add("error", "defined name refers to #REF!", f"name {dn.get('name')}")
        if re.search(r"\[\d+\]", text):
            add("error", "defined name points to an external workbook", f"name {dn.get('name')}")

    for part in sorted(n for n in names if n.startswith("xl/externalLinks/") and n.endswith(".xml")):
        targets = [t for t, mode in rels_for(z, part).values()]
        add("error", "external workbook link", f"{part} -> {', '.join(targets) or '?'}")

    shared = []
    if "xl/sharedStrings.xml" in names:
        for si in read_xml(z, "xl/sharedStrings.xml").iter(M + "si"):
            shared.append("".join(t.text or "" for t in si.iter(M + "t")))

    for sheet in wb.iter(M + "sheet"):
        sname = sheet.get("name")
        state = sheet.get("state", "visible")
        if state != "visible":
            add("info", f"{state} sheet", sname)
        target = wb_rels.get(sheet.get(R + "id"), (None, None))[0]
        if not target or target not in names:
            continue
        cells = {}          # (col,row) -> r1c1 form
        shared_masters = {}  # si -> r1c1 form
        pending_shared = []
        for c in read_xml(z, target).iter(M + "c"):
            ref = c.get("r")
            if not ref:
                continue
            col, row = split_ref(ref)
            loc = f"{sname}!{ref}"
            t = c.get("t")
            v = c.find(M + "v")
            f = c.find(M + "f")
            if t == "e" and v is not None:
                add("error", f"cell shows {v.text}", loc)
            text_value = None
            if t == "s" and v is not None and (v.text or "").isdigit() and int(v.text) < len(shared):
                text_value = shared[int(v.text)]
            elif t == "inlineStr":
                text_value = "".join(x.text or "" for x in c.iter(M + "t"))
            if f is None and text_value is not None and NUMERIC_TEXT.match(text_value):
                add("warning", "number stored as text", loc)
            if f is None:
                continue
            ftext = f.text or ""
            if f.get("t") == "shared":
                si = f.get("si")
                if ftext:
                    shared_masters[si] = to_r1c1(ftext, col, row)
                else:
                    pending_shared.append((si, col, row))
                    continue
            elif f.get("t") == "array" and not ftext:
                continue
            if (v is None or not (v.text or "").strip()) and t != "str":
                add("warning", "formula has no cached value (never calculated)", loc)
            if "#REF!" in ftext:
                add("error", "formula contains #REF!", loc)
            if re.search(r"\[\d+\]", ftext):
                add("error", "formula references an external workbook", loc)
            if VOLATILE.search(clean(ftext).upper()):
                add("info", "volatile function", loc)
            if lookup_issue(ftext):
                add("warning", "VLOOKUP/HLOOKUP approximate match (missing or TRUE 4th arg)", loc)
            nums = [a or b for a, b in NUM_IN_ARITH.findall(CELL_REF.sub("REF", clean(ftext)))]
            nums = [n for n in nums if n not in ("0", "1")]
            if nums:
                add("warning", "hard-coded constant in formula arithmetic", f"{loc} ({', '.join(nums[:3])})")
            cells[(col, row)] = to_r1c1(ftext, col, row)
        for si, col, row in pending_shared:
            if si in shared_masters:
                cells[(col, row)] = shared_masters[si]

        for (col, row), form in cells.items():
            for d1, d2 in (((0, -1), (0, 1)), ((-1, 0), (1, 0))):
                a = cells.get((col + d1[0], row + d1[1]))
                b = cells.get((col + d2[0], row + d2[1]))
                if a is not None and a == b and a != form:
                    ref = f"{sname}!{num_to_col(col)}{row}"
                    add("warning", "formula inconsistent with neighbours", ref)
                    break

    report = []
    order = {"error": 0, "warning": 1, "info": 2}
    for (sev, cat), locs in sorted(findings.items(), key=lambda kv: (order[kv[0][0]], kv[0][1])):
        report.append({"severity": sev, "category": cat, "count": len(locs), "examples": locs[:limit]})
    return report


def num_to_col(n):
    s = ""
    while n:
        n, r = divmod(n - 1, 26)
        s = chr(65 + r) + s
    return s


def main():
    ap = argparse.ArgumentParser(description="Static audit of an .xlsx workbook")
    ap.add_argument("workbook")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--strict", action="store_true", help="exit 1 on warnings too")
    ap.add_argument("--limit", type=int, default=10, help="example cells listed per finding")
    args = ap.parse_args()
    try:
        report = audit(args.workbook, args.limit)
    except (OSError, zipfile.BadZipFile, KeyError, ET.ParseError) as exc:
        print(f"error: cannot read {args.workbook} as .xlsx: {exc}", file=sys.stderr)
        return 2
    errors = sum(r["count"] for r in report if r["severity"] == "error")
    warnings = sum(r["count"] for r in report if r["severity"] == "warning")
    if args.json:
        print(json.dumps({"errors": errors, "warnings": warnings, "findings": report}, indent=2))
    else:
        for r in report:
            print(f"[{r['severity'].upper():7}] {r['category']}: {r['count']}")
            for loc in r["examples"]:
                print(f"           {loc}")
        print(f"{errors} error(s), {warnings} warning(s)")
    return 1 if errors or (args.strict and warnings) else 0


if __name__ == "__main__":
    sys.exit(main())