double GetAvgDuration(CouponBondInfo info, DateTime targetDate, double avgRate)
{
DateTime settlement = targetDate; // 당일결제
DateTime maturity = info.Maturity;
double couponRate = info.CouponRate;
int frequencyPerYear = info.FrequencyPerYear;
double duration = BondMathUtil.GetDuration(settlement, maturity, couponRate, avgRate, frequencyPerYear);
return duration;
}
double GetAvgYld(CouponBondInfo bondInfo, DateTime targetDate, double avgPrice)
{
DateTime settlement = targetDate; // 당일결제
DateTime maturity = bondInfo.Maturity;
double couponRate = bondInfo.CouponRate;
int frequencyPerYear = bondInfo.FrequencyPerYear;
int price = (int)Math.Round(avgPrice, 0);
double yld = BondMathUtil.GetYield(settlement, maturity, couponRate, frequencyPerYear, price);
return yld;
}
List<CouponBondInfo> GetBondInfos()
{
String query = "select * from ktb_spot_info";
DataRowCollection rows = DBUtil.SelectFromDB(query, CommonConst.DATABASE_MADVIPER);
List<CouponBondInfo> output = new List<CouponBondInfo>();
for (int i = 0; i < rows.Count; ++i)
{
DataRow row = rows[i];
String code = Convert.ToString(row["code"]);
String name = Convert.ToString(row["name"]);
DateTime maturity = Convert.ToDateTime(row["maturity"]);
double coupon = Convert.ToDouble(row["coupon"]);
int frequencyPerYear = Convert.ToInt32(row["frequencyPerYear"]);
String logicalType = Convert.ToString(row["logical_type"]);
coupon /= 100.0;
CouponBondInfo bond = new CouponBondInfo();
bond.Code = code;
bond.Name = name;
bond.Maturity = maturity;
bond.CouponRate = coupon;
bond.FrequencyPerYear = frequencyPerYear;
output.Add(bond);
}
return output;
}
void LoadAndInsertAvgRateToDB_Raw(CouponBondInfo bondInfo, DateTime targetDate)
{
// 0.5초 간격의 데이터를 구한다.
PeriodicMarketDataCollection periodicData = GetPeriodicCollection(bondInfo.Code, targetDate);
// 평균가격을 구한다.
double avgPrice = GetAvgPrice(periodicData);
// 금리를 구한다.
double avgRate = GetAvgYld(bondInfo, targetDate, avgPrice);
// 듀레이션을 구한다.
double avgDuration = GetAvgDuration(bondInfo, targetDate, avgRate);
// DB에 데이터를 넣는다.
InsertToDB_Raw(bondInfo, targetDate, avgPrice, avgRate, avgDuration);
}
void InsertToDB_Raw(
CouponBondInfo bondInfo, DateTime targetDate, double avgPrice, double avgRate, double avgDuration)
{
String code = bondInfo.Code;
DateTime maturity = bondInfo.Maturity;
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 spot {0}, {1}, {2})", code, avgRate, targetDate.ToShortDateString());
}
}