public void UpdateMarketHistorical(DateTime sd, DateTime ed, int MarketId) { using (MarketsDBEntities mde = new MarketsDBEntities()) { YahooFinance yf = new YahooFinance(); Market m = mde.Markets.Where(x => x.MarketId == MarketId).FirstOrDefault(); List<DailyCandle> dcs = yf.GetHistoricalData(sd, ed, m); foreach (DailyCandle td in dcs) { //see if this day is in the db for this issue int x = (from oa in mde.DailyCandles where oa.MarketId == MarketId && oa.Date == td.Date select oa).Count<DailyCandle>(); if (x == 0) { mde.DailyCandles.Add(td); } //if yes, no nothing for now (consider overwriting at a certain point?) //if no, insert this row into the table } mde.SaveChanges(); } }
public void CatchUpDailyCandles(int MarketId) { using (MarketsDBEntities mdb = new MarketsDBEntities()) { //load the current daily candles var CompiledCandles = mdb.Candles.Where(x => x.MarketId == MarketId && x.PeriodId == 5); //load from the data var RawData = mdb.DailyCandles.Where(x => x.MarketId == MarketId); //compare lengths, if same then return Console.WriteLine(CompiledCandles.Count()); Console.WriteLine(RawData.Count()); if (CompiledCandles.Count() < RawData.Count()) { //if not same, need to add some candles HashSet<DateTime> processedDates = new HashSet<DateTime>(CompiledCandles.Select(s => s.StartDate)); var results = RawData.Where(m => !processedDates.Contains(m.Date)); foreach (var dc in results) { Candle c = new Candle(); c.Close = dc.Close; c.EndDate = dc.Date; c.High = dc.High; c.Low = dc.Low; c.MarketId = dc.MarketId; c.Open = dc.Open; c.PeriodId = 5; c.StartDate = dc.Date; c.Volume = dc.Volume; using (MarketsDBEntities mdbSaver = new MarketsDBEntities()) { mdbSaver.Candles.Add(c); mdbSaver.SaveChanges(); } } } } }
public void FillUnfilledGaps() { using (MarketsDBEntities mde = new MarketsDBEntities()) { foreach (var gap in mde.Gaps.Where(x => x.FillDate == null)) { var candles = mde.DailyCandles.Where(x => x.MarketId == gap.MarketId && x.Date > gap.OpenDate); foreach (var candle in candles) { if (gap.Direction == "Up") { if (candle.Low <= gap.GapBottom) { Console.WriteLine("Gap fill on " + candle.Date.ToShortDateString()); gap.FillDate = candle.Date; break; } } else { if (candle.High >= gap.GapTop) { Console.WriteLine("Gap fill on " + candle.Date.ToShortDateString()); gap.FillDate = candle.Date; break; } } } } mde.SaveChanges(); } }
public void GetCurrentPrices() { using (MarketsDBEntities mde = new MarketsDBEntities()) { foreach (var market in mde.Markets) { using (MarketsDBEntities mdeSaver = new MarketsDBEntities()) { var lastCandle = mdeSaver.DailyCandles.Where(x => x.MarketId == market.MarketId).OrderByDescending(x => x.Date).FirstOrDefault(); market.CurrentPrice = lastCandle.Close; Console.WriteLine("Updated price for " + market.Symbol); } } mde.SaveChanges(); } }
public void GenerateMonthlyCandles(int marketId) { using (MarketsDBEntities mde = new MarketsDBEntities()) { var candles = mde.DailyCandles.Where(x => x.MarketId == marketId) .OrderBy(x => x.Date) .GroupBy(x => x.Date.Month.ToString() + "-" + x.Date.Year.ToString()) .Select(x => new CandleListItem { MarketId = marketId, StartDate = x.Min(y => y.Date), High = x.Max(y => y.High), Low = x.Min(y => y.High), Open = 0, Close = 0, Volume = x.Sum(y => y.Volume), }); foreach (CandleListItem candle in candles) { //get open and close var dailyCandles = mde.DailyCandles.Where(x => x.MarketId == marketId && x.Date.Month == candle.StartDate.Month && x.Date.Year == candle.StartDate.Year).OrderBy(x => x.Date); candle.Open = dailyCandles.First().Open; candle.Close = dailyCandles.OrderByDescending(x => x.Date).First().Close; Candle c = new Candle(); c.Close = candle.Close; c.EndDate = dailyCandles.OrderByDescending(x => x.Date).First().Date; c.High = candle.High; c.Low = candle.Low; c.MarketId = marketId; c.Open = candle.Open; c.PeriodId = 2; c.StartDate = candle.StartDate; c.Volume = candle.Volume; if (mde.Candles.Where(x=>x.MarketId == marketId && x.StartDate == c.StartDate && x.PeriodId == c.PeriodId).Count() == 0) { using (MarketsDBEntities mdeSaver = new MarketsDBEntities()) { mdeSaver.Candles.Add(c); mdeSaver.SaveChanges(); } } Console.WriteLine(candle.StartDate.ToShortDateString() + " H:" + candle.High); } } }
public void GapStats() { using (MarketsDBEntities mde = new MarketsDBEntities()) { foreach (var m in mde.Markets) { int marketId = m.MarketId; //get all unfilled gaps var market = mde.Markets.Find(marketId); var gaps = market.Gaps.Where(x => x.MarketId == marketId && x.FillDate == null); //ordered by date, which direction is the last unfilled gap? var lastGap = gaps.OrderByDescending(x => x.OpenDate).FirstOrDefault(); if (lastGap.OpenDate > DateTime.Now.AddYears(-100)) { market.GapsCurrentDirection = lastGap.Direction; if (lastGap.Direction == "Up") market.GapsFirstPrice = lastGap.GapTop; else market.GapsFirstPrice = lastGap.GapBottom; } } mde.SaveChanges(); } }
public void GapFinder(int marketId) { using (MarketsDBEntities mde = new MarketsDBEntities()) { //find all candles that don't have a corresponding gap var candles = mde.DailyCandles.Where (x => x.MarketId == marketId && x.Gaps.Count == 0); foreach (var candle in candles.OrderBy(x=>x.Date)) { Console.WriteLine("Finding gap for " + candle.Date.ToShortDateString()); //1. Find the candle one candle back date-wise int trys = 0; DailyCandle lastCandle = new DailyCandle(); DateTime lastDate = candle.Date.AddDays(-1); while (trys < 6 && lastCandle.Date< DateTime.Now.AddYears(-100)) { //Console.WriteLine(lastDate.Date.ToShortDateString()); if (mde.DailyCandles.Where(x => x.MarketId == marketId && x.Date == lastDate).Count() > 0) { Console.WriteLine("Last candle found."); lastCandle = mde.DailyCandles.Where(x => x.MarketId == marketId && x.Date == lastDate).First(); } trys++; lastDate = lastDate.AddDays(-1); } if (lastCandle.Date< DateTime.Now.AddYears(-100)) Console.WriteLine("No last candle found!"); Console.WriteLine("Last candle: " + lastCandle.Date.ToShortDateString()); Gap g = new Gap(); g.MarketId = marketId; g.DailyCandleId = candle.DailyCandleId; g.OpenDate = candle.Date; g.GapSize = candle.Open - lastCandle.Close; if (candle.Open >= lastCandle.Close) { g.Direction = "Up"; g.GapTop = candle.Open; g.GapBottom = lastCandle.Close; if (candle.Low <= lastCandle.Close) { g.FillDate = candle.Date; } } else { g.Direction = "Down"; g.GapTop = lastCandle.Close; g.GapBottom = candle.Open; if (candle.High >= lastCandle.Close) { g.FillDate = candle.Date; } } g.GapExtension = candle.Open + g.GapSize; using (MarketsDBEntities mdeAdder = new MarketsDBEntities()) { mdeAdder.Gaps.Add(g); mdeAdder.SaveChanges(); } } } }
public void FirstAndLastDates() { using (MarketsDBEntities mde = new MarketsDBEntities()) { foreach (var m in mde.Markets) { m.DateFirstCandle = m.DailyCandles.OrderBy(x => x.Date).First().Date; m.DateLastCandle = m.DailyCandles.OrderByDescending(x => x.Date).First().Date; } mde.SaveChanges(); } }