SPB Git

Repository Public

AuditModel.swift 9.3 KB 169 lines swift

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
Files main
Menu

Theme