Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/FeatureAnalysis/Tests/FinanceStudioTests/PlanningAndPortfolioTests.swift10// Module : FinanceStudioTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90import SheetUI91import Statistics92import FinanceModels93import AnalysisKit94@testable import FinanceStudio9596@MainActor @Suite struct RatioTests {97 static let english = ["Revenue", "Cost of goods sold", "Operating income (EBIT)", "Interest expense", "Pre-tax income", "Net income", "Depreciation & amortization",98 "Cash", "Marketable securities", "Accounts receivable", "Inventory", "Total current assets", "Total assets", "Accounts payable",99 "Total current liabilities", "Total debt", "Total liabilities", "Shareholders' equity"]100 static let french = ["Chiffre d’affaires", "Coût des marchandises vendues", "Résultat d’exploitation (BAII)", "Charges d’intérêts", "Bénéfice avant impôts",101 "Bénéfice net", "Amortissement", "Trésorerie", "Titres négociables", "Comptes clients", "Stocks", "Total de l’actif à court terme",102 "Total de l’actif", "Fournisseurs", "Total du passif à court terme", "Dette totale", "Total du passif", "Capitaux propres"]103 static let ids = ["revenue", "costOfGoodsSold", "operatingIncome", "interestExpense", "pretaxIncome", "netIncome", "depreciationAmortization", "cash",104 "marketableSecurities", "receivables", "inventory", "currentAssets", "totalAssets", "payables", "currentLiabilities", "totalDebt",105 "totalLiabilities", "equity"]106 static let fy25 = [5_240_000.0, 3_196_000, 786_000, 92_000, 694_000, 510_000, 214_000, 412_000, 120_000, 655_000, 708_000, 1_960_000, 4_870_000, 468_000,107 1_045_000, 1_520_000, 2_790_000, 2_080_000]108 static let fy24 = [4_810_000.0, 2_982_000, 673_000, 98_000, 575_000, 423_000, 201_000, 355_000, 90_000, 610_000, 690_000, 1_805_000, 4_590_000, 441_000,109 1_010_000, 1_610_000, 2_840_000, 1_750_000]110111 @Test func labelsAreMatchedInEnglishAndFrench() {112 for labels in [Self.english, Self.french] {113 let m = RatioMapping.autoMatch(labels: labels)114 for (i, id) in Self.ids.enumerated() { #expect(m[id] == i, "\(id) in \(labels[i])") }115 }116 // shuffled order and extra lines117 let shuffled = ["Notes", "Total assets", "Total current assets", "Cash flow from operations", "Cash and cash equivalents", "EBITDA", "EBIT"]118 let m = RatioMapping.autoMatch(labels: shuffled)119 #expect(m["totalAssets"] == 1 && m["currentAssets"] == 2 && m["cash"] == 4 && m["operatingIncome"] == 6 && m["revenue"] == nil)120 // explicit choices override, −1 clears121 let r = RatioMapping.resolve(labels: shuffled, explicit: ["cash": 3, "totalAssets": -1])122 #expect(r["cash"] == 3 && r["totalAssets"] == nil && r["currentAssets"] == 2)123 #expect(RatioMapping.decode(RatioMapping.encode(["a": 1, "b": -1])) == ["a": 1, "b": -1])124 }125126 func session(_ labels: [String]) -> WorkbookSession {127 makeSession([["Line", "FY2025", "FY2024"]] + labels.indices.map { [labels[$0], String(Self.fy25[$0]), String(Self.fy24[$0])] })128 }129130 @Test func ratiosMatchTheEngineWithAndWithoutAPriorPeriod() throws {131 let s = session(Self.french)132 let rows = RatioMapping.autoMatch(labels: Self.french)133 let cur = RatioMapping.statement(rows, values: Self.fy25), prior = RatioMapping.statement(rows, values: Self.fy24)134 #expect(cur.revenue == 5_240_000 && cur.equity == 2_080_000 && prior.totalDebt == 1_610_000)135 let with = try run(RatiosTool.descriptor, session: s) { v in v.set("data", range: "A1:C19"); v.set("labels", column: 0); v.set("current", column: 1); v.set("prior", column: 2) }136 let e = FinancialRatios.compute(cur, prior: prior)137 #expect(close(with.number("Return on equity"), e["Return on equity"] ?? .nan) && close(with.number("Current ratio"), e["Current ratio"] ?? .nan))138 #expect(close(with.number("Cash conversion cycle"), e["Cash conversion cycle"] ?? .nan) && close(with.number("DuPont ROE (5-step)"), e["DuPont ROE (5-step)"] ?? .nan))139 #expect(with.warnings.isEmpty)140 let without = try run(RatiosTool.descriptor, session: s) { v in v.set("data", range: "A1:C19"); v.set("labels", column: 0); v.set("current", column: 1) }141 #expect(close(without.number("Return on equity"), FinancialRatios.compute(cur)["Return on equity"] ?? .nan))142 #expect(abs((without.number("Return on equity") ?? 0) - (with.number("Return on equity") ?? 0)) > 1e-4)143 }144145 @Test func unmatchedLinesAreFlaggedAndManualMappingWorks() throws {146 var labels = Self.english147 labels[0] = "Top line"148 let s = session(labels)149 let auto = try run(RatiosTool.descriptor, session: s) { v in v.set("data", range: "A1:C19"); v.set("labels", column: 0); v.set("current", column: 1) }150 #expect(auto.warnings.first?.contains("Revenue") == true)151 let fixed = try run(RatiosTool.descriptor, session: s) { v in152 v.set("data", range: "A1:C19"); v.set("labels", column: 0); v.set("current", column: 1); v.set("mapping", text: "revenue=0")153 }154 #expect(fixed.warnings.isEmpty && close(fixed.number("Net margin"), 510_000 / 5_240_000))155 }156}157158@MainActor @Suite struct ModelStyleTests {159 @Test func markInputAndOutputAreUndoableAndDetected() {160 let s = makeSession([["Price", "48"], ["Units", "12000"], ["Revenue", "=B1*B2"]])161 let before = snapshot(s.workbook)162 s.select(RangeRef(a1: "B1:B2")!)163 ModelStyle.markSelection(s, asInput: true)164 #expect(s.engine.undoLabel == "Mark as Input")165 s.select(RangeRef(a1: "B3")!)166 ModelStyle.markSelection(s, asInput: false)167 let sh = s.workbook.sheets[0]168 #expect(ModelStyle.isInput(s.workbook.styles[sh.styleID(0, 1)]) && s.workbook.styles[sh.styleID(2, 1)].bold)169 #expect(!ModelStyle.isInput(s.workbook.styles[sh.styleID(2, 1)]))170 let cells = ModelStyle.assumptionCells(workbook: s.workbook, sheet: 0)171 #expect(cells.map(\.cell) == [CellRef(0, 1), CellRef(1, 1)])172 #expect(cells.map(\.label) == ["Price", "Units"] && cells[1].value == .number(12000) && !cells[0].isFormula)173 s.undo(); s.undo()174 #expect(snapshot(s.workbook) == before && ModelStyle.assumptionCells(workbook: s.workbook, sheet: 0).isEmpty)175 }176177 @Test func workspaceActionsAreRegistrable() {178 let ids = FinanceTools.workspaceActions.map(\.id)179 #expect(ids == ["analysis.mark-input", "analysis.mark-output"])180 #expect(Set(FinanceTools.all.map(\.id)).count == FinanceTools.all.count && FinanceTools.all.count == 12)181 #expect(FinanceTools.all.filter { $0.customPanel == nil }.allSatisfy { !$0.conventions.isEmpty })182 }183}184185@MainActor @Suite struct PortfolioAndPlanningTests {186 static func prices() -> [[String]] {187 var rng = Xoshiro256StarStar(seed: 9)188 var a = 100.0, b = 50.0, m = 1000.0189 var rows = [["Date", "A", "B", "Index"]]190 for i in 0..<120 {191 let x = 0.0004 + 0.01 * rng.nextNormal()192 m *= exp(x); a *= exp(0.0025 + 1.2 * x + 0.008 * rng.nextNormal()); b *= exp(0.0002 + 0.5 * x + 0.006 * rng.nextNormal())193 rows.append([String(45000 + i), String(a), String(b), String(m)])194 }195 return rows196 }197198 @Test func portfolioMeasuresMatchTheEngine() throws {199 let s = makeSession(Self.prices())200 let r = try run(PortfolioTool.descriptor, session: s) { v in201 v.set("data", range: "A1:D121"); v.set("assets", columns: [0, 1, 2]); v.set("benchmark", column: 3); v.set("ppy", number: 252); v.set("rf", number: 0.0252)202 }203 let sh = s.workbook.sheets[0]204 func col(_ c: Int) -> [Double] { (1...120).compactMap { sh.value($0, c).numberValue } }205 let ra = PortfolioAnalytics.returns(prices: col(1)), rm = PortfolioAnalytics.returns(prices: col(3))206 let rf = 0.0252 / 252207 #expect(close(r.number("Annualized return (252 periods/year)"), PortfolioAnalytics.annualizedReturn(ra, periodsPerYear: 252)))208 #expect(close(r.number("Annualized volatility (252 periods/year)"), PortfolioAnalytics.annualizedVolatility(ra, periodsPerYear: 252)))209 #expect(close(r.number("Sharpe ratio (annualized)"), PortfolioAnalytics.sharpeRatio(ra, riskFreePerPeriod: rf, periodsPerYear: 252)))210 #expect(close(r.number("Maximum drawdown"), PortfolioAnalytics.maxDrawdown(returns: ra)?.maxDrawdown ?? .nan))211 #expect(close(r.number("Historical VaR (95.0%, one period)"), try PortfolioAnalytics.valueAtRisk(ra, confidence: 0.95).valueAtRisk))212 #expect(close(r.number("Parametric CVaR (normal)"), try PortfolioAnalytics.valueAtRisk(ra, confidence: 0.95, method: .parametricNormal).conditionalVaR))213 let b = try PortfolioAnalytics.benchmarkStatistics(ra, benchmark: rm, riskFreePerPeriod: rf, periodsPerYear: 252)214 #expect(close(r.number("Beta"), b.beta) && close(r.number("Alpha (annualized)"), b.alphaAnnualized) && close(r.number("Tracking error (annualized)"), b.trackingError))215 // the date column was left out, and said so216 let perf = try #require(r.table(titled: "Performance and risk"))217 #expect(perf.rows[0] == [.text("Measure"), .text("A"), .text("B"), .text("Index")])218 // frontier: long-only weights are valid219 let f = try #require(r.table(titled: "Frontier portfolios"))220 for row in f.rows.dropFirst() {221 let w = row.suffix(2).compactMap(\.numberValue)222 #expect(abs(w.reduce(0, +) - 1) < 1e-8 && w.allSatisfy { $0 > -1e-9 })223 }224 let rb = PortfolioAnalytics.returns(prices: col(2))225 let mv = try MeanVariance.longOnlyMinimumVariance(expectedReturns: [Descriptive.mean(ra), Descriptive.mean(rb)], covariance: try PortfolioAnalytics.covarianceMatrix([ra, rb]))226 #expect(close(r.number("Minimum variance", column: 2), mv.volatility * 252.0.squareRoot(), 1e-7))227 }228229 @Test func returnsInputAndUnconstrainedFrontier() throws {230 let rows = Self.prices()231 let sh0 = makeSession(rows).workbook.sheets[0]232 func col(_ c: Int) -> [Double] { (1...120).compactMap { sh0.value($0, c).numberValue } }233 let ra = PortfolioAnalytics.returns(prices: col(1), kind: .log), rb = PortfolioAnalytics.returns(prices: col(2), kind: .log)234 let s = makeSession([["A", "B"]] + ra.indices.map { [String(ra[$0]), String(rb[$0])] })235 let r = try run(PortfolioTool.descriptor, session: s) { v in236 v.set("data", range: "A1:B120"); v.set("input", choice: "returns"); v.set("kind", choice: "log"); v.set("assets", columns: [0, 1])237 v.set("frontier", choice: "unconstrained"); v.set("ppy", number: 252)238 }239 #expect(close(r.number("Annualized return (252 periods/year)"), PortfolioAnalytics.annualizedReturn(ra, periodsPerYear: 252, kind: .log), 1e-7))240 #expect(r.table(titled: "Frontier portfolios") != nil && r.charts.count == 1)241 }242243 @Test func breakEvenMatchesTheClosedForm() throws {244 let s = makeSession()245 let r = try run(BreakEvenTool.descriptor, session: s)246 #expect(close(r.number("Break-even volume (units)"), 150_000 / 19.0) && close(r.number("Units for the target profit"), 200_000 / 19.0))247 #expect(close(r.number("Margin of safety"), (12_000 - 150_000 / 19.0) / 12_000))248 let none = try run(BreakEvenTool.descriptor, session: s) { $0.set("variable", number: 60) }249 #expect(none.warnings.count == 1 && none.charts.isEmpty)250 }251252 @Test func forecastsMatchTheEngineAndCanBeAppended() throws {253 let y = (0..<36).map { 100 + 2.5 * Double($0) + 8 * sin(Double($0) * .pi / 6) }254 let s = makeSession([["Sales"]] + y.map { [String($0)] })255 let before = snapshot(s.workbook)256 let trend = try run(ForecastingTool.descriptor, session: s) { v in v.set("data", range: "A1:A37"); v.set("series", column: 0); v.set("horizon", int: 6) }257 let t = try Forecasting.linearTrend(y)258 #expect(close(trend.number("Slope per period"), t.slope) && close(trend.number("CAGR per period"), Forecasting.cagr(y) ?? .nan))259 let table = try #require(trend.table(titled: "Forecasts"))260 #expect(table.rows.count == 7 && close(table.rows[6][1].numberValue, t.forecast(steps: 6)))261 #expect(close(table.rows[1][2].numberValue, t.predictionInterval(steps: 1).lower))262 let hw = try run(ForecastingTool.descriptor, session: s) { v in263 v.set("data", range: "A1:A37"); v.set("series", column: 0); v.set("method", choice: "hw"); v.set("season", int: 12); v.set("horizon", int: 12)264 }265 let fit = try Forecasting.holtWinters(y, seasonLength: 12)266 #expect(close(hw.table(titled: "Forecasts")?.rows[12][1].numberValue, fit.forecast(12)[11], 1e-9))267 _ = try hw.action("append-forecasts")?.perform(s)268 #expect(close(s.workbook.sheets[0].value(37, 0).numberValue, fit.forecast(12)[0], 1e-9) && s.workbook.sheets[0].value(48, 0).isNumber)269 #expect(throws: ToolError.self) { _ = try hw.action("append-forecasts")?.perform(s) } // cells are now busy270 s.undo()271 #expect(snapshot(s.workbook) == before)272 }273274 @Test func percentListsAndDates() {275 #expect(Fin.percentList("5", years: 3, locale: en) == [0.05, 0.05, 0.05])276 #expect(Fin.percentList("5%, 4.5 %, 4", years: 3, locale: en) == [0.05, 0.045, 0.04])277 #expect(Fin.percentList("5; 4,5", years: 2, locale: Locale(identifier: "fr_CA")) == [0.05, 0.045])278 #expect(Fin.percentList("5, 4", years: 3, locale: en) == nil && Fin.percentList("abc", years: 1, locale: en) == nil)279 #expect(Fin.calendarDate(Fin.date(2026, 3, 15)) == CalendarDate(2026, 3, 15))280 }281}282