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());
}
public void ReadData(Stream csvdata, string filename)
{
string market_name = Path.GetFileNameWithoutExtension(filename);
Dictionary <string, int> cache = new Dictionary <string, int>();
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
try
{
StreamReader reader = new StreamReader(csvdata);
reader.ReadLine();//avoid headers
string line = reader.ReadLine();
int stock_id = 0;
while (line != null)
{
try
{
string[] cols = line.Split(',');
if (!(String.IsNullOrEmpty(cols[4]) || String.IsNullOrEmpty(cols[6]) || String.IsNullOrEmpty(cols[7]) || String.IsNullOrEmpty(cols[8])))
{
string symbol = cols[0].Trim();
if (!cache.Keys.Contains(cols[0].Trim()))
{
var currentStock = entities.Stocks.Where(s => s.symbol == symbol && s.Market.name == market_name).FirstOrDefault();
if (currentStock != null)
{
cache[cols[0].Trim()] = currentStock.Id;
}
else
{
cache[cols[0].Trim()] = 0;
line = reader.ReadLine();
continue;
}
}
else if (cache[cols[0].Trim()] == 0)
{
line = reader.ReadLine();
continue;
}
stock_id = cache[cols[0].Trim()];
string[] split_date = cols[1].Split('/');
DateTime date = DateTime.Parse(split_date[1] + "/" + split_date[0] + "/" + split_date[2]);
double open = double.Parse(cols[6]);
double high = double.Parse(cols[7]);
double low = double.Parse(cols[8]);
double close = double.Parse(cols[4]);
decimal volu = (String.IsNullOrEmpty(cols[9])) ? 0 : decimal.Parse(cols[9]);
bool delete = false;
var sqs = entities.Stock_Quote.Where(s => s.stock_id == stock_id && s.date_round.Year == date.Year && s.date_round.Month == date.Month && s.date_round.Day == date.Day);
foreach (Stock_Quote sq1 in sqs)
{
entities.Stock_Quote.Remove(sq1);
delete = true;
}
if (delete)
{
entities.SaveChanges();
}
Stock_Quote sq = new Stock_Quote();
sq.stock_id = stock_id;
sq.date_round = date;
sq.opening = open;
sq.closing = close;
sq.maximun = high;
sq.minimun = low;
sq.volume = volu;
entities.Stock_Quote.Add(sq);
entities.SaveChanges();
}
line = reader.ReadLine();
}
catch (Exception ex) { }
}
}
catch (Exception ex) { }
if (entities.Database.Connection.State != System.Data.ConnectionState.Closed)
{
entities.Database.Connection.Close();
}
}
}
/// <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();
}
}
}
public static List <Candel> GetSymbolQuotes(int symbol_id, ChartRange chartRange, CandelRange candelRange, string user_type)
{
DateTime chartMinDate = DateTime.Now;
DateTime today = DateTime.Now.Date;
switch (chartRange)
{
case ChartRange.Month:
chartMinDate = chartMinDate.AddMonths(-1);
break;
case ChartRange.ThreeMonths:
chartMinDate = chartMinDate.AddMonths(-3);
break;
case ChartRange.SixMonths:
chartMinDate = chartMinDate.AddMonths(-6);
break;
case ChartRange.Year:
chartMinDate = chartMinDate.AddYears(-1);
break;
case ChartRange.ThreeYears:
chartMinDate = chartMinDate.AddYears(-3);
break;
default:
chartMinDate = DateTime.MinValue.AddDays(200);
break;
}
List <Candel> candels = new List <Candel>();
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
bool createCandel = false;
Candel currentCandel = null;
DateTime startDate = chartMinDate.AddDays(-200);
List <Stock_Quote> quotes = entities.Stock_Quote.Where(s => s.stock_id == symbol_id && (s.date_round > startDate && (user_type != "FREE" || s.date_round < today))).OrderBy(sq => sq.date_round).ToList();
Stock_Quote quote = null;
for (int i = 0; i < quotes.Count; i++)
{
quote = quotes[i];
currentCandel = (currentCandel != null) ? currentCandel : new Candel()
{
Date = quote.date_round, Open = quote.opening, Close = quote.closing, Minimun = quote.minimun, Maximun = quote.maximun, Volume = (double)quote.volume
};
if (candelRange == CandelRange.Daily)
{
createCandel = true;
}
else if (candelRange == CandelRange.Weekly)
{
currentCandel.Minimun = (currentCandel.Minimun > quote.minimun) ? quote.minimun : currentCandel.Minimun;
currentCandel.Maximun = (currentCandel.Maximun < quote.maximun) ? quote.maximun : currentCandel.Maximun;
if (quote.date_round.DayOfWeek == DayOfWeek.Friday)
{
currentCandel.Close = quote.closing;
currentCandel.Date = quote.date_round;
createCandel = true;
}
}
else
{
currentCandel.Minimun = (currentCandel.Minimun > quote.minimun) ? quote.minimun : currentCandel.Minimun;
currentCandel.Maximun = (currentCandel.Maximun < quote.maximun) ? quote.maximun : currentCandel.Maximun;
if (currentCandel.Date.Month != quote.date_round.Month)
{
createCandel = true;
i--;
}
else
{
currentCandel.Date = quote.date_round;
}
}
if (createCandel || i + 1 == quotes.Count)
{
currentCandel.Close = quote.closing;
currentCandel.Visible = (currentCandel.Date >= chartMinDate.Date);
candels.Add(currentCandel);
currentCandel = null;
createCandel = false;
}
}
entities.Database.Connection.Close();
}
return(candels);
}
/// <summary>
/// Update Historical Data
/// </summary>
/// <param name="startdate">Date with format DD/MM/YYYY</param>
/// <param name="enddate">Date with format DD/MM/YYYY</param>
public void ReadHistoricalData(DateTime startdate, DateTime enddate, string stock_symbol, int stock_id, string market_identifier_in_yahoo)
{
string csvHistoricalData;
if (stock_id == 671)
{
market_identifier_in_yahoo = "";
}
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
string start_month = (startdate.Month - 1) < 10 ? "0" + (startdate.Month - 1).ToString() : (startdate.Month - 1).ToString();
string start_day = startdate.Day < 10 ? "0" + startdate.Day.ToString() : startdate.Day.ToString();
string start_year = startdate.Year.ToString();
string end_month = (enddate.Month - 1) < 10 ? "0" + (enddate.Month - 1).ToString() : (enddate.Month - 1).ToString();
string end_day = enddate.Day < 10 ? "0" + enddate.Day.ToString() : enddate.Day.ToString();
string end_year = enddate.Year.ToString();
using (WebClient web = new WebClient())
{
try
{
csvHistoricalData = web.DownloadString("http://ichart.finance.yahoo.com/table.csv?s=" + stock_symbol.Trim() + market_identifier_in_yahoo + "&a=" + start_month + "&b=" + start_day + "&c=" + start_year + "&d=" + end_month + "&e=" + end_day + "&f=" + end_year);
string[] rows = csvHistoricalData.Replace("\r", "").Split('\n');
string row;
for (int i = 1; i < rows.Length; i++)
{
row = rows[i];
if (string.IsNullOrEmpty(row))
{
continue;
}
try
{
string[] cols = row.Split(',');
DateTime date = DateTime.Parse(cols[0]);
double open = double.Parse(cols[1]);
double high = double.Parse(cols[2]);
double low = double.Parse(cols[3]);
double close = double.Parse(cols[4]);
decimal volu = decimal.Parse(cols[5]);
double adjclo = double.Parse(cols[6]);
bool delete = false;
var sqs = entities.Stock_Quote.Where(s => s.stock_id == stock_id && s.date_round.Year == date.Year && s.date_round.Month == date.Month && s.date_round.Day == date.Day);
foreach (Stock_Quote sq1 in sqs)
{
entities.Stock_Quote.Remove(sq1);
delete = true;
}
if (delete)
{
entities.SaveChanges();
}
Stock_Quote sq = new Stock_Quote();
sq.stock_id = stock_id;
sq.date_round = date;
sq.opening = open;
sq.adj_close = adjclo;
sq.closing = close;
sq.maximun = high;
sq.minimun = low;
sq.volume = volu;
entities.Stock_Quote.Add(sq);
entities.SaveChanges();
}
catch (Exception ex) { }
}
}
catch (Exception ex) { }
}
if (entities.Database.Connection.State != System.Data.ConnectionState.Closed)
{
entities.Database.Connection.Close();
}
}
}
