🥮 mooncakes.io

    sheetsentry

    Calculation slicing and model audit for Excel files, written in MoonBit.

    excel
    xlsx
    slice
    calculation
    audit
    moonbit
    Download zip
    Author
    Version
    0.10.0
    License
    Apache-2.0
    Repository
    Last updated
    2 days ago
    Downloads
    118

    #SheetSentry

    Calculation slicing and model audit for Excel files, written in MoonBit.

    SheetSentry extracts a reproducible calculation slice from any target cell in an .xlsx workbook: it follows the target's precedents, writes a new standalone workbook containing only the required sheets and cells, and records a SHA-256 manifest plus target-value verification. An audit layer adds formula-risk rules, baselines, filters, and reports.

    It is designed for finance, audit, FP&A, and data teams that want spreadsheet checks to run like code checks: deterministic, scriptable, and CI-friendly.

    #Why

    Spreadsheet errors are expensive and easy to miss:

    • a formula is copied down a column but one row points at the wrong cell;
    • a model output depends on a hidden sheet;
    • a hardcoded growth rate is buried inside a formula;
    • a circular reference only appears after a workbook is opened;
    • an error value is cached behind a formula and never noticed.

    Excel's built-in Trace Precedents / Trace Dependents are useful, but they are manual, single-cell, and not available in CI. SheetSentry makes the same ideas scriptable.

    #Status

    0.10.0 MVP, implemented in MoonBit. The current release supports:

    • XLSX reading and writing through moonbitlang/mbtexcel
    • calculation-slice extraction to a standalone .xlsx
    • multi-target slices and an HTML slice certificate
    • slice/source SHA-256, minimality detail checks, and per-target verification
    • independent recalculation of every target with a calc_verified result
    • 30-function coverage fixture plus cross-implementation checks with formulas==1.3.4
    • --targets-file batch input and --strict CI gates
    • named ranges, literal INDIRECT, constant OFFSET, and table structured references
    • external workbook mapping with value import and formula rewriting
    • SHA-256 slice manifests with completeness and unresolved-dependency reporting
    • verify-slice for target-value reproducibility checks
    • cross-sheet formula reference extraction
    • dependency graph construction
    • ten audit rules, baseline suppression, config files, and graph export as supporting diagnostics
    • terminal, JSON, SARIF, and self-contained HTML reports
    • trace for precedents and dependents
    • DOT / Mermaid dependency graph export
    • JSON config files for rule filtering and ignored cells
    • CI-friendly --fail-on exit codes and baselines

    #Calculation slice

    A slice answers: “What is the smallest self-contained workbook that reproduces this output?”

    moon run --target native cmd/main -- slice model.xlsx --target "Summary!B3" --out slice.xlsx --manifest slice.json
moon run --target native cmd/main -- verify-slice slice.xlsx --manifest slice.json

    Common reference support:

    • named ranges are resolved and copied into the slice;
    • INDIRECT("Sheet!A1") and constant OFFSET(A1,r,c) are resolved;
    • table references such as OpexTable[Amount] are resolved and rewritten to A1 ranges;
    • external references can be imported with --external book.xlsx=path; referenced values are copied into __ext__* sheets and formulas are rewritten.

    The manifest records:

    • the target and included cells/sheets;
    • source and slice SHA-256 hashes;
    • target value before/after extraction;
    • whether the slice is complete;
    • unresolved dynamic references, named ranges, external links, or missing dependencies.

    Only the sheets and cells needed by the target are copied. Unrelated sheets and data are not included.

    #Quick start

    Requires the MoonBit toolchain.

    moon update
moon check --target native
moon test --target native
moon run --target native cmd/main -- audit fixtures/bad_model.xlsx

    Expected output:

    SheetSentry: fixtures/bad_model.xlsx
  sheets: 5  formula cells: 17  findings: 14
  high: 4  medium: 7  low: 3
  [medium] SS005  Opex!C6  Formula lives on a hidden row.
  [medium] SS003  Summary!B2  Formula embeds numeric constant 1.05; prefer a named input cell.
  [high] SS001  Summary!B4  Cell contains error value #REF!.
  ...

    #CLI

    # Terminal report
