在Python中从Excel复制YEARFRAC()函数

w9apscun  于 2023-11-20  发布在  Python
关注(0)|答案(5)|浏览(155)

所以我使用python来自动化一些我必须在excel中完成的重复任务。我需要做的一个计算需要使用yearfrac（）。这在python中被复制了吗？
我找到了this，但它与我尝试的值不正确。
(From 2011年12月19日至2017年3月31日，yearfrac（）给出5.283333333333300000，但链接中的python函数给出5.2807978099335156。

excel

5条答案

按热度按时间
zzlelutf

zzlelutf1#

我发现an e-mail thread from actual Office developers提供了YEARFRAC算法的一个可扩展实现。

  1. Public Function FIsLeapYear(Year As Integer) As Boolean
  2.     If (Year Mod 4) > 0 Then
  3.         FIsLeapYear = False
  4.     ElseIf (Year Mod 100) > 0 Then
  5.         FIsLeapYear = True
  6.     ElseIf (Year Mod 400) = 0 Then
  7.         FIsLeapYear = True
  8.     Else
  9.         FIsLeapYear = False
  10.     End If
  12. End Function
  14. Public Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As Boolean
  15.     Select Case Month
  16.         Case 1, 3, 5, 7, 8, 10, 12
  17.             FIsEndOfMonth = (Day = 31)
  18.         Case 4, 6, 9, 11
  19.             FIsEndOfMonth = (Day = 30)
  20.         Case 2
  21.             If FIsLeapYear(Year) Then
  22.                 FIsEndOfMonth = (Day = 29)
  23.             Else
  24.                 FIsEndOfMonth = (Day = 28)
  25.             End If
  26.     End Select
  28. End Function
  31. Public Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As Integer
  33.     Days360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay)
  34. End Function
  36. Public Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As Integer
  37.     Dim StartDay As Integer
  38.     Dim StartMonth As Integer
  39.     Dim StartYear As Integer
  40.     Dim EndDay As Integer
  41.     Dim EndMonth As Integer
  42.     Dim EndYear As Integer
  44.     StartDay = Day(StartDate)
  45.     StartMonth = Month(StartDate)
  46.     StartYear = Year(StartDate)
  47.     EndDay = Day(EndDate)
  48.     EndMonth = Month(EndDate)
  49.     EndYear = Year(EndDate)
  51.     If (EndMonth = 2 And FIsEndOfMonth(EndDay, EndMonth, EndYear)) And ((StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Or Method = 3) Then
  52.         EndDay = 30
  53.     End If
  54.     If EndDay = 31 And (StartDay >= 30 Or Method = 3) Then
  55.         EndDay = 30
  56.     End If
  57.     If StartDay = 31 Then
  58.        StartDay = 30
  59.     End If
  60.     If (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Then
  61.         StartDay = 30
  62.     End If
  63.     TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
  65. End Function
  67. Public Function TmpDays360Euro(StartDate As Date, EndDate As Date)
  68.     Dim StartDay As Integer
  69.     Dim StartMonth As Integer
  70.     Dim StartYear As Integer
  71.     Dim EndDay As Integer
  72.     Dim EndMonth As Integer
  73.     Dim EndYear As Integer
  75.     StartDay = Day(StartDate)
  76.     StartMonth = Month(StartDate)
  77.     StartYear = Year(StartDate)
  78.     EndDay = Day(EndDate)
  79.     EndMonth = Month(EndDate)
  80.     EndYear = Year(EndDate)
  82.     If (StartDay = 31) Then
  83.         StartDay = 30
  84.     End If
  85.     If (EndDay = 31) Then
  86.         EndDay = 30
  87.     End If
  88.     TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
  89. End Function
  91. Public Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As Integer
  93.     Select Case Basis
  94.         Case 0 'atpmBasis30360
  95.             TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True)
  97.         Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of days
  98.             TmpDiffDates = DateDiff("d", StartDate, EndDate)
  100.         Case 4 'atpmBasisE30360
  101.             TmpDiffDates = TmpDays360Euro(StartDate, EndDate)
  102.     End Select
  104. End Function
  106. Public Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As Double
  107.     Dim StartDay As Integer
  108.     Dim StartMonth As Integer
  109.     Dim StartYear As Integer
  110.     Dim EndDay As Integer
  111.     Dim EndMonth As Integer
  112.     Dim EndYear As Integer
  113.     Dim iYear As Integer
  115.     Select Case Basis
  116.         Case 0, 2, 4  'atpmBasis30360 atpmBasisActual360 atpmBasisE30360
  117.             TmpCalcAnnualBasis = 360
  118.         Case 3 'atpmBasisActual365
  119.             TmpCalcAnnualBasis = 365
  120.         Case 1 ' atpmBasisActual
  121.             StartDay = Day(StartDate)
  122.             StartMonth = Month(StartDate)
  123.             StartYear = Year(StartDate)
  124.             EndDay = Day(EndDate)
  125.             EndMonth = Month(EndDate)
  126.             EndYear = Year(EndDate)
  128.             If (StartYear = EndYear) Then
  129.                 If FIsLeapYear(StartYear) Then
  130.                     TmpCalcAnnualBasis = 366
  131.                 Else
  132.                     TmpCalcAnnualBasis = 365
  133.                 End If
  134.             ElseIf ((EndYear - 1) = StartYear) And ((StartMonth > EndMonth) Or ((StartMonth = EndMonth) And StartDay >= EndDay)) Then
  135.                 If FIsLeapYear(StartYear) Then
  136.                     If StartMonth < 2 Or (StartMonth = 2 And StartDay <= 29) Then
  137.                         TmpCalcAnnualBasis = 366
  138.                     Else
  139.                         TmpCalcAnnualBasis = 365
  140.                     End If
  141.                 ElseIf FIsLeapYear(EndYear) Then
  142.                     If EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) Then
  143.                         TmpCalcAnnualBasis = 366
  144.                     Else
  145.                         TmpCalcAnnualBasis = 365
  146.                     End If
  147.                 Else
  148.                     TmpCalcAnnualBasis = 365
  149.                 End If
  150.             Else
  151.                 For iYear = StartYear To EndYear
  152.                     If FIsLeapYear(iYear) Then
  153.                         TmpCalcAnnualBasis = TmpCalcAnnualBasis + 366
  154.                     Else
  155.                         TmpCalcAnnualBasis = TmpCalcAnnualBasis + 365
  156.                     End If
  157.                 Next iYear
  158.                 TmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1)
  160.             End If
  161.     End Select
  163. End Function
  165. Public Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer)
  166.     Dim nNumerator As Integer
  167.     Dim nDenom As Double
  169.     nNumerator = TmpDiffDates(StartDate, EndDate, Basis)
  170.     nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis)
  172.     TmpYearFrac = nNumerator / nDenom
  173. End Function
  175. =end VBA source code for YearFrac
  177. #-----------------------------------------------------------------------------
  178. # Ruby version starts here, with VBA code in comment blocks for comparison ...
  179. #-----------------------------------------------------------------------------
  181. Public Function FIsLeapYear(Year As Integer) As Boolean
  182.     If (Year Mod 4) > 0 Then
  183.         FIsLeapYear = False
  184.     ElseIf (Year Mod 100) > 0 Then
  185.         FIsLeapYear = True
  186.     ElseIf (Year Mod 400) = 0 Then
  187.         FIsLeapYear = True
  188.     Else
  189.         FIsLeapYear = False
  190.     End If
  192. End Function
  194. Public Function FIsEndOfMonth(Day As Integer, Month As Integer, Year As Integer) As Boolean
  195.     Select Case Month
  196.         Case 1, 3, 5, 7, 8, 10, 12
  197.             FIsEndOfMonth = (Day = 31)
  198.         Case 4, 6, 9, 11
  199.             FIsEndOfMonth = (Day = 30)
  200.         Case 2
  201.             If FIsLeapYear(Year) Then
  202.                 FIsEndOfMonth = (Day = 29)
  203.             Else
  204.                 FIsEndOfMonth = (Day = 28)
  205.             End If
  206.     End Select
  208. End Function
  211. Public Function Days360(StartYear As Integer, EndYear As Integer, StartMonth As Integer, EndMonth As Integer, StartDay As Integer, EndDay As Integer) As Integer
  213.     Days360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay)
  214. End Function
  216. Public Function TmpDays360Nasd(StartDate As Date, EndDate As Date, Method As Integer, UseEom As Boolean) As Integer
  217.     Dim StartDay As Integer
  218.     Dim StartMonth As Integer
  219.     Dim StartYear As Integer
  220.     Dim EndDay As Integer
  221.     Dim EndMonth As Integer
  222.     Dim EndYear As Integer
  224.     StartDay = Day(StartDate)
  225.     StartMonth = Month(StartDate)
  226.     StartYear = Year(StartDate)
  227.     EndDay = Day(EndDate)
  228.     EndMonth = Month(EndDate)
  229.     EndYear = Year(EndDate)
  231.     If (EndMonth = 2 And FIsEndOfMonth(EndDay, EndMonth, EndYear)) And ((StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Or Method = 3) Then
  232.         EndDay = 30
  233.     End If
  234.     If EndDay = 31 And (StartDay >= 30 Or Method = 3) Then
  235.         EndDay = 30
  236.     End If
  237.     If StartDay = 31 Then
  238.        StartDay = 30
  239.     End If
  240.     If (UseEom And StartMonth = 2 And FIsEndOfMonth(StartDay, StartMonth, StartYear)) Then
  241.         StartDay = 30
  242.     End If
  243.     TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
  245. End Function
  247. Public Function TmpDays360Euro(StartDate As Date, EndDate As Date)
  248.     Dim StartDay As Integer
  249.     Dim StartMonth As Integer
  250.     Dim StartYear As Integer
  251.     Dim EndDay As Integer
  252.     Dim EndMonth As Integer
  253.     Dim EndYear As Integer
  255.     StartDay = Day(StartDate)
  256.     StartMonth = Month(StartDate)
  257.     StartYear = Year(StartDate)
  258.     EndDay = Day(EndDate)
  259.     EndMonth = Month(EndDate)
  260.     EndYear = Year(EndDate)
  262.     If (StartDay = 31) Then
  263.         StartDay = 30
  264.     End If
  265.     If (EndDay = 31) Then
  266.         EndDay = 30
  267.     End If
  268.     TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
  269. End Function
  271. Public Function TmpDiffDates(StartDate As Date, EndDate As Date, Basis As Integer) As Integer
  273.     Select Case Basis
  274.         Case 0 'atpmBasis30360
  275.             TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True)
  277.         Case 1, 2, 3 'atpmBasisActual atpmBasisActual360 atpmBasisActual365 -- use actual count of days
  278.             TmpDiffDates = DateDiff("d", StartDate, EndDate)
  280.         Case 4 'atpmBasisE30360
  281.             TmpDiffDates = TmpDays360Euro(StartDate, EndDate)
  282.     End Select
  284. End Function
  286. Public Function TmpCalcAnnualBasis(StartDate As Date, EndDate As Date, Basis As Integer) As Double
  287.     Dim StartDay As Integer
  288.     Dim StartMonth As Integer
  289.     Dim StartYear As Integer
  290.     Dim EndDay As Integer
  291.     Dim EndMonth As Integer
  292.     Dim EndYear As Integer
  293.     Dim iYear As Integer
  295.     Select Case Basis
  296.         Case 0, 2, 4  'atpmBasis30360 atpmBasisActual360 atpmBasisE30360
  297.             TmpCalcAnnualBasis = 360
  298.         Case 3 'atpmBasisActual365
  299.             TmpCalcAnnualBasis = 365
  300.         Case 1 ' atpmBasisActual
  301.             StartDay = Day(StartDate)
  302.             StartMonth = Month(StartDate)
  303.             StartYear = Year(StartDate)
  304.             EndDay = Day(EndDate)
  305.             EndMonth = Month(EndDate)
  306.             EndYear = Year(EndDate)
  308.             If (StartYear = EndYear) Then
  309.                 If FIsLeapYear(StartYear) Then
  310.                     TmpCalcAnnualBasis = 366
  311.                 Else
  312.                     TmpCalcAnnualBasis = 365
  313.                 End If
  314.             ElseIf ((EndYear - 1) = StartYear) And ((StartMonth > EndMonth) Or ((StartMonth = EndMonth) And StartDay >= EndDay)) Then
  315.                 If FIsLeapYear(StartYear) Then
  316.                     If StartMonth < 2 Or (StartMonth = 2 And StartDay <= 29) Then
  317.                         TmpCalcAnnualBasis = 366
  318.                     Else
  319.                         TmpCalcAnnualBasis = 365
  320.                     End If
  321.                 ElseIf FIsLeapYear(EndYear) Then
  322.                     If EndMonth > 2 Or (EndMonth = 2 And EndDay = 29) Then
  323.                         TmpCalcAnnualBasis = 366
  324.                     Else
  325.                         TmpCalcAnnualBasis = 365
  326.                     End If
  327.                 Else
  328.                     TmpCalcAnnualBasis = 365
  329.                 End If
  330.             Else
  331.                 For iYear = StartYear To EndYear
  332.                     If FIsLeapYear(iYear) Then
  333.                         TmpCalcAnnualBasis = TmpCalcAnnualBasis + 366
  334.                     Else
  335.                         TmpCalcAnnualBasis = TmpCalcAnnualBasis + 365
  336.                     End If
  337.                 Next iYear
  338.                 TmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1)
  340.             End If
  341.     End Select
  343. End Function
  345. Public Function TmpYearFrac(StartDate As Date, EndDate As Date, Basis As Integer)
  346.     Dim nNumerator As Integer
  347.     Dim nDenom As Double
  349.     nNumerator = TmpDiffDates(StartDate, EndDate, Basis)
  350.     nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis)
  352.     TmpYearFrac = nNumerator / nDenom
  353. End Function

