SPB Git

Repository Public

PublicAPITests.swift 16.8 KB 227 lines swift

1import Foundation2import Testing3import SheetCore4import FunctionsLookup56/// The Swift API reused by the Financial Modeling Studio.7@Suite struct PublicAPITests {8    func day(_ y: Int, _ m: Int, _ d: Int) -> CivilDate { CivilDate(year: y, month: m, day: d) }910    @Test func civilDates() {11        let x = CivilDate(serial: 45351)                              // 29 Feb 202412        #expect(x == day(2024, 2, 29))13        #expect(x.serial() == 45351)14        #expect(x.isEndOfMonth && x.isEndOfFebruary && x.isLeapYear)15        #expect(day(2024, 8, 31).adding(months: -6) == day(2024, 2, 29))16        #expect(day(2023, 8, 31).adding(months: -6) == day(2023, 2, 28))17        #expect(day(2024, 2, 29).adding(months: 6, endOfMonth: true) == day(2024, 8, 31))18        #expect(day(2024, 1, 30).adding(months: 1) == day(2024, 2, 29))19        #expect(day(2024, 11, 15).adding(months: 3) == day(2025, 2, 15))20        #expect(day(2024, 1, 15).adding(months: -13) == day(2022, 12, 15))21        #expect(day(2024, 3, 1) > day(2024, 2, 29))22        #expect(CivilDate(serial: 0, date1904: true) == day(1904, 1, 1))23    }2425    @Test func dayCounts30360() {26        // US (NASD) rules as Excel's YEARFRAC applies them27        #expect(DayCount.days30US(day(2008, 2, 15), day(2008, 5, 15)) == 90)28        #expect(DayCount.days30US(day(2021, 1, 31), day(2021, 3, 31)) == 60)        // both 31 → 3029        #expect(DayCount.days30US(day(2021, 1, 30), day(2021, 3, 31)) == 60)        // start 30, end 31 → 3030        #expect(DayCount.days30US(day(2021, 1, 29), day(2021, 3, 31)) == 62)        // end 31 stays 3131        #expect(DayCount.days30US(day(2021, 1, 31), day(2021, 2, 28)) == 28)        // only the start is adjusted32        #expect(DayCount.days30US(day(2021, 2, 28), day(2022, 2, 28)) == 360)       // end of February both sides33        #expect(DayCount.days30US(day(2020, 2, 29), day(2021, 2, 28)) == 360)34        #expect(DayCount.days30US(day(2019, 2, 28), day(2020, 1, 31)) == 331)       // end of Feb start: end 31 stays 3135        #expect(DayCount.days30US(day(2024, 2, 29), day(2024, 3, 31)) == 31)36        #expect(DayCount.days30US(day(2024, 2, 28), day(2024, 3, 31)) == 33)        // 28 Feb in a leap year is not month end37        // European rules: 31 → 30 on both sides, February untouched38        #expect(DayCount.days30EU(day(2021, 1, 31), day(2021, 3, 31)) == 60)39        #expect(DayCount.days30EU(day(2021, 1, 29), day(2021, 3, 31)) == 61)40        #expect(DayCount.days30EU(day(2021, 2, 28), day(2021, 3, 31)) == 32)41        #expect(DayCount.days30EU(day(2024, 2, 29), day(2024, 3, 31)) == 31)42        #expect(DayCount.days(from: day(2024, 2, 1), to: day(2024, 3, 1), basis: .actualActual) == 29)43        #expect(DayCount.days(from: day(2024, 2, 1), to: day(2024, 3, 1), basis: .us30360) == 30)44        #expect(DayCount.Basis(rawValue: 5) == nil)45        #expect(DayCount.Basis.allCases.count == 5)46    }4748    @Test func yearFractions() {49        func yf(_ a: CivilDate, _ b: CivilDate, _ basis: DayCount.Basis) -> Double { DayCount.yearFraction(from: a, to: b, basis: basis) }50        #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .us30360), 209.0 / 360))51        #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .actualActual), 211.0 / 366))52        #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .actual360), 211.0 / 360))53        #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .actual365), 211.0 / 365))54        #expect(near(yf(day(2012, 1, 1), day(2012, 7, 30), .european30360), 209.0 / 360))55        // order does not matter56        #expect(yf(day(2012, 7, 30), day(2012, 1, 1), .actualActual) == yf(day(2012, 1, 1), day(2012, 7, 30), .actualActual))57        // actual/actual, span of at most a year across a year end: 366 only when a 29 February is inside58        #expect(near(yf(day(2023, 12, 31), day(2024, 12, 31), .actualActual), 1))            // 366 / 36659        #expect(near(yf(day(2024, 2, 29), day(2025, 2, 28), .actualActual), 365.0 / 366))60        #expect(near(yf(day(2024, 3, 1), day(2025, 3, 1), .actualActual), 1))                // 365 / 36561        #expect(near(yf(day(2023, 3, 1), day(2024, 2, 29), .actualActual), 365.0 / 366))     // ends on the leap day62        #expect(near(yf(day(2022, 11, 1), day(2023, 2, 1), .actualActual), 92.0 / 365))63        // more than a year: average length of the calendar years touched64        #expect(near(yf(day(2011, 1, 1), day(2013, 1, 1), .actualActual), 731.0 / ((365.0 + 366 + 365) / 3)))65        #expect(near(yf(day(2008, 1, 1), day(2016, 1, 1), .actualActual), 2922.0 / (3288.0 / 9)))66        #expect(yf(day(2020, 5, 5), day(2020, 5, 5), .actualActual) == 0)67        #expect(near(DayCount.yearFraction(fromSerial: 39493, toSerial: 39583, basis: .actual360), 0.25))68        #expect(DayCount.yearLength(from: day(2021, 1, 1), to: day(2021, 6, 1), basis: .actual365) == 365)69    }7071    @Test func couponSchedules() {72        var p = DayCount.CouponPeriod(settlement: day(2011, 1, 25), maturity: day(2011, 11, 15), frequency: 2, basis: .actualActual)73        #expect(p.previous == day(2010, 11, 15) && p.next == day(2011, 5, 15) && p.remaining == 2)74        #expect(p.daysFromPrevious == 71 && p.daysInPeriod == 181 && p.daysToNext == 110)75        p = DayCount.CouponPeriod(settlement: day(2011, 1, 25), maturity: day(2011, 11, 15), frequency: 2, basis: .us30360)76        #expect(p.daysFromPrevious == 70 && p.daysInPeriod == 180 && p.daysToNext == 110)77        p = DayCount.CouponPeriod(settlement: day(2019, 3, 15), maturity: day(2021, 2, 28), frequency: 2, basis: .actual365)78        #expect(p.previous == day(2019, 2, 28) && p.next == day(2019, 8, 31) && p.remaining == 4)79        #expect(p.daysInPeriod == 182.5 && p.daysFromPrevious == 15 && p.daysToNext == 169)80        p = DayCount.CouponPeriod(settlement: day(1995, 6, 6), maturity: day(2045, 3, 1), frequency: 4, basis: .actual360)81        #expect(p.previous == day(1995, 6, 1) && p.next == day(1995, 9, 1) && p.remaining == 199)82        // the day before maturity is in the last period83        p = DayCount.CouponPeriod(settlement: day(2030, 12, 30), maturity: day(2030, 12, 31), frequency: 1, basis: .actualActual)84        #expect(p.previous == day(2029, 12, 31) && p.next == day(2030, 12, 31) && p.remaining == 1 && p.daysToNext == 1)85    }8687    @Test func cashFlowSolvers() {88        #expect(near(CashFlow.pmt(rate: 0.08 / 12, nper: 10, pv: 10000), -1037.0320893591606, 1e-12))89        #expect(near(CashFlow.fv(rate: 0.005, nper: 10, pmt: -200, pv: -500, type: 1), 2581.4033740601362, 1e-12))90        #expect(near(CashFlow.pv(rate: 0.08 / 12, nper: 240, pmt: 500), -59777.145851187764, 1e-12))91        #expect(near(CashFlow.nper(rate: 0.01, pmt: -100, pv: -1000, fv: 10000, type: 1)!, 59.67386567429457, 1e-12))92        #expect(CashFlow.nper(rate: 0.05, pmt: -10, pv: 1000) == nil)93        #expect(near(CashFlow.rate(nper: 48, pmt: -200, pv: 8000)!, 0.0077014724882014, 1e-11))94        #expect(CashFlow.rate(nper: 12, pmt: 100, pv: 1000) == nil)95        // tiny rates do not lose precision96        let tiny = CashFlow.pmt(rate: 1e-12, nper: 120, pv: 100_000)97        #expect(near(tiny, -100_000.0 / 120 * (1 + 60.5e-12), 1e-13))98        // identities99        let pay = CashFlow.pmt(rate: 0.004, nper: 360, pv: 300_000, fv: -20_000, type: 1)100        #expect(near(CashFlow.fv(rate: 0.004, nper: 360, pmt: pay, pv: 300_000, type: 1), -20_000, 1e-9))101        #expect(near(CashFlow.pv(rate: 0.004, nper: 360, pmt: pay, fv: -20_000, type: 1), 300_000, 1e-9))102        #expect(near(CashFlow.nper(rate: 0.004, pmt: pay, pv: 300_000, fv: -20_000, type: 1)!, 360, 1e-9))103        #expect(near(CashFlow.rate(nper: 360, pmt: pay, pv: 300_000, fv: -20_000, type: 1)!, 0.004, 1e-10))104        var interest = 0.0, principal = 0.0105        for per in 1...360 {106            interest += CashFlow.ipmt(rate: 0.004, per: Double(per), nper: 360, pv: 300_000)107            principal += CashFlow.ppmt(rate: 0.004, per: Double(per), nper: 360, pv: 300_000)108        }109        #expect(near(principal, -300_000, 1e-9))110        #expect(near(interest + principal, 360 * CashFlow.pmt(rate: 0.004, nper: 360, pv: 300_000), 1e-9))111112        let flows: [Double] = [-70000, 12000, 15000, 18000, 21000, 26000]113        let irr = CashFlow.irr(values: flows)!114        #expect(near(irr, 0.0866309480365315, 1e-12))115        #expect(abs(flows[0] + CashFlow.npv(rate: irr, values: Array(flows.dropFirst()))) < 1e-7)116        #expect(CashFlow.irr(values: [1, 2, 3]) == nil)117        #expect(near(CashFlow.mirr(values: [-120000, 39000, 30000, 21000, 37000, 46000], financeRate: 0.1, reinvestRate: 0.12)!, 0.1260941303659051, 1e-12))118        #expect(CashFlow.mirr(values: [1, 2], financeRate: 0.1, reinvestRate: 0.1) == nil)119        let values: [Double] = [-10000, 2750, 4250, 3250, 2750], dates: [Double] = [39448, 39508, 39751, 39859, 39904]120        #expect(near(CashFlow.xnpv(rate: 0.09, values: values, dates: dates), 2086.6476020315354, 1e-12))121        let xirr = CashFlow.xirr(values: values, dates: dates)!122        #expect(near(xirr, 0.3733625335188314, 1e-9))123        #expect(abs(CashFlow.xnpv(rate: xirr, values: values, dates: dates)) < 1e-8)124        #expect(CashFlow.xirr(values: [1, 2], dates: [1, 2]) == nil)125        #expect(near(CashFlow.fvSchedule(principal: 1, rates: [0.09, 0.11, 0.1]), 1.33089, 1e-12))126    }127128    @Test func rootFinders() {129        #expect(near(RootFinder.brent(0, 3) { $0 * $0 - 2 }!, 2.0.squareRoot(), 1e-14))130        #expect(RootFinder.brent(2, 3) { $0 * $0 - 2 } == nil)                       // no sign change131        #expect(near(RootFinder.newton(guess: 1) { ($0 * $0 - 2, 2 * $0) }!, 2.0.squareRoot(), 1e-14))132        #expect(RootFinder.newton(guess: 0) { ($0 * $0 + 1, 2 * $0) } == nil)        // zero slope / no root133        // root nearest the guess among several134        let cubic: (Double) -> Double = { ($0 - 0.1) * ($0 - 0.2) * ($0 - 0.3) }135        #expect(near(RootFinder.bracketedRate(guess: 0.02, cubic)!, 0.1, 1e-12))136        #expect(near(RootFinder.bracketedRate(guess: 0.24, cubic)!, 0.2, 1e-12))137        #expect(near(RootFinder.bracketedRate(guess: 9, cubic)!, 0.3, 1e-12))138        #expect(RootFinder.bracketedRate(guess: 0.1) { _ in 1 } == nil)139    }140141    @Test func bondsAndDepreciation() {142        let s = day(2008, 2, 15), m = day(2017, 11, 15)143        let price = Bond.price(settlement: s, maturity: m, rate: 0.0575, yield: 0.065, redemption: 100, frequency: 2, basis: .us30360)144        #expect(near(price, 94.63436162132214, 1e-12))145        #expect(near(Bond.yield(settlement: s, maturity: m, rate: 0.0575, price: price, redemption: 100, frequency: 2, basis: .us30360)!, 0.065, 1e-12))146        let dur = Bond.duration(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), coupon: 0.08, yield: 0.09, frequency: 2, basis: .actualActual)147        #expect(near(dur, 5.993774955545185, 1e-12))148        #expect(near(Bond.modifiedDuration(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), coupon: 0.08, yield: 0.09, frequency: 2, basis: .actualActual), dur / 1.045, 1e-14))149        // modified duration is the price sensitivity −dP/dy / dirty price150        let h = 1e-6151        let up = Bond.price(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), rate: 0.08, yield: 0.09 + h, redemption: 100, frequency: 2, basis: .actualActual)152        let down = Bond.price(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), rate: 0.08, yield: 0.09 - h, redemption: 100, frequency: 2, basis: .actualActual)153        let mid = Bond.price(settlement: day(2008, 1, 1), maturity: day(2016, 1, 1), rate: 0.08, yield: 0.09, redemption: 100, frequency: 2, basis: .actualActual)154        #expect(near(-(up - down) / (2 * h) / mid, dur / 1.045, 1e-7))155        #expect(near(Bond.accruedInterest(issue: day(2008, 3, 1), firstInterest: day(2008, 8, 31), settlement: day(2008, 5, 1), rate: 0.1, par: 1000, frequency: 2, basis: .us30360), 1000 * 0.1 * 60 / 360, 1e-12))156        // actual/actual: 61 days of a 184-day quasi-coupon period (29 Feb → 31 Aug 2008)157        #expect(near(Bond.accruedInterest(issue: day(2008, 3, 1), firstInterest: day(2008, 8, 31), settlement: day(2008, 5, 1), rate: 0.1, par: 1000, frequency: 2, basis: .actualActual), 50.0 * 61 / 184, 1e-12))158        #expect(near(Bond.tbillEquivalentYield(discount: 0.0914, days: 62)!, 0.09415149356594302, 1e-12))159        #expect(Depreciation.sln(cost: 30000, salvage: 7500, life: 10) == 2250)160        #expect(Depreciation.sln(cost: 1, salvage: 0, life: 0) == nil)161        #expect(near(Depreciation.db(cost: 1_000_000, salvage: 100_000, life: 6, period: 7, month: 7)!, 15845.098473848071, 1e-10))162        #expect(near(Depreciation.ddb(cost: 2400, salvage: 300, life: 10, period: 2, factor: 1.5)!, 306, 1e-12))163        #expect(near(Depreciation.vdb(cost: 2400, salvage: 300, life: 10, start: 0, end: 0.875, factor: 1.5)!, 315, 1e-12))164        // the whole schedule depreciates exactly cost − salvage165        #expect(near(Depreciation.vdb(cost: 2400, salvage: 300, life: 10, start: 0, end: 10)!, 2100, 1e-12))166        var total = 0.0167        for p in 1...10 { total += Depreciation.syd(cost: 2400, salvage: 300, life: 10, period: Double(p))! }168        #expect(near(total, 2100, 1e-12))169        #expect(Depreciation.vdb(cost: 100, salvage: 10, life: 5, start: 3, end: 2) == nil)170    }171}172173@Suite struct RegistrationTests {174    static let lookup = ["VLOOKUP", "HLOOKUP", "LOOKUP", "MATCH", "XMATCH", "XLOOKUP", "ADDRESS", "AREAS", "HYPERLINK", "TRANSPOSE"]175    static let arrays = ["FILTER", "SORT", "SORTBY", "UNIQUE", "SEQUENCE", "TAKE", "DROP", "CHOOSEROWS", "CHOOSECOLS", "EXPAND", "HSTACK", "VSTACK",176                         "TOCOL", "TOROW", "WRAPROWS", "WRAPCOLS", "MAP", "REDUCE", "SCAN", "BYROW", "BYCOL", "MAKEARRAY"]177    static let financial = ["PMT", "IPMT", "PPMT", "PV", "FV", "NPER", "RATE", "NPV", "XNPV", "IRR", "XIRR", "MIRR", "CUMIPMT", "CUMPRINC", "EFFECT", "NOMINAL",178                            "SLN", "SYD", "DB", "DDB", "VDB", "FVSCHEDULE", "PDURATION", "RRI", "ISPMT", "DOLLARDE", "DOLLARFR", "PRICE", "YIELD", "DURATION",179                            "MDURATION", "ACCRINT", "ACCRINTM", "DISC", "INTRATE", "RECEIVED", "PRICEDISC", "PRICEMAT", "YIELDDISC", "YIELDMAT", "TBILLEQ",180                            "TBILLPRICE", "TBILLYIELD", "COUPDAYBS", "COUPDAYS", "COUPDAYSNC", "COUPNCD", "COUPNUM", "COUPPCD"]181182    @Test func everyFunctionIsRegisteredWithMetadata() {183        let registry = FunctionRegistry()184        LookupFunctions.register(into: registry)185        FinancialFunctions.register(into: registry)186        let expected: [(String, FunctionCategory)] = Self.lookup.map { ($0, .lookup) } + Self.arrays.map { ($0, .array) } + Self.financial.map { ($0, .financial) }187        #expect(registry.count == expected.count)188        #expect(expected.count == 81)189        for (name, category) in expected {190            guard let spec = registry.lookup(name) else { Issue.record("\(name) is not registered"); continue }191            #expect(spec.category == category, "\(name)")192            #expect(spec.signature.hasPrefix(name + "("), "\(name): \(spec.signature)")193            #expect(spec.signature.hasSuffix(")"))194            #expect(spec.summary.count > 15 && spec.summary.hasSuffix("."), "\(name): \(spec.summary)")195            #expect(!spec.isVolatile)196            #expect(spec.minArgs <= spec.maxArgs)197            // the signature lists as many parameters as the function accepts (… = variadic)198            let inner = spec.signature.dropFirst(name.count + 1).dropLast()199            let params = inner.split(separator: ",").map { $0.trimmingCharacters(in: .whitespaces) }200            var required = params.filter { !$0.hasPrefix("[") && $0 != "…" }.count201            if name == "REDUCE" || name == "SCAN" { required += 1 }      // the optional initial value comes first: its position is mandatory202            #expect(required == spec.minArgs, "\(name): \(spec.signature) vs minArgs \(spec.minArgs)")203            if spec.maxArgs != variadic { #expect(params.count == spec.maxArgs, "\(name): \(spec.signature) vs maxArgs \(spec.maxArgs)") }204            else { #expect(spec.signature.contains("…"), "\(name)") }205        }206    }207208    @Test func coreBuiltinsAreNotOverridden() {209        let registry = FunctionRegistry()210        LookupFunctions.register(into: registry)211        FinancialFunctions.register(into: registry)212        for core in ["SUM", "AVERAGE", "MIN", "MAX", "COUNT", "COUNTA", "AND", "OR", "XOR", "NOT", "TRUE", "FALSE", "NA", "ABS", "ROW", "COLUMN", "ROWS",213                     "COLUMNS", "INDEX", "OFFSET", "INDIRECT", "NOW", "TODAY", "RAND", "IF", "IFS", "IFERROR", "IFNA", "SWITCH", "CHOOSE", "LET", "LAMBDA", "ISOMITTED"] {214            #expect(registry.lookup(core) == nil, "\(core) must not be registered by this package")215        }216    }217218    @Test func wrongArgumentCounts() {219        let b = Book()220        #expect(b.eval("=VLOOKUP(1,A1:B2)") == .error(.value))221        #expect(b.eval("=XLOOKUP(1,A1:A2)") == .error(.value))222        #expect(b.eval("=PMT(0.1,2)") == .error(.value))223        #expect(b.eval("=SEQUENCE()") == .error(.value))224        #expect(b.eval("=CUMIPMT(0.1,2,3,1,2)") == .error(.value))225    }226}227
Files main
Menu

Theme