moon run --target native cmd/main -- audit model.xlsx

# JSON
moon run --target native cmd/main -- audit model.xlsx --format json --out findings.json

# SARIF for code scanning
moon run --target native cmd/main -- audit model.xlsx --format sarif --out results.sarif

# Self-contained HTML report
moon run --target native cmd/main -- audit model.xlsx --format html --out report.html

# Trace precedents (what this cell depends on)
moon run --target native cmd/main -- trace model.xlsx --cell "Summary!B7" --direction precedents --depth 4

# Trace dependents (what breaks if this cell changes)
moon run --target native cmd/main -- trace model.xlsx --cell "Inputs!B2" --direction dependents --depth 4

# Fail CI if any high-severity finding exists
moon run --target native cmd/main -- audit model.xlsx --fail-on high

# Create a baseline for existing findings
moon run --target native cmd/main -- audit model.xlsx --write-baseline baseline.json

# Fail only on new findings
moon run --target native cmd/main -- audit model.xlsx --baseline baseline.json --fail-on low

# Show only high-severity findings
moon run --target native cmd/main -- audit model.xlsx --min-severity high

# Disable noisy rules for a specific run
moon run --target native cmd/main -- audit model.xlsx --disable SS003,SS004

# Load a project config
moon run --target native cmd/main -- audit model.xlsx --config sheetsentry.json

    #Rules

    IDSeverityRuleWhy it matters
    SS001HighError value#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?, #NUM! silently poison downstream results.
    SS002MediumFormula inconsistencyOne cell in a copied row/column uses a different formula pattern.
    SS003MediumHardcoded constantA numeric constant is embedded in a formula instead of living in an input cell.
    SS004LowVolatile functionTODAY, NOW, RAND, RANDBETWEEN, OFFSET, INDIRECT, INFO, CELL make results time- or environment-dependent.
    SS005MediumHidden formulaFormulas on hidden sheets or hidden rows are easy to miss during review.
    SS006HighCircular referenceA dependency cycle makes calculation order undefined or unstable.
    SS007LowDynamic referenceINDIRECT / OFFSET prevent complete static dependency tracing.
    SS008MediumExternal workbookFormula references another workbook, making the model non-self-contained.
    SS009LowWhole-axis referenceA:A / 1:1 can be expensive and ambiguous.
    SS010LowMissing cached valueSome readers may show a blank until the workbook is recalculated.

    The rule catalog is intentionally small and deterministic. Every finding has a stable rule ID, severity, sheet, cell, message, optional formula, and related cells.

    #Config

    {
  "min_severity": "medium",
  "disabled_rules": ["SS004"],
  "ignore_cells": ["Summary!B2"],
  "fail_on": "high"
}

    moon run --target native cmd/main -- audit model.xlsx --config sheetsentry.json

    #Baseline

    Large models often have known findings. Generate a baseline once, commit it, and let CI fail only on new findings:

    moon run --target native cmd/main -- audit model.xlsx --write-baseline sheetsentry.baseline.json
moon run --target native cmd/main -- audit model.xlsx --baseline sheetsentry.baseline.json --fail-on low

    A fingerprint is rule|sheet|cell, so moving a finding to another cell makes it new again.

    #Reports

    • Terminal — fast review in a shell.
    • JSON — machine-readable for custom dashboards.
    • SARIF 2.1.0 — intended for GitHub Code Scanning and other SARIF consumers.
    • HTML — a single self-contained file with summary cards and a findings table; no network or external assets.

    #CI example

    - name: Audit Excel model
  run: |
    moon run --target native cmd/main -- \
      audit model.xlsx --format sarif --out results.sarif --fail-on high

