// retrieving all data for Tips from TSR and keep them in a dictionary: dict, for inputs to InflationCurve
public static Dictionary <string, string> GetTipsData4ModelInput(string asOfDate)
{
var db = new OracleData();
// prepare baseNameList: 'GTII5 Govt, GTII10 Govt, GTII30 Govt'
var baseNameList = TipsMap.Keys.Aggregate("'", (current, key) => current + TipsMap[key] + "', '");
baseNameList = baseNameList.TrimEnd(new char[] { ',', ' ', '\'' });
baseNameList = baseNameList + "'";
// dynamically get the baseNameList for the query
var mySql = UpdateTsrSql4Tips(baseNameList);
// dynamically get the baseNameList for the query
var table = db.GetDataTable(mySql);
var dict = new Dictionary <string, string>();
foreach (var key in TipsMap.Keys)
{
var ticker = TipsMap[key];
var valStr = "";
var dataRows = GetTipsDataPerTicker(asOfDate, db, table, ticker); // tenor => ticker mapping
// var dataRows = GetTipsDataPerTicker(asOfDate, sql, ticker); // avoid do database access every time
for (var i = 0; i < dataRows.Count(); i++)
{
valStr = valStr + (dataRows[i][1] + ":" + dataRows[i][2] + "|");
}
dict[key] = "Ticker:" + ticker + "|" + valStr;
}
return(dict);
}
public static DataRow[] GetTreasuryDataPerTicker(string asOfDate, string sql, string ticker)
{
var db = new OracleData();
var table = db.GetDataTable(sql);
var dataRows = db.SelectDataRows(table, " Base_Name = '" + ticker
+ "' AND (FIELD = 'MATURITY' OR FIELD = 'ISSUE DATE' or FIELD = 'CALC TYPE' or FIELD = 'QUOTE' OR FIELD = 'COUPON') ");
return(dataRows);
}
/*** The goal is to produce the following list:
* tipsData["1Y"] = "Ticker:912828EA Govt|ISSUE DATE:2005-07-15|MATURITY:2015-07-15|COUPON:1.875|QUOTE:103.539063";
* tipsData["2Y"] = "Ticker:912828FL Govt|ISSUE DATE:2006-07-17|MATURITY:2016-07-15|COUPON:2.5|QUOTE:108.109375";
* tipsData["3Y"] = "Ticker:912828GX Govt|ISSUE DATE:2007-07-16|MATURITY:2017-07-15|COUPON:2.625|QUOTE:111.289063";
* tipsData["5Y"] = "Ticker:GTII5 Govt|ISSUE DATE:2014-04-30|MATURITY:2019-04-15|COUPON:0.125|QUOTE:102.351563";
* tipsData["7Y"] = "Ticker:912828QV Govt|ISSUE DATE:2011-07-29|MATURITY:2021-07-15|COUPON:.625|QUOTE:105.039063";
* tipsData["10Y"] ="Ticker:GTII10 Govt|ISSUE DATE:2014-01-31|MATURITY:2024-01-15|COUPON:0.625|QUOTE:103.300781";
* tipsData["20Y"] ="Ticker:GTII20 Govt|ISSUE DATE:2001-10-15|MATURITY:2032-04-15|COUPON:3.375|QUOTE:144.796875";
* tipsData["30Y"] ="Ticker:GTII30 Govt|ISSUE DATE:2014-02-28|MATURITY:2044-02-15|COUPON:1.375|QUOTE:109.921875";
***/
public static DataRow[] GetTipsDataByMaturity(string asOfDate, string sql)
{
var db = new OracleData();
var ds = db.GetDataSet(sql);
var table = db.GetDataTable(ds);
var dataRows = db.SelectDataRows(table, " FIELD = 'MATURITY' ");
return(dataRows);
}
// Prepapring TIPS Inputs for Model
public static DataRow[] GetTipsDataPerTicker(string asOfDate, string sql, string ticker)
{
var db = new OracleData();
// prepare baseNameList: 'GTII5 Govt, GTII10 Govt, GTII30 Govt'
var baseNameList = TipsMap.Keys.Aggregate("'", (current, key) => current + TipsMap[key] + ",");
baseNameList = baseNameList.TrimEnd(',');
// dynamically get the baseNameList for the query
var mySql = UpdateTsrSql4Tips(baseNameList);
var table = db.GetDataTable(mySql);
var dataRows = db.SelectDataRows(table, " Base_Name = '" + ticker
+ "' AND (FIELD = 'MATURITY' OR FIELD = 'CALC TYPE' OR FIELD = 'ISSUE DATE' or FIELD = 'QUOTE' OR FIELD = 'COUPON') ");
return(dataRows);
}
// retrieving all data for Treasury from TSR and keep them in a dictionary: dict, for inputs to InflationCurve
public static Dictionary <string, string> GetTreasuryData4ModelInput(string asOfDate, string sql)
{
var db = new OracleData();
var table = db.GetDataTable(sql);
var dict = new Dictionary <string, string>();
foreach (var key in TreasuryMap.Keys)
{
var ticker = TreasuryMap[key];
var valStr = "";
var dataRows = GetTreasuryDataPerTicker(asOfDate, db, table, ticker); // tenor => ticker mapping
// var dataRows = GetTreasuryDataPerTicker(asOfDate, sql, ticker); // avoid do database access every time
for (var i = 0; i < dataRows.Count(); i++)
{
valStr = valStr + (dataRows[i][1] + ":" + dataRows[i][2] + "|");
}
dict[key] = "Ticker:" + ticker + "|" + valStr;
}
return(dict);
}