字符串
深入挖掘，我found an article提供了一个伪代码实现，看起来很像Python。没有时间测试它，下面是伪代码：

  1. def appears_le_year(date1, date2):
  2.     # Returns True if date1 and date2 "appear" to be 1 year or less apart.
  3.     # This compares the values of year, month, and day directly to each other.
  4.     # Requires date1 <= date2; returns boolean. Used by basis 1.
  5.     if date1.year == date2.year:
  6.         return True
  7.     if (((date1.year + 1) == date2.year) and
  8.         ((date1.month > date2.month) or
  9.         ((date1.month == date2.month) and (date1.day >= date2.day)))):
  10.         return True
  11.     return False
  13. def basis0(date1,date2):
  14.     # Swap so date1 <= date2 in all cases:
  15.     if date1 > date2:
  16.         date1, date2 = date2, date1
  17.     if date1 == date2:
  18.         return 0.0
  20.     # Change day-of-month for purposes of calculation.
  21.     date1day, date1month, date1year = date1.day, date1.month, date1.year
  22.     date2day, date2month, date2year = date2.day, date2.month, date2.year
  23.     if (date1day == 31 and date2day == 31):
  24.         date1day = 30
  25.         date2day = 30
  26.     elif date1day == 31:
  27.         date1day = 30
  28.     elif (date1day == 30 and date2day == 31):
  29.         date2day = 30
  30.     # Note: If date2day==31, it STAYS 31 if date1day < 30.
  31.     # Special fixes for February:
  32.     elif (date1month == 2 and date2month == 2 and
  33.           last_day_of_month(date1) and
  34.           last_day_of_month(date2)):
  35.         date1day = 30 # Set the day values to be equal
  36.         date2day = 30
  37.     elif date1month == 2 and last_day_of_month(date1):
  38.         date1day = 30 # "Illegal" Feb 30 date.
  40.     daydiff360 = ((date2day + date2month * 30 + date2year * 360) -
  41.                   (date1day + date1month * 30 + date1year * 360))
  42.     return daydiff360 / 360
  44. def basis1(date1,date2):
  45.     # Swap so date1 <= date2 in all cases:
  46.     if date1 > date2:
  47.         date1, date2 = date2, date1
  48.     if date1 == date2:
  49.         return 0.0
  50.     if appears_le_year(date1, date2):
  51.         if (date1.year == date2.year and is_leap_year(date1.year)):
  52.             year_length = 366.
  53.         elif (feb29_between(date1, date2) or
  54.             (date2.month == 2 and date2.day == 29)): # fixed, 2008-04-18
  55.     year_length = 366.
  56.     else:
  57.     year_length = 365.
  58.     return diffdays(date1, date2) / year_length
  59.     else:
  60.     num_years = (date2.year - date1.year) + 1
  61.     days_in_years = diffdays(date(date1.year, 1, 1), date(date2.year+1, 1, 1))
  62.     average_year_length = days_in_years / num_years
  63.     return diffdays(date1, date2) / average_year_length
  65. def basis2(date1,date2):
  66.     # Swap so date1 <= date2 in all cases:
  67.     if date1 > date2:
  68.         date1, date2 = date2, date1
  69.     return diffdays(date1, date2) / 360.
  71. def basis3(date1,date2):
  72.     # Swap so date1 <= date2 in all cases:
  73.     if date1 > date2:
  74.         date1, date2 = date2, date1
  75.     return diffdays(date1, date2) / 365.
  77. def basis4(date1,date2):
  78.     # Swap so date1 <= date2 in all cases:
  79.     if date1 > date2:
  80.         date1, date2 = date2, date1
  81.     if date1 == date2:
  82.         return 0.0
  83.     # Change day-of-month for purposes of calculation.
  84.     date1day, date1month, date1year = date1.day, date1.month, date1.year
  85.     date2day, date2month, date2year = date2.day, date2.month, date2.year
  86.     if date1day == 31:
  87.         date1day = 30
  88.     if date2day == 31:
  89.         date2day = 30
  90.     # Remarkably, do NOT change Feb. 28 or 29 at ALL.
  92.     daydiff360 = ( (date2day + date2month * 30 + date2year * 360) -
  93.     (date1day + date1month * 30 + date1year * 360))
  94.     return daydiff360 / 360