- name: Upload SARIF
  if: always()
  uses: github/codeql-action/upload-sarif@v3
  with:
    sarif_file: results.sarif

    The repository's own CI runs moon update, moon check, moon test, and moon build on every push.

    #Architecture

    flowchart LR
  XLSX[XLSX bytes] --> Reader[mbtexcel reader]
  Reader --> Cells[Formula cells + cached values + visibility]
  Cells --> Refs[Formula reference parser]
  Refs --> Graph[Cross-sheet dependency graph]
  Graph --> Rules[Rule engine]
  Cells --> Rules
  Rules --> Report[Terminal / JSON / SARIF / HTML]
  Graph --> Trace[Precedents / dependents]

    Core modules:

    • slice.mbt — calculation-slice extraction, standalone workbook reconstruction, SHA-256 manifest, and verification.
    • formula.mbt — A1 / range / sheet-qualified reference extraction, normalization, function names, numeric literals.
    • audit.mbt — workbook scan, dependency graph, rule engine, cycle detection.
    • trace.mbt — graph construction and BFS traversal for precedents/dependents.
    • report.mbt — terminal, JSON, SARIF, and HTML renderers.
    • cmd/main/main.mbt — CLI.

    #Testing

    moon test --target native

    Tests cover:

    • reference extraction including quoted sheet names and absolute references;
    • function-name and numeric-literal extraction;
    • formula normalization across rows;
    • all ten audit rules on fixtures/bad_model.xlsx;
    • a clean workbook with zero findings;
    • report rendering;
    • baseline round-trip and suppression;
    • severity and disabled-rule filters;
    • graph export;
    • rule catalog;
    • 74 tests in total.
    • precedents and dependents tracing;
    • fail thresholds.

    Fixtures are generated reproducibly with:

    PYTHONPATH=../tools/pylib python3 scripts/make_fixtures.py

    The Python script is a test-data generator only; the analyzer itself is MoonBit.

    #Limitations

    • XLSX only (no .xls, .ods, or Google Sheets yet).
    • Formula references are extracted statically; INDIRECT and OFFSET are reported but cannot be fully resolved.
    • Structured references (Table1[Column]) and external workbook links are not yet part of the dependency graph.
    • Cycle detection currently reports simple dependency cycles; complex strongly connected components are on the roadmap.
    • The rule engine is heuristic. Use the rule catalog and CI thresholds to fit your team's conventions.

    #Roadmap

    • Baseline file to suppress known findings and fail only on new ones.
    • More rules: orphan calculations, external links, merged cells in data regions, inconsistent units, over-long formulas.
    • Better cycle reporting with strongly connected components.
    • --fix for safe, deterministic rewrites.
    • WASM build so the report can run fully in a browser.

    #License

    Apache-2.0. See LICENSE.

    #Hackathon

    Built for the 2026 MoonBit Hackathon, October season.

    AuditConfig

    source
    pub(all) struct AuditConfig {
      min_severity : String
      disabled_rules : Array[String]
      ignore_cells : Array[String]
      fail_on : String
    }

    AuditReport

    source
    pub(all) struct AuditReport {
      file : String
      sheets : Array[String]
      formula_cells : Int
      findings : Array[Finding]
      suppressed : Array[Finding]
    }

    CellRef

    source
    pub(all) struct CellRef {
      start : Int
      end : Int
      sheet : String?
      row : Int
      col : Int
      end_row : Int
      end_col : Int
      abs_row : Bool
      abs_col : Bool
    }

    DependencyGraph

    source
    pub(all) struct DependencyGraph {
      edges : Map[String, Array[String]]
      reverse : Map[String, Array[String]]
    }

    DependencyGraph::new

    source

    ExternalRef

    source
    pub(all) struct ExternalRef {
      workbook : String
      sheet : String
      row : Int
      col : Int
      start : Int
      end : Int
    }

    Finding

    source
    pub(all) struct Finding {
      rule_id : String
      severity : Severity
      sheet : String
      cell : String
      formula : String?
      message : String
      related : Array[String]
    }

    FormulaRefs

    source
    pub(all) struct FormulaRefs {
      cells : Array[CellRef]
      names : Array[String]
      dynamic : Bool
    }

    Severity

    source
    pub(all) enum Severity {
      High
      Medium
      Low
    }

    Severity::label

    source
    fn Severity::label(self : Severity) -> String

    SliceManifest

    source
    pub(all) struct SliceManifest {
      source_file : String
      target : String
      targets : Array[SliceTarget]
      source_sha256 : String
      slice_sha256 : String
      included_cells : Array[String]
      included_sheets : Array[String]
      unresolved : Array[String]
      external_workbooks : Array[String]
      external_cells : Array[String]
      total_used_cells : Int
      included_used_cells : Int
      excluded_used_cells : Int
      reduction_ratio : String
      minimality_verified : Bool
      unreachable_included : Array[String]
      missing_required : Array[String]
      excluded_reachable : Array[String]
      target_value_before : String?
      target_value_after : String?
      verified : Bool
      calc_verified : Bool
      complete : Bool
    }

    SliceResult

    source
    pub(all) struct SliceResult {
      data : Bytes
      manifest : SliceManifest
    }

    SliceTarget

    source
    pub(all) struct SliceTarget {
      target : String
      before : String?
      after : String?
      calculated : String?
      verified : Bool
      calc_verified : Bool
    }

    SliceVerification

    source
    pub(all) struct SliceVerification {
      target : String
      before : String?
      after : String?
      verified : Bool
      hash_verified : Bool
      targets_total : Int
      targets_verified : Int
      targets_calc_verified : Int
      calc_verified : Bool
      source_hash_verified : Bool
      minimality_verified : Bool
      error : String?
    }

    TableInfo

    source
    pub(all) struct TableInfo {
      name : String
      sheet : String
      start_row : Int
      start_col : Int
      end_row : Int
      end_col : Int
      columns : Array[String]
    }

    WorkbookSummary

    source
    pub(all) struct WorkbookSummary {
      sheets : Array[String]
      formula_cells : Int
      stored_cells : Int
    }

    all_fingerprints

    source
    fn all_fingerprints(report : AuditReport) -> Array[String]

    apply_baseline

    source
    fn apply_baseline(report : AuditReport, fingerprints : Array[String]) -> AuditReport

    apply_config

    source
    fn apply_config(report : AuditReport, config : AuditConfig) -> AuditReport

    audit_bytes

    source
    fn audit_bytes(bytes : Bytes, file : String) -> AuditReport raise 
    XlsxError

    baseline_json

    source
    fn baseline_json(report : AuditReport) -> String

    build_graph_bytes

    source
    fn build_graph_bytes(bytes : Bytes) -> DependencyGraph raise 
    XlsxError

    count_severity_public

    source
    fn count_severity_public(report : AuditReport, severity : Severity) -> Int

    default_config

    source
    fn default_config() -> AuditConfig

    dynamic_refs

    source
    fn dynamic_refs(formula : StringView, current_sheet : String, _current_row : Int, _workbook : 
    Workbook    ) -> Array[CellRef]

    external_refs

    source
    fn external_refs(formula : StringView) -> Array[ExternalRef]

    extract_refs

    source
    fn extract_refs(formula : StringView, current_sheet : StringView) -> FormulaRefs

    filter_report

    source
    fn filter_report(report : AuditReport, min_severity : String, disabled_rules : Array[String]) -> AuditReport

    finding_fingerprint

    source
    fn finding_fingerprint(finding : Finding) -> String

    function_names

    source
    fn function_names(formula : StringView) -> Array[String]

    graph_to_dot

    source
    fn graph_to_dot(graph : DependencyGraph) -> String

    graph_to_mermaid

    source
    fn graph_to_mermaid(graph : DependencyGraph) -> String

    is_table_symbol

    source
    fn is_table_symbol(tables : Array[TableInfo], name : String) -> Bool

    named_refs

    source
    fn named_refs(workbook : 
    Workbook    , name : String, current_sheet : String) -> Array[CellRef]

    normalize_formula

    source
    fn normalize_formula(formula : StringView, current_sheet : StringView, row : Int, col : Int) -> String

    numeric_literals

    source
    fn numeric_literals(formula : StringView) -> Array[String]

    parse_baseline

    source
    fn parse_baseline(text : String) -> Array[String] raise 
    ParseError

    parse_config

    source
    fn parse_config(text : String) -> AuditConfig raise 
    ParseError

    parse_targets_file

    source
    fn parse_targets_file(text : String) -> Array[String]

    resolve_formula_cells

    source
    fn resolve_formula_cells(workbook : 
    Workbook    , tables : Array[TableInfo], sheet : String, formula : StringView, row : Int) -> Array[CellRef]

    rewrite_dynamic_refs

    source
    fn rewrite_dynamic_refs(formula : StringView, current_sheet : String, _current_row : Int) -> String raise 
    XlsxError

    rewrite_structured_refs

    source
    fn rewrite_structured_refs(formula : StringView, current_sheet : String, current_row : Int, tables : Array[TableInfo]) -> String raise 
    XlsxError

    rule_catalog

    source
    fn rule_catalog() -> String

    severity_rank

    source
    fn severity_rank(severity : Severity) -> Int

    should_fail

    source
    fn should_fail(report : AuditReport, threshold : String) -> Bool

    slice_bytes

    source
    fn slice_bytes(source_bytes : Bytes, source_file : String, target : String, max_depth : Int) -> SliceResult raise 
    XlsxError

    slice_bytes_many

    source
    fn slice_bytes_many(source_bytes : Bytes, source_file : String, targets : Array[String], max_depth : Int, external_workbooks : Map[String, Bytes]) -> SliceResult raise 
    XlsxError

    slice_bytes_with_external

    source
    fn slice_bytes_with_external(source_bytes : Bytes, source_file : String, target : String, max_depth : Int, external_workbooks : Map[String, Bytes]) -> SliceResult raise 
    XlsxError

    slice_manifest_json

    source
    fn slice_manifest_json(manifest : SliceManifest) -> String

    slice_report_html

    source
    fn slice_report_html(manifest : SliceManifest) -> String

    summarize_bytes

    source
    fn summarize_bytes(bytes : Bytes) -> WorkbookSummary raise 
    XlsxError

    table_catalog

    source
    fn table_catalog(bytes : BytesView, sheets : Array[String]) -> Array[TableInfo]

    table_column_index

    source
    fn table_column_index(table : TableInfo, column : String) -> Int?

    table_refs

    source
    fn table_refs(formula : StringView, current_row : Int, tables : Array[TableInfo]) -> Array[CellRef]

    to_html

    source
    fn to_html(report : AuditReport) -> String

    to_json

    source
    fn to_json(report : AuditReport) -> String

    to_sarif

    source
    fn to_sarif(report : AuditReport) -> String

    to_terminal

    source
    fn to_terminal(report : AuditReport) -> String

    tool_version

    source
    fn tool_version() -> String

    trace_bytes

    source
    fn trace_bytes(bytes : Bytes, start : String, direction : String, max_depth : Int) -> Array[String] raise 
    XlsxError

    verify_slice_bytes

    source
    fn verify_slice_bytes(slice_bytes : Bytes, manifest_text : String) -> SliceVerification raise 
    ParseError

    verify_slice_with_source

    source
    fn verify_slice_with_source(source_bytes : Bytes, slice_bytes : Bytes, manifest_text : String) -> SliceVerification raise 
    ParseError

    Source Files

    Powered by MoonBit

    Site sourceReport issueModulesSkillsCLIBuild queueStatistics

    © 2026 mooncakes.io