Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Sources/Audit/Auditor/RulesContent.swift10// Module : Audit11// 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 SheetCore8990extension ModelAuditor.Scan {91 // MARK: Hardcoded constants inside formulas9293 /// Numeric literals of a formula that look like embedded inputs (display form, e.g. "1.15", "5%").94 func hardcodedConstants(_ expr: Expr) -> [String] {95 var out = [String]()96 func note(_ value: Double, _ display: String) {97 if !options.allowedConstants.contains(value), !out.contains(display) { out.append(display) }98 }99 func visit(_ e: Expr, structural: Bool) {100 switch e {101 case .number(let d): if !structural { note(d, Expr.number(d).formulaText()) }102 case .percent(.number(let d)): if !structural { note(d / 100, Expr.number(d).formulaText() + "%") }103 case .unary(.neg, .number(let d)): if !structural { note(-d, "-" + Expr.number(d).formulaText()) }104 case .unary(_, let x), .percent(let x), .paren(let x), .spill(let x), .implicit(let x): visit(x, structural: structural)105 case .binary(.pow, let a, let b):106 visit(a, structural: structural)107 // exponents (x^2, x^(1/12)) are part of the formula's structure108 visit(b, structural: true)109 case .binary(_, let a, let b): visit(a, structural: structural); visit(b, structural: structural)110 case .call(let name, let args):111 let s = options.functionsWithStructuralConstants.contains(name)112 for a in args {113 // only literals passed directly are structural; nested calculations are judged on their own114 switch a {115 case .number, .percent(.number), .unary(.neg, .number): visit(a, structural: s)116 default: visit(a, structural: false)117 }118 }119 case .invoke(let f, let args): visit(f, structural: false); for a in args { visit(a, structural: false) }120 default: break // array constants, text, references121 }122 }123 visit(expr, structural: false)124 return out125 }126127 mutating func ruleHardcodedConstants() {128 for si in wb.sheets.indices {129 for group in groupsByShape(si, where: { !hardcodedConstants($0.expr).isEmpty }) {130 let first = group[0]131 let constants = hardcodedConstants(first.expr)132 add(.hardcodedConstant, .info, sheet: si, cells: group.map(\.ref),133 evidence: "\(first.ref.a1): \(text(first)) contains the constant\(constants.count == 1 ? "" : "s") \(constants.joined(separator: ", "))"134 + (group.count > 1 ? " (same formula in \(group.count) cells: \(rangesText(group.map(\.ref))))" : "") + ".",135 explanation: "A number typed inside a formula is an assumption that cannot be seen, labelled or changed in one place.",136 action: "Move the value to a labelled input cell (or a defined name) and refer to it, unless it is a true constant of the calculation.")137 }138 }139 }140141 // MARK: Volatile functions142143 func volatileFunctions(_ expr: Expr) -> [String] {144 var out = [String]()145 expr.walk { node in146 if case .call(let n, _) = node, engine.registry.lookup(n)?.isVolatile == true, !out.contains(n) { out.append(n) }147 }148 return out149 }150151 mutating func ruleVolatile() {152 for si in wb.sheets.indices {153 var byFunction = [String: [CellRef]]()154 var wide = [(ModelAuditor.FormulaCell, [String], [String])]()155 for f in formulas[si] {156 let names = volatileFunctions(f.expr)157 guard !names.isEmpty else { continue }158 for n in names { byFunction[n, default: []].append(f.ref) }159 var whole = [String]()160 f.expr.walk { node in161 if case .range(let r) = node, r.kind != .cells { whole.append(node.formulaText()) }162 }163 if !whole.isEmpty { wide.append((f, names, whole)) }164 }165 if options.rules.contains(.volatileFunction) {166 for name in byFunction.keys.sorted() {167 let cells = byFunction[name]!168 add(.volatileFunction, .info, sheet: si, cells: cells, key: name,169 evidence: "\(name) is used in \(cells.count) cell\(cells.count == 1 ? "" : "s"): \(rangesText(cells)).",170 explanation: "\(name) is volatile: it is recalculated — together with everything that depends on it — on every change anywhere in the workbook.",171 action: "Keep volatile functions to a few input cells; replace OFFSET/INDIRECT by INDEX or direct references where possible.")172 }173 }174 if options.rules.contains(.volatileWholeColumn) {175 for (f, names, whole) in wide {176 add(.volatileWholeColumn, .warning, sheet: si, cells: [f.ref],177 evidence: "\(f.ref.a1): \(text(f)) combines \(names.joined(separator: ", ")) with the whole-row/column reference\(whole.count == 1 ? "" : "s") \(whole.joined(separator: ", ")).",178 explanation: "The formula is recalculated on every change (volatile) and scans an entire row or column each time.",179 action: "Limit the reference to the data range (or a table column) or remove the volatile function.")180 }181 }182 }183 }184185 // MARK: Complexity186187 mutating func ruleComplexity() {188 for si in wb.sheets.indices {189 func exceeds(_ c: ModelAuditor.Complexity) -> Bool {190 c.length > options.maxFormulaLength || c.nestingDepth > options.maxNestingDepth || c.score > options.maxComplexityScore191 }192 for group in groupsByShape(si, where: { exceeds(ModelAuditor.complexity(of: $0.expr)) }) {193 let first = group[0]194 let c = ModelAuditor.complexity(of: first.expr)195 let severe = c.length >= 2 * options.maxFormulaLength || c.nestingDepth >= options.maxNestingDepth + 3196 var shown = text(first)197 if shown.count > 120 { shown = String(shown.prefix(117)) + "…" }198 add(.complexFormula, severe ? .warning : .info, sheet: si, cells: group.map(\.ref),199 evidence: "\(first.ref.a1): \(shown) — \(c.length) characters, nesting depth \(c.nestingDepth), \(c.functionCount) function calls, \(c.referenceCount) references, complexity score \(Value.generalString((c.score * 10).rounded() / 10))"200 + (group.count > 1 ? " (\(group.count) cells)" : "") + ".",201 explanation: "Long or deeply nested formulas are hard to read, test and maintain, and errors in them go unnoticed.",202 action: "Split the calculation into intermediate cells, or use LET to name the steps.")203 }204 }205 }206207 // MARK: External workbook references208209 static func externalWorkbook(_ sheet: String?) -> String? {210 guard let s = sheet, let open = s.firstIndex(of: "["), let close = s.firstIndex(of: "]"), open < close else { return nil }211 return String(s[s.index(after: open)..<close])212 }213214 func externalWorkbooks(_ expr: Expr) -> [String] {215 var out = [String]()216 expr.walk { node in217 var s: String?218 switch node {219 case .cell(let sh, _): s = sh220 case .range(let r): s = r.sheet221 case .name(_, let sh): s = sh222 default: break223 }224 if let book = ModelAuditor.Scan.externalWorkbook(s), !out.contains(book) { out.append(book) }225 }226 return out227 }228229 mutating func ruleExternal() {230 for si in wb.sheets.indices {231 var byBook = [String: [ModelAuditor.FormulaCell]]()232 for f in formulas[si] { for b in externalWorkbooks(f.expr) { byBook[b, default: []].append(f) } }233 for book in byBook.keys.sorted() {234 let cells = byBook[book]!235 add(.externalReference, .warning, sheet: si, cells: cells.map(\.ref), key: book,236 evidence: "\(cells.count) formula\(cells.count == 1 ? "" : "s") read\(cells.count == 1 ? "s" : "") from the external workbook \"\(book)\", e.g. \(cells[0].ref.a1): \(text(cells[0])).",237 explanation: "Links to other workbooks break when files are moved or renamed, and their values cannot be recalculated or audited from here.",238 action: "Import the data into this workbook (or a data connection) and refer to the local copy.")239 }240 }241 for dn in wb.names.values.sorted(by: { $0.name < $1.name }) {242 guard let e = try? FormulaParser.parse(dn.formula) else { continue }243 let books = externalWorkbooks(e)244 guard !books.isEmpty else { continue }245 add(.externalReference, .warning, sheet: nil, cells: [], key: "name:" + dn.name,246 evidence: "The defined name \(dn.name) (=\(dn.formula)) refers to the external workbook \"\(books[0])\".",247 explanation: "Links to other workbooks break when files are moved or renamed, and their values cannot be recalculated or audited from here.",248 action: "Point the name to cells of this workbook.")249 }250 }251252 // MARK: References to blank cells253254 /// Blank cells and wholly blank ranges a formula reads outside blank-tolerant functions.255 func blankReferences(_ f: ModelAuditor.FormulaCell, sheet si: Int) -> [String] {256 var out = [String]()257 func check(_ node: Expr) {258 for r in FormulaIntel.directRefs(node, workbook: wb, sheet: si) {259 let s = wb.sheets[r.sheet]260 if r.range.isSingleCell {261 if !(s.cell(r.range.r0, r.range.c0)?.hasContent ?? false), !out.contains(r.text) { out.append(r.text) }262 } else if !r.range.isWholeColumns, !r.range.isWholeRows {263 var any = false264 s.cells.forEach(in: r.range) { _, _, c in if c.hasContent { any = true } }265 if !any, !out.contains(r.text) { out.append(r.text) }266 }267 }268 }269 func visit(_ e: Expr) {270 switch e {271 case .cell, .range: check(e)272 case .call(let name, let args):273 if options.blankTolerantFunctions.contains(name) {274 // conditions and tests may look at blanks; still inspect nested calculations275 for a in args { if case .call = a { visit(a) } else if case .binary(let op, _, _) = a, [.add, .sub, .mul, .div, .pow].contains(op) { visit(a) } }276 } else { for a in args { visit(a) } }277 case .binary(let op, let a, let b):278 if op == .eq || op == .ne, a == .text("") || b == .text("") { return } // explicit blank test279 if op == .range { return }280 visit(a); visit(b)281 case .unary(_, let x), .percent(let x), .paren(let x), .implicit(let x): visit(x)282 case .invoke(_, let args): for a in args { visit(a) }283 default: break284 }285 }286 visit(f.expr)287 return out288 }289290 mutating func ruleBlankReferences() {291 for si in wb.sheets.indices {292 var order = [String]()293 var groups = [String: [(ModelAuditor.FormulaCell, [String])]]()294 for f in formulas[si] {295 let blanks = blankReferences(f, sheet: si)296 guard !blanks.isEmpty else { continue }297 if groups[f.shape] == nil { order.append(f.shape) }298 groups[f.shape, default: []].append((f, blanks))299 }300 for shape in order {301 let g = groups[shape]!302 let (first, blanks) = g[0]303 add(.blankReference, .warning, sheet: si, cells: g.map(\.0.ref),304 evidence: "\(first.ref.a1): \(text(first)) reads the empty cell\(blanks.count == 1 ? "" : "s") \(blanks.joined(separator: ", "))"305 + (g.count > 1 ? " (\(g.count) cells with the same formula read blanks)" : "") + ".",306 explanation: "An empty cell counts as 0 (or as empty text) in a calculation, so a missing input silently produces a plausible-looking result.",307 action: "Fill in the missing input, correct the reference, or make the formula handle the blank case explicitly.")308 }309 }310 }311}312