double GetAvgDuration(KtbFutureInfo info, double avgRate)
{
DateTime settlement = info.Maturity.AddDays(1); // T + 1일 결제, T is 선물 만기일
DateTime maturity = info.UnderlyingMaturity;
double couponRate = info.UnderlyingCouponRate;
int frequencyPerYear = info.UnderlyingFrequencyPerYear;
double duration = BondMathUtil.GetDuration(settlement, maturity, couponRate, avgRate, frequencyPerYear);
return duration;
}
double GetAvgYld(KtbFutureInfo info, double avgPrice)
{
DateTime settlement = info.Maturity.AddDays(1); // T + 1일 결제, T is 선물 만기일
DateTime maturity = info.UnderlyingMaturity;
double couponRate = info.UnderlyingCouponRate;
int frequencyPerYear = info.UnderlyingFrequencyPerYear;
int price = (int)Math.Round(avgPrice * 100, 0);
double yld = BondMathUtil.GetYield(settlement, maturity, couponRate, frequencyPerYear, price);
return yld;
}
List<KtbFutureInfo> GetBondInfos()
{
String query = "select * from ktb_future_info";
DataRowCollection rows = DBUtil.SelectFromDB(query, CommonConst.DATABASE_MADVIPER);
List<KtbFutureInfo> infos = new List<KtbFutureInfo>();
for (int i = 0; i < rows.Count; ++i)
{
DataRow row = rows[i];
String futType = Convert.ToString(row["fut_type"]);
int seriesType = Convert.ToInt32(row["series_type"]);
String code = Convert.ToString(row["code"]);
String name = Convert.ToString(row["name"]);
DateTime maturity = Convert.ToDateTime(row["maturity"]);
DateTime underlyingMaturity = Convert.ToDateTime(row["underlying_maturity"]);
double couponPercent = Convert.ToDouble(row["underlying_coupon"]);
int frequencyPerYear = Convert.ToInt32(row["underlying_frequency_per_year"]);
double couponRate = couponPercent / 100.0;
KtbFutureInfo info = new KtbFutureInfo();
info.Code = code;
info.Name = name;
info.Maturity = maturity;
info.UnderlyingMaturity = underlyingMaturity;
info.UnderlyingCouponRate = couponRate;
info.UnderlyingFrequencyPerYear = frequencyPerYear;
infos.Add(info);
}
return infos;
}
void LoadAndInsertAvgRateToDB_Raw(KtbFutureInfo info, DateTime targetDate)
{
// 0.5초 간격의 데이터를 구한다.
PeriodicMarketDataCollection periodicData = GetPeriodicCollection(info.Code, targetDate);
// 평균가격을 구한다.
double avgPrice = GetAvgPrice(periodicData);
// 금리를 구한다.
double avgRate = GetAvgYld(info, avgPrice);
// 듀레이션을 구한다.
double avgDuration = GetAvgDuration(info, avgRate);
// DB에 데이터를 넣는다.
InsertToDB_Raw(info, targetDate, avgPrice, avgRate, avgDuration);
}
void InsertToDB_Raw(
KtbFutureInfo info, DateTime targetDate, double avgPrice, double avgRate, double avgDuration)
{
String code = info.Code;
DateTime maturity = info.UnderlyingMaturity;
String template =
"insert into daydata_ktb_avg (cal_date, code, maturity, avg_price, avg_rate, avg_duration) values " +
"('{0}', '{1}', '{2}', {3}, {4}, {5})";
String query = String.Format(
template,
targetDate.ToString("yyyy-MM-dd"),
code,
maturity.ToString("yyyy-MM-dd"),
avgPrice,
avgRate,
avgDuration);
int ret = DBUtil.Execute(query, CommonConst.DATABASE_MADVIPER);
if (ret != 1)
{
logger.Warn("ret is {0}.", ret);
}
else
{
logger.Info("Insert success. (ktb future {0}, {1}, {2})", code, avgRate, targetDate.ToShortDateString());
}
}