private DiscountedCashFlow GetNetIncomeDiscountedCashFlow(CompanyValuationStatistics cvs, IncomeStatementCollection incProjections,
                                                                  decimal wacc, decimal terminalGrowth)
        {
            FreeCashFlowCollection fcfs     = _fcfMngr.CalculateFreeCashFlow(cvs.Symbol, cvs.Year, cvs.NoOfYears, incProjections);
            DiscountedCashFlow     dcf      = null;
            BalanceSheet           bs       = BalanceSheetBL.Instance.GetBalanceSheet(cvs.Symbol, cvs.Year);
            CompanyAnnualData      compData = CompanyAnnualDataBL.Instance.GetCompanyAnnual(cvs.Symbol, cvs.Year);

            if (fcfs != null)
            {
                dcf = new DiscountedCashFlow(cvs.Symbol, cvs.Year, fcfs, wacc, terminalGrowth);
                dcf.EnterpriseValue = 0;
                for (int i = 1; i < cvs.NoOfYears + 1; i++)
                {
                    decimal discountTo = (decimal)(Math.Pow((double)dcf.DiscountFactor, i));
                    dcf.EnterpriseValue += (dcf.FreeCashFlows[i - 1].NetIncomeCashFlow * discountTo);
                }

                dcf.EnterpriseValue += (dcf.TerminalValue * (decimal)(Math.Pow((double)dcf.DiscountFactor, dcf.FreeCashFlows.Count)));

                dcf.EquityValue = dcf.EnterpriseValue + bs.Cash - bs.Debt;

                dcf.StockValue = dcf.EquityValue / compData.SharesOutstanding;
            }

            return(dcf);
        }
 private static void SetCompanyAnnualParameters(CompanyAnnualData comp, MySqlParameter[] parms)
 {
     parms[0].Value = comp.Symbol;
     parms[1].Value = comp.Year;
     parms[2].Value = comp.CostOfDebt;
     parms[3].Value = comp.LeveredBeta;
     parms[4].Value = comp.SharesOutstanding;
     parms[5].Value = comp.DividendYield;
 }
 public void UpdateCompanyAnnual(CompanyAnnualData comp)
 {
     MySqlParameter[] parms = GetCompanyAnnualParameters();
     SetCompanyAnnualParameters(comp, parms);
     using (MySqlConnection conn = new MySqlConnection(MySqlHelper.SV_CONN_STRING))
     {
         conn.Open();
         MySqlHelper.ExecuteNonQuery(conn, SQL_UPDATE_COMPANYANNUAL, parms);
     }
 }
Пример #4
0
 public void UpdateCompanyAnnual(CompanyAnnualData comp)
 {
     if (CompanyAnnualExists(comp.Symbol, comp.Year))
     {
         _dao.UpdateCompanyAnnual(comp);
     }
     else if (comp.SharesOutstanding != 0)
     {
         _dao.InsertCompanyAnnual(comp);
     }
 }
        public void AddCompanyToDatabase(string symbol, int year, int yearsToLoad)
        {
            CompanyBL.Instance.UpdateCompany(YahooHtmlParser.Instance.GetCompanyProfile(symbol));

            IncomeStatementCollection incs   = YahooHtmlParser.Instance.GetIncomeStatementData(symbol, year, yearsToLoad);
            BalanceSheetCollection    bals   = YahooHtmlParser.Instance.GetBalanceSheetData(symbol, year, yearsToLoad);
            CompanyAnnualData         compAn = YahooHtmlParser.Instance.GetCompanyAnnualData(symbol, year);

            YahooHtmlParser.Instance.GetCashFlowStatementData(symbol, year, yearsToLoad, incs);

            EnterFinancialData(incs, bals, compAn, year, yearsToLoad);
        }
        private CompanyAnnualData ConvertReaderToCompanyAnnualObject(MySqlDataReader rdr)
        {
            CompanyAnnualData cmp = new CompanyAnnualData();

            cmp.Symbol            = MySqlHelper.ConvertReaderToString(rdr, "SYMBOL");
            cmp.Year              = MySqlHelper.ConvertReaderToInt(rdr, "YEAR");
            cmp.CostOfDebt        = MySqlHelper.ConvertReaderToDecimal(rdr, "COST_OF_DEBT");
            cmp.LeveredBeta       = MySqlHelper.ConvertReaderToDecimal(rdr, "LEVERED_BETA");
            cmp.SharesOutstanding = MySqlHelper.ConvertReaderToDecimal(rdr, "SHARES_OUTSTANDING");
            cmp.DividendYield     = MySqlHelper.ConvertReaderToDecimal(rdr, "DIVIDEND_YIELD");

            return(cmp);
        }
