public RowData InsertData(string excelYear, RowData dt, string data) { if (excelYear == "2011" || excelYear == "42916" || excelYear == "30-Jun-17") { dt.fData1 = data; } if (excelYear == "2012" || excelYear == "43008" || excelYear == "30-Sep-17") { dt.fData2 = data; } if (excelYear == "2013" || excelYear == "43190" || excelYear == "31-Mar-18") { dt.fData3 = data; } if (excelYear == "2014" || excelYear == "43281" || excelYear == "30-Jun-18") { dt.fData4 = data; } if (excelYear == "2015" || excelYear == "43373" || excelYear == "30-Sep-18") { dt.fData5 = data; } if (excelYear == "2016" || excelYear == "43555" || excelYear == "31-Mar-19") { dt.fData6 = data; } if (excelYear == "2017" || excelYear == "43646" || excelYear == "30-Jun-19") { dt.fData7 = data; } if (excelYear == "2018" || excelYear == "43738" || excelYear == "30-Sep-19") { dt.fData8 = data; } return(dt); }
public void Ratio(DataSet ds, int j, string fileName) { string[] Qyear = new string[] { "42916", "43008", "43190", "43281", "43373", "43555", "43646", "43738", "30-Jun-17", "30-Sep-17", "31-Mar-18", "30-Jun-18", "30-Sep-18", "31-Mar-19", "30-Jun-19", "30-Sep-19" }; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString); string query1 = string.Empty; string company = string.Empty; int fRow = 0; bool YearlyBlc = false; bool QBlc = false; for (int row = 0; row < ds.Tables[j].Rows.Count; row++) { dt = new RowData(); company = ds.Tables[j].Columns[0].ToString().Replace("'", string.Empty).Trim(); string check = ds.Tables[j].Rows[row][0].ToString().Replace("'", string.Empty).Trim(); for (int col = 0; col < ds.Tables[j].Columns.Count; col++) { var data = ds.Tables[j].Rows[row][col].ToString().Replace("%", string.Empty).Trim(); var excelYear = ds.Tables[j].Rows[fRow][col].ToString(); if (data == "2018" || data == "2017" || data == "2016" || data == "2015" || data == "2014" || data == "2013") { fRow = row; YearlyBlc = true; break; } if (Qyear.Contains(data)) { fRow = row; QBlc = true; break; } try { data = decimal.Round(decimal.Parse(data) * 100, 2).ToString(); dt = InsertData(excelYear, dt, data); } catch (Exception) { continue; } } if (fRow == 0 || row == fRow) { continue; } if (YearlyBlc == true) { query1 = "Insert into Ratios(Head1,Y2013,Y2014,Y2015,Y2016,Y2017,Y2018,CompanyName,FileName) Values('" + check + "','" + dt.fData3 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (QBlc == true) { query1 = "Insert into QRatio(Head1,[30Jun17],[30Sep17],[31Mar18],[30Jun18],[30Sep18],[31Mar19],[30Jun19],[30Sep19],CompanyName,FileName) Values('" + check + "','" + dt.fData1 + "','" + dt.fData2 + "','" + dt.fData3 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (check == "Advance/Investment to Deposit Ratio" || check == "Investment to Deposit Ratio") { break; } } }
public void BalanceSheet(DataSet ds, int j, string fileName) { string[] sub = new string[] { "propertyandassets", "liabilitiesandcapital", "assets", "equityandliabilities" }; string[] sub2 = new string[] { "cash", "noncurrentassets", "currentassets", "balancewithotherbanksandfinancialinstitutions", "moneyatcallandonshortnotice", "currentliabilities", "noncurrentliabilities", "investments", "loansandadvances/investments", "totalassets", "fixedassetsincludingpremises,furnitureandfixtures", "otherassets", "non-bankingassets", "liabilities", "borrowingsfromotherbanks,financialinstitutionsandagents", "abbanksubordinatedbond", "depositsandotheraccounts", "otherliabilities", "shareholdersequity", "shareholders’equity", "equityattributable\ntoequityholdersoftheparentcompany", "non-controllinginterest", "netassetsvaluepershare", "sharestocalculatenavps" }; string[] Qyear = new string[] { "42916", "43008", "43190", "43281", "43373", "43555", "43646", "43738", "30-Jun-17", "30-Sep-17", "31-Mar-18", "30-Jun-18", "30-Sep-18", "31-Mar-19", "30-Jun-19", "30-Sep-19" }; string head1 = string.Empty; string head2 = string.Empty; string head3 = string.Empty; int fRow = 0; string company = ds.Tables[j].Columns[0].ToString().Replace("'", string.Empty).Trim(); bool YearlyBlc = false; bool QBlc = false; for (int row = 0; row < ds.Tables[j].Rows.Count; row++) { dt = new RowData(); string check = ds.Tables[j].Rows[row][0].ToString().Replace("'", string.Empty).Trim(); SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString); string query1 = string.Empty; for (int col = 0; col < ds.Tables[j].Columns.Count; col++) { var data = ds.Tables[j].Rows[row][col].ToString().Replace(",", string.Empty).Trim(); if (data == string.Empty) { continue; } if (data == "2018" || data == "2017" || data == "2016" || data == "2015" || data == "2014" || data == "2013") { fRow = row; YearlyBlc = true; break; } if (Qyear.Contains(data)) { fRow = row; QBlc = true; break; } if (sub.Contains(string.Concat(check.Where(c => !char.IsWhiteSpace(c))).ToLower())) { head1 = check; head2 = string.Empty; head3 = string.Empty; } else if (sub2.Contains(string.Concat(check.Where(c => !char.IsWhiteSpace(c))).ToLower())) { head2 = check; head3 = string.Empty; } else { head3 = check; } var excelYear = ds.Tables[j].Rows[fRow][col].ToString(); dt = InsertData(excelYear, dt, data); } if (fRow == 0 || fRow == row) { continue; } if (YearlyBlc == true) { query1 = "Insert into BalanceSheets(Head1,head2,Head3,Y2011,Y2012,Y2013,Y2014,Y2015,Y2016,Y2017,Y2018,CompanyName,FileName) Values('" + head1 + "','" + head2 + "','" + head3 + "','" + dt.fData1 + "','" + dt.fData2 + "','" + dt.fData3 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (QBlc == true) { query1 = "Insert into QBalanceSheet(Head1,Head2,Head3,[30Jun17],[30Sep17],[31Mar18],[30Jun18],[30Sep18],[31Mar19],[30Jun19],[30Sep19],CompanyName,FileName) Values('" + head1 + "','" + head2 + "','" + head3 + "','" + dt.fData1 + "','" + dt.fData2 + "','" + dt.fData3 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (head2 == "Shares to calculate NAVPS") { break; } } }
public void CashFlow(DataSet ds, int j, string fileName) { string[] sub = new string[] { "netcashflows-operatingactivities", "operatingprofitbeforechangesinoperatingassetsandliabilities", "increase/(decrease)inoperatingassetsandliabilities", "netcashflows-investmentactivities", "netcashflows-financingactivities", "netchangeincashflows", "cashandcashequivalentsatbeginningperiod", "cashandcashequivalentsatendofperiod", "netoperatingcashflowpershare", "effectsofexchangeratechangesoncashandcashequivalents", "adjustmentofdisposalofbits", "sharestocalculatenocfps" }; string[] Qyear = new string[] { "42916", "43008", "43190", "43281", "43373", "43555", "43646", "43738", "30-Jun-17", "30-Sep-17", "31-Mar-18", "30-Jun-18", "30-Sep-18", "31-Mar-19", "30-Jun-19", "30-Sep-19" }; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString); string company = string.Empty; string head1 = string.Empty; string head2 = string.Empty; int coln = 0; int fRow = 0; bool YearlyBlc = false; bool Qblc = false; for (int row = 0; row < ds.Tables[j].Rows.Count; row++) { dt = new RowData(); string query1 = string.Empty; for (int col = 0; col < ds.Tables[j].Columns.Count; col++) { var data = ds.Tables[j].Rows[row][col].ToString(); var excelYear = ds.Tables[j].Rows[fRow][col].ToString(); if (data == string.Empty) { continue; } if (data == "2018" || data == "2017" || data == "2016" || data == "2015" || data == "2014" || data == "2013") { fRow = row; YearlyBlc = true; break; } if (Qyear.Contains(data)) { fRow = row; Qblc = true; break; } if (company == string.Empty) { company = ds.Tables[j].Columns[col].ToString().Replace("'", string.Empty).Trim(); coln = col; } string check = ds.Tables[j].Rows[row][coln].ToString().Replace("'", string.Empty).Trim(); if (sub.Contains(string.Concat(check.Where(c => !char.IsWhiteSpace(c))).ToLower())) { head1 = check; head2 = string.Empty; } else { head2 = check; } dt = InsertData(excelYear, dt, data); } if (fRow == 0 || fRow == row) { continue; } if (YearlyBlc == true) { query1 = "Insert into YCashflow(Head1,Head2,[Y2011],[Y2012],[Y2013],[Y2014],[Y2015],[Y2016],[Y2017],[Y2018],CompanyName,FileName) Values('" + head1 + "','" + head2 + "','" + dt.fData1 + "','" + dt.fData2 + "','" + dt.fData3 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (Qblc == true) { query1 = "Insert into QCashFlow(Head1,Head2,[30Jun17],[30Sep17],[31Mar18],[30Jun18],[30Sep18],[31Mar19],[30Jun19],[30Sep19],CompanyName,FileName) Values('" + head1 + "','" + head2 + "','" + dt.fData1 + "','" + dt.fData2 + "','" + dt.fData3 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (head1 == "Shares to Calculate NOCFPS") { break; } } }
public void IncomeStatement(DataSet ds, int j, string fileName) { string[] sub = new string[] { "operatingincome", "netinterestincome/netprofitoninvestments", "operatingexpense", "operatingprofit", "totalprovisions", "lossondisposalofabexchange(uk)ltd.", "profitbeforetaxation", "provisionfortaxation", "netprofit", "earningspershare(parvaluetaka10)", "sharestocalculateeps" }; string[] Qyear = new string[] { "42916", "43008", "43190", "43281", "43373", "43555", "43646", "43738", "30-Jun-17", "30-Sep-17", "31-Mar-18", "30-Jun-18", "30-Sep-18", "31-Mar-19", "30-Jun-19", "30-Sep-19" }; string head1 = string.Empty; string head2 = string.Empty; int fRow = 0; bool YearlyBlc = false; bool Qblc = false; for (int row = 0; row < ds.Tables[j].Rows.Count; row++) { string company = ds.Tables[j].Columns[0].ToString().Replace("'", string.Empty).Trim(); string check = ds.Tables[j].Rows[row][0].ToString().Replace("'", string.Empty).Trim(); SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString); string query1 = string.Empty; dt = new RowData(); //check insert for (int col = 0; col < ds.Tables[j].Columns.Count; col++) { var data = ds.Tables[j].Rows[row][col].ToString(); var excelYear = ds.Tables[j].Rows[fRow][col].ToString(); if (data == string.Empty) { continue; } if (data == "2018" || data == "2017" || data == "2016" || data == "2015" || data == "2014" || data == "2013") { fRow = row; YearlyBlc = true; break; } if (Qyear.Contains(data)) { fRow = row; Qblc = true; break; } else if (sub.Contains(string.Concat(check.Where(c => !char.IsWhiteSpace(c))).ToLower())) { head1 = check; head2 = string.Empty; } else { head2 = check; } dt = InsertData(excelYear, dt, data); } if (fRow == 0 || fRow == row) { continue; } if (YearlyBlc == true) { query1 = "Insert into IncomeStates(Head1,Head2,Y2014,Y2015,Y2016,Y2017,Y2018,CompanyName,FileName) Values('" + head1 + "','" + head2 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (Qblc == true) { query1 = "Insert into QIncomeState(Head1,Head2,[30Jun17],[30Sep17],[31Mar18],[30Jun18],[30Sep18],[31Mar19],[30Jun19],[30Sep19],CompanyName,FileName) Values('" + head1 + "','" + head2 + "','" + dt.fData1 + "','" + dt.fData2 + "','" + dt.fData3 + "','" + dt.fData4 + "','" + dt.fData5 + "','" + dt.fData6 + "','" + dt.fData7 + "','" + dt.fData8 + "','" + company + "','" + fileName + "')"; con.Open(); SqlCommand cmd = new SqlCommand(query1, con); cmd.ExecuteNonQuery(); con.Close(); } if (head1 == "Shares to Calculate EPS") { break; } } }