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