Repository Public
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