public static SymbolIntradiaryInfo GetSymbolIntradiaryInfo(int stock_id) { SymbolIntradiaryInfo result = new SymbolIntradiaryInfo(); result.SymbolId = stock_id; using (ctaDBEntities entities = new ctaDBEntities()) { entities.Database.Connection.Open(); Stock_Quote_Intradiary info = entities.Stock_Quote_Intradiary.Where(sqi => sqi.stock_id == stock_id).OrderByDescending(sqi => sqi.datetime).FirstOrDefault(); if (info != null) { result.Ask = info.ask; result.AskSize = info.ask_size; result.Bid = info.bid; result.BidSize = info.bid_size; result.LastTradeDate = info.last_trade_date; result.LastTradePrice = info.last_trade_price; result.LastTradeTime = info.last_trade_time; result.LastTradeSize = (double)info.last_trade_size; } Stock_Quote historic = entities.Stock_Quote.Where(sq => sq.stock_id == stock_id).OrderByDescending(h => h.date_round).FirstOrDefault(); if (historic != null) { double previousClosing = entities.Stock_Quote.Where(sq => sq.stock_id == stock_id && sq.date_round < historic.date_round).OrderByDescending(h => h.date_round).Select(sq => sq.closing).FirstOrDefault(); if (previousClosing > 0) { result.Change = historic.closing - previousClosing; result.ChangePercent = ((historic.closing / previousClosing) - 1) * 100; result.PreviousClosing = previousClosing; } result.Date = historic.date_round; result.Opening = historic.opening; result.Minimun = historic.minimun; result.Maximun = historic.maximun; result.LastTradePrice = (result.LastTradePrice > 0) ? result.LastTradePrice : ((historic.closing > 0) ? historic.closing : previousClosing); result.Volume = (double)historic.volume; } if (!(entities.Database.Connection.State == ConnectionState.Closed)) { entities.Database.Connection.Close(); } } return(result ?? new SymbolIntradiaryInfo()); }
/// <summary> /// /// </summary> /// <param name="market_name">BCAB;NYSE;NASDAQ</param> /// <param name="market_id">[BCAB,1];[NYSE,2];[NASDAQ,3]</param> /// <param name="utc_time_offset">[BCBA,-3];[NYSE,];[NASDAQ,]</param> /// <param name="market_yahoo_api_id">[BCBA,".BA"];[NYSE,""];[NASDAQ,""]</param> public void ReadMarketIntradiaryData(string market_name, int market_id, DateTime date, string market_yahoo_api_id) { string csvData = ""; List <IntradiaryData> prices = null; using (ctaDBEntities entities = new ctaDBEntities()) { entities.Database.Connection.Open(); Dictionary <string, int> stocks_id = new Dictionary <string, int>(); string symbols = string.Empty; try { foreach (Stock smb in entities.Stocks.Where(s => s.market_id == market_id && s.active.HasValue && s.active.Value)) { symbols += ((symbols == string.Empty) ? "" : "+") + smb.symbol.Trim() + ((smb.Id == 671) ? "" : market_yahoo_api_id); stocks_id[smb.symbol.Trim() + ((smb.Id == 671) ? "" : market_yahoo_api_id)] = smb.Id; } } catch (Exception ex) { EmailSender.SendErrorGettingSymbols("Message: " + ex.Message + Environment.NewLine + "Source: " + ex.Source + Environment.NewLine + "StackTrace: " + ex.StackTrace + Environment.NewLine + ((ex.InnerException != null) ? "Inner Message: " + ex.InnerException.Message + Environment.NewLine + "Inner Source: " + ex.InnerException.Source + Environment.NewLine + "Inner StackTrace: " + ex.InnerException.StackTrace : ""), market_name, date); } using (WebClient web = new WebClient()) { try { csvData = web.DownloadString("http://finance.yahoo.com/d/quotes.csv?s=" + symbols + "&f=sopaa5bb6c1p2d1t1l1k3vm"); prices = this.Parse(csvData); foreach (IntradiaryData intradiary in prices) { try { Stock_Quote_Intradiary sqi = new Stock_Quote_Intradiary(); sqi.ask = intradiary.Ask; sqi.ask_size = intradiary.AskSize; sqi.bid = intradiary.Bid; sqi.bid_size = intradiary.BidSize; sqi.change = intradiary.Change; sqi.change_percent = intradiary.ChangePercent; sqi.last_trade_size = intradiary.LastTradeSize; sqi.last_trade_date = (intradiary.LastTradeDate) == DateTime.MinValue ? date.Date : intradiary.LastTradeDate; sqi.last_trade_price = intradiary.LastTradePrice; sqi.last_trade_time = intradiary.LastTradeTime; sqi.opening = intradiary.Open; sqi.prev_closing = intradiary.PreviousClose; sqi.stock_id = stocks_id[intradiary.Symbol.Trim()]; sqi.datetime = date; entities.Stock_Quote_Intradiary.Add(sqi); } catch (Exception ex) { EmailSender.SendErrorGettingSymbolsStocks("StockName: " + intradiary.Symbol + " Message: " + ex.Message + Environment.NewLine + "Source: " + ex.Source + Environment.NewLine + "StackTrace: " + ex.StackTrace + Environment.NewLine + ((ex.InnerException != null) ? "Inner Message: " + ex.InnerException.Message + Environment.NewLine + "Inner Source: " + ex.InnerException.Source + Environment.NewLine + "Inner StackTrace: " + ex.InnerException.StackTrace : ""), market_name, date); } } } catch (Exception ex) { EmailSender.SendErrorGettingSymbolsStocks("Message: " + ex.Message + Environment.NewLine + "Source: " + ex.Source + Environment.NewLine + "StackTrace: " + ex.StackTrace + Environment.NewLine + ((ex.InnerException != null) ? "Inner Message: " + ex.InnerException.Message + Environment.NewLine + "Inner Source: " + ex.InnerException.Source + Environment.NewLine + "Inner StackTrace: " + ex.InnerException.StackTrace : ""), market_name, date); } } try { entities.SaveChanges(); //EmailSender.SendIntradiarySaved(market_name, date); } catch (Exception ex) { EmailSender.SendErrorSavingChangesInDataBase("Message: " + ex.Message + Environment.NewLine + "Source: " + ex.Source + Environment.NewLine + "StackTrace: " + ex.StackTrace + Environment.NewLine + ((ex.InnerException != null) ? "Inner Message: " + ex.InnerException.Message + Environment.NewLine + "Inner Source: " + ex.InnerException.Source + Environment.NewLine + "Inner StackTrace: " + ex.InnerException.StackTrace : ""), market_name, date); } try { //Update Historical Data foreach (IntradiaryData intradiary in prices) { try { int currentStockId = stocks_id[intradiary.Symbol.Trim()]; Stock_Quote quote = entities.Stock_Quote.Where(sq => sq.stock_id == currentStockId && sq.date_round.Year == date.Year && sq.date_round.Month == date.Month && sq.date_round.Day == date.Day).FirstOrDefault(); if (quote != null) { if (intradiary.LastTradePrice > 0) { quote.minimun = Math.Min(intradiary.LastTradePrice, quote.minimun); quote.maximun = Math.Max(intradiary.LastTradePrice, quote.maximun); quote.closing = intradiary.LastTradePrice; } if (intradiary.Volume > 0) { quote.volume = intradiary.Volume; } } else { if (intradiary.LastTradePrice > 0 && intradiary.Open > 0) { quote = new Stock_Quote(); quote.date_round = date; quote.opening = intradiary.Open; quote.minimun = Math.Min(intradiary.LastTradePrice, intradiary.Open); quote.maximun = Math.Max(intradiary.LastTradePrice, intradiary.Open); quote.closing = intradiary.Open; quote.stock_id = stocks_id[intradiary.Symbol.Trim()]; quote.volume = intradiary.Volume; entities.Stock_Quote.Add(quote); } } } catch (Exception ex) { EmailSender.SendErrorUpdatingHistorical("StockName: " + intradiary.Symbol + " Message: " + ex.Message + Environment.NewLine + "Source: " + ex.Source + Environment.NewLine + "StackTrace: " + ex.StackTrace + Environment.NewLine + ((ex.InnerException != null) ? "Inner Message: " + ex.InnerException.Message + Environment.NewLine + "Inner Source: " + ex.InnerException.Source + Environment.NewLine + "Inner StackTrace: " + ex.InnerException.StackTrace : ""), market_name, date); } } entities.SaveChanges(); //EmailSender.SendHistoricalUpdated(market_name, date); } catch (Exception ex) { EmailSender.SendErrorUpdatingHistorical("Message: " + ex.Message + Environment.NewLine + "Source: " + ex.Source + Environment.NewLine + "StackTrace: " + ex.StackTrace + Environment.NewLine + ((ex.InnerException != null) ? "Inner Message: " + ex.InnerException.Message + Environment.NewLine + "Inner Source: " + ex.InnerException.Source + Environment.NewLine + "Inner StackTrace: " + ex.InnerException.StackTrace : ""), market_name, date); } if (entities.Database.Connection.State != System.Data.ConnectionState.Closed) { entities.Database.Connection.Close(); } } }