Репликация функции YEARFRAC() из Excel в Python
Поэтому я использую python для автоматизации некоторых повторяющихся задач, которые я должен выполнять в excel. Одно из вычислений, которое мне нужно сделать, требует использования yearfrac (). Это было воспроизведено в python?
Я нашел это но это неверно для значения, которое я пробовал.
(С 19.12.2011 по 31.3.17, yearfrac () дает 5.2833333333333300000, но функция python в ссылке дает 5.2807978099335156.)
1 ответ:
Я нашел поток электронной почты от реальных разработчиков Office, предоставляющий реализацию VBA алгоритма
YEARFRAC
.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. Не имея времени проверить его, ниже приведен псевдокод как есть:
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