Repository Public
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