public void UpdateCompanyFinancialStatistics(CompanyFinancialStatistics cfs)
{
if (CompanyFinancialStatisticsExists(cfs.Symbol, cfs.Date))
{
_dao.UpdateCompanyFinancialStatistics(cfs);
}
else
{
_dao.InsertCompanyFinancialStatistics(cfs);
}
}
public void EnterAllCompaniesFinancialStatistics()
{
CompanyCollection companies = CompanyBL.Instance.GetCompanies();
foreach (Company comp in companies)
{
try
{
CompanyFinancialStatistics cfs = new CompanyFinancialStatistics(comp.Symbol, DateTime.Today);
YahooHtmlParser.Instance.GetCompaniesFinancialData(cfs);
CompanyFinancialStatisticsBL cfsBL = new CompanyFinancialStatisticsBL();
cfsBL.UpdateCompanyFinancialStatistics(cfs);
Console.WriteLine("LOADED " + comp.Symbol);
}
catch (Exception e)
{
Console.WriteLine("DID NOT LOAD " + comp.Symbol);
}
}
}
public void InsertCompanyFinancialStatistics(CompanyFinancialStatistics inc)
{
MySqlParameter[] parms = GetCompanyFinancialStatisticsParameters();
SetIncomeStatementParameters(inc, parms);
using (MySqlConnection conn = new MySqlConnection(MySqlHelper.SV_CONN_STRING))
{
conn.Open();
MySqlHelper.ExecuteNonQuery(conn, InsertSQLStatement(), parms);
}
}
private static void SetIncomeStatementParameters(CompanyFinancialStatistics comp, MySqlParameter[] parms)
{
int i = 0;
parms[i++].Value = comp.Symbol;
parms[i++].Value = comp.Date;
parms[i++].Value = comp.BookValuePerShare;
parms[i++].Value = comp.StockPrice;
parms[i++].Value = comp.ReturnOnEquity;
parms[i++].Value = comp.ReturnOnAssets;
parms[i++].Value = comp.DebtToEquity;
parms[i++].Value = comp.YearHigh;
parms[i++].Value = comp.YearLow;
parms[i++].Value = comp.Eps;
parms[i++].Value = comp.RevenuePerShare;
parms[i++].Value = comp.AverageVolume;
parms[i++].Value = comp.MarketCap;
parms[i++].Value = comp.TotalDebt;
parms[i++].Value = comp.CurrentSharesShort;
parms[i++].Value = comp.LastMonthSharesShort;
parms[i++].Value = comp.FiftyMovingAverage;
parms[i++].Value = comp.TwoHundredMovingAverage;
parms[i++].Value = comp.DividendYieldPercent;
parms[i++].Value = comp.OperatingMargin;
parms[i++].Value = comp.ProfitMargin;
parms[i++].Value = comp.TrailingPE;
parms[i++].Value = comp.ForwardPE;
parms[i++].Value = comp.PegRatio;
parms[i++].Value = comp.EnterpriseValue;
parms[i++].Value = comp.PriceToSales;
parms[i++].Value = comp.PriceToBook;
parms[i++].Value = comp.EvToRevenue;
parms[i++].Value = comp.EvToEbitda;
parms[i++].Value = comp.QuarterlyRevenueGrowth;
parms[i++].Value = comp.QuarterlyEarningsGrowth;
parms[i++].Value = comp.Ebitda;
parms[i++].Value = comp.NetIncomeToCommonShares;
parms[i++].Value = comp.TotalCash;
parms[i++].Value = comp.CurrentRatio;
parms[i++].Value = comp.OperatingCashFlow;
parms[i++].Value = comp.LeveredCashFlow;
}
private CompanyFinancialStatistics ConvertReaderToCompanyFinancialStatisticsObject(MySqlDataReader rdr)
{
List<string> sqlParams = new List<string>();
foreach (string s in _KEY_COLS)
{
sqlParams.Add(s);
}
foreach (string s in _NON_KEY_COLS)
{
sqlParams.Add(s);
}
int i = 0;
CompanyFinancialStatistics comp = new CompanyFinancialStatistics();
comp.Symbol = MySqlHelper.ConvertReaderToString(rdr, sqlParams[i++]);
comp.Date = MySqlHelper.ConvertReaderToDateTime(rdr, sqlParams[i++]);
comp.BookValuePerShare = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.StockPrice = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.ReturnOnEquity = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.ReturnOnAssets = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.DebtToEquity = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.YearHigh = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.YearLow = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.Eps = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.RevenuePerShare = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.AverageVolume = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.MarketCap = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.TotalDebt = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.CurrentSharesShort = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.LastMonthSharesShort = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.FiftyMovingAverage = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.TwoHundredMovingAverage = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.DividendYieldPercent = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.OperatingMargin = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.ProfitMargin = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.TrailingPE = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.ForwardPE = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.PegRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.EnterpriseValue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.PriceToSales = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.PriceToBook = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.EvToRevenue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.EvToEbitda = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.QuarterlyRevenueGrowth = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.QuarterlyEarningsGrowth = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.Ebitda = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.NetIncomeToCommonShares = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.TotalCash = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.CurrentRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.OperatingCashFlow = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
comp.LeveredCashFlow = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
return comp;
}
public void GetCompaniesFinancialData(CompanyFinancialStatistics fin)
{
string url = "http://finance.yahoo.com/q/ks?s=" + fin.Symbol + "+Key+Statistics";
HtmlDocument doc = new HtmlWeb().Load(url);
foreach (HtmlNode div in doc.DocumentNode.SelectNodes("//span[@class='time_rtq_ticker']"))
{
fin.StockPrice = GetDecimalFromConString(div.ChildNodes[0].InnerHtml);
}
foreach (HtmlNode div in doc.DocumentNode.SelectNodes("//td[@class='yfnc_tablehead1' and @width='74%']"))
{
if (div.InnerHtml.Contains("Market Cap (intraday)"))
{
fin.MarketCap = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Enterprise Value") && !div.InnerHtml.Contains("Revenue") && !div.InnerHtml.Contains("EBITDA"))
{
fin.EnterpriseValue = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Trailing P/E"))
{
fin.TrailingPE= GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Forward P/E"))
{
fin.ForwardPE = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("PEG Ratio"))
{
fin.PegRatio = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Price/Sales"))
{
fin.PriceToSales = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Price/Book"))
{
fin.PriceToBook = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Enterprise Value/Revenue"))
{
fin.EvToRevenue = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Enterprise Value/EBITDA"))
{
fin.EvToEbitda = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Profit Margin"))
{
fin.ProfitMargin = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Operating Margin"))
{
fin.OperatingMargin = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Return on Assets"))
{
fin.ReturnOnAssets = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Return on Equity"))
{
fin.ReturnOnEquity = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Revenue Per Share"))
{
fin.RevenuePerShare = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Qtrly Revenue Growth (yoy)"))
{
fin.QuarterlyRevenueGrowth = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("EBITDA (ttm)"))
{
fin.Ebitda = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Net Income Avl to Common"))
{
fin.NetIncomeToCommonShares = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Diluted EPS"))
{
fin.Eps = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Qtrly Earnings Growth (yoy)"))
{
fin.QuarterlyEarningsGrowth = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Total Cash (mrq)"))
{
fin.TotalCash = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Total Debt (mrq)"))
{
fin.TotalDebt = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Total Debt/Equity"))
{
fin.DebtToEquity = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Current Ratio"))
{
fin.CurrentRatio = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Book Value Per Share"))
{
fin.BookValuePerShare = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Operating Cash Flow"))
{
fin.OperatingCashFlow = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Levered Free Cash Flow"))
{
fin.LeveredCashFlow = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Avg Vol (3 month)"))
{
fin.AverageVolume = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Shares Short (as of"))
{
fin.CurrentSharesShort = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Shares Short (prior month)"))
{
fin.LastMonthSharesShort = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("Forward Annual Dividend Yield"))
{
fin.DividendYieldPercent = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("50-Day Moving Average"))
{
fin.FiftyMovingAverage = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("200-Day Moving Average"))
{
fin.TwoHundredMovingAverage = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("52-Week High"))
{
fin.YearHigh = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
else if (div.InnerHtml.Contains("52-Week Low"))
{
fin.YearLow = GetDecimalFromConString(div.NextSibling.ChildNodes[0].InnerHtml);
}
}
}
private CompanyFinancialStatistics GetFinancialStatistics(CompanyValuationStatistics cvs)
{
CompanyFinancialStatistics cfs = new CompanyFinancialStatistics(cvs.Symbol, new DateTime());
FSFMngr().PopulateCompanyFinancialStatisticsFromStatements(cfs, cvs.AvgGrowthProjections.Find(cvs.Year),
BalanceSheetBL.Instance.GetBalanceSheet(cvs.Symbol, cvs.Year),
CompanyAnnualDataBL.Instance.GetCompanyAnnual(cvs.Symbol, cvs.Year));
//FSFMngr().PopulateCompanyFinancialStatisticsFromYahooApi(cfs);
return cfs;
}
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;
}
