Repository Public
1import Foundation2import SheetCore34public enum AuditSeverity: String, Sendable, Codable, CaseIterable, Comparable {5 /// Worth knowing; usually not a defect.6 case info7 /// Likely to cause a wrong result or to make the model fragile.8 case warning9 /// The model is wrong or broken as it stands.10 case error1112 var rank: Int { self == .error ? 2 : (self == .warning ? 1 : 0) }13 public static func < (a: AuditSeverity, b: AuditSeverity) -> Bool { a.rank < b.rank }14}1516public enum AuditRule: String, Sendable, Codable, CaseIterable {17 case errorValue, brokenReference, circularReference, inconsistentFormula, hardcodedOverwrite, hardcodedConstant18 case blankReference, textNumberInArithmetic, numberStoredAsText, mixedTypesInTableColumn, textResultInNumericColumn19 case duplicatedCalculation, volatileFunction, volatileWholeColumn, complexFormula, slowFormula20 case unusedName, brokenName, externalReference, mergedCellsInData, hiddenContent2122 public var title: String {23 switch self {24 case .errorValue: "Error value"25 case .brokenReference: "Broken reference"26 case .circularReference: "Circular reference"27 case .inconsistentFormula: "Inconsistent formula"28 case .hardcodedOverwrite: "Hardcoded value in a formula range"29 case .hardcodedConstant: "Hardcoded constant in a formula"30 case .blankReference: "Reference to a blank cell"31 case .textNumberInArithmetic: "Text that looks like a number used in a calculation"32 case .numberStoredAsText: "Number stored as text"33 case .mixedTypesInTableColumn: "Mixed data types in a table column"34 case .textResultInNumericColumn: "Formula returning text in a numeric column"35 case .duplicatedCalculation: "Duplicated calculation"36 case .volatileFunction: "Volatile function"37 case .volatileWholeColumn: "Whole-column reference in a volatile formula"38 case .complexFormula: "Long or deeply nested formula"39 case .slowFormula: "Slow formula"40 case .unusedName: "Unused defined name"41 case .brokenName: "Broken defined name"42 case .externalReference: "External workbook reference"43 case .mergedCellsInData: "Merged cells inside a data range"44 case .hiddenContent: "Hidden content"45 }46 }47}4849public struct AuditFinding: Hashable, Sendable, Codable, Identifiable {50 /// Stable identifier: rule, sheet and first location (e.g. "inconsistentFormula:Sales!D7").51 public var id: String52 public var rule: AuditRule53 public var severity: AuditSeverity54 /// Sheet name; nil for workbook-level findings (defined names).55 public var sheet: String?56 public var sheetIndex: Int?57 /// The cells concerned, in row-major order (capped at `AuditOptions.maxCellsPerFinding`; `ranges` always covers all of them).58 public var cells: [CellRef]59 /// The same cells grouped into rectangles, plus any ranges the finding is about.60 public var ranges: [RangeRef]61 /// What was observed (formula text, values, counts).62 public var evidence: String63 /// Why it matters.64 public var explanation: String65 public var recommendedAction: String6667 /// "Sheet!A1, Sheet!B2:B9" style location text.68 public var location: String {69 let p = sheet.map { FormulaIntel.prefix($0) } ?? ""70 if ranges.isEmpty && cells.isEmpty { return sheet ?? "Workbook" }71 let parts = ranges.isEmpty ? cells.map(\.a1) : ranges.map(\.a1)72 return parts.prefix(8).map { p + $0 }.joined(separator: ", ") + (parts.count > 8 ? ", …" : "")73 }74}7576public struct AuditReport: Sendable, Codable {77 public struct Statistics: Sendable, Codable, Hashable {78 public var sheets = 079 public var cells = 080 public var formulas = 081 public var definedNames = 082 /// Wall-clock time of the audit in seconds.83 public var seconds = 0.084 }85 /// Findings sorted by severity (errors first), then sheet, rule and position.86 public var findings: [AuditFinding]87 public var statistics: Statistics88 /// Rules that were run.89 public var rules: [AuditRule]9091 public func count(_ s: AuditSeverity) -> Int { findings.filter { $0.severity == s }.count }92 public func findings(for rule: AuditRule) -> [AuditFinding] { findings.filter { $0.rule == rule } }9394 /// Report as a grid: a title block, a summary, then one row per finding.95 public func table() -> [[Value]] {96 var rows: [[Value]] = [97 [.text("Model audit")],98 [.text("Sheets"), .number(Double(statistics.sheets)), .text("Cells"), .number(Double(statistics.cells)), .text("Formulas"), .number(Double(statistics.formulas))],99 [.text("Errors"), .number(Double(count(.error))), .text("Warnings"), .number(Double(count(.warning))), .text("Info"), .number(Double(count(.info)))],100 [],101 ["Severity", "Rule", "Location", "Evidence", "Explanation", "Recommended action", "Id"].map { Value.text($0) },102 ]103 for f in findings {104 rows.append([.text(f.severity.rawValue), .text(f.rule.title), .text(f.location), .text(f.evidence), .text(f.explanation), .text(f.recommendedAction), .text(f.id)])105 }106 let width = rows.map(\.count).max() ?? 0107 return rows.map { $0 + Array(repeating: Value.blank, count: width - $0.count) }108 }109110 /// Report as Markdown, grouped by severity.111 public func markdown() -> String {112 var out = "# Model audit\n\n"113 out += "\(statistics.sheets) sheet\(statistics.sheets == 1 ? "" : "s"), \(statistics.cells) cells, \(statistics.formulas) formulas — "114 out += "**\(count(.error)) errors**, **\(count(.warning)) warnings**, \(count(.info)) info\n"115 if findings.isEmpty { return out + "\nNo findings.\n" }116 func esc(_ s: String) -> String { s.replacingOccurrences(of: "|", with: "\\|").replacingOccurrences(of: "\n", with: " ") }117 for sev in [AuditSeverity.error, .warning, .info] {118 let group = findings.filter { $0.severity == sev }119 guard !group.isEmpty else { continue }120 out += "\n## " + (sev == .error ? "Errors" : sev == .warning ? "Warnings" : "Information") + " (\(group.count))\n\n"121 out += "| Rule | Location | Evidence | Recommended action |\n|---|---|---|---|\n"122 for f in group { out += "| \(esc(f.rule.title)) | \(esc(f.location)) | \(esc(f.evidence)) | \(esc(f.recommendedAction)) |\n" }123 }124 return out125 }126}127128public struct AuditOptions: Sendable {129 /// Rules to run (default: all).130 public var rules: Set<AuditRule> = Set(AuditRule.allCases)131 /// Numeric literals that are never reported as hardcoded constants.132 public var allowedConstants: Set<Double> = [0, 1, -1, 2, 4, 7, 10, 12, 24, 52, 60, 100, 360, 365, 1000]133 /// Literals passed directly to these functions are structural (digits, indexes, positions), not model inputs.134 public var functionsWithStructuralConstants: Set<String> = [135 "ROUND", "ROUNDUP", "ROUNDDOWN", "TRUNC", "MROUND", "INDEX", "CHOOSE", "OFFSET", "VLOOKUP", "HLOOKUP", "XLOOKUP", "MATCH", "XMATCH",136 "LEFT", "RIGHT", "MID", "DATE", "TIME", "EDATE", "EOMONTH", "LARGE", "SMALL", "MOD", "POWER", "TEXT", "SUBTOTAL", "AGGREGATE",137 "PERCENTILE", "PERCENTILE.INC", "PERCENTILE.EXC", "QUARTILE", "QUARTILE.INC", "QUARTILE.EXC", "ROW", "COLUMN", "SEQUENCE", "REPT", "WEEKDAY",138 ]139 /// Minimum number of identical neighbouring formulas that makes a pattern (inconsistency and overwrite rules).140 public var runLength = 3141 /// A sub-calculation over fixed ranges repeated in at least this many cells is reported as duplicated.142 public var duplicateThreshold = 5143 /// Functions whose range arguments silently skip text (numeric text there is reported).144 public var aggregateFunctions: Set<String> = ["SUM", "AVERAGE", "MIN", "MAX", "COUNT", "PRODUCT", "SUMPRODUCT", "MEDIAN", "STDEV", "STDEV.S", "STDEV.P", "VAR", "VAR.S", "VAR.P", "SUBTOTAL"]145 /// References inside these functions are expected to meet blanks or text and are not reported by the blank-reference rule.146 public var blankTolerantFunctions: Set<String> = ["ISBLANK", "COUNTBLANK", "COUNTA", "COUNT", "ISNUMBER", "ISTEXT", "LEN", "IFERROR", "IFNA", "N", "T", "IF", "IFS"]147 /// Complexity thresholds: a formula is reported when any of them is exceeded.148 public var maxFormulaLength = 200149 public var maxNestingDepth = 5150 public var maxComplexityScore = 80.0151 /// A column counts as numeric when at least this share of its non-blank values are numbers.152 public var numericColumnShare = 0.8153 /// Report formulas that return "" in a numeric column (off by default: a common display idiom).154 public var flagEmptyTextResults = false155 /// Profiling: at most this many formulas are timed (evenly spaced over the workbook, deterministic).156 public var profileSampleLimit = 2000157 /// Each sampled formula is evaluated this many times; the fastest run counts.158 public var profileRepetitions = 3159 /// Number of slowest formulas reported.160 public var slowFormulaTopN = 10161 /// Formulas faster than this (seconds per evaluation) are never reported as slow.162 public var slowFormulaThreshold = 0.002163 /// Full cycle search is skipped above this many formulas (cells showing #CIRC! are always analysed).164 public var cycleScanFormulaLimit = 50_000165 /// Cap on the `cells` list of one finding.166 public var maxCellsPerFinding = 200167 public init() {}168}169