型

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
dvtswwa3

dvtswwa32#

  1. import datetime
  3. def IsLeapYear(year):
  4.     if year % 4 > 0:
  5.         IsLeapYear = False
  6.     elif year % 100 > 0:
  7.         IsLeapYear = True
  8.     elif year % 400 == 0:
  9.         IsLeapYear = True
  10.     else:
  11.         IsLeapYear = False
  12.     return IsLeapYear
  14. def IsEndOfMonth(day, month, year):
  15.     if month in [1,3,5,7,8,10,12]:
  16.         IsEndOfMonth = (day == 31)
  17.     if month in [4,6,9,11]:
  18.         IsEndOfMonth = (day == 30)
  19.     if month in [2]:
  20.         if IsLeapYear(year):
  21.             IsEndOfMonth = (day == 29)
  22.         else:
  23.             IsEndOfMonth = (day == 28)
  24.     return IsEndOfMonth
  26. def Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay):
  27.     Days360 = ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay)
  28.     return Days360
  30. def TmpDays360Nasd(StartDate, EndDate, Method, UseEom):
  31.     StartDay = StartDate.day
  32.     StartMonth = StartDate.month
  33.     StartYear = StartDate.year
  34.     EndDay = EndDate.day
  35.     EndMonth = EndDate.month
  36.     EndYear = EndDate.year
  37.     if (EndMonth == 2 and IsEndOfMonth(EndDay, EndMonth, EndYear)) and ((StartMonth == 2 and IsEndOfMonth(StartDay, StartMonth, StartYear)) or Method == 3):
  38.         EndDay = 30     
  39.     if EndDay == 31 and (StartDay >= 30 or Method == 3):
  40.         EndDay = 30
  41.     if StartDay == 31:
  42.         StartDay = 30
  43.     if (UseEom and StartMonth == 2 and IsEndOfMonth(StartDay, StartMonth, StartYear)):
  44.         StartDay = 30   
  45.     return Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
  47. def TmpDays360Euro(StartDate, EndDate):
  48.     StartDay = StartDate.day
  49.     StartMonth = StartDate.month
  50.     StartYear = StartDate.year
  51.     EndDay = EndDate.day
  52.     EndMonth = EndDate.month
  53.     EndYear = EndDate.year
  54.     if StartDay == 31:
  55.         StartDay = 30
  56.     if EndDay == 31:
  57.         EndDay = 30
  58.     return Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
  60. def DateDiff(StartDate, EndDate):
  61.     return abs((StartDate - EndDate).days)
  63. def TmpDiffDates(StartDate, EndDate, Basis):
  64.     if Basis in [0]:
  65.         TmpDiffDates = TmpDays360Nasd(StartDate, EndDate, 0, True)
  66.     elif Basis in [1,2,3]:
  67.         TmpDiffDates = DateDiff(StartDate, EndDate)
  68.     elif Basis in [4]:
  69.        TmpDiffDates = TmpDays360Euro(StartDate, EndDate)
  70.     return TmpDiffDates
  72. def TmpCalcAnnualBasis(StartDate, EndDate, Basis):
  73.     if Basis in [0,2,4]:
  74.         TmpCalcAnnualBasis = 360
  75.     elif Basis in [3]:
  76.         TmpCalcAnnualBasis = 365
  77.     elif Basis in [1]:
  78.         StartDay = StartDate.day
  79.         StartMonth = StartDate.month
  80.         StartYear = StartDate.year
  81.         EndDay = EndDate.day
  82.         EndMonth = EndDate.month
  83.         EndYear = EndDate.year
  85.         if StartYear == EndYear:
  86.             if IsLeapYear(StartYear):
  87.                 TmpCalcAnnualBasis = 366
  88.             else:
  89.                 TmpCalcAnnualBasis = 365
  90.         elif (EndYear - 1) == StartYear and (StartMonth > EndMonth or (StartMonth == EndMonth and StartDay >= EndDay)):
  91.             if IsLeapYear(StartYear):        
  92.                 if StartMonth < 2 or (StartMonth == 2 and StartDay <= 29):
  93.                     TmpCalcAnnualBasis = 366
  94.                 else:
  95.                     TmpCalcAnnualBasis = 365
  96.             elif IsLeapYear(EndYear):
  97.                 if EndMonth > 2 or (EndMonth == 2 and EndDay == 29):
  98.                     TmpCalcAnnualBasis = 366
  99.                 else:
  100.                     TmpCalcAnnualBasis = 365  
  101.             else:
  102.                 TmpCalcAnnualBasis = 365
  103.         else:
  104.             TmpCalcAnnualBasis = 0
  105.             for iYear in range(StartYear, EndYear + 1 ): 
  106.                 if IsLeapYear(iYear):
  107.                     TmpCalcAnnualBasis = TmpCalcAnnualBasis + 366
  108.                 else:
  109.                     TmpCalcAnnualBasis = TmpCalcAnnualBasis + 365
  110.             TmpCalcAnnualBasis = TmpCalcAnnualBasis / (EndYear - StartYear + 1)
  111.     return TmpCalcAnnualBasis
  113. def YearFrac(StartDate, EndDate, Basis):
  114.     Numerator = TmpDiffDates(StartDate, EndDate, Basis)
  115.     Denom = TmpCalcAnnualBasis(StartDate, EndDate, Basis)
  116.     YearFrac = Numerator/Denom
  117.     return YearFrac
  119. # TESTING    
  120. start = datetime.datetime(1978, 2, 28)
  121. end = datetime.datetime(2020, 5, 17)
  123. x0 = YearFrac(start, end, 0)
  124. x1 = YearFrac(start, end, 1)
  125. x2 = YearFrac(start, end, 2)
  126. x3 = YearFrac(start, end, 3)
  127. x4 = YearFrac(start, end, 4)

