SPB Git

Repository Public

GoalSeek.swift 9.2 KB 196 lines swift

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

Theme