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