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