[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Excel's YEARFRAC algorithm
David, Attached are two programs that demonstrate the algorithm
Excel uses for the YEARFRAC function. Note that these are not actual
source code from Excel, but rather sample programs that we have put together to
help the formula subcommittee understand Excel’s implementation
better. As we discussed in Monday’s call, the goal in sharing these
is to help you verify that the algorithm you’ve created in Python matches
what Excel is doing for YearFrac. Just FYI, I want to be careful to set expectations
appropriately going forward, since I can’t be certain that I’ll
always be able to share this type of sample for other functions we may discuss
in the future. My goal is to help the formula subcommittee and the
broader community understand our implementation whenever possible, but
we’ll need to handle these sorts of situations on a case-by-case
basis. I may be able to share sample code like this for other functions,
or pseudocode, or a narrative description of our algorithm, but in some cases I
may not be able to obtain or provide specific information on a particular
algorithm in a timely fashion. I’ll do my best to provide
what’s needed, and in this case I’m able to share these code
samples with the TC. I hope you’ll find them useful, and please let
me know if you need anything else from me on this matter. FYI for everyone else on the list, I have joined the ODF TC
and the formula subcommittee in the last week as Microsoft’s
representative. I’m traveling through the end of this week but will
be back in my office as of next Monday, and I’m looking forward to
working with all of you going forward. Best regards, Doug Doug Mahugh | Senior Product
Manager, Office Client Interoperability | 425-707-1182 | blogs.msdn.com/dmahugh |
Public Function FIsLeapYear(Year As Integer) As Boolean If (Year Mod 4) > 0 Then FIsLeapYear = False ElseIf (Year Mod 100) > 0 Then FIsLeapYear = True ElseIf (Year Mod 400) = 0 Then FIsLeapYear = True Else FIsLeapYear = False End If End Function Public Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As Boolean Select Case Month Case 1, 3, 5, 7, 8, 10, 12 FIsEndOfMonth = (Day = 31) Case 4, 6, 9, 11 FIsEndOfMonth = (Day = 30) Case 2 If FIsLeapYear(Year) Then FIsEndOfMonth = (Day = 29) Else FIsEndOfMonth = (Day = 28) End If End Select End Function Public Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As Integer Days360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay) End Function Public Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As Integer Dim StartDay As Integer Dim StartMonth As Integer Dim StartYear As Integer Dim EndDay As Integer Dim EndMonth As Integer Dim EndYear As Integer StartDay = Day(StartDate) StartMonth = Month(StartDate) StartYear = Year(StartDate) EndDay = Day(EndDate) EndMonth = Month(EndDate) EndYear = Year(EndDate) If (EndMonth = 2 And FIsEndOfMonth(EndDay, EndMonth, EndYear)) And ((StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Or Method = 3) Then EndDay = 30 End If If EndDay = 31 And (StartDay >= 30 Or Method = 3) Then EndDay = 30 End If If StartDay = 31 Then StartDay = 30 End If If (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Then StartDay = 30 End If TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay) End Function Public Function TmpDays360Euro(StartDate As Date, EndDate As Date) Dim StartDay As Integer Dim StartMonth As Integer Dim StartYear As Integer Dim EndDay As Integer Dim EndMonth As Integer Dim EndYear As Integer StartDay = Day(StartDate) StartMonth = Month(StartDate) StartYear = Year(StartDate) EndDay = Day(EndDate) EndMonth = Month(EndDate) EndYear = Year(EndDate) If (StartDay = 31) Then StartDay = 30 End If If (EndDay = 31) Then EndDay = 30 End If TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay) End Function Public Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As Integer Select Case Basis Case 0 'atpmBasis30360 TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True) Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of days TmpDiffDates = DateDiff("d", StartDate, EndDate) Case 4 'atpmBasisE30360 TmpDiffDates = TmpDays360Euro(StartDate, EndDate) End Select End Function Public Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As Double Dim StartDay As Integer Dim StartMonth As Integer Dim StartYear As Integer Dim EndDay As Integer Dim EndMonth As Integer Dim EndYear As Integer Dim iYear As Integer Select Case Basis Case 0, 2, 4 'atpmBasis30360 atpmBasisActual360 atpmBasisE30360 TmpCalcAnnualBasis = 360 Case 3 'atpmBasisActual365 TmpCalcAnnualBasis = 365 Case 1 ' atpmBasisActual StartDay = Day(StartDate) StartMonth = Month(StartDate) StartYear = Year(StartDate) EndDay = Day(EndDate) EndMonth = Month(EndDate) EndYear = Year(EndDate) If (StartYear = EndYear) Then If FIsLeapYear(StartYear) Then TmpCalcAnnualBasis = 366 Else TmpCalcAnnualBasis = 365 End If ElseIf ((EndYear - 1) = StartYear) And ((StartMonth > EndMonth) Or ((StartMonth = EndMonth) And StartDay >= EndDay)) Then If FIsLeapYear(StartYear) Then If StartMonth < 2 Or (StartMonth = 2 And StartDay <= 29) Then TmpCalcAnnualBasis = 366 Else TmpCalcAnnualBasis = 365 End If ElseIf FIsLeapYear(EndYear) Then If EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) Then TmpCalcAnnualBasis = 366 Else TmpCalcAnnualBasis = 365 End If Else TmpCalcAnnualBasis = 365 End If Else For iYear = StartYear To EndYear If FIsLeapYear(iYear) Then TmpCalcAnnualBasis = TmpCalcAnnualBasis + 366 Else TmpCalcAnnualBasis = TmpCalcAnnualBasis + 365 End If Next iYear TmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1) End If End Select End Function Public Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer) Dim nNumerator As Integer Dim nDenom As Double nNumerator = TmpDiffDates(StartDate, EndDate, Basis) nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis) TmpYearFrac = nNumerator / nDenom End Function =end VBA source code for YearFrac #----------------------------------------------------------------------------- # Ruby version starts here, with VBA code in comment blocks for comparison ... #----------------------------------------------------------------------------- Public Function FIsLeapYear(Year As Integer) As Boolean If (Year Mod 4) > 0 Then FIsLeapYear = False ElseIf (Year Mod 100) > 0 Then FIsLeapYear = True ElseIf (Year Mod 400) = 0 Then FIsLeapYear = True Else FIsLeapYear = False End If End Function Public Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As Boolean Select Case Month Case 1, 3, 5, 7, 8, 10, 12 FIsEndOfMonth = (Day = 31) Case 4, 6, 9, 11 FIsEndOfMonth = (Day = 30) Case 2 If FIsLeapYear(Year) Then FIsEndOfMonth = (Day = 29) Else FIsEndOfMonth = (Day = 28) End If End Select End Function Public Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As Integer Days360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay) End Function Public Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As Integer Dim StartDay As Integer Dim StartMonth As Integer Dim StartYear As Integer Dim EndDay As Integer Dim EndMonth As Integer Dim EndYear As Integer StartDay = Day(StartDate) StartMonth = Month(StartDate) StartYear = Year(StartDate) EndDay = Day(EndDate) EndMonth = Month(EndDate) EndYear = Year(EndDate) If (EndMonth = 2 And FIsEndOfMonth(EndDay, EndMonth, EndYear)) And ((StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Or Method = 3) Then EndDay = 30 End If If EndDay = 31 And (StartDay >= 30 Or Method = 3) Then EndDay = 30 End If If StartDay = 31 Then StartDay = 30 End If If (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Then StartDay = 30 End If TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay) End Function Public Function TmpDays360Euro(StartDate As Date, EndDate As Date) Dim StartDay As Integer Dim StartMonth As Integer Dim StartYear As Integer Dim EndDay As Integer Dim EndMonth As Integer Dim EndYear As Integer StartDay = Day(StartDate) StartMonth = Month(StartDate) StartYear = Year(StartDate) EndDay = Day(EndDate) EndMonth = Month(EndDate) EndYear = Year(EndDate) If (StartDay = 31) Then StartDay = 30 End If If (EndDay = 31) Then EndDay = 30 End If TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay) End Function Public Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As Integer Select Case Basis Case 0 'atpmBasis30360 TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True) Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of days TmpDiffDates = DateDiff("d", StartDate, EndDate) Case 4 'atpmBasisE30360 TmpDiffDates = TmpDays360Euro(StartDate, EndDate) End Select End Function Public Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As Double Dim StartDay As Integer Dim StartMonth As Integer Dim StartYear As Integer Dim EndDay As Integer Dim EndMonth As Integer Dim EndYear As Integer Dim iYear As Integer Select Case Basis Case 0, 2, 4 'atpmBasis30360 atpmBasisActual360 atpmBasisE30360 TmpCalcAnnualBasis = 360 Case 3 'atpmBasisActual365 TmpCalcAnnualBasis = 365 Case 1 ' atpmBasisActual StartDay = Day(StartDate) StartMonth = Month(StartDate) StartYear = Year(StartDate) EndDay = Day(EndDate) EndMonth = Month(EndDate) EndYear = Year(EndDate) If (StartYear = EndYear) Then If FIsLeapYear(StartYear) Then TmpCalcAnnualBasis = 366 Else TmpCalcAnnualBasis = 365 End If ElseIf ((EndYear - 1) = StartYear) And ((StartMonth > EndMonth) Or ((StartMonth = EndMonth) And StartDay >= EndDay)) Then If FIsLeapYear(StartYear) Then If StartMonth < 2 Or (StartMonth = 2 And StartDay <= 29) Then TmpCalcAnnualBasis = 366 Else TmpCalcAnnualBasis = 365 End If ElseIf FIsLeapYear(EndYear) Then If EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) Then TmpCalcAnnualBasis = 366 Else TmpCalcAnnualBasis = 365 End If Else TmpCalcAnnualBasis = 365 End If Else For iYear = StartYear To EndYear If FIsLeapYear(iYear) Then TmpCalcAnnualBasis = TmpCalcAnnualBasis + 366 Else TmpCalcAnnualBasis = TmpCalcAnnualBasis + 365 End If Next iYear TmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1) End If End Select End Function Public Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer) Dim nNumerator As Integer Dim nDenom As Double nNumerator = TmpDiffDates(StartDate, EndDate, Basis) nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis) TmpYearFrac = nNumerator / nDenom End Function
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]