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"); }