SPB Git

Repository Public

PortfolioTool.swift 14.8 KB 191 lines swift

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
Files main
Menu

Theme