TC ArchivesMailing list preservation
← Prev in month ← Prev in thread
Next in thread → Next in month →

Excel's YEARFRAC algorithm

From
Doug Mahugh <>
Date
2008-06-12T14:01:38+00:00
ID
Thread
Excel's YEARFRAC algorithm
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
View original preserved HTML → View on OASIS Connected Community →
← Prev in month ← Prev in thread
Next in thread → Next in month →