SPB Git

Repository Public

TornadoTool.swift 5.8 KB 79 lines swift

1import Foundation2import SheetCore3import Statistics4import WhatIf5import AnalysisKit67/// One-at-a-time sensitivity analysis shown as a tornado.8enum TornadoTool {9    static let descriptor: ToolDescriptor = {10        var t = ToolDescriptor(11            id: "tornado", title: L("Sensitivity (Tornado)"), group: L("Sensitivity"), systemImage: "chart.bar.doc.horizontal",12            summary: L("Moves each input up and down by a percentage, one at a time, and ranks the inputs by their effect on an output cell."),13            fields: [14                .cells("inputs", L("Input cells"), help: L("Cells holding numbers (not formulas).")),15                .cell("output", L("Output cell")),16                .percent("percent", L("Change of each input (±)"), 0.10, min: 0.0001, max: 10),17            ],18            info: [19                L("Each input is set to base × (1 − x) and base × (1 + x) while the other inputs stay at their base values; interactions between inputs are not measured."),20                L("Swing = |output at high − output at low|. Elasticity is the arc elasticity between the low and high points."),21                L("Inputs whose base value is 0 are left unchanged (a percentage of 0 is 0)."),22                L("The model is evaluated on a private copy of the workbook; validated against analytic deltas of a linear model (1e-12 relative)."),23            ],24            needsModel: true, run: run)25        t.demo = { _, v in v.set("inputs", text: "Model!B4:B9"); v.set("output", text: "Model!B17"); v.set("percent", number: 0.10) }26        return t27    }()2829    static func run(_ input: ToolInput) throws -> ToolResult {30        let model = try input.requireModel()31        let wb = model.workbook32        let inputs = try input.cells("inputs"), output = try input.cell("output")33        guard inputs.count <= 200 else { throw ToolError(L("Choose at most %d input cells.", 200)) }34        for c in inputs { try WhatIfSupport.requireInput(c, in: wb) }35        let percent = input.values.number("percent")36        let r = try Sensitivity.tornado(engine: model, inputs: inputs.map(\.modelCell), names: inputs.map { WhatIfSupport.name($0, in: wb) },37                                        output: output.modelCell, percent: percent)38        guard r.baseOutput.isFinite else { throw ToolError(L("The output cell %@ does not hold a number.", WhatIfSupport.label(output, in: wb))) }39        let outName = WhatIfSupport.name(output, in: wb), outFormat = WhatIfSupport.format(output, in: wb)40        let pct = Fmt.percent(percent, percent * 100 == (percent * 100).rounded() ? 0 : 2, locale: input.locale)4142        var rows: [[Value]] = [[.text(L("Input")), .text(L("Base")), .text(L("Low")), .text(L("High")), .text(L("Output at low")), .text(L("Output at high")),43                                .text(L("Swing")), .text(L("Elasticity"))]]44        for b in r.bars {45            rows.append([.text(b.input), .finite(b.baseInput), .finite(b.lowInput), .finite(b.highInput), .finite(b.outputAtLow), .finite(b.outputAtHigh),46                         .finite(b.swing), .finite(b.elasticity)])47        }48        var table = ReportTable(title: L("Inputs ranked by swing"), rows: rows,49                                columnFormats: [4: outFormat, 5: outFormat, 6: outFormat, 7: .fixed(3)])50        // base / low / high of each input in the input cell's own number format51        var formats = [String: CellFormat]()52        for c in inputs { formats[WhatIfSupport.name(c, in: wb)] = WhatIfSupport.format(c, in: wb) }53        for (i, b) in r.bars.enumerated() { if let f = formats[b.input], f != .auto { for col in 1...3 { table.cellFormats[CellRef(i + 1, col)] = f } } }54        let shown = Array(r.bars.prefix(15))55        var spec = ChartKit.tornado(inputs: shown.map(\.input), deltaLow: shown.map(\.deltaLow), deltaHigh: shown.map(\.deltaHigh),56                                    lowName: L("Input −%@", pct), highName: L("Input +%@", pct), valueTitle: L("Change in %@", outName))57        spec.annotations = [.referenceLine(.horizontal, at: 0)]      // zero line of the value axis (bars are horizontal)58        let chart = ChartBlock(title: L("Effect of ±%@ on %@", pct, outName), spec: spec,59                               summary: summary(r, outName: outName, locale: input.locale), height: Double(min(max(shown.count, 3), 15)) * 26 + 90)60        var blocks: [ResultBlock] = [.metrics([Metric(L("Base output"), r.baseOutput, outFormat), Metric(L("Inputs"), Double(r.bars.count), .integer)])]61        if let top = r.bars.first, top.swing.isFinite, top.swing > 0 {62            blocks.append(.interpretation(L("%@ is most sensitive to %@: ±%@ on it moves the output between %@ and %@.", outName, top.input, pct,63                                            Fmt.text(.number(min(top.outputAtLow, top.outputAtHigh)), outFormat, locale: input.locale),64                                            Fmt.text(.number(max(top.outputAtLow, top.outputAtHigh)), outFormat, locale: input.locale))))65        }66        blocks.append(.chart(chart))67        blocks.append(.table(table))68        blocks.append(.note([L("One input changes at a time; all others stay at their base values."),69                             L("Output: %@ — %@", WhatIfSupport.label(output, in: wb), WhatIfSupport.formulaText(output, in: wb) ?? L("constant (no formula)"))]))70        return ToolResult(title: L("Sensitivity (Tornado)"), subtitle: L("%@, each input ±%@", WhatIfSupport.nameWithAddress(output, in: wb), pct),71                          blocks: blocks, sheetName: L("Sensitivity"))72    }7374    static func summary(_ r: TornadoResult, outName: String, locale: Locale) -> String {75        let top = r.bars.prefix(3).map { L("%@ (swing %@)", $0.input, Fmt.number($0.swing, locale: locale)) }.joined(separator: ", ")76        return L("Tornado chart of %d inputs for %@, largest effects first: %@.", r.bars.count, outName, top)77    }78}79
Files main
Menu

Theme