SPB Git

Repository Public

ForecastingTool.swift 9.1 KB 124 lines swift

1import Foundation2import SheetCore3import SheetUI4import SheetCharts5import Statistics6import FinanceModels7import AnalysisKit89/// Growth rate and simple forecasts of a series.10enum ForecastingTool {11    static let descriptor: ToolDescriptor = {12        let seasonal: @Sendable (FormValues) -> Bool = { ["hw", "hwm", "snaive"].contains($0.choice("method")) }13        var t = ToolDescriptor(14            id: "finance.forecast", title: L("Forecasting Helpers"), group: FinGroup.planning, systemImage: "chart.line.flattrend.xyaxis",15            summary: L("Compound annual growth rate of a series and forecasts by linear trend, Holt–Winters, seasonal naive or drift."),16            fields: [17                .range("data", L("Data range")),18                .column("series", L("Series"), help: L("Observations in time order, one per period.")),19                .choice("method", L("Method"), [FieldOption("trend", L("Linear trend")), FieldOption("hw", L("Holt–Winters (additive seasonality)")),20                                                FieldOption("hwm", L("Holt–Winters (multiplicative seasonality)")), FieldOption("snaive", L("Seasonal naive")),21                                                FieldOption("drift", L("Drift"))]),22                .integer("season", L("Season length"), 12, min: 2, max: 366, help: L("Periods per seasonal cycle (12 for monthly data)."), visible: seasonal),23                .integer("horizon", L("Forecast horizon"), 12, min: 1, max: 600, help: L("Number of periods ahead.")),24                .percent("level", L("Interval confidence level"), 0.95, min: 0.5, max: 0.999, visible: { ["trend", "hw"].contains($0.choice("method")) }),25            ],26            conventions: [27                L("CAGR = (last / first)^(1/(n − 1)) − 1 per period; it is not defined when the first or last value is not positive."),28                L("Linear trend: ordinary least squares of y on t = 1…n; forecast ŷ(n+h) = a + b(n+h); prediction interval ŷ ± t(1−α/2; n−2)·s·√(1 + 1/n + (t₀ − t̄)²/S_tt), assuming independent normal errors."),29                L("Holt–Winters: additive trend with additive or multiplicative seasonality; smoothing parameters minimise the in-sample sum of squared errors; needs two full seasons. Intervals exist only for additive seasonality."),30                L("Seasonal naive: ŷ(n+h) = the value one season earlier. Drift: ŷ(n+h) = y_n + h·(y_n − y_1)/(n − 1)."),31                L("Missing values are dropped before fitting; observations are assumed equally spaced."),32            ],33            runTitle: L("Forecast"),34            run: run)35        t.demo = { _, v in v.set("data", range: "Series!A1:B85"); v.set("series", column: 1); v.set("method", choice: "hw") }36        return t37    }()3839    static func run(_ input: ToolInput) throws -> ToolResult {40        let frame = try input.frame(), v = input.values, loc = input.locale41        let column = try input.column("series")42        let y = column.present43        try Fin.require(y.count >= 3, L("At least three observations are needed."))44        let h = max(v.int("horizon"), 1), m = max(v.int("season"), 2), level = v.number("level")45        let method = v.choice("method")46        var forecast = [Double](), lower: [Double]?, upper: [Double]?, fitted: [Double]?47        var details: [(String, Value, CellFormat)] = []48        var notes = [String]()49        let methodName: String50        switch method {51        case "hw", "hwm":52            let fit = try Forecasting.holtWinters(y, seasonLength: m, multiplicative: method == "hwm")53            forecast = fit.forecast(h)54            fitted = fit.fitted55            if let iv = fit.forecastInterval(h, confidence: level) { lower = iv.lower; upper = iv.upper }56            else { notes.append(L("No prediction interval: closed formulas exist only for additive seasonality.")) }57            methodName = method == "hwm" ? L("Holt–Winters (multiplicative seasonality)") : L("Holt–Winters (additive seasonality)")58            details = [(L("Level smoothing (α)"), .number(fit.parameters.alpha), .fixed(4)), (L("Trend smoothing (β)"), .finite(fit.parameters.beta), .fixed(4)),59                       (L("Seasonal smoothing (γ)"), .finite(fit.parameters.gamma), .fixed(4)), (L("Root mean squared error"), .number(fit.rmse), .auto),60                       (L("Mean absolute percentage error"), .number(fit.mape / 100), .percent(2))]61            if !fit.converged { notes.append(L("The parameter search did not fully converge; check the fit.")) }62        case "snaive":63            forecast = try Forecasting.seasonalNaive(y, seasonLength: m, horizon: h)64            methodName = L("Seasonal naive")65        case "drift":66            forecast = try Forecasting.drift(y, horizon: h)67            methodName = L("Drift")68        default:69            let t = try Forecasting.linearTrend(y)70            forecast = t.forecasts(horizon: h)71            fitted = t.fitted72            let iv = (1...h).map { t.predictionInterval(steps: $0, confidence: level) }73            lower = iv.map(\.lower); upper = iv.map(\.upper)74            methodName = L("Linear trend")75            details = [(L("Slope per period"), .number(t.slope), .auto), (L("Intercept"), .number(t.intercept), .auto), (L("R²"), .number(t.rSquared), .fixed(4)),76                       (L("Residual standard error"), .number(t.standardError), .auto)]77        }78        let n = y.count79        let cagr = Forecasting.cagr(y)80        var metrics = [Metric(L("Observations"), Double(n), .integer)]81        metrics.append(cagr.map { Metric(L("CAGR per period"), $0, .percent(3)) } ?? Metric(L("CAGR per period"), text: "—"))82        metrics.append(Metric(L("Forecast, period n + %d", h), forecast.last ?? .nan))83        var items: [(String, Value, CellFormat)] = [(L("Method"), .text(methodName), .auto), (L("First value"), .number(y[0]), .auto), (L("Last value"), .number(y[n - 1]), .auto),84                                                    (L("CAGR per period"), .finite(cagr), .percent(4))]85        items += details86        var rows: [[Value]] = [[.text(L("Period")), .text(L("Forecast"))] + (lower != nil ? [.text(L("Lower bound")), .text(L("Upper bound"))] : [])]87        for k in 0..<h { rows.append([.number(Double(n + k + 1)), .finite(forecast[k])] + (lower != nil ? [.finite(lower?[k]), .finite(upper?[k])] : [])) }88        var table = ReportTable(title: L("Forecasts"), rows: rows, headerColumn: false, columnFormats: [0: .integer])89        if lower != nil { table.note = L("Bounds of the %@ prediction interval.", Fmt.percent(level, 0, locale: loc)) }9091        let xs = (1...n).map(Double.init), xf = (1...h).map { Double(n + $0) }92        var lines: [ChartKit.Line] = [.init(column.name, y, x: xs)]93        if let fitted, fitted.count == n { lines.append(.init(L("Fitted"), fitted, x: xs, dash: .dot)) }94        lines.append(.init(L("Forecast"), forecast, x: xf, lower: lower, upper: upper, dash: .dash))95        var spec = ChartKit.lines(lines, xTitle: L("Period"), yTitle: column.name)96        Fin.mark(&spec, vertical: Double(n) + 0.5)97        let chart = ChartBlock(title: L("History and forecast"), spec: spec,98                               summary: L("%d observations of %@ followed by %d forecast periods by %@; the last forecast is %@.", n, column.name, h, methodName,99                                          Fmt.number(forecast.last ?? .nan, locale: loc)), height: 260)100        if cagr == nil { notes.append(L("CAGR is not defined: the first and last values must be positive.")) }101        var blocks: [ResultBlock] = [.metrics(metrics), .chart(chart), .table(.keyValue(title: L("Model"), items)), .table(table)]102        if !notes.isEmpty { blocks.append(.note(notes)) }103104        let target = CellRef(frame.range.r1 + 1, column.sheetColumn), sheet = frame.sheet105        let values = forecast106        let action = ResultAction(id: "append-forecasts", title: L("Append Forecasts Below the Series"), systemImage: "arrow.down.to.line",107                                  help: L("Writes the forecasts as values under the last observation (italic). One step to undo.")) { session in108            guard sheet < session.workbook.sheets.count else { throw ToolError(L("The sheet of the series no longer exists.")) }109            let sh = session.workbook.sheets[sheet]110            let rg = RangeRef(r0: target.row, c0: target.col, r1: target.row + values.count - 1, c1: target.col)111            var busy = false112            sh.cells.forEach(in: rg) { _, _, c in if c.hasContent { busy = true } }113            if busy { throw ToolError(L("The cells below the series are not empty; nothing was written.")) }114            session.perform(L("Append Forecasts")) { e in115                for (k, x) in values.enumerated() where x.isFinite { e.set(sheet: sheet, row: target.row + k, col: target.col, .value(.number(x))) }116                e.setStyle(sheet: sheet, range: rg) { $0.italic = true }117            }118            session.select(rg, sheet: sheet)119            return L("%d forecasts written to %@", values.count, rg.a1)120        }121        return ToolResult(title: L("Forecast"), subtitle: L("%@ — %@, %d periods ahead", column.name, methodName, h), blocks: blocks, actions: [action], sheetName: L("Forecast"))122    }123}124
Files main
Menu

Theme