//public void Run()
//{
// var dataTable = ComputeSicrInput();
// string stop = "stop";
//}
//public List<SicrInputs> ComputeSicrInput()
//{
// var sicrInput = new List<SicrInputs>();
// //string[] testAccounts = { "15033346", "15036347", "222017177" };
// var loanbookTable = GetLoanbookData().Where(x => x.ContractId.Substring(0, 3) != ECLStringConstants.i.ExpiredContractsPrefix).ToList();
// var lifetimePds = _scenarioLifetimePd.ComputeLifetimePd();
// var redefaultLifetimePds = _scenarioRedefaultLifetimePd.ComputeRedefaultLifetimePd();
// //var pdMapping = _pdMapping.ComputePdMappingTable();
// foreach (var loanbookRow in loanbookTable)
// {
// var contractPdMapping = pdMapping.FirstOrDefault(x => x.ContractId == loanbookRow.ContractId);
// if (contractPdMapping == null)
// {
// continue;
// }
// string contractPdGroup = contractPdMapping.PdGroup;
// int contractTtmMonths = contractPdMapping.TtmMonths;
// string impairedDate = null;
// if(loanbookRow.ImpairedDate != null)
// {
// impairedDate=loanbookRow.ImpairedDate.ToString().Contains("1900") ? null : loanbookRow.ImpairedDate.ToString();
// }
// string defaultDate = null;
// if(loanbookRow.DefaultDate!=null)
// {
// defaultDate = loanbookRow.DefaultDate.ToString().Contains("1900") ? null : loanbookRow.DefaultDate.ToString();
// }
// int maxClassification = contractPdMapping.MaxClassificationScore;
// long maxDpd = contractPdMapping.MaxDpd;
// var sicrRow = new SicrInputs();
// sicrRow.ContractId = loanbookRow.ContractId;
// sicrRow.Pd12Month = ComputeLifetimeAndRedefaultPds(lifetimePds, contractPdGroup, 12);
// sicrRow.LifetimePd = ComputeLifetimeAndRedefaultPds(lifetimePds, contractPdGroup, contractTtmMonths);
// sicrRow.RedefaultLifetimePd = ComputeLifetimeAndRedefaultPds(redefaultLifetimePds, contractPdGroup, contractTtmMonths);
// sicrRow.Stage1Transition = Math.Round(ComputeStageDaysPastDue(impairedDate));
// sicrRow.Stage2Transition = ComputeStageDaysPastDue(defaultDate);
// sicrRow.DaysPastDue = ComputeDaysPastDue(maxClassification, maxDpd);
// sicrInput.Add(sicrRow);
// }
// return sicrInput;
//}
public SicrInputs ComputeSICRInput(Loanbook_Data loanbookRow, PdMappings contractPdMapping, List <LifeTimeObject> lifetimePds, List <LifeTimeObject> redefaultLifetimePds)
{
string contractPdGroup = contractPdMapping.PdGroup;
int contractTtmMonths = contractPdMapping.TtmMonths;
string impairedDate = null;
if (loanbookRow.ImpairedDate != null)
{
impairedDate = loanbookRow.ImpairedDate.ToString().Contains("1900") ? null : loanbookRow.ImpairedDate.ToString();
}
string defaultDate = null;
if (loanbookRow.DefaultDate != null)
{
defaultDate = loanbookRow.DefaultDate.ToString().Contains("1900") ? null : loanbookRow.DefaultDate.ToString();
}
int maxClassification = contractPdMapping.MaxClassificationScore;
long maxDpd = contractPdMapping.MaxDpd;
var sicrRow = new SicrInputs();
//sicrRow.ContractId = loanbookRow.ContractId;
sicrRow.Pd12Month = ComputeLifetimeAndRedefaultPds(lifetimePds, contractPdGroup, 12);
sicrRow.LifetimePd = ComputeLifetimeAndRedefaultPds(lifetimePds, contractPdGroup, contractTtmMonths);
sicrRow.RedefaultLifetimePd = ComputeLifetimeAndRedefaultPds(redefaultLifetimePds, contractPdGroup, contractTtmMonths);
sicrRow.Stage1Transition = int.Parse(Math.Round(ComputeStageDaysPastDue(impairedDate)).ToString());
sicrRow.Stage2Transition = int.Parse(Math.Round(ComputeStageDaysPastDue(defaultDate)).ToString());
sicrRow.DaysPastDue = ComputeDaysPastDue(maxClassification, maxDpd);
return(sicrRow);
}
protected int ComputeRatingUsedPerRecord(Loanbook_Data loanbookRecord)
{
loanbookRecord.CurrentRating = loanbookRecord.CurrentRating ?? "1";
var current_rating = loanbookRecord.CurrentRating.Replace("+", "").Replace("-", "").Replace(" ", "").Trim();
return(int.Parse(current_rating));//>10 ? int.Parse(current_rating.Substring(0,1)) : int.Parse(current_rating);
}
private int ComputeCreditRatingScore(Loanbook_Data loanBookRecord, List <EclAssumptions> assumption)
{
try
{
double stage2to3CreditRating = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.CreditRatingDefaultIndicator));
double lowHighRiskThreshold = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.CreditRatingRankLowHighRisk));
double normalRiskThreshold = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.CreditRatingRankLowRisk));
double highRiskThreshold = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.CreditRatingRankHighRisk));
var currentRating = loanBookRecord.CurrentRating;
var originalRating = loanBookRecord.OriginalRating;
double currentCreditRankRating = string.IsNullOrWhiteSpace(currentRating.ToString()) ? 1 : Convert.ToDouble(assumption.FirstOrDefault(o => o.AssumptionGroup == 6 && o.Value == currentRating).Key.Replace("CreditRatingRank", ""));// && Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.CreditRatingRank + currentRating.ToString()));
double originalCreditRankRating = string.IsNullOrWhiteSpace(originalRating.ToString()) ? 1 : Convert.ToDouble(assumption.FirstOrDefault(o => o.AssumptionGroup == 6 && o.Value == originalRating).Key.Replace("CreditRatingRank", ""));
if (currentCreditRankRating >= stage2to3CreditRating)
{
return(3);
}
else
{
if (currentCreditRankRating <= lowHighRiskThreshold)
{
return(currentCreditRankRating - originalCreditRankRating > normalRiskThreshold ? 2 : 1);
}
else
{
return(currentCreditRankRating - originalCreditRankRating > highRiskThreshold ? 2 : 1);
}
}
}catch (Exception ex)
{
var cc = ex;
return(1);
}
}
protected int?ComputeClassificationScorePerRecord(Loanbook_Data loanbookRecord)
{
string classification = loanbookRecord.Classification.ToUpper();
switch (classification)
{
case "P":
return(1);
case "W":
return(2);
case "S":
return(3);
case "D":
return(4);
case "L":
return(5);
default:
return(null);
}
}
protected double ComputeMaxDpdPerRecord(Loanbook_Data loanbookRecord, List <Loanbook_Data> loanbook)
{
//var temp = loanbook.Where(o => o.AccountNo == loanbookRecord.AccountNo).Max(p => p.DaysPastDue);
var temp = loanbook.Where(o => o.ContractId == loanbookRecord.ContractId).Max(p => p.DaysPastDue);
return(temp ?? 0);
}
protected int ComputeTimeToMaturityMonthsPerRecord(Loanbook_Data loanbookRecord, int expOdPerformacePastRepoting, int odPerformancePastExpiry)
{
if (loanbookRecord.ContractId.Substring(0, 3) == ECLStringConstants.i.ExpiredContractsPrefix)
{
return(0);
}
else
{
int xValue = 0;
int yValue = 0;
DateTime?endDate = new DateTime(1900, 01, 01);
if (loanbookRecord.RestructureIndicator && loanbookRecord.RestructureEndDate != null)
{
if (loanbookRecord.RestructureEndDate == null)
{
xValue = 0;
}
else
{
endDate = DateTime.Parse(loanbookRecord.RestructureEndDate.ToString());
}
}
else
{
if (loanbookRecord.ContractEndDate == null)
{
xValue = 0;
}
else
{
endDate = DateTime.Parse(loanbookRecord.ContractEndDate.ToString());
}
}
var prod = 0;
if (endDate != null && endDate != new DateTime(1900, 01, 01))
{
if (!endDate.ToString().Contains("0001"))
{
var eomonth = ExcelFormulaUtil.EOMonth(endDate);
var yearFrac = ExcelFormulaUtil.YearFrac(GetReportingDate(_eclType, _eclId), eomonth);
var round = Convert.ToInt32(Math.Round(yearFrac * 12, 0));
var rptDate = GetReportingDate(_eclType, _eclId);
xValue = endDate > rptDate ? round : 0;
var maxx = Math.Max(expOdPerformacePastRepoting - round, 0);
prod = endDate < rptDate ? maxx : odPerformancePastExpiry;
}
}
loanbookRecord.ProductType = loanbookRecord.ProductType ?? "";
yValue = loanbookRecord.ProductType.ToLower() == ECLStringConstants.i._productType_card.ToLower() || loanbookRecord.ProductType.ToLower() == ECLStringConstants.i._productType_od.ToLower() ? prod : 0;
//Financial.YearFrac()
return(xValue + yValue);
}
}
private int ComputeWatchlistIndicatorScore(Loanbook_Data loanBookRecord, List <EclAssumptions> assumption)
{
string useWatchlistIndicator = GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.UseWatchlistIndicator);
if (useWatchlistIndicator.ToLower() == ECLStringConstants.i.yes)
{
return(loanBookRecord.WatchlistIndicator ? 2 : 1);
}
else
{
return(1);
}
}
protected DateTime?ComputeRestructureEndDatePerRecord(Loanbook_Data loanbookRecord)
{
var restructureEndDate = loanbookRecord.RestructureEndDate;
if (restructureEndDate == null)
{
return(null);
}
else
{
return(restructureEndDate);
}
}
private int ComputeForwardScore(SicrInputs sicrInputRecord, Loanbook_Data loanBookRecord, List <EclAssumptions> assumption)
{
var currentRating = loanBookRecord.CurrentRating ?? "";
double currentCreditRankRating = string.IsNullOrWhiteSpace(currentRating) ? 1 : Convert.ToDouble(assumption.FirstOrDefault(o => o.AssumptionGroup == 6 && o.Value == currentRating).Key.Replace("CreditRatingRank", ""));// && Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.CreditRatingRank + currentRating.ToString()));
double stage2to3creditRating = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.CreditRatingDefaultIndicator));
double stage1to2Forward = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.ForwardTransitionStage1to1));
double stage2to3Forward = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.ForwardTransitionStage2to3));
long daysPastDue = sicrInputRecord.DaysPastDue;
if (currentCreditRankRating < stage2to3creditRating)
{
return(daysPastDue < stage1to2Forward ? 1 : (daysPastDue >= stage2to3Forward ? 3 : 2));
}
else
{
return(3);
}
}
private int ComputeStage(SicrInputs sicrInputRecord, Loanbook_Data loanBookRecord, List <EclAssumptions> assumption, string pdMapping)
{
int pdAbsoluteScore = ComputePdAbsoluteScore(sicrInputRecord, loanBookRecord, assumption);
int pdRelativeScore = ComputePdRelativeScore(sicrInputRecord, loanBookRecord, assumption);
int creditRatingScore = ComputeCreditRatingScore(loanBookRecord, assumption);
int watchlistScore = ComputeWatchlistIndicatorScore(loanBookRecord, assumption);
int restructureScore = ComputeRestructureIndicatorScore(loanBookRecord, assumption);
int forwardScore = ComputeForwardScore(sicrInputRecord, loanBookRecord, assumption);
int backwardScore = ComputeBackwardScore(sicrInputRecord, assumption);
int expDefault = ComputeExpDefaultScore(pdMapping);
int maxScore1 = Math.Max(pdAbsoluteScore, pdRelativeScore);
int maxScore2 = Math.Max(creditRatingScore, watchlistScore);
int maxScore3 = Math.Max(forwardScore, backwardScore);
int maxScore4 = Math.Max(restructureScore, expDefault);
int maxScore5 = Math.Max(maxScore1, maxScore2);
int maxScore6 = Math.Max(maxScore3, maxScore4);
return(Math.Max(maxScore5, maxScore6));
}
private int ComputePdAbsoluteScore(SicrInputs sicrInputRecord, Loanbook_Data loanBookRecord, List <EclAssumptions> assumption)
{
string absoluteType = GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.AbsoluteCreditQualityCriteria);
double absoluteThreshold = Convert.ToDouble(GetImpairmentAssumptionValue(assumption, ImpairmentRowKeys.AbsoluteCreditQualityThreshold));
switch (absoluteType)
{
case FrameworkConstants.CreditQualityCriteriaLifetimePd:
double sicrLifetimePd = sicrInputRecord.LifetimePd;
double loanLifetimePd = loanBookRecord.LifetimePD ?? 0;
return(((sicrLifetimePd - loanLifetimePd) > absoluteThreshold) ? 2 : 1);
case FrameworkConstants.CreditQualityCriteria12MonthPd:
double sicr12MonthPd = sicrInputRecord.Pd12Month;
double loan12MonthPd = loanBookRecord.Month12PD ?? 0;
return(((sicr12MonthPd - loan12MonthPd) > absoluteThreshold) ? 2 : 1);
default:
return(0);
}
}
private void WriteLoanBook(List <Loanbook_Data> loanbook, Loanbook_Data loanbook_Data, string loanbookPath, bool includeDummyLoanBook)
{
if (includeDummyLoanBook)
{
var maxContractDate = loanbook.Max(o => o.ContractEndDate);
loanbook_Data.ContractEndDate = maxContractDate.Value.AddYears(1);
loanbook_Data.ContractNo = AppSettings.DumbContract;
loanbook_Data.OutstandingBalanceLCY = 0;
loanbook_Data.DebentureOMV = 0.0;
loanbook_Data.DebentureFSV = 0.0;
loanbook_Data.CashOMV = 0.0;
loanbook_Data.CashFSV = 0.0;
loanbook_Data.InventoryOMV = 0.0;
loanbook_Data.InventoryFSV = 0.0;
loanbook_Data.PlantEquipmentOMV = 0.0;
loanbook_Data.PlantEquipmentFSV = 0.0;
loanbook_Data.ResidentialPropertyOMV = 0.0;
loanbook_Data.ResidentialPropertyFSV = 0.0;
loanbook_Data.CommercialPropertyOMV = 0.0;
loanbook_Data.CommercialProperty = 0.0;
loanbook_Data.ReceivablesOMV = 0.0;
loanbook_Data.ReceivablesFSV = 0.0;
loanbook_Data.SharesOMV = 0.0;
loanbook_Data.SharesFSV = 0.0;
loanbook_Data.VehicleOMV = 0.0;
loanbook_Data.VehicleFSV = 0.0;
loanbook_Data.CureRate = 0.0;
loanbook_Data.GuaranteeIndicator = false;
loanbook_Data.GuaranteeValue = 0.0;
loanbook_Data.GuaranteeLevel = 0.0;
loanbook.Add(loanbook_Data);
}
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo(loanbookPath)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
// get number of rows in the sheet
int rows = worksheet.Dimension.Rows; // 10
package.Workbook.CalcMode = ExcelCalcMode.Automatic;
for (int i = 0; i < loanbook.Count; i++)
{
var p = loanbook[i];
worksheet.Cells[i + 4, 1 + 1].Value = p.CustomerNo;
worksheet.Cells[i + 4, 1 + 2].Value = p.AccountNo;
worksheet.Cells[i + 4, 1 + 3].Value = p.ContractNo;
worksheet.Cells[i + 4, 1 + 4].Value = p.CustomerName;
worksheet.Cells[i + 4, 1 + 5].Value = p.SnapshotDate;
worksheet.Cells[i + 4, 1 + 6].Value = p.Segment;
worksheet.Cells[i + 4, 1 + 7].Value = p.Sector;
worksheet.Cells[i + 4, 1 + 8].Value = p.Currency;
worksheet.Cells[i + 4, 1 + 9].Value = p.ProductType;
worksheet.Cells[i + 4, 1 + 10].Value = p.ProductMapping;
worksheet.Cells[i + 4, 1 + 11].Value = p.SpecialisedLending;
worksheet.Cells[i + 4, 1 + 12].Value = p.RatingModel;
worksheet.Cells[i + 4, 1 + 13].Value = p.OriginalRating;
worksheet.Cells[i + 4, 1 + 14].Value = p.CurrentRating;
worksheet.Cells[i + 4, 1 + 15].Value = p.LifetimePD;
worksheet.Cells[i + 4, 1 + 16].Value = p.Month12PD;
worksheet.Cells[i + 4, 1 + 17].Value = p.DaysPastDue;
worksheet.Cells[i + 4, 1 + 18].Value = p.WatchlistIndicator ? "1" : "";
worksheet.Cells[i + 4, 1 + 19].Value = p.Classification;
worksheet.Cells[i + 4, 1 + 20].Value = p.ImpairedDate;
worksheet.Cells[i + 4, 1 + 21].Value = p.DefaultDate;
worksheet.Cells[i + 4, 1 + 22].Value = p.CreditLimit;
worksheet.Cells[i + 4, 1 + 23].Value = p.OriginalBalanceLCY;
worksheet.Cells[i + 4, 1 + 24].Value = p.OutstandingBalanceLCY;
worksheet.Cells[i + 4, 1 + 25].Value = p.OutstandingBalanceACY;
worksheet.Cells[i + 4, 1 + 26].Value = p.ContractStartDate;
worksheet.Cells[i + 4, 1 + 27].Value = p.ContractEndDate;
worksheet.Cells[i + 4, 1 + 28].Value = p.RestructureIndicator ? "1" : "";
worksheet.Cells[i + 4, 1 + 29].Value = p.RestructureRisk;
worksheet.Cells[i + 4, 1 + 30].Value = p.RestructureType;
worksheet.Cells[i + 4, 1 + 31].Value = p.RestructureStartDate;
worksheet.Cells[i + 4, 1 + 32].Value = p.RestructureEndDate;
worksheet.Cells[i + 4, 1 + 33].Value = p.PrincipalPaymentTermsOrigination;
worksheet.Cells[i + 4, 1 + 34].Value = p.PPTOPeriod;
worksheet.Cells[i + 4, 1 + 35].Value = p.InterestPaymentTermsOrigination;
worksheet.Cells[i + 4, 1 + 36].Value = p.IPTOPeriod;
worksheet.Cells[i + 4, 1 + 37].Value = p.PrincipalPaymentStructure;
worksheet.Cells[i + 4, 1 + 38].Value = p.InterestPaymentStructure;
worksheet.Cells[i + 4, 1 + 39].Value = p.InterestRateType;
worksheet.Cells[i + 4, 1 + 40].Value = p.BaseRate;
worksheet.Cells[i + 4, 1 + 41].Value = p.OriginationContractualInterestRate;
worksheet.Cells[i + 4, 1 + 42].Value = p.IntroductoryPeriod;
worksheet.Cells[i + 4, 1 + 43].Value = p.PostIPContractualInterestRate;
worksheet.Cells[i + 4, 1 + 44].Value = p.CurrentContractualInterestRate;
worksheet.Cells[i + 4, 1 + 45].Value = p.EIR;
worksheet.Cells[i + 4, 1 + 46].Value = p.DebentureOMV;
worksheet.Cells[i + 4, 1 + 47].Value = p.DebentureFSV;
worksheet.Cells[i + 4, 1 + 48].Value = p.CashOMV;
worksheet.Cells[i + 4, 1 + 49].Value = p.CashFSV;
worksheet.Cells[i + 4, 1 + 50].Value = p.InventoryOMV;
worksheet.Cells[i + 4, 1 + 51].Value = p.InventoryFSV;
worksheet.Cells[i + 4, 1 + 52].Value = p.PlantEquipmentOMV;
worksheet.Cells[i + 4, 1 + 53].Value = p.PlantEquipmentFSV;
worksheet.Cells[i + 4, 1 + 54].Value = p.ResidentialPropertyOMV;
worksheet.Cells[i + 4, 1 + 55].Value = p.ResidentialPropertyFSV;
worksheet.Cells[i + 4, 1 + 56].Value = p.CommercialPropertyOMV;
worksheet.Cells[i + 4, 1 + 57].Value = p.CommercialProperty;
worksheet.Cells[i + 4, 1 + 58].Value = p.ReceivablesOMV;
worksheet.Cells[i + 4, 1 + 59].Value = p.ReceivablesFSV;
worksheet.Cells[i + 4, 1 + 60].Value = p.SharesOMV;
worksheet.Cells[i + 4, 1 + 61].Value = p.SharesFSV;
worksheet.Cells[i + 4, 1 + 62].Value = p.VehicleOMV;
worksheet.Cells[i + 4, 1 + 63].Value = p.VehicleFSV;
worksheet.Cells[i + 4, 1 + 64].Value = p.CureRate;
worksheet.Cells[i + 4, 1 + 65].Value = p.GuaranteeIndicator?"1":"";
worksheet.Cells[i + 4, 1 + 66].Value = p.GuarantorPD;
worksheet.Cells[i + 4, 1 + 67].Value = p.GuarantorLGD;
worksheet.Cells[i + 4, 1 + 68].Value = p.GuaranteeValue;
worksheet.Cells[i + 4, 1 + 69].Value = p.GuaranteeLevel;
}
package.Save();
}
}