在Python中从Excel复制YEARFRAC（）函数 [英] Replicating YEARFRAC() function from Excel in Python
本文介绍了在Python中从Excel复制YEARFRAC（）函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
所以我正在使用python来自动化我必须在excel中执行的一些重复性任务。我需要做的其中一项计算需要使用yearfrac（）。
So I am using python in order to automate some repetitive tasks I must do in excel. One of the calculations I need to do requires the use of yearfrac(). Has this been replicated in python?
我发现此
，但对于我尝试的值来说是错误的。
I found this but it is incorrect for the value I tried.
（从2011年12月19日至3 / 31/17，yearfrac（）给出5.2833333333333300000，但是链接中的python函数给出5.2807978099335156。）
(From 12/19/2011 to 3/31/17, yearfrac() gives 5.2833333333333300000, but the python function in the link gives 5.2807978099335156.)
推荐答案
我发现<一个href = https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html rel = noreferrer>来自实际Office开发人员的电子邮件线程提供了VBA YEARFRAC 算法的实现。
I found an e-mail thread from actual Office developers providing a VBA implementation of the 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
深入研究，我找到了一篇文章，该文章提供了一个看起来很像Python的伪代码实现。没有时间对其进行测试，下面是按原样编写的伪代码：
Digging deeper, I found an article that provides a pseudocode implementation that looks an awful lot like Python. Not having the time to test it, below is the pseudocode as is:
def appears_le_year(date1, date2):
# Returns True if date1 and date2 "appear" to be 1 year or less apart.
# This compares the values of year, month, and day directly to each other.
# Requires date1 <= date2; returns boolean. Used by basis 1.
if date1.year == date2.year:
return True
if (((date1.year + 1) == date2.year) and
((date1.month > date2.month) or
((date1.month == date2.month) and (date1.day >= date2.day)))):
return True
return False
def basis0(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
if date1 == date2:
return 0.0
# Change day-of-month for purposes of calculation.
date1day, date1month, date1year = date1.day, date1.month, date1.year
date2day, date2month, date2year = date2.day, date2.month, date2.year
if (date1day == 31 and date2day == 31):
date1day = 30
date2day = 30
elif date1day == 31:
date1day = 30
elif (date1day == 30 and date2day == 31):
date2day = 30
# Note: If date2day==31, it STAYS 31 if date1day < 30.
# Special fixes for February:
elif (date1month == 2 and date2month == 2 and
last_day_of_month(date1) and
last_day_of_month(date2)):
date1day = 30 # Set the day values to be equal
date2day = 30
elif date1month == 2 and last_day_of_month(date1):
date1day = 30 # "Illegal" Feb 30 date.
daydiff360 = ((date2day + date2month * 30 + date2year * 360) -
(date1day + date1month * 30 + date1year * 360))
return daydiff360 / 360
def basis1(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
if date1 == date2:
return 0.0
if appears_le_year(date1, date2):
if (date1.year == date2.year and is_leap_year(date1.year)):
year_length = 366.
elif (feb29_between(date1, date2) or
(date2.month == 2 and date2.day == 29)): # fixed, 2008-04-18
year_length = 366.
else:
year_length = 365.
return diffdays(date1, date2) / year_length
else:
num_years = (date2.year - date1.year) + 1
days_in_years = diffdays(date(date1.year, 1, 1), date(date2.year+1, 1, 1))
average_year_length = days_in_years / num_years
return diffdays(date1, date2) / average_year_length
def basis2(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
return diffdays(date1, date2) / 360.
def basis3(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
return diffdays(date1, date2) / 365.
def basis4(date1,date2):
# Swap so date1 <= date2 in all cases:
if date1 > date2:
date1, date2 = date2, date1
if date1 == date2:
return 0.0
# Change day-of-month for purposes of calculation.
date1day, date1month, date1year = date1.day, date1.month, date1.year
date2day, date2month, date2year = date2.day, date2.month, date2.year
if date1day == 31:
date1day = 30
if date2day == 31:
date2day = 30
# Remarkably, do NOT change Feb. 28 or 29 at ALL.
daydiff360 = ( (date2day + date2month * 30 + date2year * 360) -
(date1day + date1month * 30 + date1year * 360))
return daydiff360 / 360
这篇关于在Python中从Excel复制YEARFRAC（）函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
