在c#中模拟Excel's YearFrac
本文关键字：YearFrac Excel 模拟 | 更新日期: 2023-09-27 18:18:13
如何在我的c#应用程序中获得Excel YearFrac函数的相同结果?
计算两个日期(start_date和end_date)之间的天数所表示的年份的百分比。使用YEARFRAC工作表函数确定要分配给特定期限的全年利益或义务的比例。
这是一个很好的片段。
YearFrac函数的算法实际上非常复杂。
可以直接使用Excel的功能来计算YearFrac。微软说你不应该使用它，但它运行得很好。如果你需要与Excel 100%兼容，这个解决方案是很难打败的。您需要在您的项目中添加对Microsoft.Office.Interop.Excel的引用，以便编译此代码。
static void Main() {
var excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.WorksheetFunction wsf = excel.WorksheetFunction;
var start = new DateTime(1999, 11, 1);
var end = new DateTime(1999, 1, 11);
for (var basis = 0; basis != 5; basis++) {
Console.WriteLine(wsf.YearFrac(start, end, basis));
}
}
YEARFRAC的签名是YEARFRAC(Date startDate, Date endDate, int convention)。计算YEARFRAC的方法取决于约定。
对于约定= 2,YEARFRAC将使用ACT/360方法计算YEARFRAC。ACT/360的实现可以在svn.finmath.net找到，具体是DayCountConvention_ACT_360.java
对于convention = 3, YEARFRAC将使用ACT/365方法计算YEARFRAC。具体来说，ACT/365的实现可以在svn.finmath.net上找到DayCountConvention_ACT_365.java
对于约定= 4,YEARFRAC将使用30E/360方法计算YEARFRAC。30E/360的实现可以在svn.finmath.net找到，特别是DayCountConvention_30E_360.java
对于convention = 1，文档声明使用ACT/ACT约定计算YEARFRAC。然而，ACT/ACT有多个版本，我相信许多金融产品的标准是ACT/ACT ISDA。我发现YEARFRAC与ACT/ACT IDSA公约有一点不同!ACT/ACT IDSA的实现可以在DayCountConvention_ACT_ACT_ISDA.java
找到。我还没有检查其他act/act版本，但我不会依赖于模拟YEARFRAC act/act，当它不清楚他们实现什么样的方法…
我建议:
public static double Yearfrac(DateTime startDate,DateTime endDate,DayCount daycount=DayCount.ActAct)
{
var nbDaysInPeriod = (double)(endDate - startDate).Days;
switch(daycount)
{
case (DayCount.Act360):
return nbDaysInPeriod / (double)360;
case (DayCount.Act365):
return nbDaysInPeriod / (double)365;
case (DayCount.ActAct):
return GetActAct(startDate,endDate);
case (DayCount.Days360):
var result = (endDate.Year - startDate.Year) * 360.0 + (endDate.Month - startDate.Month) * 30.0 + (Math.Min(endDate.Day, 30.0) - Math.Min(startDate.Day, 30.0));
return result/360;
default:
return nbDaysInPeriod / (double)365;
}
}
public static double GetActAct(DateTime startDate, DateTime endDate)
{
// Reproduce Excel Yearfrac as per http://www.dwheeler.com/yearfrac/excel-ooxml-yearfrac.pdf
var nbDaysInPeriod = (double)(endDate - startDate).Days;
if(startDate.Year==endDate.Year || (endDate.Year-1==startDate.Year&&(startDate.Month>endDate.Month||startDate.Month==endDate.Month&&(startDate.Day>=endDate.Day))))
{
var den = 365.0;
if (startDate.Year == endDate.Year && DateTime.IsLeapYear(startDate.Year))
{
den++;
}
else
{
if (endDate.Day == 29 && endDate.Month == 2)
{
den++;
}
else
{
if (DateTime.IsLeapYear(startDate.Year))
{
var feb = new DateTime(startDate.Year, 2, 29);
if (startDate<=feb && feb<=endDate) den++;
}
else
{
if (DateTime.IsLeapYear(endDate.Year))
{
var feb = new DateTime(endDate.Year, 2, 29);
if (startDate <= feb && feb <= endDate) den++;
}
}
}
}
}
else
{
var nbYears = endDate.Year - startDate.Year+1;
var den = nbYears * 365.0;
for (var i=0;i<nbYears;i++)
{
if (DateTime.IsLeapYear(startDate.Year + i)) den++;
}
den /= nbYears;
return nbDaysInPeriod / den;
}
return nbDaysInPeriod / 365.0;
}
如果你想要一个精确的YEARFRAC，测量到一天(所以不需要指定任何日计数约定)，并给出一个负数的时期在过去尝试这个日期扩展…
public static double YearFrac(this DateTime startDate, DateTime endDate)
{
//---------------------------------------------------------------------------
startDate = DateTime.Parse(startDate.ToString("dd-MMM-yyyy"));
endDate = DateTime.Parse(endDate.ToString("dd-MMM-yyyy"));
//---------------------------------------------------------------------------
if (startDate == endDate)
return 0.0;
//---------------------------------------------------------------------------
double reverse = 1.0;
//---------------------------------------------------------------------------
if (startDate > endDate)
{
var ed = endDate;
endDate = startDate;
startDate = ed;
reverse = -1.0;
}
//---------------------------------------------------------------------------
int y1 = startDate.Year;
int y2 = endDate.Year;
int m1 = startDate.Month;
int m2 = endDate.Month;
int d1 = startDate.Day;
int d2 = endDate.Day;
int diy = startDate.DaysInYear();
int days = (endDate - startDate).Days;
//---------------------------------------------------------------------------
if (y1 == y2)
{
return (double)days / (double)diy * reverse;
}
//---------------------------------------------------------------------------
int wholeyears = y2 - y1 - 1;
DateTime lastDateA = ValidDate(y2 - 1, m2, d2);
DateTime lastDateB = ValidDate(y2 - 1, m1, d1);
int period1 = (endDate - lastDateA).Days;
int period2 = (endDate - lastDateB).Days;
//---------------------------------------------------------------------------
if (m1 > m2 || (m1 == m2 && d1 > d2))
{
return ((double)wholeyears + (double)period2 / (double)period1) * reverse;
}
//---------------------------------------------------------------------------
DateTime lastDateC = ValidDate(y2, m1, d1);
int period3 = (endDate - lastDateC).Days;
//---------------------------------------------------------------------------
return ((double)wholeyears + 1.0 + (double)period3 / (double)period1) * reverse;
//---------------------------------------------------------------------------
}
public static DateTime ValidDate(int y, int m, int d)
{
try
{
DateTime dt1 = new DateTime(y, m, d);
return dt1;
}
catch (Exception)
{
try
{
if (d == 29 && m == 2)
{
return new DateTime(y, 3, 1);
}
return new DateTime(y, m, d);
}
catch (Exception)
{
throw;
}
}
}
public static int DaysInYear(this DateTime date)
{
int year = date.Year;
DateTime d1 = DateTime.Parse("01-Jan-" + year.ToString());
DateTime d2 = DateTime.Parse("01-Jan-" + (year + 1).ToString());
int diy = (d2 - d1).Days;
return diy;
}