public List <VasicekEtiNplIndex> ComputeVasicekScenario()
{
var vasicek = new List <VasicekEtiNplIndex>();
double pdTtc = ComputePdTtc();
double averageFittedIndex = ComputeVasicekAverageFitted();
var indexForecast = GetScenarioIndexForecastResult();
int month = 1;
var ecls = Queries.EclsRegister(_eclType.ToString(), _eclId.ToString());
var dtR = DataAccess.i.GetData(ecls);
var eclReg = new EclRegister {
OrganizationUnitId = -1
};
if (dtR.Rows.Count > 0)
{
eclReg = DataAccess.i.ParseDataToObject(new EclRegister(), dtR.Rows[0]);
}
foreach (var row in indexForecast)
{
double scenarioPd = ComputeVasicekIndex(row.Standardised, pdTtc, ECLNonStringConstants.i.Rho(eclReg.OrganizationUnitId));
var dr = new VasicekEtiNplIndex();
dr.Date = row.Date;
dr.Month = month;
dr.ScenarioIndex = row.Standardised;
dr.ScenarioPd = scenarioPd;
dr.ScenarioFactor = averageFittedIndex == 0 ? 1 : scenarioPd / averageFittedIndex;
vasicek.Add(dr);
month++;
}
return(vasicek);
}
public List <MacroResult_PrincipalComponentSummary> GetMacroResult_PCSummary(Guid eclId, EclType eclType)
{
string qry = Queries.GetPrincipalComponentSummary(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new List <MacroResult_PrincipalComponentSummary>());
}
var itms = new List <MacroResult_PrincipalComponentSummary>();
foreach (DataRow dr in dt.Rows)
{
var itm = new MacroResult_PrincipalComponentSummary();
try { itm.PricipalComponentLabelA = dr["PricipalComponentLabelA"].ToString(); } catch { itm.PricipalComponentLabelA = ""; }
try { itm.PricipalComponentLabelB = dr["PricipalComponentLabelB"].ToString(); } catch { itm.PricipalComponentLabelB = ""; }
try { itm.Value = double.Parse(dr["Value"].ToString().Trim()); } catch { itm.Value = 0; }
try { itm.PrincipalComponentIdA = int.Parse(dr["PrincipalComponentIdA"].ToString().Trim()); } catch { itm.PrincipalComponentIdA = 0; }
try { itm.PrincipalComponentIdB = int.Parse(dr["PrincipalComponentIdB"].ToString().Trim()); } catch { itm.PrincipalComponentIdB = 0; }
itms.Add(itm);
}
return(itms);
}
public CalibrationResult_LGD_HairCut GetLGDHaircutSummaryData(Guid eclId, EclType eclType)
{
string qry = Queries.GetLGDHaircutSummaryData(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new CalibrationResult_LGD_HairCut {
Debenture = 0, Cash = 0, Inventory = 0, Plant_And_Equipment = 0, Residential_Property = 0, Commercial_Property = 0, Receivables = 0, Shares = 0, Vehicle = 0
});
}
DataRow dr = dt.Rows[0];
var itm = new CalibrationResult_LGD_HairCut();
try { itm.Debenture = double.Parse(dr["Debenture"].ToString().Trim()); } catch { itm.Debenture = 0; }
try { itm.Cash = double.Parse(dr["Cash"].ToString().Trim()); } catch { itm.Cash = 0; }
try { itm.Inventory = double.Parse(dr["Inventory"].ToString().Trim()); } catch { itm.Inventory = 0; }
try { itm.Plant_And_Equipment = double.Parse(dr["Plant_And_Equipment"].ToString().Trim()); } catch { itm.Plant_And_Equipment = 0; }
try { itm.Residential_Property = double.Parse(dr["Residential_Property"].ToString().Trim()); } catch { itm.Residential_Property = 0; }
try { itm.Commercial_Property = double.Parse(dr["Commercial_Property"].ToString().Trim()); } catch { itm.Commercial_Property = 0; }
try { itm.Receivables = double.Parse(dr["Receivables"].ToString().Trim()); } catch { itm.Receivables = 0; }
try { itm.Shares = double.Parse(dr["Shares"].ToString().Trim()); } catch { itm.Shares = 0; }
try { itm.Vehicle = double.Parse(dr["Vehicle"].ToString().Trim()); } catch { itm.Vehicle = 0; }
return(itm);
}
private DateTime GetReportingDate(EclType _eclType, Guid eclId)
{
var ecls = Queries.EclsRegister(_eclType.ToString(), _eclId.ToString());
var dtR = DataAccess.i.GetData(ecls);
if (dtR.Rows.Count > 0)
{
var itm = DataAccess.i.ParseDataToObject(new EclRegister(), dtR.Rows[0]);
return(itm.ReportingDate);
}
return(DateTime.Now);
}
public CalibrationResult_EAD_Behavioural GetBehaviouralData(Guid eclId, EclType eclType)
{
string qry = Queries.GetEADBehaviouralData(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new CalibrationResult_EAD_Behavioural {
Expired = 0, FrequencyExpired = 0, FrequencyNonExpired = 0, NonExpired = 0
});
}
DataRow dr = dt.Rows[0];
var itm = new CalibrationResult_EAD_Behavioural();
try { itm.Expired = double.Parse(dr["Assumption_Expired"].ToString().Trim()); } catch { itm.Expired = 0; }
try { itm.FrequencyNonExpired = double.Parse(dr["Freq_NonExpired"].ToString().Trim()); } catch { itm.FrequencyNonExpired = 0; }
try { itm.FrequencyExpired = double.Parse(dr["Freq_Expired"].ToString().Trim()); } catch { itm.FrequencyExpired = 0; }
try { itm.NonExpired = double.Parse(dr["Assumption_NonExpired"].ToString().Trim()); } catch { itm.NonExpired = 0; }
return(itm);
}
public List <PD12Months> GetPD12MonthsPD(Guid eclId, EclType eclType)
{
string qry = Queries.GetPD12MonthsPD(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new List <PD12Months>());
}
var lst = new List <PD12Months>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
var itm = new PD12Months();
try { itm.Rating = int.Parse(dr["Rating"].ToString().Trim()); } catch { itm.Rating = 0; }
try { itm.Months_PDs_12 = double.Parse(dr["Months_PDs_12"].ToString().Trim()); } catch { itm.Months_PDs_12 = 0; }
lst.Add(itm);
}
return(lst);
}
public CalibrationResult_LGD_RecoveryRate GetLGDRecoveryRateData(Guid eclId, EclType eclType)
{
string qry = Queries.GetLGDRecoveryRateData(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new CalibrationResult_LGD_RecoveryRate {
Corporate_RecoveryRate = 1, Commercial_RecoveryRate = 1, Consumer_RecoveryRate = 1, Overall_RecoveryRate = 1
});
}
DataRow dr = dt.Rows[0];
var itm = new CalibrationResult_LGD_RecoveryRate();
try { itm.Corporate_RecoveryRate = double.Parse(dr["Corporate_RecoveryRate"].ToString().Trim()); } catch { itm.Corporate_RecoveryRate = 1; }
try { itm.Commercial_RecoveryRate = double.Parse(dr["Commercial_RecoveryRate"].ToString().Trim()); } catch { itm.Commercial_RecoveryRate = 1; }
try { itm.Consumer_RecoveryRate = double.Parse(dr["Consumer_RecoveryRate"].ToString().Trim()); } catch { itm.Consumer_RecoveryRate = 1; }
try { itm.Overall_RecoveryRate = double.Parse(dr["Overall_RecoveryRate"].ToString().Trim()); } catch { itm.Overall_RecoveryRate = 1; }
return(itm);
}
/// <summary>
/// return index 1= Redefault_Factor
/// return index 2= Cure_Rate
/// </summary>
/// <param name="eclId"></param>
/// <param name="eclType"></param>
/// <returns></returns>
public double[] GetPDRedefaultFactorCureRate(Guid eclId, EclType eclType)
{
string qry = Queries.GetPDRedefaultFactor(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new double[] { 0, 0 });
}
DataRow dr = dt.Rows[0];
var Redefault_Factor = 0.0;
var Cure_Rate = 0.0;
var Commercial_CureRate = 0.0;
var Consumer_CureRate = 0.0;
try { Redefault_Factor = double.Parse(dr["Redefault_Factor"].ToString().Trim()); } catch { Redefault_Factor = 0; }
try { Cure_Rate = double.Parse(dr["Cure_Rate"].ToString().Trim()); } catch { Cure_Rate = 0; }
try { Commercial_CureRate = double.Parse(dr["Commercial_CureRate"].ToString().Trim()); } catch { Commercial_CureRate = 0; }
try { Consumer_CureRate = double.Parse(dr["Consumer_CureRate"].ToString().Trim()); } catch { Consumer_CureRate = 0; }
return(new double[] { Redefault_Factor, Cure_Rate, Commercial_CureRate, Consumer_CureRate });
}
public List <MacroResult_IndexData> GetMacroResult_IndexData(Guid eclId, EclType eclType)
{
string qry = Queries.GetPDIndexData(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new List <MacroResult_IndexData>());
}
var itms = new List <MacroResult_IndexData>();
foreach (DataRow dr in dt.Rows)
{
var itm = new MacroResult_IndexData();
try { itm.Period = dr["StandardIndex"].ToString(); } catch { itm.Period = ""; }
try { itm.StandardIndex = double.Parse(dr["StandardIndex"].ToString().Trim()); } catch { itm.StandardIndex = 0; }
try { itm.BfNpl = double.Parse(dr["BfNpl"].ToString().Trim()); } catch { itm.BfNpl = 0; }
try { itm.Index = double.Parse(dr["Index"].ToString().Trim()); } catch { itm.Index = 0; }
itms.Add(itm);
}
return(itms);
}
public MacroResult_Statistics GetMacroResult_Statistics(Guid eclId, EclType eclType)
{
string qry = Queries.GetPDStatistics(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new MacroResult_Statistics());
}
var itms = new MacroResult_IndexData();
DataRow dr = dt.Rows[0];
var itm = new MacroResult_Statistics();
try { itm.IndexWeight1 = double.Parse(dr["IndexWeight1"].ToString().Trim()); } catch { itm.IndexWeight1 = 0; }
try { itm.IndexWeight2 = double.Parse(dr["IndexWeight2"].ToString().Trim()); } catch { itm.IndexWeight2 = 0; }
try { itm.IndexWeight3 = double.Parse(dr["IndexWeight3"].ToString().Trim()); } catch { itm.IndexWeight3 = 0; }
try { itm.IndexWeight4 = double.Parse(dr["IndexWeight4"].ToString().Trim()); } catch { itm.IndexWeight4 = 0; }
try { itm.Average = double.Parse(dr["Average"].ToString().Trim()); } catch { itm.Average = 0; }
try { itm.StandardDev = double.Parse(dr["StandardDev"].ToString().Trim()); } catch { itm.StandardDev = 0; }
return(itm);
}
public CalibrationResult_EAD_CCF_Summary GetCCFData(Guid eclId, EclType eclType)
{
string qry = Queries.GetEADCCFData(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new CalibrationResult_EAD_CCF_Summary {
OD_TotalLimitOdDefaultedLoan = 0, OD_BalanceAtDefault = 0, OD_Balance12MonthBeforeDefault = 0, OD_TotalConversation = 0, OD_CCF = 0, Card_TotalLimitOdDefaultedLoan = 0, Card_BalanceAtDefault = 0, Card_Balance12MonthBeforeDefault = 0, Card_TotalConversation = 0, Card_CCF = 0, Overall_TotalLimitOdDefaultedLoan = 0, Overall_BalanceAtDefault = 0, Overall_Balance12MonthBeforeDefault = 0, Overall_TotalConversation = 0, Overall_CCF = 0
});
}
DataRow dr = dt.Rows[0];
var itm = new CalibrationResult_EAD_CCF_Summary();
try { itm.OD_TotalLimitOdDefaultedLoan = double.Parse(dr["OD_TotalLimitOdDefaultedLoan"].ToString().Trim()); } catch { itm.OD_TotalLimitOdDefaultedLoan = 0; }
try { itm.OD_BalanceAtDefault = double.Parse(dr["OD_BalanceAtDefault"].ToString().Trim()); } catch { itm.OD_BalanceAtDefault = 0; }
try { itm.OD_Balance12MonthBeforeDefault = double.Parse(dr["OD_Balance12MonthBeforeDefault"].ToString().Trim()); } catch { itm.OD_Balance12MonthBeforeDefault = 0; }
try { itm.OD_TotalConversation = double.Parse(dr["OD_TotalConversation"].ToString().Trim()); } catch { itm.OD_TotalConversation = 0; }
try { itm.OD_CCF = double.Parse(dr["OD_CCF"].ToString().Trim()); } catch { itm.OD_CCF = 0; }
try { itm.Card_TotalLimitOdDefaultedLoan = double.Parse(dr["Card_TotalLimitOdDefaultedLoan"].ToString().Trim()); } catch { itm.Card_TotalLimitOdDefaultedLoan = 0; }
try { itm.Card_BalanceAtDefault = double.Parse(dr["Card_BalanceAtDefault"].ToString().Trim()); } catch { itm.Card_BalanceAtDefault = 0; }
try { itm.Card_Balance12MonthBeforeDefault = double.Parse(dr["Card_Balance12MonthBeforeDefault"].ToString().Trim()); } catch { itm.Card_Balance12MonthBeforeDefault = 0; }
try { itm.Card_TotalConversation = double.Parse(dr["Card_TotalConversation"].ToString().Trim()); } catch { itm.Card_TotalConversation = 0; }
try { itm.Card_CCF = double.Parse(dr["Card_CCF"].ToString().Trim()); } catch { itm.Card_CCF = 0; }
try { itm.Overall_TotalLimitOdDefaultedLoan = double.Parse(dr["Overall_TotalLimitOdDefaultedLoan"].ToString().Trim()); } catch { itm.Overall_TotalLimitOdDefaultedLoan = 0; }
try { itm.Overall_BalanceAtDefault = double.Parse(dr["Overall_BalanceAtDefault"].ToString().Trim()); } catch { itm.Overall_BalanceAtDefault = 0; }
try { itm.Overall_Balance12MonthBeforeDefault = double.Parse(dr["Overall_Balance12MonthBeforeDefault"].ToString().Trim()); } catch { itm.Overall_Balance12MonthBeforeDefault = 0; }
try { itm.Overall_TotalConversation = double.Parse(dr["Overall_TotalConversation"].ToString().Trim()); } catch { itm.Overall_TotalConversation = 0; }
try { itm.Overall_CCF = double.Parse(dr["Overall_CCF"].ToString().Trim()); } catch { itm.Overall_CCF = 0; }
return(itm);
}
public string FrameworkResultOverride(EclType eclType)
{
return($"{eclType.ToString()}ECLFrameworkFinalOverride");
}
public string LGDCollateral_Table(EclType eclType)
{
return($"{eclType.ToString()}LGDCollateral");
}
public static string Get_pdInputAssumptionsQuery(Guid eclId, EclType eclType)
{
return($"SELECT PdGroup, [Key],Value, InputName, {eclType.ToString()}EclId FROM {eclType.ToString()}EclPdAssumptions where {eclType.ToString()}EclId='{eclId.ToString()}'");
}
public static string Get_statisticalInputsQuery(Guid eclId, EclType eclType)
{
return($"SELECT InputName [Mode], MacroeconomicVariableId MacroEconomicVariableId,Value MacroEconomicValue, {eclType.ToString()}EclId EclId FROM {eclType.ToString()}EclPdAssumptionMacroeconomicInputs where {eclType.ToString()}EclId='{eclId.ToString()}'");
}
public static string Get_snpCummulativeDefaultRateQuery(Guid eclId, EclType eclType)
{
return($"SELECT [Rating],[Years],[Value] FROM {eclType.ToString()}EclPdSnPCummulativeDefaultRates where {eclType.ToString()}EclId = '{eclId.ToString()}'");
}
//Wholesale PD tables
public string PDCreditIndex_Table(EclType eclType)
{
return($"{eclType.ToString()}PDCreditIndex");
}
public string EadCirProjections_Table(EclType eclType)
{
return($"{eclType.ToString()}EadCirProjections");
}
public static string Get_etiNplQuery(Guid eclId, EclType eclType)
{
return($"SELECT Period,BfNpl FROM MacroResult_IndexData where MacroId=(select top 1 Id from CalibrationRunMacroAnalysis where OrganizationUnitId=(select top 1 OrganizationUnitId from {eclType.ToString()}Ecls where Id='{eclId.ToString()}') and [status]=7)");
}
public string PdRedefaultLifetimeOptimistics_Table(EclType eclType)
{
return($"{eclType.ToString()}PdRedefaultLifetimeOptimistics");
}
public string PdRedefaultLifetimeDownturns_Table(EclType eclType)
{
return($"{eclType.ToString()}PdRedefaultLifetimeDownturns");
}
public string PdLifetimeBests_Table(EclType eclType)
{
return($"{eclType.ToString()}PdLifetimeBests");
}
public string PdMappings_Table(EclType eclType)
{
return($"{eclType.ToString()}PdMappings");
}
public string FrameworkResult(EclType eclType)
{
return($"{eclType.ToString()}ECLFrameworkFinal");
}
public string EclFramworkReportDetail(EclType eclType)
{
return($"{eclType.ToString()}EclFramworkReportDetail");
}
public static string Get_macroEconomicsQuery(Guid eclId, EclType eclType)
{
return($"SELECT [Date],MacroeconomicVariableId MacroEconomicVariableId,BestValue BestEstimateMacroEconomicValue,OptimisticValue OptimisticMacroEconomicValue, DownturnValue DownturnMacroEconomicValue, {eclType.ToString()}EclId EclId FROM {eclType.ToString()}EclPdAssumptionMacroeconomicProjections where {eclType.ToString()}EclId='{eclId.ToString()}'");
}
public static string Get_nonInternalmodelInputQuery(Guid eclId, EclType eclType, int month)
{
var subQry = "";
if (month > 0)
{
subQry = $" and Month ={month} ";
}
return($"SELECT Month, PdGroup, MarginalDefaultRate, CummulativeSurvival FROM {eclType.ToString()}EclPdAssumptionNonInternalModels where {eclType.ToString()}EclId='{eclId.ToString()}' {subQry}");
}
public static string Get_historicIndexQuery(Guid eclId, EclType eclType)
{
return($"SELECT [Period],[Index],StandardIndex FROM MacroResult_IndexData where MacroId=(select top 1 Id from CalibrationRunMacroAnalysis where OrganizationUnitId=(select top 1 OrganizationUnitId from {eclType.ToString()}Ecls where Id='{eclId.ToString()}') and [status]=7)");
}
public string LGDAccountData_Table(EclType eclType)
{
return($"{eclType.ToString()}LGDAccountData");
}