Пример #7
0
        public void ProcessXbrlInstanceDocument(string xmlUrl, Dictionary <string, List <string> > xbrlTaxonomyTree)
        {
            JeffFerguson.Gepsio.XbrlDocument xbrlDoc = new XbrlDocument();
            xbrlDoc.Load(xmlUrl);

            //AddXbrlNodes(xbrlDoc);

            int yearsToLoad = 3;

            string fileName     = System.IO.Path.GetFileNameWithoutExtension(xmlUrl);
            string tickerSymbol = fileName.Split(new[] { '-' })[0];
            int    year         = GetFiscalYear(tickerSymbol, xbrlDoc.XbrlFragments[0]);

            IncomeStatement   incToAdd     = new IncomeStatement();
            BalanceSheet      balToAdd     = new BalanceSheet();
            CompanyAnnualData compAnnToAdd = new CompanyAnnualData();

            Dictionary <string, Object> yahooStats = GetYahooStatistics(tickerSymbol);

            CreateCompanyObject(tickerSymbol, xbrlDoc, yahooStats);

            for (int y = year; y > year - yearsToLoad; y--)
            {
                XbrlIncomeStatementFilter incFilter    = new XbrlIncomeStatementFilter();
                XbrlBalanceSheetFilter    bsFilter     = new XbrlBalanceSheetFilter();
                XbrlCompanyAnnualFilter   compAnFilter = new XbrlCompanyAnnualFilter();

                incToAdd     = incFilter.Populate(this, xbrlDoc, tickerSymbol, y, xbrlTaxonomyTree);
                balToAdd     = bsFilter.Populate(xbrlDoc, tickerSymbol, y, xbrlTaxonomyTree);
                compAnnToAdd = compAnFilter.Populate(xbrlDoc, tickerSymbol, y);

                compAnnToAdd.LeveredBeta   = Convert.ToDecimal(yahooStats["Beta"]);
                compAnnToAdd.DividendYield = Convert.ToDecimal(yahooStats["DividendYield"]);

                IncomeStatementBL.Instance.UpdateIncomeStatement(incToAdd);
                BalanceSheetBL.Instance.UpdateBalanceSheet(balToAdd);
                CompanyAnnualDataBL.Instance.UpdateCompanyAnnual(compAnnToAdd);
            }
        }
