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