SPB Git

Repository Public

main.swift 9.9 KB 189 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Tests/FinanceModelsTests/Generators/ExcelCompat/Sources/gen-excel-compat/main.swift10// Module           : gen-excel-compat11// 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 FunctionsLookup8990// Writes the expected values of FinanceModels' Excel-compatible conventions, computed by91// FunctionsLookup's `DayCount` and `Bond` (the implementation behind YEARFRAC, PRICE, YIELD,92// DURATION and MDURATION, validated against values computed by Microsoft Excel).93//94// Output (argument 1): a JSON object95//   daycount: [[y1, m1, d1, y2, m2, d2, days basis 0]]            US (NASD) 30/360, dates as given96//   bonds:    [{settle, maturity, coupon, yield, freq, redemption, basis, prev, next, n, A, E, x,97//               price, yield_back, duration?, mduration?}]        duration only for a redemption of 1009899func isLeap(_ y: Int) -> Bool { (y % 4 == 0 && y % 100 != 0) || y % 400 == 0 }100func monthLength(_ y: Int, _ m: Int) -> Int { m == 2 ? (isLeap(y) ? 29 : 28) : ([4, 6, 9, 11].contains(m) ? 30 : 31) }101func ymd(_ d: CivilDate) -> [Int] { [d.year, d.month, d.day] }102103// MARK: day counts — every month end and the days around it, over leap and non-leap years104105var gridDates = [CivilDate]()106for (year, months) in [(2019, Array(1...12)), (2020, Array(1...12)), (2024, [1, 2, 3, 8, 12]), (2100, [2, 3])] {107    for m in months {108        let last = monthLength(year, m)109        for d in Set([15, 28, 29, 30, 31, last]).sorted() where d <= last { gridDates.append(CivilDate(year: year, month: m, day: d)) }110    }111}112var daycount = [[Int]]()113for a in gridDates {114    for b in gridDates where a <= b {115        daycount.append(ymd(a) + ymd(b) + [DayCount.days30US(a, b)])116    }117}118// a few reversed pairs (the dates are used as given)119for (a, b) in [(CivilDate(year: 2020, month: 1, day: 31), CivilDate(year: 2019, month: 2, day: 28)),120               (CivilDate(year: 2020, month: 3, day: 31), CivilDate(year: 2020, month: 2, day: 29)),121               (CivilDate(year: 2019, month: 8, day: 30), CivilDate(year: 2019, month: 5, day: 31))] {122    daycount.append(ymd(a) + ymd(b) + [DayCount.days30US(a, b)])123}124125// MARK: bonds — five bases, several coupons, frequencies and settlement positions126127let maturities = [CivilDate(year: 2031, month: 2, day: 28), CivilDate(year: 2032, month: 2, day: 29), CivilDate(year: 2030, month: 11, day: 15),128                  CivilDate(year: 2029, month: 8, day: 31)]129let terms: [(coupon: Double, yield: Double, redemption: Double)] = [130    (0.0575, 0.065, 100), (0.02, 0.0125, 100), (0.10, 0.035, 105), (0, 0.04, 100), (0.03, -0.004, 100),131]132var bonds = [[String: Any]]()133for maturity in maturities {134    for frequency in [1, 2, 4] {135        let step = 12 / frequency136        let eom = maturity.isEndOfMonth137        // settlement positions: in the last coupon period, around coupon dates (incl. end of February), mid-period, far from maturity138        var settlements = [CivilDate]()139        let lastCoupon = maturity.adding(months: -step, endOfMonth: eom)140        settlements.append(CivilDate(serial: lastCoupon.serial() + 1))                 // day after the last coupon date (N = 1)141        settlements.append(CivilDate(serial: maturity.serial() - 1))                   // day before maturity142        settlements.append(maturity.adding(months: -step * 3, endOfMonth: eom))        // on a coupon date143        settlements.append(CivilDate(serial: maturity.adding(months: -step * 3, endOfMonth: eom).serial() - 1))   // day before a coupon date144        settlements.append(CivilDate(serial: maturity.adding(months: -step * 5, endOfMonth: eom).serial() + 37))  // inside a period145        settlements.append(CivilDate(year: 2024, month: 2, day: 29))146        settlements.append(CivilDate(year: 2025, month: 2, day: 28))147        settlements.append(CivilDate(year: 2025, month: 12, day: 31))148        settlements.append(CivilDate(year: 2026, month: 7, day: 30))149        for settlement in settlements where settlement < maturity {150            for basis in DayCount.Basis.allCases {151                for t in terms {152                    let p = DayCount.CouponPeriod(settlement: settlement, maturity: maturity, frequency: frequency, basis: basis)153                    let price = Bond.price(settlement: settlement, maturity: maturity, rate: t.coupon, yield: t.yield, redemption: t.redemption,154                                           frequency: frequency, basis: basis)155                    guard price.isFinite, price > 0 else { continue }156                    var row: [String: Any] = [157                        "settle": ymd(settlement), "maturity": ymd(maturity), "coupon": t.coupon, "yield": t.yield, "freq": frequency,158                        "redemption": t.redemption, "basis": basis.rawValue, "prev": ymd(p.previous), "next": ymd(p.next), "n": p.remaining,159                        "A": p.daysFromPrevious, "E": p.daysInPeriod, "x": p.remainingFraction, "price": price,160                    ]161                    if let y = Bond.yield(settlement: settlement, maturity: maturity, rate: t.coupon, price: price, redemption: t.redemption,162                                          frequency: frequency, basis: basis), y.isFinite {163                        row["yield_back"] = y164                    }165                    if t.redemption == 100 {166                        row["duration"] = Bond.duration(settlement: settlement, maturity: maturity, coupon: t.coupon, yield: t.yield, frequency: frequency, basis: basis)167                        row["mduration"] = Bond.modifiedDuration(settlement: settlement, maturity: maturity, coupon: t.coupon, yield: t.yield,168                                                                 frequency: frequency, basis: basis)169                    }170                    bonds.append(row)171                }172            }173        }174    }175}176177let doc: [String: Any] = [178    "name": "excel_compat",179    "description": "Expected values of FinanceModels' Excel-compatible conventions (DayCountConvention.thirty360US, BondPricingConvention.excel), "180        + "computed by FunctionsLookup (DayCount.days30US, DayCount.CouponPeriod, Bond.price/yield/duration/modifiedDuration), the implementation "181        + "of the YEARFRAC/PRICE/YIELD/DURATION/MDURATION worksheet functions. Generated by Generators/gen_excel_compat.sh; do not edit.",182    "daycount": daycount,183    "bonds": bonds,184]185let data = try JSONSerialization.data(withJSONObject: doc, options: [.sortedKeys])186let out = CommandLine.arguments.count > 1 ? CommandLine.arguments[1] : "excel_compat.json"187try data.write(to: URL(fileURLWithPath: out))188print("wrote \(out): \(daycount.count) day-count pairs, \(bonds.count) bonds")189
Files main
Menu

Theme