SPB Git

Repository Public

ExcelCompatibilityTests.swift 8.6 KB 134 lines swift

1import Foundation2import Testing3import AnalyticsTestSupport4import Statistics5@testable import FinanceModels67/// Expected values computed by FunctionsLookup, the implementation of the worksheet functions that is8/// validated against results of Microsoft Excel (`Generators/gen_excel_compat.sh`). The Finance9/// Studio and a formula such as `=PRICE(…)` or `=YEARFRAC(…, 0)` must give the same number.10let excelCompatFixture = Fixture.load("excel_compat", .module)1112@Suite struct ExcelCompatibilityTests {13    private func date(_ r: [Int], _ at: Int) -> CalendarDate { CalendarDate(r[at], r[at + 1], r[at + 2]) }1415    @Test func us30360EqualsExcelBasisZeroOnTheWholeGrid() throws {16        let rows = excelCompatFixture["daycount"].list17        #expect(rows.count > 9000)18        var mismatches = [String](), differsFromSIA = 0, differsFromISDA = 019        for row in rows {20            let r = row.ints21            let a = date(r, 0), b = date(r, 3)22            let days = DayCount.days(from: a, to: b, convention: .thirty360US)23            if days != r[6] { mismatches.append("\(a) → \(b): \(days), expected \(r[6])") }24            if a <= b, try DayCount.yearFraction(from: a, to: b, convention: .thirty360US) != Double(r[6]) / 360 {25                mismatches.append("\(a) → \(b): year fraction")26            }27            if DayCount.days(from: a, to: b, convention: .thirty360SIA) != r[6] { differsFromSIA += 1 }28            if DayCount.days(from: a, to: b, convention: .thirty360ISDA) != r[6] { differsFromISDA += 1 }29        }30        #expect(mismatches.isEmpty, "\(mismatches.count) mismatches, first: \(mismatches.prefix(5))")31        // the grid does separate the three rules32        #expect(differsFromSIA > 0 && differsFromISDA > differsFromSIA)33    }3435    @Test func theThreeUSRulesOnTheDiscriminatingPairs() {36        let a = CalendarDate(2019, 2, 28), b = CalendarDate(2020, 1, 31)37        #expect(DayCount.days(from: a, to: b, convention: .thirty360US) == 331)      // Excel: =YEARFRAC(a, b, 0)·36038        #expect(DayCount.days(from: a, to: b, convention: .thirty360SIA) == 330)39        #expect(DayCount.days(from: a, to: b, convention: .thirty360ISDA) == 333)40        // rows computed by Microsoft Excel itself (YEARFRAC basis 0 in FunctionsLookup's excel_reference.json)41        #expect(DayCount.days(from: CalendarDate(1993, 2, 28), to: CalendarDate(1996, 3, 31), convention: .thirty360US) == 1111)42        #expect(DayCount.days(from: CalendarDate(1995, 2, 28), to: CalendarDate(1996, 3, 31), convention: .thirty360US) == 391)43        // both ends on the last day of February: Excel and SIA fold both to the 30th, ISDA does not44        let c = CalendarDate(2020, 2, 29)45        #expect(DayCount.days(from: a, to: c, convention: .thirty360US) == 360)46        #expect(DayCount.days(from: a, to: c, convention: .thirty360SIA) == 360)47        #expect(DayCount.days(from: a, to: c, convention: .thirty360ISDA) == 361)48        // the rules only part ways when the start is the last day of February49        for (s, e) in [(CalendarDate(2019, 1, 31), CalendarDate(2019, 3, 31)), (CalendarDate(2019, 1, 15), CalendarDate(2019, 2, 28)),50                       (CalendarDate(2020, 2, 28), CalendarDate(2020, 8, 31)), (CalendarDate(2019, 5, 30), CalendarDate(2019, 7, 31))] {51            let us = DayCount.days(from: s, to: e, convention: .thirty360US)52            #expect(us == DayCount.days(from: s, to: e, convention: .thirty360SIA) && us == DayCount.days(from: s, to: e, convention: .thirty360ISDA))53        }54        #expect(DayCountConvention(rawValue: "thirty360US") == .thirty360US)          // stored choice of existing studies keeps its meaning of "Excel basis 0"55    }5657    private func dayCount(basis: Int) -> DayCountConvention {58        switch basis {59        case 0: .thirty360US60        case 1: .actualActualICMA61        case 2: .actual36062        case 3: .actual365Fixed63        default: .thirtyE36064        }65    }6667    @Test func excelConventionEqualsTheWorksheetFunctions() throws {68        let rows = excelCompatFixture["bonds"].list69        #expect(rows.count >= 2500)70        var failures = [String](), perBasis = [Int: Int](), streetDiffers = [Int: Int]()71        var worst = 0.072        for c in rows {73            let basis = c["basis"].i74            let ymd = { (k: String) in CalendarDate(c[k][0].i, c[k][1].i, c[k][2].i) }75            let settle = ymd("settle"), y = c["yield"].d76            let bond = Bond(maturity: ymd("maturity"), couponRate: c["coupon"].d, frequency: c["freq"].i, redemption: c["redemption"].d,77                            dayCount: dayCount(basis: basis))78            #expect(bond.convention == .excel)                                         // the default79            let tag = "basis \(basis) f \(bond.frequency) c \(bond.couponRate) \(settle) → \(bond.maturity)"80            let cp = try bond.couponPeriod(settlement: settle)81            if cp.previousCoupon != ymd("prev") || cp.nextCoupon != ymd("next") || cp.remainingCoupons != c["n"].i { failures.append("\(tag): schedule") }82            if cp.accruedDays != c["A"].d || cp.periodDays != c["E"].d || abs(cp.fractionToNextCoupon - c["x"].d) > 1e-15 { failures.append("\(tag): A/E/x") }83            let price = try bond.cleanPrice(settlement: settle, yield: y)84            worst = max(worst, abs(price - c["price"].d))85            if abs(price - c["price"].d) > 1e-10 { failures.append("\(tag): price \(price) vs \(c["price"].d)") }86            let a = try bond.analytics(settlement: settle, yield: y)87            if abs(a.cleanPrice - c["price"].d) > 1e-10 { failures.append("\(tag): analytics price") }88            if !c["duration"].isNull {89                if abs(a.macaulayDuration - c["duration"].d) > 1e-10 { failures.append("\(tag): duration \(a.macaulayDuration) vs \(c["duration"].d)") }90                if abs(a.modifiedDuration - c["mduration"].d) > 1e-10 { failures.append("\(tag): modified duration") }91            }92            if !c["yield_back"].isNull {93                let back = try bond.yieldToMaturity(settlement: settle, cleanPrice: c["price"].d)94                if abs(back - c["yield_back"].d) > 1e-10 { failures.append("\(tag): yield \(back) vs \(c["yield_back"].d)") }95            }96            perBasis[basis, default: 0] += 197            var street = bond98            street.convention = .streetActual99            if abs(try street.cleanPrice(settlement: settle, yield: y) - price) > 1e-9 { streetDiffers[basis, default: 0] += 1 }100        }101        #expect(failures.isEmpty, "\(failures.count) failures, first: \(failures.prefix(6))")102        #expect(worst <= 1e-10, "largest price difference \(worst)")103        #expect(perBasis.keys.sorted() == [0, 1, 2, 3, 4] && perBasis.values.allSatisfy { $0 >= 500 })104        // the two conventions are the same thing for 30/360 and ACT/ACT, and differ for ACT/360 and ACT/365105        #expect(streetDiffers[0] == nil && streetDiffers[1] == nil && streetDiffers[4] == nil)106        #expect((streetDiffers[2] ?? 0) > 100 && (streetDiffers[3] ?? 0) > 100)107    }108109    @Test func parBondOnACouponDateIsExactlyParInEveryBasisUnderExcel() throws {110        let settle = CalendarDate(2026, 6, 15)111        for dc in DayCountConvention.allCases {112            let bond = Bond(maturity: CalendarDate(2034, 6, 15), couponRate: 0.045, frequency: 2, dayCount: dc)113            #expect(isClose(try bond.cleanPrice(settlement: settle, yield: 0.045), 100, abs: 1e-11), "\(dc)")114        }115        // street convention: the first period of ACT/360 lasts 183/180 of a nominal period, so par is not 100116        let street = Bond(maturity: CalendarDate(2034, 6, 15), couponRate: 0.045, frequency: 2, dayCount: .actual360, convention: .streetActual)117        let cp = try street.couponPeriod(settlement: settle)118        #expect(cp.daysToNextCoupon == 183 && cp.periodDays == 180 && cp.fractionToNextCoupon == 183.0 / 180)119        #expect(abs(try street.cleanPrice(settlement: settle, yield: 0.045) - 100) > 0.01)120        let excel = Bond(maturity: CalendarDate(2034, 6, 15), couponRate: 0.045, frequency: 2, dayCount: .actual360)121        #expect(try excel.couponPeriod(settlement: settle).fractionToNextCoupon == 1)122    }123124    @Test func conventionSurvivesCodingAndOldArchivesDecodeAsExcel() throws {125        let b = Bond(maturity: CalendarDate(2030, 1, 15), couponRate: 0.04, dayCount: .actual365Fixed, convention: .streetActual)126        let data = try JSONEncoder().encode(b)127        #expect(try JSONDecoder().decode(Bond.self, from: data) == b)128        var object = try #require(try JSONSerialization.jsonObject(with: data) as? [String: Any])129        object["convention"] = nil130        let legacy = try JSONDecoder().decode(Bond.self, from: JSONSerialization.data(withJSONObject: object))131        #expect(legacy.convention == .excel && legacy.dayCount == .actual365Fixed)132    }133}134
Files main
Menu

Theme