Пример #8
0
        public CompanyAnnualData Populate(XbrlDocument xbrlDoc, string symbol, int year)
        {
            CompanyAnnualData ann = new CompanyAnnualData();

            ann.Symbol = symbol;
            ann.Year   = year;

            CompanyAnnualFilterItem item = new CompanyAnnualFilterItem();

            foreach (XbrlFragment frag in xbrlDoc.XbrlFragments)
            {
                foreach (Item xbrlItem in frag.Facts)
                {
                    if (xbrlItem.ContextRef.InstantDate.Year == ann.Year)
                    {
                        if (!xbrlItem.Type.Name.Equals("monetaryItemType"))
                        {
                            item = CheckItem(xbrlItem, item);
                        }
                    }
                }
            }
            ann = PopulateFinancialStatement(item, ann);

            if (ann.SharesOutstanding < 0)
            {
                ann.SharesOutstanding = ann.SharesOutstanding * -1;
            }
            if (ann.SharesOutstanding < 1000)
            {
                ann.SharesOutstanding = ann.SharesOutstanding * 1000000;
            }
            else if (ann.SharesOutstanding < 1000000)
            {
                ann.SharesOutstanding = ann.SharesOutstanding * 1000;
            }

            return(ann);
        }
        public CompanyAnnualData GetCompanyAnnual(string tickerSymbol, int year)
        {
            CompanyAnnualData cmp = null;

            MySqlParameter[] parms = new MySqlParameter[] {
                new MySqlParameter("@SYMBOL", MySqlDbType.VarChar),
                new MySqlParameter("@YEAR", MySqlDbType.Int16)
            };

            parms[0].Value = tickerSymbol;
            parms[1].Value = year;

            //Execute Query
            using (MySqlDataReader rdr = MySqlHelper.ExecuteReader(MySqlHelper.SV_CONN_STRING, SQL_SELECT_COMPANY, parms))
            {
                if (rdr.Read())
                {
                    cmp = ConvertReaderToCompanyAnnualObject(rdr);
                }
            }

            return(cmp);
        }
        private void EnterFinancialData(IncomeStatementCollection incs, BalanceSheetCollection bals, CompanyAnnualData cad, int year, int yearsToLoad)
        {
            CompanyAnnualDataBL.Instance.UpdateCompanyAnnual(cad);

            for (int y = year; y > year - yearsToLoad; y--)
            {
                IncomeStatementBL.Instance.UpdateIncomeStatement(incs.Find(y));
                BalanceSheetBL.Instance.UpdateBalanceSheet(bals.Find(y));
            }
        }
        public void PopulateCompanyFinancialStatisticsFromStatements(CompanyFinancialStatistics finStats, IncomeStatement inc, BalanceSheet bs, CompanyAnnualData cad)
        {
            finStats.BookValuePerShare = bs.ShareholdersEquity / cad.SharesOutstanding;
            finStats.DebtToEquity      = bs.Debt / bs.ShareholdersEquity;
            finStats.ProfitMargin      = inc.NetIncome / inc.Revenue;
            finStats.ReturnOnAssets    = inc.NetIncome / bs.TotalAssets;

            if (bs.ShareholdersEquity == 0)
            {
                finStats.ReturnOnEquity = 0;
            }
            else
            {
                finStats.ReturnOnEquity = inc.NetIncome / bs.ShareholdersEquity;
            }

            finStats.RevenuePerShare = inc.Revenue / cad.SharesOutstanding;
            finStats.TotalDebt       = bs.Debt;
        }
