455 lines
18 KiB
C#
455 lines
18 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class YieldCurveDA
|
|
{
|
|
private YieldCurveDA()
|
|
{
|
|
}
|
|
private static bool DeleteYieldCurve(YieldCurve yieldCurve, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
|
|
{
|
|
String strQuery = null;
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("delete from yieldcurve ");
|
|
sb.Append("where date in ").Append(SqlUtils.CreateInClauseYear(yieldCurve.GetDistinctDates()));
|
|
strQuery = sb.ToString();
|
|
MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
sqlCommand.Dispose();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
}
|
|
}
|
|
public static bool InsertOrUpdate(YieldCurve yieldCurve)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (null == yieldCurve || 0 == yieldCurve.Count) return false;
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
DeleteYieldCurve(yieldCurve,sqlConnection,sqlTransaction);
|
|
DateTime modified = DateTime.Now;
|
|
for (int index = 0; index < yieldCurve.Count; index++)
|
|
{
|
|
YieldCurveData yieldCurveData = yieldCurve[index];
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into yieldcurve(date,1mo,3mo,6mo,1yr,2yr,3yr,5yr,7yr,10yr,20yr,30yr,modified)values(");
|
|
sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(yieldCurveData.Date))).Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Mo1))sb.Append(yieldCurveData.Mo1).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Mo3))sb.Append(yieldCurveData.Mo3).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Mo3))sb.Append(yieldCurveData.Mo6).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr1))sb.Append(yieldCurveData.Yr1).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr2))sb.Append(yieldCurveData.Yr2).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr3))sb.Append(yieldCurveData.Yr3).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr5))sb.Append(yieldCurveData.Yr5).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr7))sb.Append(yieldCurveData.Yr7).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr10))sb.Append(yieldCurveData.Yr10).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr20))sb.Append(yieldCurveData.Yr20).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if(!double.IsNaN(yieldCurveData.Yr30))sb.Append(yieldCurveData.Yr30).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(modified)));
|
|
sb.Append(")");
|
|
strQuery = sb.ToString();
|
|
MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
sqlCommand.Dispose();
|
|
}
|
|
sqlTransaction.Commit();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if (null != sqlTransaction) sqlTransaction.Dispose();
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static YieldCurve GetYieldCurve(DateTime startDate, int days)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
YieldCurve yieldCurve = new YieldCurve();
|
|
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
DateGenerator dateGenerator = new DateGenerator();
|
|
DateTime maxHistoricalDate = dateGenerator.GenerateHistoricalDate(startDate, days * 2);
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date,1mo,3mo,6mo,1yr,2yr,3yr,5yr,7yr,10yr,20yr,30yr from yieldcurve ");
|
|
sb.Append("where date<='").Append(Utility.DateTimeToStringYYYYHMMHDD(startDate)).Append("' ");
|
|
sb.Append("and date>='").Append(Utility.DateTimeToStringYYYYHMMHDD(maxHistoricalDate)).Append("' ");
|
|
sb.Append(" order by date desc");
|
|
sb.Append(" limit ").Append(days);
|
|
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while(sqlDataReader.Read())
|
|
{
|
|
YieldCurveData yieldCurveData = new YieldCurveData();
|
|
yieldCurveData.Date = sqlDataReader.GetDateTime(0);
|
|
yieldCurveData.Mo1 = sqlDataReader.GetDouble(1);
|
|
yieldCurveData.Mo3 = sqlDataReader.GetDouble(2);
|
|
yieldCurveData.Mo6 = sqlDataReader.GetDouble(3);
|
|
yieldCurveData.Yr1 = sqlDataReader.GetDouble(4);
|
|
yieldCurveData.Yr2 = sqlDataReader.GetDouble(5);
|
|
yieldCurveData.Yr3 = sqlDataReader.GetDouble(6);
|
|
yieldCurveData.Yr5 = sqlDataReader.GetDouble(7);
|
|
yieldCurveData.Yr7 = sqlDataReader.GetDouble(8);
|
|
yieldCurveData.Yr10 = sqlDataReader.GetDouble(9);
|
|
yieldCurveData.Yr20 = sqlDataReader.GetDouble(10);
|
|
yieldCurveData.Yr30 = sqlDataReader.GetDouble(11);
|
|
yieldCurve.Add(yieldCurveData);
|
|
}
|
|
return yieldCurve;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static YieldCurveData GetYieldCurve(DateTime effectiveDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
YieldCurveData yieldCurveData = null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date,1mo,3mo,6mo,1yr,2yr,3yr,5yr,7yr,10yr,20yr,30yr from yieldcurve ");
|
|
sb.Append("where date='").Append(Utility.DateTimeToStringYYYYHMMHDD(effectiveDate)).Append("' ");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read())return null;
|
|
yieldCurveData = new YieldCurveData();
|
|
yieldCurveData.Date = sqlDataReader.GetDateTime(0);
|
|
yieldCurveData.Mo1 = sqlDataReader.GetDouble(1);
|
|
yieldCurveData.Mo3 = sqlDataReader.GetDouble(2);
|
|
yieldCurveData.Mo6 = sqlDataReader.GetDouble(3);
|
|
yieldCurveData.Yr1 = sqlDataReader.GetDouble(4);
|
|
yieldCurveData.Yr2 = sqlDataReader.GetDouble(5);
|
|
yieldCurveData.Yr3 = sqlDataReader.GetDouble(6);
|
|
yieldCurveData.Yr5 = sqlDataReader.GetDouble(7);
|
|
yieldCurveData.Yr7 = sqlDataReader.GetDouble(8);
|
|
yieldCurveData.Yr10 = sqlDataReader.GetDouble(9);
|
|
yieldCurveData.Yr20 = sqlDataReader.GetDouble(10);
|
|
yieldCurveData.Yr30 = sqlDataReader.GetDouble(11);
|
|
return yieldCurveData;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static YieldCurve GetYieldCurve(int dayCount)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
YieldCurve yieldCurve = new YieldCurve();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date,1mo,3mo,6mo,1yr,2yr,3yr,5yr,7yr,10yr,20yr,30yr from yieldcurve ");
|
|
sb.Append(" order by date desc");
|
|
sb.Append(" limit ").Append(dayCount);
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
YieldCurveData yieldCurveData = new YieldCurveData();
|
|
yieldCurveData.Date = sqlDataReader.GetDateTime(0);
|
|
yieldCurveData.Mo1 = sqlDataReader.GetDouble(1);
|
|
yieldCurveData.Mo3 = sqlDataReader.GetDouble(2);
|
|
yieldCurveData.Mo6 = sqlDataReader.GetDouble(3);
|
|
yieldCurveData.Yr1 = sqlDataReader.GetDouble(4);
|
|
yieldCurveData.Yr2 = sqlDataReader.GetDouble(5);
|
|
yieldCurveData.Yr3 = sqlDataReader.GetDouble(6);
|
|
yieldCurveData.Yr5 = sqlDataReader.GetDouble(7);
|
|
yieldCurveData.Yr7 = sqlDataReader.GetDouble(8);
|
|
yieldCurveData.Yr10 = sqlDataReader.GetDouble(9);
|
|
yieldCurveData.Yr20 = sqlDataReader.GetDouble(10);
|
|
yieldCurveData.Yr30 = sqlDataReader.GetDouble(11);
|
|
yieldCurve.Add(yieldCurveData);
|
|
}
|
|
return yieldCurve;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static YieldCurve GetYieldCurve()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
YieldCurve yieldCurve = new YieldCurve();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date,1mo,3mo,6mo,1yr,2yr,3yr,5yr,7yr,10yr,20yr,30yr from yieldcurve ");
|
|
sb.Append(" order by date desc");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
YieldCurveData yieldCurveData = new YieldCurveData();
|
|
yieldCurveData.Date=sqlDataReader.GetDateTime(0);
|
|
yieldCurveData.Mo1=sqlDataReader.GetDouble(1);
|
|
yieldCurveData.Mo3=sqlDataReader.GetDouble(2);
|
|
yieldCurveData.Mo6=sqlDataReader.GetDouble(3);
|
|
yieldCurveData.Yr1=sqlDataReader.GetDouble(4);
|
|
yieldCurveData.Yr2=sqlDataReader.GetDouble(5);
|
|
yieldCurveData.Yr3=sqlDataReader.GetDouble(6);
|
|
yieldCurveData.Yr5=sqlDataReader.GetDouble(7);
|
|
yieldCurveData.Yr7=sqlDataReader.GetDouble(8);
|
|
yieldCurveData.Yr10=sqlDataReader.GetDouble(9);
|
|
yieldCurveData.Yr20=sqlDataReader.GetDouble(10);
|
|
yieldCurveData.Yr30 = sqlDataReader.GetDouble(11);
|
|
yieldCurve.Add(yieldCurveData);
|
|
}
|
|
return yieldCurve;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null != sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static double GetRiskFreeRate1Yr(DateTime effectiveDate,int lookAhead)
|
|
{
|
|
DateGenerator dateGenerator=new DateGenerator();
|
|
YieldCurveData yieldCurveData=null;
|
|
for(int index=0;index<lookAhead;index++)
|
|
{
|
|
yieldCurveData=GetYieldCurve(effectiveDate);
|
|
if(null!=yieldCurveData)break;
|
|
effectiveDate=dateGenerator.FindNextBusinessDay(effectiveDate);
|
|
}
|
|
if(null==yieldCurveData)return double.NaN;
|
|
return yieldCurveData.Yr1;
|
|
}
|
|
public static double GetRiskFreeRate(DateTime asOf)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
double riskFreeRate = double.NaN;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select 3mo from yieldcurve ");
|
|
sb.Append("where date<='").Append(SqlUtils.FormatDate(asOf)).Append("'").Append(" order by date desc limit 1");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
riskFreeRate = sqlDataReader.GetDouble(0);
|
|
}
|
|
return riskFreeRate;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return riskFreeRate;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static double GetRiskFreeRate()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
double riskFreeRate = double.NaN;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select 3mo from yieldcurve order by date desc limit 1");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
riskFreeRate = sqlDataReader.GetDouble(0);
|
|
}
|
|
return riskFreeRate;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return riskFreeRate;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static DateTime GetMaxYieldCurveDate()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select max(date) from yieldcurve");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if(!sqlDataReader.Read())return Utility.Epoch;
|
|
return sqlDataReader.GetDateTime(0);
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return Utility.Epoch;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static List<DateTime> GetYieldCurveDates()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
List<DateTime> yieldCurveDates=new List<DateTime>();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date from yieldcurve order by date desc");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while(sqlDataReader.Read())
|
|
{
|
|
yieldCurveDates.Add(sqlDataReader.GetDateTime(0));
|
|
}
|
|
return yieldCurveDates;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return new List<DateTime>();
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|