SPB Git

Repository Public

MonteCarloTool.swift 17.3 KB 237 lines swift

1import Foundation2import SwiftUI3import SheetCore4import Statistics5import WhatIf6import AnalysisKit78/// Monte Carlo simulation of the live model: input cells follow distributions, output cells are recorded.9enum MonteCarloTool {10    static let descriptor: ToolDescriptor = {11        var t = ToolDescriptor(12            id: "montecarlo", title: L("Monte Carlo Simulation"), group: L("Simulation"), systemImage: "dice",13            summary: L("Samples the input cells from probability distributions, recalculates the model for every trial and summarises the output cells."),14            fields: [15                FormField("bindings", "", .text(default: "[]", placeholder: ""), visible: { _ in false }),16                .cells("outputs", L("Output cells"), help: L("Formula cells recorded in every trial.")),17                .integer("trials", L("Trials"), 10_000, min: 100, max: 1_000_000),18                .integer("seed", L("Seed"), 1, min: 0, max: 2_000_000_000, help: L("The same seed reproduces the same results exactly.")),19                .text("threshold", L("Threshold"), placeholder: L("optional"), help: L("Reports the probability that each output exceeds this value.")),20                .choice("correlation", L("Rank correlation of the inputs"), [21                    FieldOption("none", L("Independent")), FieldOption("common", L("Common ρ")), FieldOption("matrix", L("Matrix")),22                ]),23                .number("rho", L("Spearman ρ between every pair"), 0.5, min: -0.99, max: 0.99, visible: { $0.choice("correlation") == "common" }),24                .text("matrix", L("Spearman matrix"), placeholder: L("e.g. 1 0.5; 0.5 1"),25                      help: L("One row per input in the order below, rows separated by “;”."), visible: { $0.choice("correlation") == "matrix" }),26                .choice("method", L("Correlation method"), [FieldOption("imanConover", L("Iman–Conover")), FieldOption("gaussianCopula", L("Gaussian copula"))],27                        visible: { $0.choice("correlation") != "none" }),28            ],29            info: [30                L("Sampling: inversion of each distribution with the xoshiro256** generator; runs with the same seed are bit-identical."),31                L("Rank correlation: Iman–Conover reorders independent draws (marginals are preserved exactly); the Gaussian copula samples correlated normals. Both reach the target Spearman matrix within about 0.02 at 20,000 trials."),32                L("Percentiles use linear interpolation (type 7); the tornado shows the Spearman rank correlation of each input with the output and its normalised ρ² contribution."),33                L("Latin hypercube sampling is not available."),34                L("Each trial recalculates a private copy of the workbook. The live workbook, its undo and redo stacks are untouched, also after a cancellation (a cancelled run reports the trials completed so far)."),35                L("Lognormal inputs are entered by the mean and standard deviation of the variable itself; PERT uses λ = 4."),36            ],37            needsModel: true, runTitle: L("Simulate"), run: run)38        t.extraForm = { context in AnyView(MonteCarloEditor(session: context.session, values: context.values)) }39        t.demo = { _, v in40            var price = MCBinding.preset(cell: "Model!B4", name: "", value: 48); price.kind = .triangular; price.p1 = 42; price.p2 = 48; price.p3 = 5241            var units = MCBinding.preset(cell: "Model!B5", name: "", value: 12_000); units.p2 = 1_50042            var cost = MCBinding.preset(cell: "Model!B6", name: "", value: 29); cost.kind = .pert; cost.p1 = 26; cost.p2 = 29; cost.p3 = 3543            v.set("bindings", text: MCBinding.encode([price, units, cost]))44            v.set("outputs", text: "Model!B17"); v.set("trials", int: 10_000); v.set("seed", int: 2026); v.set("threshold", text: "50000")45        }46        return t47    }()4849    struct Setup {50        var inputs: [ModelInput]51        var outputs: [ModelOutput]52        var outputCells: [CellAddress]53        var options: MonteCarloOptions54        var threshold: Double?55    }5657    /// Validates the form and turns it into the engine's inputs.58    static func setup(_ input: ToolInput, model: CalcEngine) throws -> Setup {59        let wb = model.workbook60        let bindings = MCBinding.decode(input.values.text("bindings"))61        guard !bindings.isEmpty else { throw ToolError(L("Add at least one input cell with a distribution.")) }62        var inputs = [ModelInput]()63        var seen = Set<CellAddress>()64        for b in bindings {65            guard let cells = RangeReader.cells(b.cell, workbook: wb, sheet: input.activeSheet), cells.count == 1, let c = cells.first else {66                throw ToolError(L("“%@” is not a single input cell.", b.displayCell))67            }68            guard seen.insert(c).inserted else { throw ToolError(L("%@ is bound to two distributions.", WhatIfSupport.label(c, in: wb))) }69            try WhatIfSupport.requireInput(c, in: wb)70            let name = b.name.trimmingCharacters(in: .whitespaces)71            inputs.append(ModelInput(cell: c.modelCell, name: name.isEmpty ? WhatIfSupport.name(c, in: wb) : name, distribution: try b.distribution(locale: input.locale)))72        }73        let outputCells = try input.cells("outputs")74        guard outputCells.count <= 20 else { throw ToolError(L("Choose at most %d output cells.", 20)) }75        for c in outputCells where seen.contains(c) { throw ToolError(L("%@ is both an input and an output.", WhatIfSupport.label(c, in: wb))) }76        var options = MonteCarloOptions(trials: max(input.values.int("trials"), 1), seed: UInt64(max(input.values.int("seed"), 0)))77        options.progressInterval = max(16, min(256, options.trials / 100))78        let method: MonteCarloCorrelation.Method = input.values.choice("method") == "gaussianCopula" ? .gaussianCopula : .imanConover79        switch input.values.choice("correlation") {80        case "common":81            guard inputs.count >= 2 else { throw ToolError(L("A rank correlation needs at least two inputs.")) }82            let rho = input.values.number("rho")83            guard rho > -1 / Double(inputs.count - 1) else {84                throw ToolError(L("A common correlation of %@ between %d inputs is not a valid correlation matrix (it must exceed %@).", Fmt.number(rho, locale: input.locale),85                                  inputs.count, Fmt.number(-1 / Double(inputs.count - 1), locale: input.locale)))86            }87            options.correlation = MonteCarloCorrelation(matrix: MCCorrelation.common(rho, count: inputs.count), method: method)88        case "matrix":89            options.correlation = MonteCarloCorrelation(matrix: try MCCorrelation.parse(input.values.text("matrix"), count: inputs.count, locale: input.locale), method: method)90        default: break91        }92        var threshold: Double?93        let typed = input.values.text("threshold").trimmingCharacters(in: .whitespaces)94        if !typed.isEmpty {95            guard let list = FormValues.numberList(typed, locale: input.locale), list.count == 1 else { throw ToolError(L("The threshold must be a single number.")) }96            threshold = list[0]97        }98        return Setup(inputs: inputs, outputs: outputCells.map { ModelOutput(cell: $0.modelCell, name: WhatIfSupport.name($0, in: wb)) }, outputCells: outputCells,99                     options: options, threshold: threshold)100    }101102    static func simulate(_ input: ToolInput) throws -> (Setup, MonteCarloResult) {103        let model = try input.requireModel()104        let s = try setup(input, model: model)105        let progress = input.progress106        let r = try ModelSimulation.run(engine: model, inputs: s.inputs, outputs: s.outputs, options: s.options,107                                        progress: { done, total in progress(total > 0 ? Double(done) / Double(total) : 0) }, isCancelled: input.isCancelled)108        if r.cancelled && r.trialsCompleted < 2 { throw CancellationError() }109        return (s, r)110    }111112    static func run(_ input: ToolInput) throws -> ToolResult {113        let (s, r) = try simulate(input)114        let wb = try input.requireModel().workbook115        let loc = input.locale116        var blocks = [ResultBlock]()117        if r.cancelled {118            blocks.append(.warning(L("Partial results: the simulation was cancelled after %@ of %@ trials. The workbook was not modified.",119                                     Fmt.integer(r.trialsCompleted, locale: loc), Fmt.integer(r.trialsRequested, locale: loc))))120        }121        for (k, out) in r.outputs.enumerated() {122            let format = WhatIfSupport.format(s.outputCells[k], in: wb)123            let samples = k < r.outputSamples.count ? r.outputSamples[k] : []124            var metrics = [Metric(L("Mean"), out.mean, format), Metric(L("Std. deviation"), out.standardDeviation, format), Metric(L("Median"), out.median, format),125                           Metric(L("5th – 95th percentile"), text: percentileRange(out, format, loc))]126            if let t = s.threshold {127                metrics.append(Metric(L("P(%@ > %@)", out.name, Fmt.text(.number(t), format, locale: loc)), 1 - out.probability(atMost: t, in: samples), .percent(1)))128            }129            if r.outputs.count > 1 { blocks.append(.note([WhatIfSupport.nameWithAddress(s.outputCells[k], in: wb)], title: L("Output %d", k + 1))) }130            blocks.append(.metrics(metrics))131            if out.invalidCount > 0 {132                blocks.append(.warning(L("%@ of the trials did not produce a number for %@ (errors or text) and are excluded from the statistics.",133                                         Fmt.integer(out.invalidCount, locale: loc), out.name)))134            }135            blocks.append(.charts(charts(out, inputs: r.inputs.count, locale: loc)))136            blocks.append(.table(summaryTable(out, format: format, threshold: s.threshold, samples: samples)))137            blocks.append(.table(percentileTable(out, format: format)))138            if !out.sensitivities.isEmpty { blocks.append(.table(sensitivityTable(out))) }139        }140        blocks.append(.table(inputTable(s, wb: wb)))141        if let achieved = r.achievedInputRankCorrelation, let target = s.options.correlation {142            let names = r.inputs.map(\.name)143            blocks.append(.table(ReportTable(title: L("Achieved rank correlation of the inputs (Spearman)"),144                                             rows: [[.text("")] + names.map { .text($0) }] + achieved.enumerated().map { i, row in [.text(names[i])] + row.map { .finite($0) } },145                                             columnFormats: Dictionary(uniqueKeysWithValues: names.indices.map { ($0 + 1, CellFormat.fixed(3)) }),146                                             note: L("Method: %@.", target.method == .imanConover ? L("Iman–Conover") : L("Gaussian copula")))))147        }148        blocks.append(.note([149            L("Trials: %@ completed of %@ requested; seed %@.", Fmt.integer(r.trialsCompleted, locale: loc), Fmt.integer(r.trialsRequested, locale: loc), String(r.seed)),150            L("Inputs are sampled by inversion (xoshiro256**); the same seed reproduces these results exactly."),151            L("The simulation ran on a private copy of the workbook: input cells keep their values."),152        ]))153        return ToolResult(title: L("Monte Carlo Simulation"),154                          subtitle: L("%@ trials, %d inputs, %d outputs", Fmt.integer(r.trialsCompleted, locale: loc), r.inputs.count, r.outputs.count),155                          blocks: blocks, sheetName: L("Simulation"))156    }157158    static func percentileRange(_ out: MonteCarloOutput, _ format: CellFormat, _ loc: Locale) -> String {159        func p(_ q: Double) -> String { out.percentiles.first { abs($0.probability - q) < 1e-9 }.map { Fmt.text(.number($0.value), format, locale: loc) } ?? "—" }160        return p(0.05) + " – " + p(0.95)161    }162163    static func charts(_ out: MonteCarloOutput, inputs: Int, locale loc: Locale) -> [ChartBlock] {164        var list = [ChartBlock]()165        if !out.histogram.counts.isEmpty {166            var spec = ChartKit.binned(edges: out.histogram.edges, counts: out.histogram.counts, xTitle: out.name, yTitle: L("Trials"))167            spec.categories = .labels(WhatIfSupport.binLabels(edges: out.histogram.edges, locale: loc))168            list.append(ChartBlock(title: L("Distribution of %@", out.name), spec: spec,169                                   summary: L("Histogram of %@ over %@ trials: mean %@, standard deviation %@, from %@ to %@.", out.name, Fmt.integer(out.count, locale: loc),170                                              Fmt.number(out.mean, locale: loc), Fmt.number(out.standardDeviation, locale: loc),171                                              Fmt.number(out.minimum, locale: loc), Fmt.number(out.maximum, locale: loc)),172                                   height: 230))173        }174        let conv = out.convergence.filter { $0.mean.isFinite && $0.standardError.isFinite }175        if conv.count > 2 {176            let line = ChartKit.Line(L("Running mean"), conv.map(\.mean), x: conv.map { Double($0.trials) },177                                     lower: conv.map { $0.mean - $0.standardError }, upper: conv.map { $0.mean + $0.standardError })178            list.append(ChartBlock(title: L("Convergence of the mean (± 1 standard error)"),179                                   spec: ChartKit.lines([line], xTitle: L("Trials"), yTitle: L("Mean of %@", out.name)),180                                   summary: L("Running mean of %@ against the number of trials; it ends at %@ with a standard error of %@.", out.name,181                                              Fmt.number(out.mean, locale: loc), Fmt.number(out.standardError, locale: loc)),182                                   height: 230))183        }184        let sens = out.sensitivities.filter { $0.rankCorrelation.isFinite }.sorted { abs($0.rankCorrelation) > abs($1.rankCorrelation) }.prefix(12)185        if !sens.isEmpty {186            var spec = ChartKit.bars(categories: sens.map(\.input), values: sens.map(\.rankCorrelation), xTitle: L("Rank correlation with %@", out.name))187            spec.yAxis.min = -1; spec.yAxis.max = 1188            spec.annotations = [.referenceLine(.horizontal, at: 0)]189            list.append(ChartBlock(title: L("Sensitivity (rank correlation)"), spec: spec,190                                   summary: L("Bar chart of the Spearman rank correlation of each input with %@: %@.", out.name,191                                              sens.map { "\($0.input) \(Fmt.fixed($0.rankCorrelation, 2, locale: loc))" }.joined(separator: ", ")),192                                   height: Double(max(sens.count, 3)) * 26 + 90))193        }194        return list195    }196197    static func summaryTable(_ out: MonteCarloOutput, format: CellFormat, threshold: Double?, samples: [Double]) -> ReportTable {198        var items: [(String, Value, CellFormat)] = [199            (L("Valid trials"), .number(Double(out.count)), .integer), (L("Invalid trials"), .number(Double(out.invalidCount)), .integer),200            (L("Mean"), .finite(out.mean), format), (L("Standard error of the mean"), .finite(out.standardError), format),201            (L("Standard deviation"), .finite(out.standardDeviation), format), (L("Minimum"), .finite(out.minimum), format),202            (L("Median"), .finite(out.median), format), (L("Maximum"), .finite(out.maximum), format),203            (L("Skewness"), .finite(out.skewness), .fixed(4)), (L("Excess kurtosis"), .finite(out.excessKurtosis), .fixed(4)),204        ]205        if let t = threshold {206            let below = out.probability(atMost: t, in: samples)207            items.append((L("Threshold"), .number(t), format))208            items.append((L("Probability of exceeding the threshold"), .finite(1 - below), .percent(2)))209            items.append((L("Probability at or below the threshold"), .finite(below), .percent(2)))210        }211        return .keyValue(title: L("Summary of %@", out.name), header: (L("Statistic"), L("Value")), items)212    }213214    static func percentileTable(_ out: MonteCarloOutput, format: CellFormat) -> ReportTable {215        ReportTable(title: L("Percentiles of %@", out.name),216                    rows: [[.text(L("Percentile")), .text(L("Value"))]] + out.percentiles.map { [.number($0.probability), .finite($0.value)] },217                    headerColumn: false, columnFormats: [0: .percent(0), 1: format])218    }219220    static func sensitivityTable(_ out: MonteCarloOutput) -> ReportTable {221        let sorted = out.sensitivities.sorted { abs($0.rankCorrelation) > abs($1.rankCorrelation) }222        return ReportTable(title: L("Sensitivity of %@ to the inputs", out.name),223                           rows: [[.text(L("Input")), .text(L("Rank correlation (Spearman)")), .text(L("Contribution to variance"))]]224                               + sorted.map { [.text($0.input), .finite($0.rankCorrelation), .finite($0.contributionToVariance)] },225                           columnFormats: [1: .fixed(3), 2: .percent(1)],226                           note: L("Contribution = ρ² normalised to sum to 100 %; it is an approximation that ignores interactions."))227    }228229    static func inputTable(_ s: Setup, wb: Workbook) -> ReportTable {230        var rows: [[Value]] = [[.text(L("Input")), .text(L("Cell")), .text(L("Distribution")), .text(L("Mean")), .text(L("Standard deviation"))]]231        for i in s.inputs {232            rows.append([.text(i.name), .text(i.cell.label(in: wb)), .text(i.distribution.label), .finite(i.distribution.mean), .finite(i.distribution.variance.squareRoot())])233        }234        return ReportTable(title: L("Input assumptions"), rows: rows)235    }236}237
Files main
Menu

Theme