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