SPB Git

Repository Public

AuditorTests.swift 35.7 KB 579 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Tests/AuditTests/AuditorTests.swift10// Module           : AuditTests11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import Audit9192@Suite struct AuditorModelTests {93    func audit(_ b: ACBook, _ configure: (inout AuditOptions) -> Void = { _ in }) -> AuditReport {94        var o = AuditOptions()95        o.rules.remove(.slowFormula)          // timing-based: tested separately96        configure(&o)97        return ModelAuditor.audit(workbook: b.wb, engine: b.engine, options: o)98    }99100    @Test func cleanModelsHaveNoFindings() {101        let b = ACBook(sheets: ["Loan", "DCF", "Sales"])102        AuditModels.loan(b, sheet: 0); AuditModels.dcf(b, sheet: 1); AuditModels.sales(b, sheet: 2)103        // the models compute what they should (closed forms)104        let i = 0.06 / 12105        let payment = 250000 * i / (1 - pow(1 + i, -24))106        #expect(abs((b.get("B6").numberValue ?? 0) - payment) < 1e-8)107        #expect(abs(b.get("E32").numberValue ?? 1) < 1e-6)                                   // fully amortised108        #expect(abs((b.get("C34").numberValue ?? 0) - (payment * 24 - 250000)) < 1e-6)       // total interest109        var ev = 0.0110        for t in 1...5 {111            let fcf: Double = 1000 * pow(1.05, Double(t - 1)) * 0.2112            ev += fcf / pow(1.1, Double(t))113        }114        let lastFCF: Double = 1000 * pow(1.05, 4) * 0.2115        let terminal: Double = lastFCF * 1.02 / (0.1 - 0.02)116        ev += terminal / pow(1.1, 5)117        #expect(abs((b.get("B9", sheet: 1).numberValue ?? 0) - ev) < 1e-9)118        var total = 0.0119        for r in 2...11 {120            let revenue: Double = Double(10 * r) * (Double(r) + 0.5)121            total += revenue + revenue * 0.15122        }123        #expect(abs((b.get("F12", sheet: 2).numberValue ?? 0) - total) < 1e-9)124125        let r = audit(b)126        #expect(r.findings.isEmpty, "\(r.findings.map(AuditModels.brief))")127        #expect(r.statistics.sheets == 3 && r.statistics.definedNames == 0)128        #expect(r.statistics.formulas == (3 + 23 + 24 + 24 * 3 + 2) + (4 * 5 + 3) + (30 + 4))129        #expect(r.count(.error) == 0 && r.count(.warning) == 0 && r.count(.info) == 0)130        #expect(r.rules.count == AuditRule.allCases.count - 1)131        #expect(r.markdown().contains("No findings."))132        #expect(r.table().count == 5)133        // the audit is read-only: no undo step, same cell contents134        let undoBefore = b.engine.undoLabel, changes = b.engine.changeCount135        _ = audit(b)136        #expect(b.engine.undoLabel == undoBefore && b.engine.changeCount == changes)137    }138139    @Test func seededDefectsAreFoundExactly() {140        let b = AuditModels.seeded()141        let r = audit(b)142        let expected = [143            "error errorValue Sales K2",144            "error errorValue Sales K4",145            "error errorValue Sales K11",146            "error brokenReference Sales K4",147            "error circularReference Sales K6:L6",148            "error errorValue Data C5",149            "error brokenName * -",150            "warning inconsistentFormula Sales D7",151            "warning hardcodedOverwrite Sales F5",152            "warning blankReference Sales K2",153            "warning blankReference Sales D9",154            "warning textNumberInArithmetic Sales B4",155            "warning volatileWholeColumn Sales K9",156            "warning externalReference Sales K11",157            "warning mergedCellsInData Sales N2:O2",158            "warning inconsistentFormula Data C6",159            "warning mixedTypesInTableColumn Data B5",160            "warning textResultInNumericColumn Data C6",161            "warning mergedCellsInData Data A3:A4",162            "info hardcodedConstant Sales D7",163            "info volatileFunction Sales K8:K9",164            "info complexFormula Sales K13",165            "info hiddenContent Sales P1:P20",166            "info hiddenContent Sales A20:P20",167            "info numberStoredAsText Data F2:F3",168            "info duplicatedCalculation Dup B1:B6",169            "info hiddenContent Calc A1:A2",170            "info unusedName * -",171        ]172        #expect(r.findings.map(AuditModels.brief) == expected)173        #expect(r.count(.error) == 7 && r.count(.warning) == 12 && r.count(.info) == 9)174        #expect(Set(r.findings.map(\.id)).count == r.findings.count)175        func one(_ rule: AuditRule, _ sheet: String? = nil) -> AuditFinding { r.findings.first { $0.rule == rule && (sheet == nil || $0.sheet == sheet) }! }176177        let div = one(.errorValue)178        #expect(div.id == "errorValue:Sales!K2" && div.cells == [b.ref("K2")] && div.sheetIndex == 0)179        #expect(div.evidence == "=D2/J2 → #DIV/0!. Propagates to 1 cell: Sales!L2")180        #expect(div.explanation.contains("The denominator J2 is empty"))181        #expect(!r.findings.contains { $0.rule == .errorValue && $0.cells == [b.ref("L2")] })     // L2 only inherits the error182        #expect(!r.findings.contains { $0.rule == .errorValue && ($0.cells == [b.ref("K6")] || $0.cells == [b.ref("L6")]) })183184        #expect(one(.brokenReference).evidence == "K4: =#REF!*2 — #REF! literal")185        let cyc = one(.circularReference)186        #expect(cyc.cells == [b.ref("K6"), b.ref("L6")] && cyc.evidence == "Cycle: Sales!K6 → Sales!L6 → Sales!K6")187188        let inc = one(.inconsistentFormula, "Sales")189        #expect(inc.cells == [b.ref("D7")])190        #expect(inc.evidence.contains("RC[-2]*RC[-1]+25 instead of RC[-2]*RC[-1]") && inc.evidence.contains("(nothing) → +25"))191        #expect(one(.hardcodedOverwrite).evidence == "F5 contains the constant 999 while the surrounding cells F2:F4, F6:F11 are formulas like =D4+E4.")192        #expect(one(.hardcodedConstant).evidence == "D7: =B7*C7+25 contains the constant 25.")193        let txt = one(.textNumberInArithmetic)194        #expect(txt.cells == [b.ref("B4")])195        #expect(txt.evidence.contains("converted to a number by the arithmetic in Sales!D4 (=B4*C4)"))196        #expect(txt.evidence.contains("silently ignored by SUM in Sales!B12 (=SUM(B2:B11))"))197        #expect(b.get("B12") == .number(650 - 40))       // the defect is real: SUM skipped B4198        #expect(r.findings(for: .blankReference).map(\.cells) == [[b.ref("K2")], [b.ref("D9")]])199        #expect(one(.volatileFunction).id == "volatileFunction:Sales!RAND" && one(.volatileFunction).cells == [b.ref("K8"), b.ref("K9")])200        #expect(one(.volatileWholeColumn).evidence.contains("RAND") && one(.volatileWholeColumn).evidence.contains("B:B"))201        #expect(one(.externalReference).evidence.contains("\"Budget.xlsx\""))202        #expect(one(.complexFormula).evidence.contains("nesting depth 7") && one(.complexFormula).severity == .info)203        #expect(one(.mergedCellsInData, "Sales").evidence == "The merged cells N2:O2 lie inside the range N2:O4 used by formulas.")204        #expect(one(.mergedCellsInData, "Data").evidence == "The merged cells A3:A4 lie inside table T (A1:D7).")205        #expect(!r.findings.contains { $0.ranges == [RangeRef(a1: "R30:S30")!] })                    // a merge outside any data range206        #expect(one(.mixedTypesInTableColumn).evidence == "Column \"Amount\" of table T mixes types: 5 number, 1 text. The odd ones out: B5.")207        #expect(one(.textResultInNumericColumn).cells == [b.ref("C6")])208        #expect(one(.textResultInNumericColumn).evidence.contains("→ \"big\""))209        #expect(one(.numberStoredAsText).cells == [b.ref("F2"), b.ref("F3")])                       // "007" is an identifier210        #expect(one(.duplicatedCalculation).evidence == "SUM($A$1:$A$10) is recomputed in 6 cells (B1:B6).")211        #expect(r.findings(for: .hiddenContent).map(\.evidence) == [212            "Hidden column P contains 1 value and 0 formulas.", "Hidden row 20 contains 1 value and 1 formula.",213            "The hidden sheet \"Calc\" contains 1 value and 1 formula."])214        #expect(one(.unusedName).id == "unusedName:Unused" && one(.unusedName).sheet == nil)215        #expect(one(.brokenName).evidence == "The defined name Broken is defined as =Sales!#REF!: #REF! literal.")216        #expect(r.findings.allSatisfy { !$0.explanation.isEmpty && !$0.recommendedAction.isEmpty && !$0.evidence.isEmpty })217    }218219    @Test func reportRendersAndRoundTrips() throws {220        let b = AuditModels.seeded()221        let r = audit(b)222        let data = try JSONEncoder().encode(r)223        let back = try JSONDecoder().decode(AuditReport.self, from: data)224        #expect(back.findings == r.findings && back.statistics == r.statistics && back.rules == r.rules)225        let t = r.table()226        #expect(t.count == 5 + r.findings.count)227        #expect(Set(t.map(\.count)) == [7])228        #expect(t[0][0] == .text("Model audit") && t[2][1] == .number(7) && t[2][3] == .number(12) && t[2][5] == .number(9))229        #expect(t[4].map(\.description) == ["Severity", "Rule", "Location", "Evidence", "Explanation", "Recommended action", "Id"])230        #expect(t[5][0] == .text("error") && t[5][1] == .text("Error value") && t[5][2] == .text("Sales!K2") && t[5][6] == .text("errorValue:Sales!K2"))231        let md = r.markdown()232        #expect(md.hasPrefix("# Model audit\n"))233        #expect(md.contains("**7 errors**, **12 warnings**, 9 info"))234        #expect(md.contains("## Errors (7)") && md.contains("## Warnings (12)") && md.contains("## Information (9)"))235        #expect(md.contains("| Circular reference | Sales!K6:L6 | Cycle: Sales!K6 → Sales!L6 → Sales!K6 |"))236        #expect(md.components(separatedBy: "\n").filter { $0.hasPrefix("| ") && !$0.hasPrefix("| Rule") }.count == r.findings.count)237        #expect(r.findings[0].location == "Sales!K2")238        #expect(r.findings.first { $0.rule == .unusedName }!.location == "Workbook")239        for rule in AuditRule.allCases { #expect(!rule.title.isEmpty) }240        #expect(AuditSeverity.info < .warning && AuditSeverity.warning < .error)241    }242243    @Test func rulesCanBeSelected() {244        let b = AuditModels.seeded()245        let only = audit(b) { $0.rules = [.circularReference, .hardcodedOverwrite] }246        #expect(only.findings.map(AuditModels.brief) == ["error circularReference Sales K6:L6", "warning hardcodedOverwrite Sales F5"])247        #expect(only.rules == [.circularReference, .hardcodedOverwrite])248        let noText = audit(b) { $0.rules = [.numberStoredAsText] }249        // without the arithmetic rule, B4 is reported as a number stored as text250        #expect(noText.findings.map(AuditModels.brief) == ["info numberStoredAsText Sales B4", "info numberStoredAsText Data F2:F3"])251        let none = audit(b) { $0.rules = [] }252        #expect(none.findings.isEmpty)253    }254}255256@Suite struct AuditorRuleTests {257    func audit(_ b: ACBook, _ rules: Set<AuditRule>, _ configure: (inout AuditOptions) -> Void = { _ in }) -> [AuditFinding] {258        var o = AuditOptions()259        o.rules = rules260        configure(&o)261        return ModelAuditor.audit(workbook: b.wb, engine: b.engine, options: o).findings262    }263264    @Test func inconsistencyHeuristics() {265        let b = ACBook()266        for r in 1...8 { b.set("A\(r)", String(r)); b.set("B\(r)", String(r * 2)) }267        // C: uniform run with one deviation in the middle and a total below268        for r in 1...8 { b.set("C\(r)", r == 5 ? "=A5-B5" : "=A\(r)+B\(r)") }269        b.set("C9", "=SUM(C1:C8)")270        // D: two halves with different formulas — no dominant pattern271        for r in 1...8 { b.set("D\(r)", r <= 4 ? "=A\(r)*2" : "=A\(r)*B\(r)") }272        // E: only two formulas — too short to form a pattern273        b.set("E1", "=A1+1"); b.set("E2", "=B2-A2")274        // F: an absolute reference that was not anchored (classic fill error)275        for r in 1...6 { b.set("F\(r)", r == 4 ? "=A4*B4" : "=A\(r)*$B$1") }276        var f = audit(b, [.inconsistentFormula])277        #expect(f.map(\.cells) == [[b.ref("F4")], [b.ref("C5")]])278        #expect(f.allSatisfy { $0.severity == .warning })279        #expect(f[0].evidence.contains("RC[-5]*RC[-4] instead of RC[-5]*R1C2") && f[1].evidence.hasSuffix("difference: + → -."))280        // a longer required run silences the six-cell column F but not the eight-cell column C281        f = audit(b, [.inconsistentFormula]) { $0.runLength = 6 }282        #expect(f.map(\.cells) == [[b.ref("C5")]])283        // rows work like columns284        let h = ACBook()285        for c in 0..<7 { h.set(A1.columnName(c) + "1", String(c + 1)); h.set(A1.columnName(c) + "2", c == 3 ? "=D1*3" : "=\(A1.columnName(c))1*2") }286        #expect(audit(h, [.inconsistentFormula]).map(\.cells) == [[h.ref("D2")]])287        // a grid where each column is uniform is consistent even though rows differ288        let g = ACBook()289        for r in 1...5 { g.set("A\(r)", String(r)); g.set("B\(r)", "=A\(r)*2"); g.set("C\(r)", "=A\(r)+B\(r)"); g.set("D\(r)", "=C\(r)/2") }290        #expect(audit(g, [.inconsistentFormula]).isEmpty)291        // a schedule that starts from a seed reference is not a break292        let s = ACBook()293        s.set("A1", "100"); s.set("B3", "=A1")294        for r in 4...9 { s.set("B\(r)", "=B\(r - 1)*2") }295        #expect(audit(s, [.inconsistentFormula]).isEmpty)296    }297298    @Test func hardcodedOverwriteVariants() {299        let b = ACBook()300        for c in 0..<8 { b.set(A1.columnName(c) + "1", String(c + 1)); b.set(A1.columnName(c) + "2", "=\(A1.columnName(c))1*2") }301        b.set("D2", "42"); b.set("E2", "43")                 // two overwritten cells side by side in a row of formulas302        b.set("A5", "5"); b.set("A6", "=A5+1"); b.set("A7", "17"); b.set("A8", "=A7+1")     // run too short (2 formulas)303        b.set("H2", "9")                                     // at the end of the run: not "in the middle"304        var f = audit(b, [.hardcodedOverwrite])305        #expect(f.map(\.cells) == [[b.ref("D2")], [b.ref("E2")]])306        #expect(f[0].evidence == "D2 contains the constant 42 while the surrounding cells A2:C2, F2:G2 are formulas like =C1*2.")307        f = audit(b, [.hardcodedOverwrite]) { $0.runLength = 6 }308        #expect(f.isEmpty)309        // text in the gap is a label, not an overwritten number310        let t = ACBook()311        for r in 1...7 { t.set("A\(r)", String(r)); t.set("B\(r)", "=A\(r)*2") }312        t.setText("B4", "n/a")313        #expect(audit(t, [.hardcodedOverwrite]).isEmpty)314    }315316    @Test func hardcodedConstantRules() {317        let b = ACBook()318        b.set("A1", "10"); b.set("A2", "20")319        b.set("B1", "=A1*1.15")            // flagged320        b.set("B2", "=A2*1.15")            // same shape → same finding321        b.set("B3", "=A1*12+A2/100-1")     // allowed constants only322        b.set("B4", "=A1^2+A2^0.5")        // exponents are structural323        b.set("B5", "=A1*7.5%")            // percent literal324        b.set("B6", "=IF(A1>50,A1,-3.5)")  // comparison constant and negative literal325        b.set("B7", "=INDEX(A1:A2,2)+CHOOSE(3,A1,A2,A1)")   // positions326        b.set("B8", "=SUM(A1:A2,{5,6,7})") // array constant327        b.set("B9", "=A1&\"x\"")328        b.set("B10", "=3.7")               // constant-only formula329        b.set("B11", "=INDEX(A1:A2,A1*0.3)")   // a calculation inside a structural function is judged on its own330        b.set("A5", "0.075")               // plain inputs are never flagged331        let f = audit(b, [.hardcodedConstant])332        #expect(f.map(\.cells) == [[b.ref("B1"), b.ref("B2")], [b.ref("B5")], [b.ref("B6")], [b.ref("B10")], [b.ref("B11")]])333        #expect(f.allSatisfy { $0.severity == .info })334        #expect(f[0].evidence == "B1: =A1*1.15 contains the constant 1.15 (same formula in 2 cells: B1:B2).")335        #expect(f[1].evidence == "B5: =A1*7.5% contains the constant 7.5%.")336        #expect(f[2].evidence == "B6: =IF(A1>50,A1,-3.5) contains the constants 50, -3.5.")337        #expect(f[4].evidence.contains("0.3"))338        let custom = audit(b, [.hardcodedConstant]) { $0.allowedConstants = [1.15, 0.075, 50, -3.5, 2, 3, 12, 100, 1, 0.3]; $0.functionsWithStructuralConstants = [] }339        #expect(custom.map(\.cells) == [[b.ref("B10")]])340        let strict = audit(b, [.hardcodedConstant]) { $0.allowedConstants = [] }341        #expect(strict.contains { $0.cells == [b.ref("B3")] && $0.evidence.contains("12, 100, 1") })342    }343344    @Test func errorOriginsAndPropagation() {345        let b = ACBook(sheets: ["S", "T"])346        b.set("A1", "0"); b.set("A2", "=1/A1"); b.set("A3", "=A2+1"); b.set("A4", "=A3*2"); b.set("B2", "=SUM(A2:A4)")347        b.set("C1", "=S!A4+1", sheet: 1)348        b.set("D1", "=FOO(1)"); b.set("D2", "=D1&\"x\"")349        b.engine.set(sheet: 0, "E1", .value(.error(.na))); b.set("E2", "=E1+1")350        b.set("F1", "=IFERROR(A2,0)+NA()")                 // reads an error but creates its own, different one351        let f = audit(b, [.errorValue])352        #expect(f.map(\.cells) == [[b.ref("D1")], [b.ref("E1")], [b.ref("F1")], [b.ref("A2")]])353        #expect(f.allSatisfy { $0.severity == .error && $0.sheet == "S" })354        #expect(f[3].evidence == "=1/A1 → #DIV/0!. Propagates to 4 cells: S!B2, S!A3, S!A4, T!C1")355        #expect(f[3].explanation.contains("The denominator A1 evaluates to 0. S!A1 contains 0."))356        #expect(f[0].explanation.contains("FOO is not a known function.") && f[0].evidence == "=FOO(1) → #NAME?. Propagates to 1 cell: S!D2")357        #expect(f[1].evidence == "The cell contains the constant #N/A. Propagates to 1 cell: S!E2")358        #expect(f[2].evidence == "=IFERROR(A2,0)+NA() → #N/A")359        // fixing the origin clears everything360        b.set("A1", "4"); b.set("D1", "=1"); b.set("E1", "1"); b.set("F1", "=IFERROR(A2,0)")361        #expect(audit(b, [.errorValue]).isEmpty)362    }363364    @Test func brokenReferencesAndNames() {365        let b = ACBook(sheets: ["S"])366        b.set("A1", "1"); b.set("A2", "2")367        b.set("B1", "=A1+#REF!"); b.set("B2", "=A2+#REF!")            // same shape → one finding368        b.set("B4", "=Gone!A1"); b.set("B5", "=SUM(Gone!A1:A3)"); b.set("B6", "=NoTable[Amount]")369        b.set("B8", "=A1+A2")370        let t = TableDef(id: 1, name: "T", range: RangeRef(a1: "D1:E3")!, columns: [TableColumn(name: "X"), TableColumn(name: "Y")])371        b.wb.sheets[0].meta.tables = [t]372        b.set("B7", "=SUM(T[Z])"); b.set("B9", "=SUM(T[Y])")373        var f = audit(b, [.brokenReference])374        #expect(f.map(\.cells) == [[b.ref("B1"), b.ref("B2")], [b.ref("B4")], [b.ref("B5")], [b.ref("B6")], [b.ref("B7")]])375        #expect(f[0].evidence == "B1: =A1+#REF! — #REF! literal (2 cells with the same formula)")376        #expect(f[1].evidence.hasSuffix("missing sheet \"Gone\"") && f[3].evidence.hasSuffix("missing table \"NoTable\""))377        #expect(f[4].evidence.hasSuffix("missing column \"Z\" in table T"))378        // names379        b.wb.setDefinedName(DefinedName(name: "Rate", formula: "S!$A$1"))380        b.wb.setDefinedName(DefinedName(name: "Base", formula: "S!$A$2"))381        b.wb.setDefinedName(DefinedName(name: "Derived", formula: "Base*2"))382        b.wb.setDefinedName(DefinedName(name: "Orphan", formula: "S!$A$1:$A$2"))383        b.wb.setDefinedName(DefinedName(name: "OrphanUser", formula: "SUM(Orphan)"))384        b.wb.setDefinedName(DefinedName(name: "Lost", formula: "Gone!$A$1"))385        b.wb.setDefinedName(DefinedName(name: "Garbage", formula: "1+"))386        b.wb.setDefinedName(DefinedName(name: "ForValidation", formula: "S!$A$1:$A$2"))387        b.wb.setDefinedName(DefinedName(name: "Local", formula: "S!$A$1", scopeSheetID: b.wb.sheets[0].id))388        var hidden = DefinedName(name: "HiddenOne", formula: "S!$A$1"); hidden.hidden = true389        b.wb.setDefinedName(hidden)390        b.wb.sheets[0].meta.validations = [DataValidation(ranges: [RangeRef(a1: "G1:G5")!], kind: .list, formula1: "ForValidation")]391        b.set("C1", "=A1*Rate+Derived")392        b.engine.rebuildGraph()393        f = audit(b, [.unusedName, .brokenName])394        #expect(f.map(\.id) == ["brokenName:Garbage", "brokenName:Lost", "unusedName:S!Local", "unusedName:Orphan", "unusedName:OrphanUser"])395        #expect(f[0].severity == .error && f[0].evidence.contains("not a valid formula"))396        #expect(f[1].evidence.contains("missing sheet \"Gone\""))397        #expect(f[2].sheet == "S" && f[3].sheet == nil && f[3].severity == .info)398        #expect(audit(b, [.unusedName]).map(\.id).contains("unusedName:Garbage"))       // without the broken-name rule it is merely unused399    }400401    @Test func circularReferences() {402        let b = ACBook(sheets: ["S", "T"])403        b.set("A1", "=B1+1"); b.set("B1", "=T!A1*2"); b.set("A1", "=S!A1-1", sheet: 1)     // 3-cycle across sheets404        b.set("D1", "=D1+1")                                                                 // self-reference405        b.set("F1", "1"); b.set("F2", "=F1+1"); b.set("F3", "=F2+A1")                         // downstream of the cycle, not part of it406        b.set("H1", "=SUM(H1:H3)"); b.set("H2", "5")                                          // a range that includes its own cell407        let f = audit(b, [.circularReference])408        #expect(f.count == 3 && f.allSatisfy { $0.severity == .error })409        #expect(f[0].cells == [b.ref("A1"), b.ref("B1")])410        #expect(f[0].evidence == "Cycle: S!A1 → S!B1 → T!A1 → S!A1")411        #expect(f[1].cells == [b.ref("D1")] && f[1].evidence == "Cycle: S!D1 → S!D1")412        #expect(f[2].cells == [b.ref("H1")])413        #expect(!f.contains { $0.cells.contains(b.ref("F3")) })414        // above the scan limit only the cells showing #CIRC! are analysed: same result415        let limited = audit(b, [.circularReference]) { $0.cycleScanFormulaLimit = 0 }416        #expect(limited.map(\.evidence) == f.map(\.evidence))417        // a long acyclic chain is handled iteratively and reports nothing418        let c = ACBook()419        c.set("A1", "1")420        for r in 2...3000 { c.set("A\(r)", "=A\(r - 1)+1") }421        #expect(audit(c, [.circularReference]).isEmpty)422        #expect(c.get("A3000") == .number(3000))423    }424425    @Test func duplicatedCalculations() {426        let b = ACBook(sheets: ["S", "Ref"])427        for r in 1...12 { b.set("A\(r)", String(r)); b.set("A\(r)", String(r * 3), sheet: 1) }428        for r in 1...8 { b.set("B\(r)", "=IF(A\(r)>0,A\(r)/SUM(Ref!$A$1:$A$12),0)") }        // nested in a call that varies per row429        for r in 1...8 { b.set("C\(r)", "=A\(r)/SUM(A\(r):A\(r + 4))") }   // moving window: all different430        for r in 1...4 { b.set("D\(r)", "=MAX($A$1:$A$12)-A\(r)") }                            // below the threshold of 5431        for r in 1...5 { b.set("E\(r)", "=A\(r)*MAX(MIN($A$1:$A$12),AVERAGE($A$1:$A$12))") }   // nested fixed calls → the outermost is reported432        for r in 1...6 { b.set("F\(r)", "=A\(r)+SUM($A$1:$A$12)*RAND()+ABS($A$1)") }          // volatile neighbour and single-cell call do not count433        var f = audit(b, [.duplicatedCalculation])434        #expect(f.map { $0.ranges.map(\.a1) } == [["B1:B8"], ["E1:E5"], ["F1:F6"]])435        #expect(f[0].evidence == "SUM(Ref!$A$1:$A$12) is recomputed in 8 cells (B1:B8).")436        #expect(f[1].evidence == "MAX(MIN($A$1:$A$12),AVERAGE($A$1:$A$12)) is recomputed in 5 cells (E1:E5).")437        #expect(f[2].evidence == "SUM($A$1:$A$12) is recomputed in 6 cells (F1:F6).")438        f = audit(b, [.duplicatedCalculation]) { $0.duplicateThreshold = 4 }439        #expect(f.contains { $0.ranges.map(\.a1) == ["D1:D4"] })440        f = audit(b, [.duplicatedCalculation]) { $0.duplicateThreshold = 9 }441        #expect(f.isEmpty)442    }443444    @Test func blankAndTextReferences() {445        let b = ACBook(sheets: ["S", "T"])446        b.set("A1", "5")447        b.set("B1", "=A1+A2")                    // A2 blank448        b.set("B2", "=IF(A3=\"\",0,A1)")         // explicit blank test449        b.set("B3", "=IFERROR(A1/A4,0)")         // arithmetic on a blank inside a tolerant function is still reported450        b.set("B4", "=COUNTA(A5:A6)")            // counting blanks is the point451        b.set("B5", "=SUM(D1:D5)")               // wholly empty range452        b.set("B6", "=SUM(A:A)+SUM(A1:A9)")      // whole column and partly filled range are fine453        b.set("B7", "=T!A1*2")                   // blank on another sheet454        b.set("B8", "=A1*2")455        var f = audit(b, [.blankReference])456        #expect(f.map(\.cells) == [[b.ref("B1")], [b.ref("B3")], [b.ref("B5")], [b.ref("B7")]])457        #expect(f[0].evidence == "B1: =A1+A2 reads the empty cell A2.")458        #expect(f[2].evidence == "B5: =SUM(D1:D5) reads the empty cell D1:D5.")459        #expect(f[3].evidence.contains("T!A1"))460        #expect(f.allSatisfy { $0.severity == .warning })461        // numeric text462        let t = ACBook()463        t.setText("A1", "12"); t.setText("A2", " 3.5 "); t.setText("A3", "abc"); t.setText("A4", "0012"); t.set("A5", "7")464        t.setText("A6", "1,200"); t.setText("A7", "15%")465        t.set("B1", "=A1+1"); t.set("B2", "=(A2)*2"); t.set("B3", "=A5*2"); t.set("B4", "=SUM(A1:A7)"); t.set("B5", "=-A7"); t.set("B6", "=A1&\"x\"")466        f = audit(t, [.textNumberInArithmetic, .numberStoredAsText])467        #expect(f.filter { $0.rule == .textNumberInArithmetic }.map(\.cells) == [[t.ref("A1")], [t.ref("A2")], [t.ref("A6")], [t.ref("A7")]])468        #expect(f.filter { $0.rule == .numberStoredAsText }.isEmpty)       // all numeric text is already reported; "0012" and "abc" are text469        #expect(f[0].evidence.contains("converted to a number by the arithmetic in Sheet1!B1 (=A1+1)") && f[0].evidence.contains("silently ignored by SUM"))470        #expect(f[2].evidence.contains("silently ignored by SUM in Sheet1!B4") && !f[2].evidence.contains("converted"))471        #expect(t.get("B4") == .number(7))                                  // SUM really skipped all the text472        #expect(t.get("B1") == .number(13))473    }474475    @Test func tablesMergesAndHiddenContent() {476        let b = ACBook(sheets: ["S"])477        for (i, h) in ["Name", "Qty", "Ok", "Calc"].enumerated() { b.setText(A1.columnName(i) + "1", h) }478        for r in 2...11 {479            b.setText("A\(r)", "n\(r)"); b.set("B\(r)", String(r)); b.set("C\(r)", "TRUE")480            b.set("D\(r)", r == 4 ? "=IF(B4>100,B4,\"\")" : "=B\(r)*2")481        }482        b.setText("B3", "three"); b.set("B7", "TRUE"); b.set("A9", "9"); b.setText("C5", "yes")483        var t = TableDef(id: 1, name: "Stock", range: RangeRef(a1: "A1:D11")!, columns: ["Name", "Qty", "Ok", "Calc"].map { TableColumn(name: $0) })484        t.hasHeader = true485        b.wb.sheets[0].meta.tables = [t]486        var f = audit(b, [.mixedTypesInTableColumn])487        #expect(f.map(\.cells) == [[b.ref("B3"), b.ref("B7")], [b.ref("C5")], [b.ref("A9")]])       // findings are ordered row-major488        #expect(f[0].evidence == "Column \"Qty\" of table Stock mixes types: 8 number, 1 boolean, 1 text. The odd ones out: B3, B7.")489        // "" results are a display idiom unless asked for; errors (D3 = "three"*2) are left to the error rule490        #expect(audit(b, [.textResultInNumericColumn]).isEmpty)491        f = audit(b, [.textResultInNumericColumn]) { $0.flagEmptyTextResults = true }492        #expect(f.map(\.cells) == [[b.ref("D4")]])493        // a formula column outside any table494        for r in 1...6 { b.set("G\(r)", r == 2 ? "=IF(B2>0,\"n/a\",0)" : "=\(r)*B2") }495        f = audit(b, [.textResultInNumericColumn])496        #expect(f.map(\.cells) == [[b.ref("G2")]] && f[0].evidence.hasPrefix("Formula column G1:G6: 5 of 6 values are numbers"))497        // merges: inside the table, inside an autofilter, elsewhere498        b.wb.sheets[0].meta.merges = [RangeRef(a1: "B5:C5")!, RangeRef(a1: "J2:K2")!, RangeRef(a1: "M20:N21")!]499        b.wb.sheets[0].meta.autoFilter = RangeRef(a1: "J1:L9")500        f = audit(b, [.mergedCellsInData])501        #expect(f.map { $0.ranges.map(\.a1) } == [["J2:K2"], ["B5:C5"]])502        #expect(f[0].evidence == "The merged cells J2:K2 lie inside the filtered range J1:L9.")503        // hidden rows are grouped; empty hidden rows and filtered rows are ignored504        b.wb.sheets[0].meta.hiddenRows = IndexSet([2, 3, 4, 40, 41])505        b.wb.sheets[0].meta.filteredRows = IndexSet([6])506        f = audit(b, [.hiddenContent])507        #expect(f.count == 1 && f[0].severity == .info)508        #expect(f[0].evidence == "Hidden rows 3–5 contain 9 values and 6 formulas.")509        #expect(f[0].ranges == [RangeRef(a1: "A3:G5")!])510    }511512    @Test func volatileComplexityAndExternal() {513        let reg = FunctionRegistry()514        for spec in FunctionRegistry.standard.all { reg.register(spec) }515        reg.scalar("TICK", 0...0, .dateTime, signature: "TICK()", summary: "Test clock.", volatile: true) { _, _ in .number(1) }516        let b = ACBook(registry: reg)517        b.set("A1", "=TICK()+1"); b.set("A2", "=NOW()"); b.set("A3", "=TODAY()+TICK()"); b.set("A4", "=SUM(B:B)*TICK()+SUM(3:3)"); b.set("A5", "=SUM(B:B)")518        var f = audit(b, [.volatileFunction, .volatileWholeColumn])519        #expect(f.map(\.id) == ["volatileWholeColumn:Sheet1!A4", "volatileFunction:Sheet1!TICK", "volatileFunction:Sheet1!NOW", "volatileFunction:Sheet1!TODAY"])520        #expect(f[1].cells == [b.ref("A1"), b.ref("A3"), b.ref("A4")] && f[1].severity == .info)521        #expect(f[0].severity == .warning && f[0].evidence.contains("B:B, 3:3"))522        // complexity523        let c = ACBook()524        c.set("A1", "1")525        let long = "=" + (1...60).map { "A\($0)" }.joined(separator: "+")526        c.set("C1", long)527        c.set("C2", "=IF(A1>0,IF(A1>1,IF(A1>2,IF(A1>3,IF(A1>4,IF(A1>5,IF(A1>6,IF(A1>7,ABS(A1),0),0),0),0),0),0),0),0)")528        c.set("C3", "=IF(A1>0,ABS(MAX(A1,A2)),0)")529        f = audit(c, [.complexFormula])530        #expect(f.map(\.cells) == [[c.ref("C2")], [c.ref("C1")]])531        #expect(f[0].severity == .warning && f[1].severity == .info)       // depth 9 ≥ 5 + 3; 230 characters < 400532        let m = ModelAuditor.complexity(of: try! FormulaParser.parse("=IF(A1>0,ABS(MAX(A1,A2)),0)"))533        #expect(m.length == 26 && m.nestingDepth == 3 && m.functionCount == 3 && m.referenceCount == 3 && m.nodeCount == 9)534        #expect(m.score == 9 + 5 * 2 + 2.6)535        f = audit(c, [.complexFormula]) { $0.maxNestingDepth = 2; $0.maxFormulaLength = 1000; $0.maxComplexityScore = 1e9 }536        #expect(f.map(\.cells) == [[c.ref("C2")], [c.ref("C3")]])537        // external references in formulas and names538        let e = ACBook()539        e.set("A1", "='[Budget.xlsx]Plan'!B2+1"); e.set("A2", "=SUM('[Budget.xlsx]Plan'!B2:B9)"); e.set("A3", "='C:\\[Old.xls]S'!A1"); e.set("A4", "=A5")540        e.wb.setDefinedName(DefinedName(name: "Ext", formula: "'[Rates.xlsx]FX'!$A$1"))541        f = audit(e, [.externalReference])542        #expect(f.map(\.id) == ["externalReference:Sheet1!Budget.xlsx", "externalReference:Sheet1!Old.xls", "externalReference:name:Ext"])543        #expect(f[0].cells == [e.ref("A1"), e.ref("A2")] && f.allSatisfy { $0.severity == .warning })544        // an external link is not also reported as a broken reference545        #expect(audit(e, [.brokenReference]).isEmpty)546    }547548    @Test func slowFormulasAreRankedByMeasuredTime() {549        let b = ACBook()550        for r in 1...20000 { b.engine.set(sheet: 0, row: r - 1, col: 0, .value(.number(Double(r)))) }551        b.set("C1", "=SUM(A1:A20000)+SUM(A1:A20000)+SUM(A1:A20000)+SUM(A1:A20000)")       // heavy552        for r in 2...40 { b.set("C\(r)", "=A\(r)+1") }                                      // trivial553        b.engine.recalculate()554        let f = audit(b, [.slowFormula]) { $0.slowFormulaThreshold = 0; $0.slowFormulaTopN = 3; $0.profileRepetitions = 5 }555        #expect(f.count == 3)556        #expect(f.first { $0.evidence.hasPrefix("#1 slowest of 40 formulas timed") }?.cells == [b.ref("C1")])557        #expect(f.first { $0.cells == [b.ref("C1")] }?.evidence.contains("reads about 20000 cells") == true)558        // nothing is reported under the threshold, and sampling limits how many formulas are timed559        #expect(audit(b, [.slowFormula]) { $0.slowFormulaThreshold = 60 }.isEmpty)560        let sampled = audit(b, [.slowFormula]) { $0.slowFormulaThreshold = 0; $0.profileSampleLimit = 10; $0.slowFormulaTopN = 100 }561        #expect(sampled.count == 10)562        #expect(sampled.allSatisfy { $0.evidence.contains("of 10 formulas timed") && $0.explanation.contains("1 formula in 4 was timed") })563        #expect(audit(b, [.slowFormula]) { $0.slowFormulaTopN = 0 }.isEmpty)564        // profiling does not disturb the model565        #expect(b.get("C1") == .number(800_040_000) && !b.engine.hasPendingWork)566    }567568    @Test func cellCapAndCompression() {569        let b = ACBook()570        for r in 1...300 { b.set("A\(r)", String(r)); b.set("B\(r)", "=A\(r)*1.5") }571        let f = audit(b, [.hardcodedConstant]) { $0.maxCellsPerFinding = 50 }572        #expect(f.count == 1 && f[0].cells.count == 50 && f[0].ranges == [RangeRef(a1: "B1:B300")!])573        #expect(f[0].location == "Sheet1!B1:B300")574        #expect(FormulaIntel.compress([CellRef(0, 0), CellRef(1, 0), CellRef(0, 1), CellRef(1, 1), CellRef(5, 5)]) == [RangeRef(a1: "A1:B2")!, RangeRef(a1: "F6")!])575        #expect(FormulaIntel.compress([CellRef(0, 0), CellRef(2, 0), CellRef(3, 0)]) == [RangeRef(a1: "A1")!, RangeRef(a1: "A3:A4")!])576        #expect(FormulaIntel.compress([]) == [])577    }578}579
Files main
Menu

Theme