Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Sources/WhatIf/GoalSeek.swift10// Module : WhatIf11// 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 SheetCore89import Statistics9091public struct GoalSeekOptions: Sendable, Hashable {92 /// Largest acceptable |set cell − target|, multiplied by max(1, |target|).93 public var tolerance = 1e-994 /// Iteration limit of each numerical method.95 public var maxIterations = 20096 /// Optional bounds on the changing cell.97 public var lowerBound: Double?98 public var upperBound: Double?99 /// First step of the bracketing search; nil = max(10 % of the starting value, 0.01).100 public var initialStep: Double?101 public var isolation: WhatIfIsolation = .clone102 /// On success, write the solution into the changing cell of the live workbook as ONE undoable103 /// edit labelled "Goal Seek". On failure the live workbook is never modified.104 public var commit = false105 public init() {}106}107108public struct GoalSeekResult: Sendable, Hashable, Codable, TableReport {109 /// True when |achieved − target| is within the tolerance.110 public var converged: Bool111 /// Value of the changing cell that reaches the target (when not converged: the closest value tried, if any).112 public var solution: Double?113 /// Value of the set cell at `solution`.114 public var achievedValue: Double115 public var targetValue: Double116 /// achievedValue − targetValue.117 public var residual: Double118 /// Value of the changing cell before the search (it is left untouched unless `committed`).119 public var startValue: Double120 public var iterations: Int121 /// Number of model recalculations used.122 public var evaluations: Int123 /// "brent", "secant" or "initial value".124 public var method: String125 /// Sign-change interval found by the automatic bracketing, if any.126 public var bracketLower: Double?127 public var bracketUpper: Double?128 public var message: String129 /// True when the solution was written to the live workbook.130 public var committed: Bool131132 public func table() -> [[Value]] {133 [134 [.text("Goal Seek"), .blank],135 Report.row("Status", converged ? "solution found" : "no solution found"),136 [.text("Solution"), Report.num(solution)],137 Report.row("Target value", targetValue),138 Report.row("Achieved value", achievedValue),139 Report.row("Residual", residual),140 Report.row("Starting value", startValue),141 Report.row("Method", method),142 Report.row("Iterations", iterations),143 Report.row("Recalculations", evaluations),144 Report.row("Message", message),145 ]146 }147}148149/// Goal Seek: find the value of one input cell that makes a formula cell equal a target.150public enum GoalSeek {151 /// - Parameters:152 /// - setCell: the formula cell whose value should reach `target`.153 /// - changing: the input cell to vary; it must hold a number or be blank (not a formula).154 /// - Returns: the outcome; the live workbook is unchanged unless `options.commit` is set and the search converged.155 /// - Throws: `WhatIfError` for cells outside the workbook.156 public static func run(engine: CalcEngine, setCell: ModelCell, toValue target: Double, byChanging changing: ModelCell,157 options: GoalSeekOptions = GoalSeekOptions()) throws -> GoalSeekResult {158 let wb = engine.workbook159 guard setCell.isValid(in: wb) else { throw WhatIfError.invalidCell(setCell.label(in: wb)) }160 guard changing.isValid(in: wb) else { throw WhatIfError.invalidCell(changing.label(in: wb)) }161 func failure(_ message: String, start: Double = .nan) -> GoalSeekResult {162 GoalSeekResult(converged: false, solution: nil, achievedValue: .nan, targetValue: target, residual: .nan, startValue: start,163 iterations: 0, evaluations: 0, method: "none", bracketLower: nil, bracketUpper: nil, message: message, committed: false)164 }165 guard target.isFinite else { return failure("The target value must be a finite number.") }166 let current = wb.sheets[changing.sheet].cell(changing.cell.row, changing.cell.col)167 if current?.formula != nil { return failure("The changing cell contains a formula; it must contain a value.") }168 let start: Double169 switch current?.value ?? .blank {170 case .number(let n): start = n171 case .blank: start = 0172 default: return failure("The changing cell must contain a number.")173 }174 let tol = options.tolerance * Swift.max(1, abs(target))175 let outcome = try ModelSandbox.run(on: engine, isolation: options.isolation) { box in176 try WhatIfSolver.solve(start: start, tolerance: tol, maxIterations: options.maxIterations,177 lower: options.lowerBound, upper: options.upperBound, step: options.initialStep) { x in178 try box.set(changing, number: x)179 return try box.number(setCell) - target180 }181 }182 var committed = false183 if outcome.converged, options.commit, let x = outcome.root {184 engine.transaction("Goal Seek") {185 engine.set(sheet: changing.sheet, row: changing.cell.row, col: changing.cell.col, .value(.number(x)))186 }187 engine.recalculate()188 committed = true189 }190 return GoalSeekResult(converged: outcome.converged, solution: outcome.root, achievedValue: outcome.residual + target, targetValue: target,191 residual: outcome.residual, startValue: start, iterations: outcome.iterations, evaluations: outcome.evaluations,192 method: outcome.method, bracketLower: outcome.bracket?.lowerBound, bracketUpper: outcome.bracket?.upperBound,193 message: outcome.message, committed: committed)194 }195}196