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/daycount_conventions.py10# Description : Independent reference for the three 30/360 "US"11# conventions, written from the published rule texts.12# Module : FinanceModelsTests13# Repository : https://git.spboucher.ai/orix-sheet14# Created : 2026-11-2115# Last Modified : 2026-11-2116#17# =============================================================================18# AUTHOR & PROFESSIONAL AFFILIATION19# =============================================================================20#21# Author : Simon-Pierre Boucher22# Position : Professor of Finance23# Institution : Université du Québec en Outaouais (UQO)24# Department : Département des sciences administratives25# Organization : SP Boucher26#27# Website : https://spboucher.ai28# Git : https://git.spboucher.ai29#30# =============================================================================31# OFFICIAL CONTACT DIRECTORY32# =============================================================================33#34# General : info@spboucher.ai35# Professional : contact@spboucher.ai36# Direct : simon@spboucher.ai37# Research : research@spboucher.ai38# Development : dev@spboucher.ai39# Support : support@spboucher.ai40# Security/Admin : admin@spboucher.ai41# Notifications : notifications@spboucher.ai42#43# Preferred Contact: support@spboucher.ai44#45# =============================================================================46# COPYRIGHT & INTELLECTUAL PROPERTY47# =============================================================================48#49# Copyright (c) 202650# Simon-Pierre Boucher51#52# All rights reserved where applicable.53#54# This file is governed by the applicable project license,55# copyright notices, institutional agreements and relevant law.56#57# Unless expressly authorized by the applicable license or58# rights holder, copying, redistribution, sublicensing,59# publication and commercial exploitation are not permitted.60#61# Existing third-party rights and notices remain unaffected.62#63# =============================================================================64# CONFIDENTIALITY & SECURITY65# =============================================================================66#67# Classification : Public68# Distribution : Public repository; use is governed by the project license69# Security Contact : admin@spboucher.ai70#71# Access to this source does not independently grant permission72# to redistribute or disclose confidential materials.73#74# Do not embed credentials, private keys, access tokens,75# certificates or confidential personal information.76#77# =============================================================================78# LICENSE & MAINTENANCE79# =============================================================================80#81# License : None specified (no license file in this repository)82# Maintainer : Simon-Pierre Boucher83# Technical Contact: dev@spboucher.ai84#85# =============================================================================86# END OF SP BOUCHER SOURCE HEADER87# =============================================================================8889"""Independent reference for the three 30/360 "US" conventions, written from the published rule texts.9091Not a port of the Swift code. It answers one question: which rule does each name stand for, and on92which date pairs do they differ?9394 excel_nasd US (NASD) 30/360 as Microsoft Excel computes it (YEARFRAC basis 0, COUPDAYBS, ACCRINTM).95 Rule recovered from Excel's results (the 279 basis-0 YEARFRAC rows computed by Excel in96 FunctionsLookup's excel_reference.json): the end-date tests look at the *unadjusted*97 start day.98 1. D2 = 30 if date 2 and date 1 are both the last day of February;99 2. D2 = 30 if D2 = 31 and the original D1 is 30 or 31;100 3. D1 = 30 if D1 = 31 or date 1 is the last day of February.101 sia_eom SIA "30U/360" with the end-of-month February rules (Standard Securities Calculation102 Methods, as summarised under "30/360 US" in the day-count literature), rules applied in103 sequence, each one seeing the result of the previous one:104 1. if date 1 and date 2 are both the last day of February, D2 = 30;105 2. if date 1 is the last day of February, D1 = 30;106 3. if D2 = 31 and D1 is 30 or 31, D2 = 30;107 4. if D1 = 31, D1 = 30.108 isda_bond ISDA 2006 Definitions section 4.16(f) "30/360", "360/360" or "Bond Basis":109 D1 = 30 if D1 = 31; D2 = 30 if D2 = 31 and D1 (after adjustment) is 30.110 No February rule.111112All three: days = 360*(Y2 - Y1) + 30*(M2 - M1) + (D2 - D1).113114Usage:115 python3 daycount_conventions.py [path/to/FunctionsLookup/Tests/FunctionsLookupTests/Fixtures/excel_reference.json]116117Prints the number of grid pairs on which the conventions differ, a sample of them and, when the Excel118fixture is given, how many of Excel's basis-0 YEARFRAC results each convention reproduces.119"""120import calendar121import datetime as dt122import json123import sys124125126def eofeb(d):127 return d.month == 2 and d.day == calendar.monthrange(d.year, 2)[1]128129130def count(a, b, d1, d2):131 return 360 * (b.year - a.year) + 30 * (b.month - a.month) + (d2 - d1)132133134def excel_nasd(a, b):135 d1, d2 = a.day, b.day136 if eofeb(b) and eofeb(a):137 d2 = 30138 if d2 == 31 and a.day >= 30:139 d2 = 30140 if d1 == 31 or eofeb(a):141 d1 = 30142 return count(a, b, d1, d2)143144145def sia_eom(a, b):146 d1, d2 = a.day, b.day147 if eofeb(a) and eofeb(b):148 d2 = 30149 if eofeb(a):150 d1 = 30151 if d2 == 31 and d1 in (30, 31):152 d2 = 30153 if d1 == 31:154 d1 = 30155 return count(a, b, d1, d2)156157158def isda_bond(a, b):159 d1, d2 = a.day, b.day160 if d1 == 31:161 d1 = 30162 if d2 == 31 and d1 == 30:163 d2 = 30164 return count(a, b, d1, d2)165166167def grid():168 """Month ends, the days around them and mid-month days over two leap and two common years."""169 days = []170 for y in (2019, 2020, 2023, 2024):171 for m in range(1, 13):172 last = calendar.monthrange(y, m)[1]173 for d in sorted({1, 15, 27, 28, 29, 30, 31, last} & set(range(1, last + 1))):174 days.append(dt.date(y, m, d))175 return [(a, b) for a in days for b in days if a <= b]176177178def main():179 pairs = grid()180 diff_es = [(a, b) for a, b in pairs if excel_nasd(a, b) != sia_eom(a, b)]181 diff_ei = [(a, b) for a, b in pairs if excel_nasd(a, b) != isda_bond(a, b)]182 diff_si = [(a, b) for a, b in pairs if sia_eom(a, b) != isda_bond(a, b)]183 print(f"grid pairs: {len(pairs)}")184 print(f"excel_nasd != sia_eom : {len(diff_es)}")185 print(f"excel_nasd != isda_bond: {len(diff_ei)}")186 print(f"sia_eom != isda_bond: {len(diff_si)}")187 # characterisation of every Excel/SIA difference188 assert all(eofeb(a) and b.day == 31 for a, b in diff_es), "unexpected kind of difference"189 assert all(excel_nasd(a, b) - sia_eom(a, b) == 1 for a, b in diff_es)190 print("every excel_nasd/sia_eom difference: start = last day of February, end day = 31, Excel one day longer")191 assert all(eofeb(a) for a, b in diff_ei) and all(eofeb(a) for a, b in diff_si)192 print("every difference with isda_bond: start = last day of February")193 for a, b in (diff_es[0], diff_es[-1], (dt.date(2019, 2, 28), dt.date(2020, 1, 31)), (dt.date(2019, 2, 28), dt.date(2020, 2, 29)),194 (dt.date(2020, 2, 29), dt.date(2020, 3, 30))):195 print(f" {a} -> {b}: excel_nasd {excel_nasd(a, b)} sia_eom {sia_eom(a, b)} isda_bond {isda_bond(a, b)}")196197 if len(sys.argv) > 1:198 epoch = dt.date(1899, 12, 30)199 rows = [r for r in json.load(open(sys.argv[1]))["yearfrac"] if r[2] == 0]200 for name, fn in (("excel_nasd", excel_nasd), ("sia_eom", sia_eom), ("isda_bond", isda_bond)):201 ok = 0202 for s, e, _, expect in rows:203 a, b = sorted((epoch + dt.timedelta(days=s), epoch + dt.timedelta(days=e)))204 ok += abs(fn(a, b) / 360 - expect) < 1e-9205 print(f"Excel YEARFRAC basis 0, {len(rows)} rows: {name} reproduces {ok}")206 special = [(epoch + dt.timedelta(days=s), epoch + dt.timedelta(days=e), x) for s, e, _, x in rows207 if excel_nasd(*sorted((epoch + dt.timedelta(days=s), epoch + dt.timedelta(days=e))))208 != sia_eom(*sorted((epoch + dt.timedelta(days=s), epoch + dt.timedelta(days=e))))]209 print(f"rows that discriminate excel_nasd from sia_eom: {len(special)}")210 for a, b, x in special[:5]:211 print(f" {a} -> {b}: Excel {round(x * 360)} days")212213214if __name__ == "__main__":215 main()216