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 bool ProcessMacro(int macroId, long affiliateId)
{
var qry = Queries.Affiliate_MacroeconomicVariable(affiliateId);
var dt = DataAccess.i.GetData(qry);
foreach (DataRow dr in dt.Rows)
{
affM.Add(DataAccess.i.ParseDataToObject(new AffiliateMacroEconomicVariableOffsets(), dr));
}
var lstMacroData = GeneratesaveMacroData(affiliateId, macroId);
ProcessMacroAnalysis(affiliateId, macroId);
//return true;
// Read Eingen final to determine the comp to consider
var EingenFinalPath = Path.Combine(AppSettings.MacroModelPath, affiliateId.ToString(), "ETI_Eingen_Final.csv");
var all_Eingen = File.ReadAllLines(EingenFinalPath);
var eIngenValues = new List <double>();
for (int i = 1; i < all_Eingen.Count(); i++)
{
eIngenValues.Add(double.Parse(all_Eingen[i].Split(',')[1]));
if (i == 5)
{
break;
}
}
// Read loading final to determine the comp to consider
var LoadingFinalPath = Path.Combine(AppSettings.MacroModelPath, affiliateId.ToString(), "ETI_Loadings_Final.csv");
var all_loadingFinal = File.ReadAllLines(LoadingFinalPath);
var dataLoaded = new List <List <double> >();
var macvarCol = new List <string>();
var colCount = 0;
for (int i = 0; i < all_loadingFinal.Length; i++)
{
if (i == 0)
{
continue;
}
var splitted = all_loadingFinal[i].Split(',');
macvarCol.Add(splitted[0]);
splitted = splitted.Skip(1).ToArray();
colCount = splitted.Count();
var _joined = string.Join(",", splitted);
dataLoaded.Add(_joined.Split(',').Select(r => Convert.ToDouble(r)).ToArray().ToList());
}
var loadingOutputResult = new List <List <double> >();
var finalMaxIndex = new List <int>();
var actual_macvar = new List <AffiliateMacroEconomicVariableOffsets>();
for (int i = 0; i < colCount; i++)
{
var tempResult = new List <double>();
foreach (var ln in dataLoaded)
{
var val = ln[i];
if (val < 0)
{
val = val * -1;
}
tempResult.Add(val);
}
var _indx = tempResult.Select((n, j) => (Number: n, Index: j)).Max().Index;
var tkVal = affM.Count >= 4 ? 4 : affM.Count;
if (colCount < tkVal)
{
tkVal = colCount;
}
if (!loadingOutputResult.Contains(dataLoaded[_indx].Take(tkVal).ToList()))
{
var varTitle = macvarCol[_indx];
varTitle = varTitle.Replace(" ", "").Replace("\"", "");
var indexAndBackLag = varTitle.Replace("Var", "").Split('-');
var aff = affM.FirstOrDefault(); //**************************************
try { aff = affM[int.Parse(indexAndBackLag[0]) - 1]; } catch { };
aff.varTitle = varTitle.Split('-')[0].Trim();
aff.BackwardOffset = 0;
if (indexAndBackLag.Length > 1)
{
aff.BackwardOffset = int.Parse(indexAndBackLag[1]);
}
else
{
aff.BackwardOffset = 0;
}
if (!actual_macvar.Contains(aff))
{
actual_macvar.Add(aff);
loadingOutputResult.Add(dataLoaded[_indx].Take(tkVal).ToList());
}
else
{
if (actual_macvar.Count == 3)
{
break;
}
}
}
if (loadingOutputResult.Count == tkVal)
{
break;
}
}
loadingOutputResult = loadingOutputResult.Distinct().ToList();
var maxBackLag = actual_macvar.Max(o => o.BackwardOffset);
var macrodataHeader = lstMacroData[0].Split(',').ToList();
// find and pick columsn to consider
var positionsToHold = new List <int>();
for (int i = 0; i < actual_macvar.Count; i++)
{
for (int j = 0; j < macrodataHeader.Count(); j++)
{
if (macrodataHeader[j] == actual_macvar[i].varTitle)
{
positionsToHold.Add(j);
}
}
}
//Get the actualMacroData for Analysis sheet
var firstPick = true;
var allLineData = new List <List <string> >();
var actual_filtered_lineData = new List <List <string> >();
for (int i = 1; i < lstMacroData.Count; i++)
{
var _lineData = lstMacroData[i].Split(',');
var lineData = new List <string>();
lineData.Add(_lineData[0]);
for (int m = 0; m < positionsToHold.Count; m++)
{
lineData.Add(_lineData[positionsToHold[m]]);
}
var npl = _lineData.Last();
lineData.Add(npl);
allLineData.Add(lineData);
if (!string.IsNullOrWhiteSpace(npl) && !string.IsNullOrEmpty(npl))
{
try
{
//if (double.Parse(npl.Trim()) > 0)
//{
if (firstPick)
{
actual_filtered_lineData.AddRange(allLineData.Skip(i - maxBackLag - 1).Take(maxBackLag).ToList());
firstPick = false;
}
actual_filtered_lineData.Add(lineData);
//}
}
catch { }
}
}
///i have gotten the data on sheet 1 actual_filtered_lineData
var groupMacroData = GenerateGroupMacroData(actual_filtered_lineData);
///the principal component will come from the score final sheet
var scoreFinalPath = Path.Combine(AppSettings.MacroModelPath, affiliateId.ToString(), "ETI_scores_Final.csv");
var all_score = File.ReadAllLines(scoreFinalPath);
var startPeriod = groupMacroData.FirstOrDefault(o => o.NPL > 0).period;
var endPeriod = groupMacroData.LastOrDefault(o => o.NPL > 0).period;
var scoreValues = new List <double>();
var mcPrincipalComponent = new List <MacroResult_PrincipalComponent>();
var started = false;
var startPos = lstMacroData.Count() - all_score.Count() + 1; // one is to exlude the header
// var allDataStartPeriod = lstMacroData[startPos].Split(',')[0];
for (int i = 1; i < all_score.Count(); i++)
{
var _singleLine = all_score[i].Split(',');
var posData = lstMacroData[startPos].Split(',')[0];
if (posData == startPeriod)//allDataStartPeriod
{
started = true;
}
if (started)
{
var mp = new MacroResult_PrincipalComponent();
try { mp.PrincipalComponent1 = double.Parse(_singleLine[1].Trim()); } catch { mp.PrincipalComponent1 = 0; }
try{ mp.PrincipalComponent2 = double.Parse(_singleLine[2].Trim()); } catch { mp.PrincipalComponent2 = 0; }
try{ mp.PrincipalComponent3 = double.Parse(_singleLine[3].Trim()); } catch { mp.PrincipalComponent3 = 0; }
try{ mp.PrincipalComponent4 = double.Parse(_singleLine[4].Trim()); } catch { mp.PrincipalComponent4 = 0; }
mcPrincipalComponent.Add(mp);
}
if (posData == endPeriod)
{
started = false;
break;
}
startPos++;
// allDataStartPeriod = GetNextPeriod(allDataStartPeriod, i);
}
//Continue Principal Component
//////////////////////////Remove IT///////////////////////////////
mcPrincipalComponent = mcPrincipalComponent.Take(mcPrincipalComponent.Count - 2).ToList(); //removeit
//////////////////////////Remove IT///////////////////////////////
// Principal Component SUmmary result
var groupDataStartPos = groupMacroData.IndexOf(groupMacroData.FirstOrDefault(o => o.period == startPeriod));
var lstPrinSummary = new List <MacroResult_PrincipalComponentSummary>();
for (int i = 0; i < loadingOutputResult.Count; i++)
{
var selectedVariable = actual_macvar[i];
var _extractForPrinCompSummary = groupMacroData.Skip(groupDataStartPos - selectedVariable.BackwardOffset).Take(mcPrincipalComponent.Count).ToList();
var sum = new MacroResult_PrincipalComponentSummary();
sum.PrincipalComponentIdA = 1;
sum.PrincipalComponentIdB = 4 + i;
sum.PricipalComponentLabelA = "Mean";
sum.PricipalComponentLabelB = $"PrinComp{i + 1}";
sum.MacroId = macroId;
var sum1 = new MacroResult_PrincipalComponentSummary();
sum1.PrincipalComponentIdA = 2;
sum1.PrincipalComponentIdB = 4 + i;
sum1.PricipalComponentLabelA = "std.Dev";
sum1.PricipalComponentLabelB = $"PrinComp{i + 1}";
sum1.MacroId = macroId;
if (i == 0)
{
sum.Value = _extractForPrinCompSummary.Average(o => o.MacroValue1);
sum1.Value = Computation.GetStandardDeviationS(_extractForPrinCompSummary.Select(o => o.MacroValue1));
}
if (i == 1)
{
sum.Value = _extractForPrinCompSummary.Average(o => o.MacroValue2);
sum1.Value = Computation.GetStandardDeviationS(_extractForPrinCompSummary.Select(o => o.MacroValue2));
}
if (i == 2)
{
sum.Value = _extractForPrinCompSummary.Average(o => o.MacroValue3);
sum1.Value = Computation.GetStandardDeviationS(_extractForPrinCompSummary.Select(o => o.MacroValue3));
}
if (i == 3)
{
sum.Value = _extractForPrinCompSummary.Average(o => o.MacroValue4);
sum1.Value = Computation.GetStandardDeviationS(_extractForPrinCompSummary.Select(o => o.MacroValue4));
}
lstPrinSummary.Add(sum);
lstPrinSummary.Add(sum1);
sum = new MacroResult_PrincipalComponentSummary();
sum.PrincipalComponentIdA = 3;
sum.PrincipalComponentIdB = 4 + i;
sum.PricipalComponentLabelA = "EigenValues";
sum.PricipalComponentLabelB = $"PrinComp{i + 1}";
sum.MacroId = macroId;
sum.Value = eIngenValues[i];
lstPrinSummary.Add(sum);
for (int j = 0; j < loadingOutputResult[i].Count; j++)
{
sum = new MacroResult_PrincipalComponentSummary();
sum.PrincipalComponentIdA = 4 + j;
sum.PrincipalComponentIdB = 4 + i;
sum.PricipalComponentLabelA = $"PrinComp{j + 1}";
sum.PricipalComponentLabelB = $"PrinComp{i + 1}";
sum.MacroId = macroId;
sum.Value = loadingOutputResult[i][j];
lstPrinSummary.Add(sum);
}
}
// Get Statistical Data
var statistics = new MacroResult_Statistics();
try { statistics.IndexWeight1 = eIngenValues[0] < 1 ? 0 : eIngenValues[0]; } catch { statistics.IndexWeight1 = 0; }
try{ statistics.IndexWeight2 = eIngenValues[1] < 1 ? 0 : eIngenValues[1]; } catch { statistics.IndexWeight2 = 0; }
try{ statistics.IndexWeight3 = eIngenValues[2] < 1 ? 0 : eIngenValues[2]; } catch { statistics.IndexWeight3 = 0; }
try{ statistics.IndexWeight4 = eIngenValues[3] < 1 ? 0 : eIngenValues[3]; } catch { statistics.IndexWeight4 = 0; }
statistics.IndexWeight3 = 0;
statistics.IndexWeight4 = 0;
// Get Index Data
var indxData = new List <MacroResult_IndexData>();
for (int i = 0; i < mcPrincipalComponent.Count; i++)
{
var mcp = mcPrincipalComponent[i];
var indx = new MacroResult_IndexData();
// if(extractForPrinCompSummary.Count>(i + maxBackLag))
// {
var extractForPrinCompSummary = groupMacroData.Skip(groupDataStartPos).Take(mcPrincipalComponent.Count).ToList();
indx.MacroId = macroId;
indx.Period = extractForPrinCompSummary[i].period;
indx.BfNpl = extractForPrinCompSummary[i].NPL;
mcPrincipalComponent[i].PrincipalComponent1 = mcPrincipalComponent[i].PrincipalComponent1 ?? 0;
mcPrincipalComponent[i].PrincipalComponent2 = mcPrincipalComponent[i].PrincipalComponent2 ?? 0;
mcPrincipalComponent[i].PrincipalComponent3 = mcPrincipalComponent[i].PrincipalComponent3 ?? 0;
mcPrincipalComponent[i].PrincipalComponent4 = mcPrincipalComponent[i].PrincipalComponent4 ?? 0;
statistics.IndexWeight1 = statistics.IndexWeight1 ?? 0;
statistics.IndexWeight2 = statistics.IndexWeight2 ?? 0;
statistics.IndexWeight3 = statistics.IndexWeight3 ?? 0;
statistics.IndexWeight4 = statistics.IndexWeight4 ?? 0;
indx.Index = (mcPrincipalComponent[i].PrincipalComponent1.Value * statistics.IndexWeight1.Value) + (mcPrincipalComponent[i].PrincipalComponent2.Value * statistics.IndexWeight2.Value) + (mcPrincipalComponent[i].PrincipalComponent3.Value * statistics.IndexWeight3.Value) + (mcPrincipalComponent[i].PrincipalComponent4.Value * statistics.IndexWeight4.Value);
indxData.Add(indx);
// }
}
statistics.StandardDev = 0;
statistics.Average = 0;
statistics.Correlation = 0;
statistics.TTC_PD = 0;
try { statistics.StandardDev = Computation.GetStandardDeviationP(indxData.Select(o => o.Index).ToList()); } catch { }
try{ statistics.Average = indxData.Average(o => o.Index); } catch { }
try{ statistics.TTC_PD = indxData.Average(o => o.BfNpl); } catch { }
for (int i = 0; i < indxData.Count; i++)
{
indxData[i].StandardIndex = 0;
try { indxData[i].StandardIndex = (indxData[i].Index - statistics.Average.Value) / statistics.StandardDev.Value; } catch { }
}
var arry1 = indxData.Select(o => o.StandardIndex).ToArray();
var arry2 = indxData.Select(o => o.StandardIndex).ToArray();
var fitResult = new FitResult();
statistics.Correlation = fitResult.ComputeCoeff(arry1, arry2);
// Get CorMat
var macV1 = groupMacroData.Select(o => o.MacroValue1).ToList();
var macV2 = groupMacroData.Select(o => o.MacroValue2).ToList();
var macV3 = groupMacroData.Select(o => o.MacroValue3).ToList();
var macV4 = groupMacroData.Select(o => o.MacroValue4).ToList();
var allMacV = new List <List <double> > {
macV1, macV2, macV3, macV4
};
var lstCorMat = new List <MacroResult_CorMat>();
for (int i = 0; i < actual_macvar.Count; i++)
{
for (int j = 0; j < actual_macvar.Count; j++)
{
var sum = new MacroResult_CorMat();
sum.MacroEconomicIdA = 0;
sum.MacroEconomicIdB = 0;
sum.MacroEconomicLabelA = "";
sum.MacroEconomicLabelB = "";
sum.Value = 0;
try { sum.MacroEconomicIdA = actual_macvar[i].MacroeconomicVariableId; } catch { }
try { sum.MacroEconomicIdB = actual_macvar[j].MacroeconomicVariableId; } catch { }
try { sum.MacroEconomicLabelA = $"{actual_macvar[i].varTitle}-{actual_macvar[i].BackwardOffset}"; } catch { }
try { sum.MacroEconomicLabelB = $"{actual_macvar[j].varTitle}-{actual_macvar[j].BackwardOffset}"; } catch { }
sum.MacroId = macroId;
try { sum.Value = MathNet.Numerics.Statistics.Correlation.Pearson(allMacV[i], allMacV[j]); } catch { }
lstCorMat.Add(sum);
}
}
//Delete all affiliate macroData
qry = Queries.DeleteAffiliateMacroData(macroId, affiliateId);
DataAccess.i.ExecuteQuery(qry);
var sb = new StringBuilder();
// Save Principal Component Result to DB
foreach (var prinC in mcPrincipalComponent)
{
sb.Append(Queries.MacroResult_PrinC(macroId, prinC.PrincipalComponent1, prinC.PrincipalComponent2, prinC.PrincipalComponent3, prinC.PrincipalComponent4));
}
qry = sb.ToString().Replace("NaN", "0");
DataAccess.i.ExecuteQuery(qry);
sb = new StringBuilder();
// Save Index Result to DB
foreach (var mId in indxData)
{
sb.Append(Queries.MacroResult_IndxResult(macroId, mId.Period, mId.Index, mId.StandardIndex, mId.BfNpl));
}
qry = sb.ToString().Replace("NaN", "0");
DataAccess.i.ExecuteQuery(qry);
sb = new StringBuilder();
// Save Statistics Index Result to DB
sb.Append(Queries.MacroResult_StatisticalIndex(macroId, statistics.IndexWeight1, statistics.IndexWeight2, statistics.IndexWeight3,
statistics.IndexWeight4, statistics.StandardDev, statistics.Average, statistics.Correlation, statistics.TTC_PD));
qry = sb.ToString().Replace("NaN", "0");
DataAccess.i.ExecuteQuery(qry);
sb = new StringBuilder();
// Save Correlation Mat Index Result to DB
foreach (var corMar in lstCorMat)
{
sb.Append(Queries.MacroResult_CorMat(macroId, corMar.MacroEconomicIdA, corMar.MacroEconomicIdB, corMar.MacroEconomicLabelA, corMar.MacroEconomicLabelB, corMar.Value));
}
qry = sb.ToString().Replace("NaN", "0");
DataAccess.i.ExecuteQuery(qry);
// Save Principal Component Result to DB
sb = new StringBuilder();
foreach (var pcs in lstPrinSummary)
{
sb.Append(Queries.MacroResult_PrincipalComponent(macroId, pcs.PrincipalComponentIdA, pcs.PrincipalComponentIdB, pcs.PricipalComponentLabelA, pcs.PricipalComponentLabelB, pcs.Value));
}
qry = sb.ToString().Replace("NaN", "0");
DataAccess.i.ExecuteQuery(qry);
//Actual Selected MacroEconomic Variable
sb = new StringBuilder();
foreach (var itm in actual_macvar)
{
sb.Append(Queries.MacroResult_SelectedMacroEconomicVariables(itm.BackwardOffset, itm.AffiliateId, itm.MacroeconomicVariableId));
}
qry = sb.ToString().Replace("NaN", "0");
DataAccess.i.ExecuteQuery(qry);
return(true);
}
