예제 #1
0
 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);
 }
예제 #2
0
        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;
                }
            }
        }
예제 #3
0
        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;
                }
            }
        }
예제 #4
0
        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;
                }
            }
        }
예제 #5
0
        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;
                }
            }
        }