Question 1

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.

(From 12/19/2011 to 3/31/17, yearfrac() gives 5.2833333333333300000, but the python function in the link gives 5.2807978099335156.)

Question 2

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 BooleanIf (Year Mod 4) > 0 ThenFIsLeapYear = FalseElseIf (Year Mod 100) > 0 ThenFIsLeapYear = TrueElseIf (Year Mod 400) = 0 ThenFIsLeapYear = TrueElseFIsLeapYear = FalseEnd IfEnd FunctionPublic Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As BooleanSelect Case MonthCase 1, 3, 5, 7, 8, 10, 12FIsEndOfMonth = (Day = 31)Case 4, 6, 9, 11FIsEndOfMonth = (Day = 30)Case 2If FIsLeapYear(Year) ThenFIsEndOfMonth = (Day = 29)ElseFIsEndOfMonth = (Day = 28)End IfEnd SelectEnd FunctionPublic Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As IntegerDays360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay)
End FunctionPublic Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As IntegerDim StartDay As IntegerDim StartMonth As IntegerDim StartYear As IntegerDim EndDay As IntegerDim EndMonth As IntegerDim EndYear As IntegerStartDay = 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) ThenEndDay = 30End IfIf EndDay = 31 And (StartDay >= 30 Or Method = 3) ThenEndDay = 30End IfIf StartDay = 31 ThenStartDay = 30End IfIf (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) ThenStartDay = 30End IfTmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)End FunctionPublic Function TmpDays360Euro(StartDate As Date, EndDate As Date)Dim StartDay As IntegerDim StartMonth As IntegerDim StartYear As IntegerDim EndDay As IntegerDim EndMonth As IntegerDim EndYear As IntegerStartDay = Day(StartDate)StartMonth = Month(StartDate)StartYear = Year(StartDate)EndDay = Day(EndDate)EndMonth = Month(EndDate)EndYear = Year(EndDate)If (StartDay = 31) ThenStartDay = 30End IfIf (EndDay = 31) ThenEndDay = 30End IfTmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
End FunctionPublic Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As IntegerSelect Case BasisCase 0 'atpmBasis30360TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True)Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of daysTmpDiffDates = DateDiff("d", StartDate, EndDate)Case 4 'atpmBasisE30360TmpDiffDates = TmpDays360Euro(StartDate, EndDate)End SelectEnd FunctionPublic Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As DoubleDim StartDay As IntegerDim StartMonth As IntegerDim StartYear As IntegerDim EndDay As IntegerDim EndMonth As IntegerDim EndYear As IntegerDim iYear As IntegerSelect Case BasisCase 0, 2, 4  'atpmBasis30360 atpmBasisActual360 atpmBasisE30360TmpCalcAnnualBasis = 360Case 3 'atpmBasisActual365TmpCalcAnnualBasis = 365Case 1 ' atpmBasisActualStartDay = Day(StartDate)StartMonth = Month(StartDate)StartYear = Year(StartDate)EndDay = Day(EndDate)EndMonth = Month(EndDate)EndYear = Year(EndDate)If (StartYear = EndYear) ThenIf FIsLeapYear(StartYear) ThenTmpCalcAnnualBasis = 366ElseTmpCalcAnnualBasis = 365End IfElseIf ((EndYear - 1) = StartYear) And ((StartMonth > EndMonth) Or ((StartMonth = EndMonth) And StartDay >= EndDay)) ThenIf FIsLeapYear(StartYear) ThenIf StartMonth < 2 Or (StartMonth = 2 And StartDay <= 29) ThenTmpCalcAnnualBasis = 366ElseTmpCalcAnnualBasis = 365End IfElseIf FIsLeapYear(EndYear) ThenIf EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) ThenTmpCalcAnnualBasis = 366ElseTmpCalcAnnualBasis = 365End IfElseTmpCalcAnnualBasis = 365End IfElseFor iYear = StartYear To EndYearIf FIsLeapYear(iYear) ThenTmpCalcAnnualBasis = TmpCalcAnnualBasis + 366ElseTmpCalcAnnualBasis = TmpCalcAnnualBasis + 365End IfNext iYearTmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1)End IfEnd SelectEnd FunctionPublic Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer)Dim nNumerator As IntegerDim nDenom As DoublenNumerator = 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 BooleanIf (Year Mod 4) > 0 ThenFIsLeapYear = FalseElseIf (Year Mod 100) > 0 ThenFIsLeapYear = TrueElseIf (Year Mod 400) = 0 ThenFIsLeapYear = TrueElseFIsLeapYear = FalseEnd IfEnd FunctionPublic Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As BooleanSelect Case MonthCase 1, 3, 5, 7, 8, 10, 12FIsEndOfMonth = (Day = 31)Case 4, 6, 9, 11FIsEndOfMonth = (Day = 30)Case 2If FIsLeapYear(Year) ThenFIsEndOfMonth = (Day = 29)ElseFIsEndOfMonth = (Day = 28)End IfEnd SelectEnd FunctionPublic Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As IntegerDays360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay)
End FunctionPublic Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As IntegerDim StartDay As IntegerDim StartMonth As IntegerDim StartYear As IntegerDim EndDay As IntegerDim EndMonth As IntegerDim EndYear As IntegerStartDay = 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) ThenEndDay = 30End IfIf EndDay = 31 And (StartDay >= 30 Or Method = 3) ThenEndDay = 30End IfIf StartDay = 31 ThenStartDay = 30End IfIf (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) ThenStartDay = 30End IfTmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)End FunctionPublic Function TmpDays360Euro(StartDate As Date, EndDate As Date)Dim StartDay As IntegerDim StartMonth As IntegerDim StartYear As IntegerDim EndDay As IntegerDim EndMonth As IntegerDim EndYear As IntegerStartDay = Day(StartDate)StartMonth = Month(StartDate)StartYear = Year(StartDate)EndDay = Day(EndDate)EndMonth = Month(EndDate)EndYear = Year(EndDate)If (StartDay = 31) ThenStartDay = 30End IfIf (EndDay = 31) ThenEndDay = 30End IfTmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
End FunctionPublic Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As IntegerSelect Case BasisCase 0 'atpmBasis30360TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True)Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of daysTmpDiffDates = DateDiff("d", StartDate, EndDate)Case 4 'atpmBasisE30360TmpDiffDates = TmpDays360Euro(StartDate, EndDate)End SelectEnd FunctionPublic Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As DoubleDim StartDay As IntegerDim StartMonth As IntegerDim StartYear As IntegerDim EndDay As IntegerDim EndMonth As IntegerDim EndYear As IntegerDim iYear As IntegerSelect Case BasisCase 0, 2, 4  'atpmBasis30360 atpmBasisActual360 atpmBasisE30360TmpCalcAnnualBasis = 360Case 3 'atpmBasisActual365TmpCalcAnnualBasis = 365Case 1 ' atpmBasisActualStartDay = Day(StartDate)StartMonth = Month(StartDate)StartYear = Year(StartDate)EndDay = Day(EndDate)EndMonth = Month(EndDate)EndYear = Year(EndDate)If (StartYear = EndYear) ThenIf FIsLeapYear(StartYear) ThenTmpCalcAnnualBasis = 366ElseTmpCalcAnnualBasis = 365End IfElseIf ((EndYear - 1) = StartYear) And ((StartMonth > EndMonth) Or ((StartMonth = EndMonth) And StartDay >= EndDay)) ThenIf FIsLeapYear(StartYear) ThenIf StartMonth < 2 Or (StartMonth = 2 And StartDay <= 29) ThenTmpCalcAnnualBasis = 366ElseTmpCalcAnnualBasis = 365End IfElseIf FIsLeapYear(EndYear) ThenIf EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) ThenTmpCalcAnnualBasis = 366ElseTmpCalcAnnualBasis = 365End IfElseTmpCalcAnnualBasis = 365End IfElseFor iYear = StartYear To EndYearIf FIsLeapYear(iYear) ThenTmpCalcAnnualBasis = TmpCalcAnnualBasis + 366ElseTmpCalcAnnualBasis = TmpCalcAnnualBasis + 365End IfNext iYearTmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1)End IfEnd SelectEnd FunctionPublic Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer)Dim nNumerator As IntegerDim nDenom As DoublenNumerator = TmpDiffDates(StartDate, EndDate, Basis)nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis)TmpYearFrac = nNumerator / nDenom
End Function

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 Trueif (((date1.year + 1) == date2.year) and((date1.month > date2.month) or((date1.month == date2.month) and (date1.day >= date2.day)))):return Truereturn Falsedef basis0(date1,date2):# Swap so date1 <= date2 in all cases:if date1 > date2:date1, date2 = date2, date1if date1 == date2:return 0.0# Change day-of-month for purposes of calculation.date1day, date1month, date1year = date1.day, date1.month, date1.yeardate2day, date2month, date2year = date2.day, date2.month, date2.yearif (date1day == 31 and date2day == 31):date1day = 30date2day = 30elif date1day == 31:date1day = 30elif (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 andlast_day_of_month(date1) andlast_day_of_month(date2)):date1day = 30 # Set the day values to be equaldate2day = 30elif 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 / 360def basis1(date1,date2):# Swap so date1 <= date2 in all cases:if date1 > date2:date1, date2 = date2, date1if date1 == date2:return 0.0if 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-18year_length = 366.else:year_length = 365.return diffdays(date1, date2) / year_lengthelse:num_years = (date2.year - date1.year) + 1days_in_years = diffdays(date(date1.year, 1, 1), date(date2.year+1, 1, 1))average_year_length = days_in_years / num_yearsreturn diffdays(date1, date2) / average_year_lengthdef basis2(date1,date2):# Swap so date1 <= date2 in all cases:if date1 > date2:date1, date2 = date2, date1return diffdays(date1, date2) / 360.def basis3(date1,date2):# Swap so date1 <= date2 in all cases:if date1 > date2:date1, date2 = date2, date1return diffdays(date1, date2) / 365.def basis4(date1,date2):# Swap so date1 <= date2 in all cases:if date1 > date2:date1, date2 = date2, date1if date1 == date2:return 0.0# Change day-of-month for purposes of calculation.date1day, date1month, date1year = date1.day, date1.month, date1.yeardate2day, date2month, date2year = date2.day, date2.month, date2.yearif date1day == 31:date1day = 30if 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

Replicating YEARFRAC() function from Excel in Python

Related Q&A

creating a pandas dataframe from a database query that uses bind variables

Is there a docstring autocompletion tool for jupyter notebook?

Long to wide data. Pandas

How to wrap text in Django admin(set column width)

Problems compiling mod_wsgi in virtualenv

Python - Multiprocessing Error cannot start a process twice

Printing unicode number of chars in a string (Python)

Pandas report top-n in group and pivot

virtualenv --no-site-packages is not working for me

pandas: Group by splitting string value in all rows (a column) and aggregation function