Repository Public
1import Foundation2import SheetCore3import SheetCharts4import Statistics5import FinanceModels6import AnalysisKit78/// Risk and performance of assets from a prices or returns range, with the mean–variance frontier.9enum PortfolioTool {10 static let descriptor: ToolDescriptor = {11 var t = ToolDescriptor(12 id: "finance.portfolio", title: L("Portfolio Analytics"), group: FinGroup.portfolio, systemImage: "chart.pie",13 summary: L("Annualized return and volatility, Sharpe and Sortino ratios, drawdown, value at risk, correlations, benchmark statistics and the efficient frontier of a set of assets."),14 fields: [15 .range("data", L("Prices or returns range")),16 .choice("input", L("The range contains"), [FieldOption("prices", L("Prices")), FieldOption("returns", L("Returns"))]),17 .choice("kind", L("Return type"), [FieldOption("simple", L("Simple")), FieldOption("log", L("Logarithmic"))]),18 .columns("assets", L("Assets")),19 .column("benchmark", L("Benchmark (optional)"), optional: true),20 .number("ppy", L("Periods per year"), 252, min: 1, max: 366, help: L("252 for daily, 52 for weekly, 12 for monthly data.")),21 .percent("rf", L("Risk-free rate (per year)"), 0.03),22 .percent("confidence", L("VaR confidence level"), 0.95, min: 0.5, max: 0.9999),23 .choice("frontier", L("Efficient frontier"), [FieldOption("longOnly", L("Long-only (no short sales)")), FieldOption("unconstrained", L("Short sales allowed"))]),24 ],25 conventions: [26 L("Returns: simple P_t/P_(t−1) − 1 or logarithmic ln(P_t/P_(t−1)). Rows with a missing value in any chosen column are dropped."),27 L("Annualized return is geometric: (Π(1+r))^(periods per year / n) − 1; annualized volatility = sample standard deviation (n − 1) × √(periods per year)."),28 L("The risk-free rate per period is the annual rate divided by the periods per year; it is also the minimum acceptable return of the Sortino ratio."),29 L("Sharpe = mean(r − r_f)/sd(r − r_f) × √(periods per year). Sortino uses the downside deviation √(Σ min(r − MAR, 0)² / n) over all n observations."),30 L("Maximum drawdown: largest (peak − value)/peak of the wealth index, reported as a positive fraction."),31 L("Historical VaR = −(1 − c) quantile of the returns (linear interpolation, Excel PERCENTILE.INC); CVaR = −mean of the returns at or below it. Parametric VaR and CVaR assume normal returns. Positive numbers are losses over one period."),32 L("Beta = cov(r − r_f, m − r_f)/var(m − r_f); Jensen's alpha per period is annualized by × periods per year; tracking error = sd(r − m)·√(periods per year)."),33 L("Mean–variance frontier: fully invested portfolios (weights sum to 1) from sample means and the sample covariance matrix. The long-only maximum-Sharpe portfolio is not provided (no validated solver)."),34 ],35 runTitle: L("Analyze"),36 run: run)37 t.demo = { _, v in v.set("data", range: "Prices!A1:F253"); v.set("assets", columns: [1, 2, 3, 4]); v.set("benchmark", column: 5) }38 return t39 }()4041 /// Strictly increasing whole numbers in the range of spreadsheet date serials (1955–2100).42 static func looksLikeDates(_ c: DataColumn) -> Bool {43 let x = c.present44 guard x.count >= 3, x.allSatisfy({ $0 == $0.rounded() && $0 > 20_000 && $0 < 73_100 }) else { return false }45 return zip(x, x.dropFirst()).allSatisfy { $0 < $1 }46 }4748 struct Asset: Sendable {49 var name: String50 var returns: [Double]51 }5253 /// Aligned return series of the chosen columns (benchmark last when present).54 static func returns(columns: [DataColumn], prices: Bool, kind: ReturnKind) throws -> [Asset] {55 let (_, cols) = DataFrame.listwise(columns.map(\.numbers))56 return try zip(columns, cols).map { c, x in57 if prices {58 try Fin.require(x.count >= 3, L("At least three complete rows of prices are needed."))59 try Fin.require(x.allSatisfy { $0 > 0 }, L("Prices must be positive (column “%@”).", c.name))60 return Asset(name: c.name, returns: PortfolioAnalytics.returns(prices: x, kind: kind))61 }62 try Fin.require(x.count >= 3, L("At least three complete rows of returns are needed."))63 return Asset(name: c.name, returns: x)64 }65 }6667 static func run(_ input: ToolInput) throws -> ToolResult {68 let frame = try input.frame(), v = input.values, loc = input.locale69 let picked = try input.columns("assets")70 // a column of spreadsheet dates is not an asset: leave it out (and say so)71 let assetColumns = picked.filter { !looksLikeDates($0) }72 try Fin.require(!assetColumns.isEmpty, L("Choose at least one asset column."))73 let dropped = picked.filter(looksLikeDates).map(\.name)74 let benchmarkColumn = input.optionalColumn("benchmark").flatMap { b in assetColumns.contains { $0.index == b.index } ? nil : b }75 let kind: ReturnKind = v.choice("kind") == "log" ? .log : .simple76 let ppy = max(v.number("ppy"), 1), rf = v.number("rf") / ppy, conf = v.number("confidence")77 let all = try returns(columns: assetColumns + (benchmarkColumn.map { [$0] } ?? []), prices: v.choice("input") != "returns", kind: kind)78 let assets = Array(all.prefix(assetColumns.count))79 let bench = benchmarkColumn == nil ? nil : all.last80 let n = assets[0].returns.count81 let ppyText = Fmt.number(ppy, locale: loc)8283 typealias Row = (String, (Asset) throws -> Double?, CellFormat)84 let rows: [Row] = [85 (L("Observations (returns)"), { Double($0.returns.count) }, .integer),86 (L("Mean return per period"), { Descriptive.mean($0.returns) }, .percent(4)),87 (L("Annualized return (%@ periods/year)", ppyText), { PortfolioAnalytics.annualizedReturn($0.returns, periodsPerYear: ppy, kind: kind) }, .percent(2)),88 (L("Annualized volatility (%@ periods/year)", ppyText), { PortfolioAnalytics.annualizedVolatility($0.returns, periodsPerYear: ppy) }, .percent(2)),89 (L("Sharpe ratio (annualized)"), { PortfolioAnalytics.sharpeRatio($0.returns, riskFreePerPeriod: rf, periodsPerYear: ppy) }, .fixed(3)),90 (L("Sortino ratio (annualized)"), { PortfolioAnalytics.sortinoRatio($0.returns, minimumAcceptableReturn: rf, periodsPerYear: ppy) }, .fixed(3)),91 (L("Maximum drawdown"), { a in PortfolioAnalytics.maxDrawdown(returns: kind == .log ? a.returns.map { expm1($0) } : a.returns)?.maxDrawdown }, .percent(2)),92 (L("Historical VaR (%@, one period)", Fmt.percent(conf, 1, locale: loc)), { try PortfolioAnalytics.valueAtRisk($0.returns, confidence: conf, method: .historical).valueAtRisk }, .percent(3)),93 (L("Historical CVaR (%@, one period)", Fmt.percent(conf, 1, locale: loc)), { try PortfolioAnalytics.valueAtRisk($0.returns, confidence: conf, method: .historical).conditionalVaR }, .percent(3)),94 (L("Parametric VaR (normal)"), { try PortfolioAnalytics.valueAtRisk($0.returns, confidence: conf, method: .parametricNormal).valueAtRisk }, .percent(3)),95 (L("Parametric CVaR (normal)"), { try PortfolioAnalytics.valueAtRisk($0.returns, confidence: conf, method: .parametricNormal).conditionalVaR }, .percent(3)),96 ]97 var perf = ReportTable(title: L("Performance and risk"), rows: [])98 var grid: [[Value]] = [[.text(L("Measure"))] + all.map { .text($0.name) }]99 for (i, r) in rows.enumerated() { grid.append([.text(r.0)] + (try all.map { .finite(try r.1($0)) })); perf.rowFormats[i + 1] = r.2 }100 perf.rows = grid101 perf.note = L("VaR and CVaR are positive losses over one period. Risk-free rate per period: %@.", Fmt.percent(rf, 4, locale: loc))102 var blocks: [ResultBlock] = [.table(perf)]103 if !dropped.isEmpty { blocks.append(.note([L("Left out because it contains dates, not prices: %@.", dropped.joined(separator: ", "))])) }104105 if all.count >= 2 {106 let corr = try PortfolioAnalytics.correlationMatrix(all.map(\.returns))107 var t = ReportTable(title: L("Correlation of returns"), rows: Report.labelled(rows: all.map(\.name), cols: all.map(\.name)) { corr[$0, $1] })108 for c in 1...all.count { t.columnFormats[c] = .fixed(3) }109 blocks.append(.table(t))110 }111 if let bench {112 var t = ReportTable(title: L("Against the benchmark (%@)", bench.name), rows: [])113 let stats = try assets.map { try PortfolioAnalytics.benchmarkStatistics($0.returns, benchmark: bench.returns, riskFreePerPeriod: rf, periodsPerYear: ppy) }114 let lines: [(String, (BenchmarkStatistics) -> Double, CellFormat)] = [115 (L("Beta"), { $0.beta }, .fixed(4)), (L("Alpha (annualized)"), { $0.alphaAnnualized }, .percent(2)), (L("Correlation"), { $0.correlation }, .fixed(4)),116 (L("R²"), { $0.rSquared }, .fixed(4)), (L("Tracking error (annualized)"), { $0.trackingError }, .percent(2)),117 (L("Information ratio"), { $0.informationRatio }, .fixed(3)),118 ]119 var g: [[Value]] = [[.text(L("Measure"))] + assets.map { .text($0.name) }]120 for (i, l) in lines.enumerated() { g.append([.text(l.0)] + stats.map { .finite(l.1($0)) }); t.rowFormats[i + 1] = l.2 }121 t.rows = g122 blocks.append(.table(t))123 }124 if assets.count >= 2 {125 blocks.append(contentsOf: try frontierBlocks(assets, ppy: ppy, rf: rf, longOnly: v.choice("frontier") != "unconstrained", locale: loc))126 } else {127 blocks.append(.note([L("Choose at least two assets to compute the efficient frontier.")]))128 }129 return ToolResult(title: L("Portfolio Analytics"), subtitle: L("%@ — %d assets, %d returns, %@ periods per year", frame.label, assets.count, n, ppyText),130 blocks: blocks, sheetName: L("Portfolio"))131 }132133 static func frontierBlocks(_ assets: [Asset], ppy: Double, rf: Double, longOnly: Bool, locale: Locale) throws -> [ResultBlock] {134 let mu = assets.map { Descriptive.mean($0.returns) }135 let cov = try PortfolioAnalytics.covarianceMatrix(assets.map(\.returns))136 let names = assets.map(\.name)137 let f: EfficientFrontier138 do {139 f = longOnly ? try MeanVariance.longOnlyFrontier(expectedReturns: mu, covariance: cov, count: 25, assetNames: names)140 : try MeanVariance.frontier(expectedReturns: mu, covariance: cov, count: 25, assetNames: names)141 } catch {142 // degenerate case: no asset earns more than the minimum-variance portfolio, so the frontier is a single point143 guard let mv = try? (longOnly ? MeanVariance.longOnlyMinimumVariance(expectedReturns: mu, covariance: cov)144 : MeanVariance.minimumVariancePortfolio(expectedReturns: mu, covariance: cov)) else {145 return [.warning(L("The efficient frontier could not be computed: %@", ToolError.describe(error)))]146 }147 var rows: [[Value]] = [[.text(L("Portfolio")), .text(L("Expected return (annualized)")), .text(L("Volatility (annualized)"))] + names.map { .text(L("Weight %@", $0)) }]148 rows.append([.text(L("Minimum variance")), .number(mv.expectedReturn * ppy), .number(mv.volatility * ppy.squareRoot())] + mv.weights.map { .number($0) })149 var t = ReportTable(title: L("Frontier portfolios"), rows: rows)150 for c in 1..<(rows[0].count) { t.columnFormats[c] = .percent(2) }151 return [.table(t), .warning(L("The efficient frontier reduces to the minimum-variance portfolio: no asset has an expected return above it in this sample."))]152 }153 func annRet(_ r: Double) -> Double { r * ppy * 100 }154 func annVol(_ s: Double) -> Double { s * ppy.squareRoot() * 100 }155 var series: [(name: String, x: [Double], y: [Double])] = [156 (L("Efficient frontier"), f.points.map { annVol($0.volatility) }, f.points.map { annRet($0.expectedReturn) }),157 (L("Assets"), (0..<assets.count).map { annVol(cov[$0, $0].squareRoot()) }, mu.map(annRet)),158 (L("Minimum variance"), [annVol(f.minimumVariance.volatility)], [annRet(f.minimumVariance.expectedReturn)]),159 ]160 var notes = [longOnly ? L("Long-only frontier: weights are between 0 and 1 and sum to 1 (active-set quadratic programme).")161 : L("Unconstrained frontier: short sales are allowed; weights sum to 1 (closed form)."),162 L("Expected returns are the sample means × periods per year (arithmetic annualization); volatilities are × √(periods per year).")]163 var tangency: FrontierPoint?164 if !longOnly {165 if let t = try? MeanVariance.tangencyPortfolio(expectedReturns: mu, covariance: cov, riskFree: rf) {166 tangency = t167 series.append((L("Tangency portfolio"), [annVol(t.volatility)], [annRet(t.expectedReturn)]))168 } else {169 notes.append(L("No tangency portfolio: the risk-free rate is not below the return of the minimum-variance portfolio."))170 }171 } else {172 notes.append(L("The long-only maximum-Sharpe (tangency) portfolio is not provided; scan the frontier points instead."))173 }174 let spec = ChartKit.scatter(series, xTitle: L("Annualized volatility (%)"), yTitle: L("Annualized expected return (%)"))175 let chart = ChartBlock(title: L("Efficient frontier"), spec: spec,176 summary: L("Efficient frontier of %d portfolios; the minimum-variance portfolio has an annualized volatility of %@ %% and an expected return of %@ %%.",177 f.points.count, Fmt.fixed(annVol(f.minimumVariance.volatility), 2, locale: locale),178 Fmt.fixed(annRet(f.minimumVariance.expectedReturn), 2, locale: locale)), height: 260)179 var rows: [[Value]] = [[.text(L("Portfolio")), .text(L("Expected return (annualized)")), .text(L("Volatility (annualized)"))] + names.map { .text(L("Weight %@", $0)) }]180 func row(_ label: String, _ p: FrontierPoint) -> [Value] {181 [.text(label), .number(p.expectedReturn * ppy), .number(p.volatility * ppy.squareRoot())] + p.weights.map { .number($0) }182 }183 rows.append(row(L("Minimum variance"), f.minimumVariance))184 if let tangency { rows.append(row(L("Tangency portfolio"), tangency)) }185 for (i, p) in f.points.enumerated() { rows.append(row(L("Frontier %d", i + 1), p)) }186 var t = ReportTable(title: L("Frontier portfolios"), rows: rows)187 for c in 1..<(rows[0].count) { t.columnFormats[c] = .percent(2) }188 return [.chart(chart), .table(t), .note(notes)]189 }190}191