public BalanceSheetCollection GetBalanceSheets(string tickerSymbol, int fromYear, int toYear)
{
BalanceSheetCollection bs = null;
BalanceSheet bal = null;
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@SYMBOL", MySqlDbType.VarChar),
new MySqlParameter("@FROMYEAR", MySqlDbType.Int16),
new MySqlParameter("@TOYEAR", MySqlDbType.Int16)
};
parms[0].Value = tickerSymbol;
parms[1].Value = fromYear;
parms[2].Value = toYear;
//Execute Query
using (MySqlDataReader rdr = MySqlHelper.ExecuteReader(MySqlHelper.SV_CONN_STRING, SQL_SELECT_BALANCESHEETS, parms))
{
if (rdr.Read())
{
//If there is one result
bs = new BalanceSheetCollection();
//Scroll through the results
do
{
bal = ConvertReaderToBalanceSheetObject(rdr);
bs.Add(bal);
}while (rdr.Read());
}
}
return(bs);
}
public BalanceSheetCollection ProjectBalanceSheets(string symbol, int fromYear, int yrsToProject, IncomeStatementCollection incs)
{
BalanceSheetCollection balProjections = BalanceSheetBL.Instance.GetBalanceSheets(symbol, fromYear - 2, fromYear);
if (balProjections == null || balProjections.Count < 1)
{
throw new Exception("Not enough Balance Sheet Data");
}
for (int i = fromYear + 1; i < fromYear + yrsToProject + 1; i++)
{
IncomeStatement thisInc = incs.Find(i);
if (thisInc != null)
{
balProjections.Add(new BalanceSheet(
incs[0].Symbol,
i,
thisInc.Revenue * GetAvgPercentOfSales("CurrentAssets", balProjections, incs),
thisInc.Revenue * GetAvgPercentOfSales("CurrentLiabilities", balProjections, incs),
thisInc.Revenue * GetAvgPercentOfSales("Ppe", balProjections, incs),
thisInc.Revenue * GetAvgPercentOfSales("Cash", balProjections, incs),
thisInc.Revenue * GetAvgPercentOfSales("Debt", balProjections, incs),
thisInc.Revenue * GetAvgPercentOfSales("ShareholdersEquity", balProjections, incs),
thisInc.Revenue * GetAvgPercentOfSales("TotalAssets", balProjections, incs)
));
}
}
return(balProjections);
}
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 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);
}
internal decimal GetAvgPercentOfSales(string itemName, BalanceSheetCollection balProjections, IncomeStatementCollection incs)
{
decimal[] salesMargin = new decimal[balProjections.Count];
for (int i = 0; i < salesMargin.Length; i++)
{
PropertyInfo propInfo = typeof(BalanceSheet).GetProperty(itemName);
decimal value = (decimal)propInfo.GetValue(balProjections[i], null);
salesMargin[i] = value / incs[i].Revenue;
}
decimal sum = 0;
foreach (decimal perc in salesMargin)
{
sum += perc;
}
return sum / salesMargin.Length;
}
internal decimal GetAvgPercentOfSales(string itemName, BalanceSheetCollection balProjections, IncomeStatementCollection incs)
{
decimal[] salesMargin = new decimal[balProjections.Count];
for (int i = 0; i < salesMargin.Length; i++)
{
PropertyInfo propInfo = typeof(BalanceSheet).GetProperty(itemName);
decimal value = (decimal)propInfo.GetValue(balProjections[i], null);
salesMargin[i] = value / incs[i].Revenue;
}
decimal sum = 0;
foreach (decimal perc in salesMargin)
{
sum += perc;
}
return(sum / salesMargin.Length);
}
public FreeCashFlowCollection CalculateFreeCashFlow(string symbol, int year, int yrsToProject, IncomeStatementCollection incomeStatementProjection)
{
FreeCashFlowCollection fcfs = null;
MarketParameters mktParms = MarketParametersBL.Instance.GetMarketParameters();
BalanceSheetProjectionBL bsProjectionBL = new BalanceSheetProjectionBL();
BalanceSheetCollection bsProjections = bsProjectionBL.ProjectBalanceSheets(symbol, year, yrsToProject, incomeStatementProjection);
if (incomeStatementProjection != null && bsProjections != null)
{
fcfs = new FreeCashFlowCollection();
for (int i = year + 1; i < year + yrsToProject + 1; i++)
{
IncomeStatement inc = incomeStatementProjection.Find(i);
BalanceSheet bs = bsProjections.Find(i);
BalanceSheet lastYrBs = bsProjections.Find(i - 1);
if (inc != null && bs != null)
{
fcfs.Add(new FreeCashFlow(
symbol,
i,
inc.NOP(mktParms.TaxRate),
inc.NetIncome,
(bs.Ppe - lastYrBs.Ppe + inc.Depreciation),
bs.WorkingCapital,
bs.WorkingCapital - lastYrBs.WorkingCapital,
inc.Depreciation,
bs.InvestedCapital
));
}
}
}
return(fcfs);
}
public BalanceSheetCollection GetBalanceSheets(string tickerSymbol, int fromYear, int toYear)
{
BalanceSheetCollection bs = null;
BalanceSheet bal = null;
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@SYMBOL", MySqlDbType.VarChar),
new MySqlParameter("@FROMYEAR", MySqlDbType.Int16),
new MySqlParameter("@TOYEAR", MySqlDbType.Int16)
};
parms[0].Value = tickerSymbol;
parms[1].Value = fromYear;
parms[2].Value = toYear;
//Execute Query
using (MySqlDataReader rdr = MySqlHelper.ExecuteReader(MySqlHelper.SV_CONN_STRING, SQL_SELECT_BALANCESHEETS, parms))
{
if (rdr.Read())
{
//If there is one result
bs = new BalanceSheetCollection();
//Scroll through the results
do
{
bal = ConvertReaderToBalanceSheetObject(rdr);
bs.Add(bal);
}
while (rdr.Read());
}
}
return bs;
}
public BalanceSheetCollection GetBalanceSheetData(string symbol, int year, int yearsToLoad)
{
Dictionary<int, Dictionary<string, decimal>> values = new Dictionary<int, Dictionary<string, decimal>>();
Dictionary<string, decimal> bs2012 = new Dictionary<string, decimal>();
Dictionary<string, decimal> bs2011 = new Dictionary<string, decimal>();
Dictionary<string, decimal> bs2010 = new Dictionary<string, decimal>();
string url = "http://finance.yahoo.com/q/bs?s=" + symbol + "+Balance+Sheet&annual";
HtmlDocument doc = new HtmlWeb().Load(url);
foreach (HtmlNode div in doc.DocumentNode.SelectNodes("//td[@width ='30' and @class='yfnc_tabledata1']"))
{
if (div.NextSibling.NextSibling.InnerHtml.Contains("Cash And Cash Equivalents"))
{
bs2012.Add("Cash And Cash Equivalents", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Cash And Cash Equivalents", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Cash And Cash Equivalents", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Short Term Investments"))
{
bs2012.Add("Short Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Short Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Short Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Net Receivables"))
{
bs2012.Add("Net Receivables", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Net Receivables", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Net Receivables", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Inventory"))
{
bs2012.Add("Inventory", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Inventory", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Inventory", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Other Current Assets"))
{
bs2012.Add("Other Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Other Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Other Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Accounts Payable"))
{
bs2012.Add("Accounts Payable", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Accounts Payable", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Accounts Payable", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Short/Current Long Term Debt"))
{
bs2012.Add("Short/Current Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Short/Current Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Short/Current Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Other Current Liabilities"))
{
bs2012.Add("Other Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Other Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Other Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
}
foreach (HtmlNode div in doc.DocumentNode.SelectNodes("//td[@colspan='2']"))
{
//if (div.InnerHtml.Contains("Total Current Assets"))
//{
// bs2012.Add("Total Current Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2011.Add("Total Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2010.Add("Total Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Equals("Long Term Investments"))
//{
// bs2012.Add("Long Term Investments", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Long Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Long Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
if (div.InnerHtml.Contains("Property Plant and Equipment"))
{
bs2012.Add("Property Plant and Equipment", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Property Plant and Equipment", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Property Plant and Equipment", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
//else if (div.InnerHtml.Contains("Goodwill"))
//{
// bs2012.Add("Goodwill", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Intangible Assets"))
//{
// bs2012.Add("Intangible Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Intangible Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Intangible Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Accumulated Amortization"))
//{
// bs2012.Add("Accumulated Amortization", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Accumulated Amortization", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Accumulated Amortization", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Other Assets"))
//{
// bs2012.Add("Other Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Other Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Other Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Deferred Long Term Asset Charges"))
//{
// bs2012.Add("Deferred Long Term Asset Charges", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Deferred Long Term Asset Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Deferred Long Term Asset Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
else if (div.InnerHtml.Contains("Total Assets"))
{
bs2012.Add("Total Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2011.Add("Total Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2010.Add("Total Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
}
//else if (div.InnerHtml.Contains("Total Current Liabilities"))
//{
// bs2012.Add("Total Current Liabilities", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2011.Add("Total Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2010.Add("Total Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
//}
else if (div.InnerHtml.Contains("Long Term Debt"))
{
bs2012.Add("Long Term Debt", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
//else if (div.InnerHtml.Contains("Other Liabilities"))
//{
// bs2012.Add("Other Liabilities", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Other Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Other Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Deferred Long Term Liability Charges"))
//{
// bs2012.Add("Deferred Long Term Liability Charges", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Deferred Long Term Liability Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Deferred Long Term Liability Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Minority Interest"))
//{
// bs2012.Add("Minority Interest", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Minority Interest", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Minority Interest", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Negative Goodwill"))
//{
// bs2012.Add("Negative Goodwill", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Negative Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Negative Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Total Liabilities"))
//{
// bs2012.Add("Total Liabilities", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2011.Add("Total Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2010.Add("Total Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Misc Stocks Options Warrants"))
//{
// bs2012.Add("Misc Stocks Options Warrants", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Misc Stocks Options Warrants", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Misc Stocks Options Warrants", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Redeemable Preferred Stock"))
//{
// bs2012.Add("Redeemable Preferred Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Redeemable Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Redeemable Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Preferred Stock"))
//{
// bs2012.Add("Preferred Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Common Stock"))
//{
// bs2012.Add("Common Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Common Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Common Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Retained Earnings"))
//{
// bs2012.Add("Retained Earnings", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Retained Earnings", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Retained Earnings", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Treasury Stock"))
//{
// bs2012.Add("Treasury Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Treasury Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Treasury Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Capital Surplus"))
//{
// bs2012.Add("Capital Surplus", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Capital Surplus", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Capital Surplus", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Other Stockholder Equity"))
//{
// bs2012.Add("Other Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Other Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Other Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
else if (div.InnerHtml.Contains("Total Stockholder Equity"))
{
bs2012.Add("Total Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2011.Add("Total Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2010.Add("Total Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
}
}
values.Add(2010, bs2010);
values.Add(2011, bs2011);
values.Add(2012, bs2012);
BalanceSheetCollection bss = new BalanceSheetCollection();
for (int y = year; y > year - yearsToLoad; y--)
{
if (values.ContainsKey(y))
{
bss.Add(new BalanceSheet(symbol, y,
values[y]["Cash And Cash Equivalents"] + values[y]["Short Term Investments"] + values[y]["Net Receivables"] + values[y]["Inventory"] + values[y]["Other Current Assets"],
values[y]["Accounts Payable"] + values[y]["Short/Current Long Term Debt"] + values[y]["Other Current Liabilities"],
values[y]["Long Term Debt"],
values[y]["Property Plant and Equipment"],
values[y]["Cash And Cash Equivalents"],
values[y]["Total Stockholder Equity"],
values[y]["Total Assets"]
)
);
}
}
return bss;
}
public BalanceSheetCollection GetBalanceSheetData(string symbol, int year, int yearsToLoad)
{
Dictionary <int, Dictionary <string, decimal> > values = new Dictionary <int, Dictionary <string, decimal> >();
Dictionary <string, decimal> bs2012 = new Dictionary <string, decimal>();
Dictionary <string, decimal> bs2011 = new Dictionary <string, decimal>();
Dictionary <string, decimal> bs2010 = new Dictionary <string, decimal>();
string url = "http://finance.yahoo.com/q/bs?s=" + symbol + "+Balance+Sheet&annual";
HtmlDocument doc = new HtmlWeb().Load(url);
foreach (HtmlNode div in doc.DocumentNode.SelectNodes("//td[@width ='30' and @class='yfnc_tabledata1']"))
{
if (div.NextSibling.NextSibling.InnerHtml.Contains("Cash And Cash Equivalents"))
{
bs2012.Add("Cash And Cash Equivalents", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Cash And Cash Equivalents", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Cash And Cash Equivalents", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Short Term Investments"))
{
bs2012.Add("Short Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Short Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Short Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Net Receivables"))
{
bs2012.Add("Net Receivables", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Net Receivables", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Net Receivables", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Inventory"))
{
bs2012.Add("Inventory", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Inventory", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Inventory", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Other Current Assets"))
{
bs2012.Add("Other Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Other Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Other Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Accounts Payable"))
{
bs2012.Add("Accounts Payable", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Accounts Payable", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Accounts Payable", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Short/Current Long Term Debt"))
{
bs2012.Add("Short/Current Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Short/Current Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Short/Current Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
else if (div.NextSibling.NextSibling.InnerHtml.Contains("Other Current Liabilities"))
{
bs2012.Add("Other Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Other Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Other Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
}
foreach (HtmlNode div in doc.DocumentNode.SelectNodes("//td[@colspan='2']"))
{
//if (div.InnerHtml.Contains("Total Current Assets"))
//{
// bs2012.Add("Total Current Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2011.Add("Total Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2010.Add("Total Current Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Equals("Long Term Investments"))
//{
// bs2012.Add("Long Term Investments", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Long Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Long Term Investments", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
if (div.InnerHtml.Contains("Property Plant and Equipment"))
{
bs2012.Add("Property Plant and Equipment", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Property Plant and Equipment", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Property Plant and Equipment", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
//else if (div.InnerHtml.Contains("Goodwill"))
//{
// bs2012.Add("Goodwill", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Intangible Assets"))
//{
// bs2012.Add("Intangible Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Intangible Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Intangible Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Accumulated Amortization"))
//{
// bs2012.Add("Accumulated Amortization", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Accumulated Amortization", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Accumulated Amortization", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Other Assets"))
//{
// bs2012.Add("Other Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Other Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Other Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Deferred Long Term Asset Charges"))
//{
// bs2012.Add("Deferred Long Term Asset Charges", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Deferred Long Term Asset Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Deferred Long Term Asset Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
else if (div.InnerHtml.Contains("Total Assets"))
{
bs2012.Add("Total Assets", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2011.Add("Total Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2010.Add("Total Assets", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
}
//else if (div.InnerHtml.Contains("Total Current Liabilities"))
//{
// bs2012.Add("Total Current Liabilities", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2011.Add("Total Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2010.Add("Total Current Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
//}
else if (div.InnerHtml.Contains("Long Term Debt"))
{
bs2012.Add("Long Term Debt", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
bs2011.Add("Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
bs2010.Add("Long Term Debt", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
}
//else if (div.InnerHtml.Contains("Other Liabilities"))
//{
// bs2012.Add("Other Liabilities", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Other Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Other Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Deferred Long Term Liability Charges"))
//{
// bs2012.Add("Deferred Long Term Liability Charges", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Deferred Long Term Liability Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Deferred Long Term Liability Charges", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Minority Interest"))
//{
// bs2012.Add("Minority Interest", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Minority Interest", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Minority Interest", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Negative Goodwill"))
//{
// bs2012.Add("Negative Goodwill", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Negative Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Negative Goodwill", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Total Liabilities"))
//{
// bs2012.Add("Total Liabilities", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2011.Add("Total Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
// bs2010.Add("Total Liabilities", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Misc Stocks Options Warrants"))
//{
// bs2012.Add("Misc Stocks Options Warrants", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Misc Stocks Options Warrants", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Misc Stocks Options Warrants", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Redeemable Preferred Stock"))
//{
// bs2012.Add("Redeemable Preferred Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Redeemable Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Redeemable Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Preferred Stock"))
//{
// bs2012.Add("Preferred Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Preferred Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Common Stock"))
//{
// bs2012.Add("Common Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Common Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Common Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Retained Earnings"))
//{
// bs2012.Add("Retained Earnings", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Retained Earnings", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Retained Earnings", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Treasury Stock"))
//{
// bs2012.Add("Treasury Stock", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Treasury Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Treasury Stock", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Capital Surplus"))
//{
// bs2012.Add("Capital Surplus", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Capital Surplus", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Capital Surplus", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
//else if (div.InnerHtml.Contains("Other Stockholder Equity"))
//{
// bs2012.Add("Other Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.ChildNodes[0].InnerHtml));
// bs2011.Add("Other Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
// bs2010.Add("Other Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes[0].InnerHtml));
//}
else if (div.InnerHtml.Contains("Total Stockholder Equity"))
{
bs2012.Add("Total Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2011.Add("Total Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
bs2010.Add("Total Stockholder Equity", GetValueFromInnerHtml(div.NextSibling.NextSibling.NextSibling.ChildNodes["strong"].ChildNodes[0].InnerHtml));
}
}
values.Add(2010, bs2010);
values.Add(2011, bs2011);
values.Add(2012, bs2012);
BalanceSheetCollection bss = new BalanceSheetCollection();
for (int y = year; y > year - yearsToLoad; y--)
{
if (values.ContainsKey(y))
{
bss.Add(new BalanceSheet(symbol, y,
values[y]["Cash And Cash Equivalents"] + values[y]["Short Term Investments"] + values[y]["Net Receivables"] + values[y]["Inventory"] + values[y]["Other Current Assets"],
values[y]["Accounts Payable"] + values[y]["Short/Current Long Term Debt"] + values[y]["Other Current Liabilities"],
values[y]["Long Term Debt"],
values[y]["Property Plant and Equipment"],
values[y]["Cash And Cash Equivalents"],
values[y]["Total Stockholder Equity"],
values[y]["Total Assets"]
)
);
}
}
return(bss);
}
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));
}
}
