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