public UsStockCollection ReadQuandlFromStockCsv(string symbol)
{
string url = QUANDL_COMPANY_URL_PREFIX + symbol + QUANDL_COMPANY_URL_SUFFIX_WITH_KEY;
// string url = QUANDL_COMPANY_URL_PREFIX + symbol + QUANDL_COMPANY_URL_SUFFIX_WITHOUT_KEY;
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
var reader = new StreamReader(resp.GetResponseStream());
Dictionary<string, string> columnMappings = ColumnMappingsDA.Instance.GetColumnMappings();
UsStockCollection stocks = new UsStockCollection();
List<string> columnNames = new List<string>();
List<List<string>> fieldValues = new List<List<string>>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
List<string> yearValues = new List<string>();
for (int i = 0; i < values.Length; i++)
{
yearValues.Add(values[i]);
}
fieldValues.Add(yearValues);
}
for (int i = 1; i < fieldValues.Count; i++)
{
UsStock stock = new UsStock();
stock.Symbol = symbol;
for (int j = 0; j < fieldValues[i].Count; j++)
{
if (fieldValues[0][j] == "Date")
{
PropertyInfo prop = stock.GetType().GetProperty(columnMappings[fieldValues[0][j]], BindingFlags.Public | BindingFlags.Instance);
prop.SetValue(stock, Convert.ToInt16(fieldValues[i][j].Split('-')[0]));
}
else
{
PropertyInfo prop = stock.GetType().GetProperty(columnMappings[fieldValues[0][j]], BindingFlags.Public | BindingFlags.Instance);
prop.SetValue(stock, FormatDecimal(fieldValues[i][j]));
}
}
stocks.Add(stock);
}
return stocks;
}
public void UpdateUsStock(UsStock stock)
{
if (UsStockExists(stock.Symbol, stock.Year))
{
//_dao.UpdateUsStock(stock);
}
else
{
_dao.InsertUsStock(stock);
}
}
private UsStock ConvertReaderToUsStockObject(MySqlDataReader rdr)
{
UsStock stock = new UsStock();
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;
stock.Symbol = MySqlHelper.ConvertReaderToString(rdr, sqlParams[i++]);
stock.Year = MySqlHelper.ConvertReaderToInt(rdr, sqlParams[i++]);
stock.SharesOutstanding = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.Beta3Yr = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.StdDev3YrStockPrice = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.BookDebttoCapitalRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EquityBookValue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.AssetBookValue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.CapEx = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.Cash = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.CashPercentageOfFirmValue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.CashPercentageOfRevenue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.CashPercentageOfAssets = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.ChangeNonCashWorkingCapital = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.MarketCorrelation = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.CurrentPeRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.Depreciation = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.DividendYield = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.Dividends = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.Ebit = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EbitPreviousPeriod = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.Ebitda = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EffectiveTaxRate = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EffectiveTaxRateOnIncome = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EnterpriseValue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EvToInvestedCapitalRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EvToTrailingSalesRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EvToEbitRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EvToEbitdaRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EvToSalesRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EpsExpectedGrowth = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.RevenueExpectedGrowth = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.FreeCashFlow = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.FirmValue = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.FixedToTotalAssetRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.ForwardEps = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.ForwardPeRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EpsGrowth = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.RevenueGrowthPrevYear = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.HiLoRisk = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.InsiderHoldings = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.InstitutionalHoldings = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.IntangibleAssetsToTotalAssetsRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.InvestedCapital = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.MarketCap = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.DebtToEquityRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.DebtToCapitalRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.NetIncome = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.NetMargin = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.NonCashWorkingCapital = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.NonCashWorkingCapitalToRevenueRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.PayoutRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.PriceToBookRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.PeToGrowthRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.OperatingMargin = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.PriceToSalesRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.ReinvestmentAmount = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.ReinvestmentRate = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.Revenues = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.ReturnOnCapital = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.ReturnOnEquity = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.SgaExpense = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.StockPrice = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.TotalDebt = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.TradingVolume = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.TtmRevenues = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.TrailingNetIncome = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.TrailingPeRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.TrailingRevenues = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.BetaValueLine = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
stock.EvToBookRatio = MySqlHelper.ConvertReaderToDecimal(rdr, sqlParams[i++]);
return stock;
}
private static void SetUsStockParameters(UsStock comp, MySqlParameter[] parms)
{
int i = 0;
parms[i++].Value = comp.Symbol;
parms[i++].Value = comp.Year;
parms[i++].Value = comp.SharesOutstanding;
parms[i++].Value = comp.Beta3Yr;
parms[i++].Value = comp.StdDev3YrStockPrice;
parms[i++].Value = comp.BookDebttoCapitalRatio;
parms[i++].Value = comp.EquityBookValue;
parms[i++].Value = comp.AssetBookValue;
parms[i++].Value = comp.CapEx;
parms[i++].Value = comp.Cash;
parms[i++].Value = comp.CashPercentageOfFirmValue;
parms[i++].Value = comp.CashPercentageOfRevenue;
parms[i++].Value = comp.CashPercentageOfAssets;
parms[i++].Value = comp.ChangeNonCashWorkingCapital;
parms[i++].Value = comp.MarketCorrelation;
parms[i++].Value = comp.CurrentPeRatio;
parms[i++].Value = comp.Depreciation;
parms[i++].Value = comp.DividendYield;
parms[i++].Value = comp.Dividends;
parms[i++].Value = comp.Ebit;
parms[i++].Value = comp.EbitPreviousPeriod;
parms[i++].Value = comp.Ebitda;
parms[i++].Value = comp.EffectiveTaxRate;
parms[i++].Value = comp.EffectiveTaxRateOnIncome;
parms[i++].Value = comp.EnterpriseValue;
parms[i++].Value = comp.EvToInvestedCapitalRatio;
parms[i++].Value = comp.EvToTrailingSalesRatio;
parms[i++].Value = comp.EvToEbitRatio;
parms[i++].Value = comp.EvToEbitdaRatio;
parms[i++].Value = comp.EvToSalesRatio;
parms[i++].Value = comp.EpsExpectedGrowth;
parms[i++].Value = comp.RevenueExpectedGrowth;
parms[i++].Value = comp.FreeCashFlow;
parms[i++].Value = comp.FirmValue;
parms[i++].Value = comp.FixedToTotalAssetRatio;
parms[i++].Value = comp.ForwardEps;
parms[i++].Value = comp.ForwardPeRatio;
parms[i++].Value = comp.EpsGrowth;
parms[i++].Value = comp.RevenueGrowthPrevYear;
parms[i++].Value = comp.HiLoRisk;
parms[i++].Value = comp.InsiderHoldings;
parms[i++].Value = comp.InstitutionalHoldings;
parms[i++].Value = comp.IntangibleAssetsToTotalAssetsRatio;
parms[i++].Value = comp.InvestedCapital;
parms[i++].Value = comp.MarketCap;
parms[i++].Value = comp.DebtToEquityRatio;
parms[i++].Value = comp.DebtToCapitalRatio;
parms[i++].Value = comp.NetIncome;
parms[i++].Value = comp.NetMargin;
parms[i++].Value = comp.NonCashWorkingCapital;
parms[i++].Value = comp.NonCashWorkingCapitalToRevenueRatio;
parms[i++].Value = comp.PayoutRatio;
parms[i++].Value = comp.PriceToBookRatio;
parms[i++].Value = comp.PeToGrowthRatio;
parms[i++].Value = comp.OperatingMargin;
parms[i++].Value = comp.PriceToSalesRatio;
parms[i++].Value = comp.ReinvestmentAmount;
parms[i++].Value = comp.ReinvestmentRate;
parms[i++].Value = comp.Revenues;
parms[i++].Value = comp.ReturnOnCapital;
parms[i++].Value = comp.ReturnOnEquity;
parms[i++].Value = comp.SgaExpense;
parms[i++].Value = comp.StockPrice;
parms[i++].Value = comp.TotalDebt;
parms[i++].Value = comp.TradingVolume;
parms[i++].Value = comp.TtmRevenues;
parms[i++].Value = comp.TrailingNetIncome;
parms[i++].Value = comp.TrailingPeRatio;
parms[i++].Value = comp.TrailingRevenues;
parms[i++].Value = comp.BetaValueLine;
parms[i++].Value = comp.EvToBookRatio;
}
public void UpdateUsStock(UsStock stock)
{
MySqlParameter[] parms = GetUsStockParameters();
SetUsStockParameters(stock, parms);
using (MySqlConnection conn = new MySqlConnection(MySqlHelper.SV_CONN_STRING))
{
conn.Open();
MySqlHelper.ExecuteNonQuery(conn, UpdateSQLStatement(), parms);
}
}