字符串

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
qybjjes1

qybjjes13#

Excel的YEARFRAC有多种模式（basis）。如果您将basis设置为0或省略，则默认使用US NASD 30/360计算。
你可以在python中这样做，因为这个公式很容易遵循下面的公式


的数据
这里有一个例子

  1. In [1]: import datetime as dt
  3. In [2]: d1 = dt.date(2015, 12, 19)
  5. In [3]: d2 = dt.date(2020, 10, 19)
  7. In [4]: def date_factor(date1, date2):
  8. ...:     """
  9. ...:     This formula follows the US (NASD) calculation
  10. ...:     that is used in Excel's YEARFRAC by default
  11. ...:     https://www.fundmanagersoftware.com/help/def_accrued_interest.html
  12. ...:     """
  13. ...:     num = (360 * (date2.year - date1.year) + (30*(date2.month - date1.month) + (date2.day - date1.day)))
  14. ...:     return num / 360
  15. ...:
  17. In [5]: date_factor(d1, d2)
  18. Out[5]: 4.833333333333333
  20. In [6]:

字符串
与Excel匹配


的

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
cyej8jka

cyej8jka4#

下面是Python代码的完整实现，来自提供的伪代码。它需要datetime库。输入是datetime对象。

  1. ### YEARFRAC() ###
  2. from datetime import datetime
  4. def appears_le_year(date1, date2):
  5.     ## Used by basis 1 ##
  6.     # Returns True if date1 and date2 "appear" to be 1 year or less apart.
  7.     # This compares the values of year, month, and day directly to each other.
  8.     # Requires date1 <= date2; returns boolean.
  9.     if date1.year == date2.year:
  10.         return True
  11.     if (((date1.year + 1) == date2.year) and
  12.         ((date1.month > date2.month) or
  13.         ((date1.month == date2.month) and (date1.day >= date2.day)))):
  14.         return True
  15.     return False
  17. def is_leap_year(year):
  18.     return year % 4 == 0 and (year % 100 != 0 or year % 400 == 0)
  20. def diffdays(date1, date2):
  21.     return abs((date2 - date1).days)
  23. def feb29_between(date1, date2):
  24.     year1 = date1.year
  25.     year2 = date2.year
  26.     if not year1 < year2:
  27.         year1, year2 = year2, year1
  28.     if year1 == year2:
  29.         if not is_leap_year(year1):
  30.             return False
  31.         else:
  32.             feb29 = datetime.strptime(str(year1) + '-02-29', '%Y-%m-%d')
  33.             return (date1 <= feb29 and date2 >= feb29)
  34.     else:
  35.         month1 = date1.month
  36.         month2 = date2.month
  37.         if month1 > 2:
  38.             if month2 < 2:
  39.                 return False
  40.             elif month2 == 2:
  41.                 return date2.day == 29
  42.             else:
  43.                 return True
  44.         else:
  45.             return is_leap_year(year1)
  47. def last_day_of_month(date):
  48.     next_month = date.replace(day=28) + timedelta(days=4)
  49.     last_day = next_month - timedelta(days=next_month.day)
  50.     return date == last_day
  52. def basis0(date1, date2): #US 30360
  53.         # Swap so date1 <= date2 in all cases:
  54.     if date1 > date2:
  55.         date1, date2 = date2, date1
  56.     if date1 == date2:
  57.         return 0.0
  59.     # Change day-of-month for purposes of calculation.
  60.     date1day, date1month, date1year = date1.day, date1.month, date1.year
  61.     date2day, date2month, date2year = date2.day, date2.month, date2.year
  62.     if (date1day == 31 and date2day == 31):
  63.         date1day = 30
  64.         date2day = 30
  65.     elif date1day == 31:
  66.         date1day = 30
  67.     elif (date1day == 30 and date2day == 31):
  68.         date2day = 30
  69.     # Note: If date2day==31, it STAYS 31 if date1day < 30.
  70.     # Special fixes for February:
  71.     elif (date1month == 2 and date2month == 2 and last_day_of_month(date1) and last_day_of_month(date2)):
  72.         date1day = 30 # Set the day values to be equal
  73.         date2day = 30
  74.     elif date1month == 2 and last_day_of_month(date1):
  75.         date1day = 30 # "Illegal" Feb 30 date.
  77.     daydiff360 = ((date2day + date2month * 30 + date2year * 360) - (date1day + date1month * 30 + date1year * 360))
  78.     return daydiff360 / 360
  80. def basis1(date1,date2): #Actual/actual
  81.     # Swap so date1 <= date2 in all cases:
  82.     if date1 > date2:
  83.         date1, date2 = date2, date1
  84.     if date1 == date2:
  85.         return 0.0
  86.     if appears_le_year(date1, date2):
  87.         if (date1.year == date2.year and is_leap_year(date1.year)):
  88.             year_length = 366.
  89.         elif (feb29_between(date1, date2) or (date2.month == 2 and date2.day == 29)): # fixed, 2008-04-18
  90.             year_length = 366.
  91.         else:
  92.             year_length = 365.
  93.         return diffdays(date1, date2) / year_length
  94.     else:
  95.         num_years = (date2.year - date1.year) + 1
  96.         days_in_years = diffdays(date(date1.year, 1, 1), date(date2.year+1, 1, 1))
  97.         average_year_length = days_in_years / num_years
  98.     return diffdays(date1, date2) / average_year_length
  100. def basis2(date1,date2): #Actual/360
  101.     # Swap so date1 <= date2 in all cases:
  102.     if date1 > date2:
  103.         date1, date2 = date2, date1
  104.     return diffdays(date1, date2) / 360.
  106. def basis3(date1,date2): #Actual/365
  107.     # Swap so date1 <= date2 in all cases:
  108.     if date1 > date2:
  109.         date1, date2 = date2, date1
  110.     return diffdays(date1, date2) / 365.
  112. def basis4(date1,date2): #EU 30360
  113.     # Swap so date1 <= date2 in all cases:
  114.     if date1 > date2:
  115.         date1, date2 = date2, date1
  116.     if date1 == date2:
  117.         return 0.0
  118.     # Change day-of-month for purposes of calculation.
  119.     date1day, date1month, date1year = date1.day, date1.month, date1.year
  120.     date2day, date2month, date2year = date2.day, date2.month, date2.year
  121.     if date1day == 31:
  122.         date1day = 30
  123.     if date2day == 31:
  124.         date2day = 30
  125.     # Remarkably, do NOT change Feb. 28 or 29 at ALL.
  127.     daydiff360 = ( (date2day + date2month * 30 + date2year * 360) - (date1day + date1month * 30 + date1year * 360))
  128.     return daydiff360 / 360

