SPB Git

Repository Public

GoalSeekTool.swift 9.9 KB 192 lines swift

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
Files main
Menu

Theme