Пример #12
0
        internal CompanyAnnualData PopulateFinancialStatement(CompanyAnnualFilterItem item, CompanyAnnualData ann)
        {
            ann.CostOfDebt        = GetAvgValue(item.CostOfDebt);
            ann.SharesOutstanding = GetMaxValue(item.SharesOutstanding);

            return(ann);
        }
        public void CreateAbbreviatedExcelDocument()
        {
            string filename = @"C:\Users\Daniel\Documents\Visual Studio 2012\ExcelFiles\abbreviated_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + "_" + DateTime.Now.Hour + DateTime.Now.Minute + ".xlsx";

            FileInfo newFile = new FileInfo(filename);

            using (ExcelPackage xlPackage = new ExcelPackage(newFile))
            {
                string[] columns = AbrreviatedColumns();

                ExcelWorksheet compInfoWS = xlPackage.Workbook.Worksheets.Add("Company Info");

                int i = 1;
                compInfoWS.Row(1).Style.Font.Bold = true;
                foreach (string col in columns)
                {
                    compInfoWS.Cells[1, i].Value = col;
                    i++;
                }

                CompanyCollection comps = CompanyBL.Instance.GetCompanies();

                CompanyValuationStatisticsCollection valuations = new CompanyValuationStatisticsCollection();
                CompanyFinancialStatisticsCollection statistics = new CompanyFinancialStatisticsCollection();
                CompanyValuationStatistics           cvs;

                int count = 2;
                int x     = 0;
                foreach (Company comp in comps)
                {
                    x++;
                    //if (comp.Symbol.Equals("azo"))
                    //{
                    try
                    {
                        cvs = GetCompanyValuationStatistics(comp);
                        valuations.Add(cvs);
                        CompanyFinancialStatistics stats = CompanyFinancialStatisticsBL.Instance.GetCompanyFinancialStatistics(comp.Symbol, DateTime.Today.AddDays(-1));
                        statistics.Add(stats);
                        BalanceSheet      bal  = BalanceSheetBL.Instance.GetBalanceSheet(comp.Symbol, 2012);
                        IncomeStatement   inc  = IncomeStatementBL.Instance.GetIncomeStatement(comp.Symbol, 2012);
                        CompanyAnnualData data = CompanyAnnualDataBL.Instance.GetCompanyAnnual(comp.Symbol, 2012);

                        int f = 1;

                        compInfoWS.Cells[count, f++].Value   = comp.Symbol;
                        compInfoWS.Cells[count, f++].Value   = comp.CompanyName;
                        compInfoWS.Cells[count, f++].Value   = comp.Industry;
                        compInfoWS.Cells[count, f++].Value   = comp.Sector;
                        compInfoWS.Cells[count, f++].Value   = stats.StockPrice;
                        compInfoWS.Cells[count, f++].Value   = stats.BookValuePerShare;
                        compInfoWS.Cells[count, f++].Value   = stats.ReturnOnEquity;
                        compInfoWS.Cells[count, f++].Value   = stats.ReturnOnAssets;
                        compInfoWS.Cells[count, f++].Value   = stats.DebtToEquity;
                        compInfoWS.Cells[count, f++].Value   = stats.YearHigh;
                        compInfoWS.Cells[count, f++].Value   = stats.YearLow;
                        compInfoWS.Cells[count, f++].Value   = stats.Eps;
                        compInfoWS.Cells[count, f++].Value   = stats.RevenuePerShare;
                        compInfoWS.Cells[count, f++].Value   = stats.AverageVolume;
                        compInfoWS.Cells[count, f++].Value   = stats.MarketCap;
                        compInfoWS.Cells[count, f++].Value   = stats.TotalDebt;
                        compInfoWS.Cells[count, f++].Value   = stats.CurrentSharesShort;
                        compInfoWS.Cells[count, f++].Value   = stats.LastMonthSharesShort;
                        compInfoWS.Cells[count, f++].Value   = (stats.CurrentSharesShort / data.SharesOutstanding);
                        compInfoWS.Cells[count, f++].Value   = (stats.LastMonthSharesShort / data.SharesOutstanding);
                        compInfoWS.Cells[count, f++].Value   = stats.FiftyMovingAverage;
                        compInfoWS.Cells[count, f++].Value   = stats.TwoHundredMovingAverage;
                        compInfoWS.Cells[count, f++].Value   = stats.DividendYieldPercent;
                        compInfoWS.Cells[count, f++].Value   = stats.OperatingMargin;
                        compInfoWS.Cells[count, f++].Value   = stats.ProfitMargin;
                        compInfoWS.Cells[count, f++].Value   = stats.TrailingPE;
                        compInfoWS.Cells[count, f++].Value   = stats.ForwardPE;
                        compInfoWS.Cells[count, f++].Value   = stats.PegRatio;
                        compInfoWS.Cells[count, f++].Value   = stats.EnterpriseValue;
                        compInfoWS.Cells[count, f++].Value   = stats.PriceToSales;
                        compInfoWS.Cells[count, f++].Value   = stats.PriceToBook;
                        compInfoWS.Cells[count, f++].Value   = stats.EvToRevenue;
                        compInfoWS.Cells[count, f++].Value   = stats.EvToEbitda;
                        compInfoWS.Cells[count, f++].Value   = stats.QuarterlyRevenueGrowth;
                        compInfoWS.Cells[count, f++].Value   = stats.QuarterlyEarningsGrowth;
                        compInfoWS.Cells[count, f++].Value   = stats.NetIncomeToCommonShares;
                        compInfoWS.Cells[count, f++].Value   = stats.TotalCash;
                        compInfoWS.Cells[count, f++].Value   = stats.CurrentRatio;
                        compInfoWS.Cells[count, f++].Value   = stats.OperatingCashFlow;
                        compInfoWS.Cells[count, f++].Value   = stats.LeveredCashFlow;
                        compInfoWS.Cells[count, f++].Value   = stats.Roic();
                        compInfoWS.Cells[count, f++].Value   = inc.Ebit;
                        compInfoWS.Cells[count, f++].Value   = cvs.NopDcfsAvgGrowth.GetAverage("StockValue");
                        compInfoWS.Cells[count, f++].Value   = cvs.NopDcfsAvgGrowth.GetStandardDeviation("StockValue");
                        compInfoWS.Cells[count, f++].Value   = string.Format("={0}/{1}", compInfoWS.Cells[count, 43].Address, compInfoWS.Cells[count, 44].Address);
                        compInfoWS.Cells[count, f++].Value   = cvs.NopDcfsDecayGrowth.GetAverage("StockValue");
                        compInfoWS.Cells[count, f++].Value   = cvs.NopDcfsDecayGrowth.GetStandardDeviation("StockValue");
                        compInfoWS.Cells[count, f++].Value   = string.Format("={0}/{1}", compInfoWS.Cells[count, 46].Address, compInfoWS.Cells[count, 47].Address);
                        compInfoWS.Cells[count, f++].Value   = cvs.NopDcfsNoGrowth.GetAverage("StockValue");
                        compInfoWS.Cells[count, f++].Value   = cvs.NopDcfsNoGrowth.GetStandardDeviation("StockValue");
                        compInfoWS.Cells[count, f++].Value   = string.Format("={0}/{1}", compInfoWS.Cells[count, 49].Address, compInfoWS.Cells[count, 50].Address);
                        compInfoWS.Cells[count, f++].Value   = cvs.NetIncomeDcfsAvgGrowth.GetAverage("StockValue");
                        compInfoWS.Cells[count, f++].Value   = cvs.NetIncomeDcfsAvgGrowth.GetStandardDeviation("StockValue");
                        compInfoWS.Cells[count, f++].Value   = string.Format("={0}/{1}", compInfoWS.Cells[count, 52].Address, compInfoWS.Cells[count, 53].Address);
                        compInfoWS.Cells[count, f++].Value   = cvs.NetIncomeDcfsDecayGrowth.GetAverage("StockValue");
                        compInfoWS.Cells[count, f++].Value   = cvs.NetIncomeDcfsDecayGrowth.GetStandardDeviation("StockValue");
                        compInfoWS.Cells[count, f++].Value   = string.Format("={0}/{1}", compInfoWS.Cells[count, 55].Address, compInfoWS.Cells[count, 56].Address);
                        compInfoWS.Cells[count, f++].Value   = cvs.NetIncomeDcfsNoGrowth.GetAverage("StockValue");
                        compInfoWS.Cells[count, f++].Value   = cvs.NetIncomeDcfsNoGrowth.GetStandardDeviation("StockValue");
                        compInfoWS.Cells[count, f++].Value   = string.Format("={0}/{1}", compInfoWS.Cells[count, 58].Address, compInfoWS.Cells[count, 59].Address);
                        compInfoWS.Cells[count, f++].Formula = string.Format("= {0} - {1}", compInfoWS.Cells[count, 6].Address, compInfoWS.Cells[count, 5].Address);
                        compInfoWS.Cells[count, f++].Formula = string.Format("= {0} - {1}", compInfoWS.Cells[count, 43].Address, compInfoWS.Cells[count, 5].Address);
                        compInfoWS.Cells[count, f++].Formula = string.Format("= {0} - {1}", compInfoWS.Cells[count, 46].Address, compInfoWS.Cells[count, 5].Address);
                        compInfoWS.Cells[count, f++].Formula = string.Format("= {0} - {1}", compInfoWS.Cells[count, 49].Address, compInfoWS.Cells[count, 5].Address);
                        compInfoWS.Cells[count, f++].Formula = string.Format("= {0} - {1}", compInfoWS.Cells[count, 52].Address, compInfoWS.Cells[count, 5].Address);
                        compInfoWS.Cells[count, f++].Formula = string.Format("= {0} - {1}", compInfoWS.Cells[count, 55].Address, compInfoWS.Cells[count, 5].Address);
                        compInfoWS.Cells[count, f++].Formula = string.Format("= {0} - {1}", compInfoWS.Cells[count, 58].Address, compInfoWS.Cells[count, 5].Address);

                        count++;
                        Console.WriteLine("loaded: {0}", comp.Symbol);
                    }
                    catch (Exception ex)
                    { Console.WriteLine(string.Format("ERROR: DID NOT LOAD: {0} {1}", comp.Symbol, ex.Message)); }
                    //}
                }

                xlPackage.Save();
            }
        }