字符串
使用示例：

  1. date1 = datetime.strptime('2000-01-01', '%Y-%m-%d')
  2. date2 = datetime.strptime('2000-06-06', '%Y-%m-%d')
  3. print(basis0(date1, date2),
  4.       basis1(date1, date2),
  5.       basis2(date1, date2),
  6.       basis3(date1, date2),
  7.       basis4(date1, date2))

型

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
ar5n3qh5

ar5n3qh55#

  1. `appears_le_year <- function(date1, date2) {
  2.   if (year(date1) == year(date2)) {
  3.     return(TRUE)
  4.   }
  5.   if (((year(date1) + 1) == year(date2)) &&
  6.       ((month(date1) > month(date2)) ||
  7.        ((month(date1) == month(date2)) && (mday(date1) >= mday(date2))))) {
  8.     return(TRUE)
  9.   }
  10.   return(FALSE)
  11. }
  13. is_leap_year <- function(year) {
  14.   return(year %% 4 == 0 && (year %% 100 != 0 || year %% 400 == 0))
  15. }
  17. diffdays <- function(date1, date2) {
  18.   return(abs(as.numeric(date2 - date1, units = "days")))
  19. }
  21. feb29_between <- function(date1, date2) {
  22.   year1 <- year(date1)
  23.   year2 <- year(date2)
  24.   if (!(year1 < year2)) {
  25.     year1 <- year2
  26.     year2 <- year(date1)
  27.   }
  28.   if (year1 == year2) {
  29.     if (!is_leap_year(year1)) {
  30.       return(FALSE)
  31.     } else {
  32.       feb29 <- as.Date(paste(year1, '-02-29'))
  33.       return(date1 <= feb29 & date2 >= feb29)
  34.     }
  35.   } else {
  36.     month1 <- month(date1)
  37.     month2 <- month(date2)
  38.     if (month1 > 2) {
  39.       if (month2 < 2) {
  40.         return(FALSE)
  41.       } else if (month2 == 2) {
  42.         return(mday(date2) == 29)
  43.       } else {
  44.         return(TRUE)
  45.       }
  46.     } else {
  47.       return(is_leap_year(year1))
  48.     }
  49.   }
  50. }
  52. last_day_of_month <- function(date) {
  53.   next_month <- date + months(1)
  54.   last_day <- next_month - days(mday(next_month))
  55.   return(date == last_day)
  56. }
  58. YearFracBasis0 <- function(date1, date2) {
  59.   if (date1 > date2) {
  60.     temp <- date1
  61.     date1 <- date2
  62.     date2 <- temp
  63.   }
  64.   if (date1 == date2) {
  65.     return(0.0)
  66.   }
  67.   
  68.   date1day <- mday(date1)
  69.   date1month <- month(date1)
  70.   date1year <- year(date1)
  71.   
  72.   date2day <- mday(date2)
  73.   date2month <- month(date2)
  74.   date2year <- year(date2)
  75.   
  76.   if ((date1day == 31 & date2day == 31)) {
  77.     date1day <- 30
  78.     date2day <- 30
  79.   } else if (date1day == 31) {
  80.     date1day <- 30
  81.   } else if ((date1day == 30 & date2day == 31)) {
  82.     date2day <- 30
  83.   }
  84.   
  85.   if ((month(date1) == 2 & month(date2) == 2 & last_day_of_month(date1) & last_day_of_month(date2))) {
  86.     date1day <- 30
  87.     date2day <- 30
  88.   } else if (month(date1) == 2 & last_day_of_month(date1)) {
  89.     date1day <- 30
  90.   }
  91.   
  92.   daydiff360 <- ((date2day + date2month * 30 + date2year * 360) - 
  93.                    (date1day + date1month * 30 + date1year * 360))
  94.   
  95.   return(daydiff360 / 360)
  96. }
  98. YearFracBasis1 <- function(date1, date2) {
  99.   if (date1 > date2) {
  100.     temp <- date1
  101.     date1 <- date2
  102.     date2 <- temp
  103.   }
  104.   if (date1 == date2) {
  105.     return(0.0)
  106.   }
  107.   
  108.   if (appears_le_year(date1, date2)) {
  109.     if ((year(date1) == year(date2) & is_leap_year(year(date1)))) {
  110.       year_length <- 366
  111.     } else if (feb29_between(date1, date2) || (month(date2) == 2 & mday(date2) == 29)) {
  112.       year_length <- 366
  113.     } else {
  114.       year_length <- 365
  115.     }
  116.     return(diffdays(date1, date2) / year_length)
  117.   } else {
  118.     num_years <- (year(date2) - year(date1)) + 1
  119.     days_in_years <- diffdays(as.Date(paste(year(date1), '01-01')), as.Date(paste(year(date2) + 1, '01-01')))
  120.     average_year_length <- days_in_years / num_years
  121.     return(diffdays(date1, date2) / average_year_length)
  122.   }
  123. }
  125. YearFracBasis2 <- function(date1, date2) {
  126.   if (date1 > date2) {
  127.     temp <- date1
  128.     date1 <- date2
  129.     date2 <- temp
  130.   }
  131.   return(diffdays(date1, date2) / 360)
  132. }
  134. YearFracBasis3 <- function(date1, date2) {
  135.   if (date1 > date2) {
  136.     temp <- date1
  137.     date1 <- date2
  138.     date2 <- temp
  139.   }
  140.   return(diffdays(date1, date2) / 365)
  141. }
  143. YearFracBasis4 <- function(date1, date2) {
  144.   if (date1 > date2) {
  145.     temp <- date1
  146.     date1 <- date2
  147.     date2 <- temp
  148.   }
  149.   if (date1 == date2) {
  150.     return(0.0)
  151.   }
  152.   
  153.   date1day <- mday(date1)
  154.   date1month <- month(date1)
  155.   date1year <- year(date1)
  156.   
  157.   date2day <- mday(date2)
  158.   date2month <- month(date2)
  159.   date2year <- year(date2)
  160.   
  161.   if (date1day == 31) {
  162.     date1day <- 30
  163.   }
  164.   if (date2day == 31) {
  165.     date2day <- 30
  166.   }
  167.   
  168.   daydiff360 <- ((date2day + date2month * 30 + date2year * 360) - 
  169.                    (date1day + date1month * 30 + date1year * 360))
  170.   
  171.   return(daydiff360 / 360)
  172. }`

字符串

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com