Repository Public
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