Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/FeatureAnalysis/Sources/WhatIfTools/GoalSeekTool.swift10// Module : WhatIfTools11// 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 SheetUI90import Statistics91import WhatIf92import AnalysisKit9394/// Goal Seek: the value of one input cell that makes a formula cell reach a target.95enum GoalSeekTool {96 static let descriptor: ToolDescriptor = {97 var t = ToolDescriptor(98 id: "goalseek", title: L("Goal Seek"), group: L("Solve"), systemImage: "scope",99 summary: L("Finds the value of one input cell that makes a formula cell equal a target value."),100 fields: [101 .cell("set", L("Set cell"), help: L("The formula cell that should reach the target.")),102 .number("target", L("To value"), 0),103 .cell("changing", L("By changing cell"), help: L("An input cell holding a number (not a formula).")),104 ],105 info: [106 L("Method: automatic bracketing of a sign change around the current value, then Brent's method; a secant iteration is the fallback when no bracket is found."),107 L("Convergence: |achieved − target| ≤ 1e-9 × max(1, |target|)."),108 L("The search runs on a private copy of the workbook. The live workbook, its undo and redo stacks are untouched until you apply the solution."),109 L("Validated against analytic inversions (annuity principal and term, IRR of a three-flow model): solutions agree to 1e-9 or better."),110 ],111 needsModel: true, runTitle: L("Seek"), run: run)112 t.demo = { _, v in113 v.set("set", text: "Model!B17"); v.set("target", number: 150_000); v.set("changing", text: "Model!B5")114 }115 return t116 }()117118 static func run(_ input: ToolInput) throws -> ToolResult {119 let model = try input.requireModel()120 let wb = model.workbook121 let set = try input.cell("set"), changing = try input.cell("changing")122 let target = input.values.number("target")123 guard target.isFinite else { throw ToolError(L("The target value must be a finite number.")) }124 try WhatIfSupport.requireInput(changing, in: wb)125 try WhatIfSupport.requireFormula(set, in: wb)126 let r = try GoalSeek.run(engine: model, setCell: set.modelCell, toValue: target, byChanging: changing.modelCell)127128 let setFormat = WhatIfSupport.format(set, in: wb), inputFormat = WhatIfSupport.format(changing, in: wb)129 let setName = WhatIfSupport.nameWithAddress(set, in: wb), changingName = WhatIfSupport.nameWithAddress(changing, in: wb)130 var blocks: [ResultBlock] = []131 if r.converged, let x = r.solution {132 blocks.append(.metrics([133 Metric(L("Solution"), x, inputFormat), Metric(L("Achieved value"), r.achievedValue, setFormat), Metric(L("Residual"), r.residual, .scientific),134 ]))135 blocks.append(.interpretation(L("%@ reaches %@ when %@ is set to %@ (currently %@).", setName, Fmt.text(.number(target), setFormat, locale: input.locale),136 changingName, Fmt.text(.number(x), inputFormat, locale: input.locale),137 Fmt.text(.number(r.startValue), inputFormat, locale: input.locale))))138 } else {139 blocks.append(.warning(L("No solution was found: %@ does not reach %@ by changing %@.", setName,140 Fmt.text(.number(target), setFormat, locale: input.locale), changingName)))141 }142 var items: [(String, Value, CellFormat)] = [143 (L("Status"), .text(r.converged ? L("Solution found") : L("No solution found")), .auto),144 (L("Set cell"), .text(WhatIfSupport.label(set, in: wb)), .auto),145 (L("Target value"), .number(target), setFormat),146 (L("Changing cell"), .text(WhatIfSupport.label(changing, in: wb)), .auto),147 (L("Starting value"), .finite(r.startValue), inputFormat),148 (r.converged ? L("Solution") : L("Closest value tried"), .finite(r.solution), inputFormat),149 (L("Achieved value"), .finite(r.achievedValue), setFormat),150 (L("Residual"), .finite(r.residual), .scientific),151 (L("Method"), .text(methodName(r.method)), .auto),152 (L("Iterations"), .number(Double(r.iterations)), .integer),153 (L("Recalculations"), .number(Double(r.evaluations)), .integer),154 ]155 if let lo = r.bracketLower, let hi = r.bracketUpper {156 items.append((L("Bracket, lower"), .number(lo), inputFormat))157 items.append((L("Bracket, upper"), .number(hi), inputFormat))158 }159 blocks.append(.table(.keyValue(title: L("Convergence"), header: (L("Quantity"), L("Value")), items)))160 blocks.append(.note([L("Residual = achieved value − target value."), L("Solver message: %@", r.message), L("The workbook is unchanged until you apply the solution.")]))161162 var actions = [ResultAction]()163 if r.converged, let x = r.solution {164 actions.append(ResultAction(id: "apply", title: L("Apply Solution"), systemImage: "checkmark.circle",165 help: L("Writes the solution into the changing cell. One step to undo."), isPrimary: true) { session in166 try apply(solution: x, to: changing, session: session)167 return L("%@ set to %@. Undo restores the previous value.", WhatIfSupport.label(changing, in: session.workbook), Fmt.number(x, locale: session.locale))168 })169 }170 return ToolResult(title: L("Goal Seek"), subtitle: L("%@ → %@", setName, Fmt.text(.number(target), setFormat, locale: input.locale)),171 blocks: blocks, actions: actions, sheetName: L("Goal Seek"))172 }173174 /// Writes the solution into the live workbook as ONE undoable step labelled "Goal Seek".175 @MainActor static func apply(solution x: Double, to changing: CellAddress, session: WorkbookSession) throws {176 guard changing.sheet < session.workbook.sheets.count else { throw ToolError(L("The sheet of the changing cell no longer exists.")) }177 try WhatIfSupport.requireInput(changing, in: session.workbook)178 session.perform(L("Goal Seek")) { e in179 e.set(sheet: changing.sheet, row: changing.cell.row, col: changing.cell.col, .value(.number(x)))180 }181 }182183 static func methodName(_ m: String) -> String {184 switch m {185 case "brent": L("Brent's method")186 case "secant": L("Secant method")187 case "initial value": L("Initial value (already at the target)")188 default: m189 }190 }191}192