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
更深入地说,I found an article提供了一个非常像Python的伪代码实现。由于没有时间进行测试,下面是原样的伪代码:
我发现an e-mail thread from actual Office developers提供了
YEARFRAC
算法的VBA实现。在更深入地说,I found an article提供了一个非常像Python的伪代码实现。由于没有时间进行测试,下面是原样的伪代码:
^{pr2}$相关问题 更多 >
编程相关推荐