public List <CircuitBreakerInfo> GetCircuitBreaker(int day = 0)
{
var lastTradedDate = GetLastTradeDate(day);
var Date30ago = GetLastTradeDate(day + 5);
using (var db = new StockDataContext())
{
var low_circuit = db.circuitBreaker.Where(x => (x.date.Date.CompareTo(lastTradedDate) >= 0) && (x.high_low == 'L')).ToList();
var result = db.circuitBreaker.Where(x => (x.date.Date.CompareTo(lastTradedDate) >= 0) &&
(x.high_low == 'H') &&
!low_circuit.Any(y => y.nseSymbol == x.nseSymbol))
.ToList();
var ltp = db.stockData.Where(x => x.date.CompareTo(lastTradedDate) >= 0 && (x.series == "EQ") || (x.series == "BE"))
.ToList();
return(db.circuitBreaker.Where(x => (x.date.CompareTo(Date30ago.Date) >= 0) && (x.series == "EQ" || x.series == "BE"))
.GroupBy(x => new { x.nseSymbol })
.Select(x => new CircuitBreakerInfo()
{
nseSymbol = x.Key.nseSymbol,
count_h = x.Where(y => y.high_low == 'H').Count(),
count_l = x.Where(y => y.high_low == 'L').Count(),
today = result.Any(y => y.nseSymbol == x.Key.nseSymbol) ? 1 : 0,
ltp = ltp.Where(y => y.symbol == x.Key.nseSymbol).Select(y => y.close).FirstOrDefault()
})
.Where(x => x.count_h > 0) //Lets return only those stocks which have hit upper circuits atleast once
.ToList());
}
}
public List <StockHistory> GetStockHistory(string symbol, int year, int month)
{
using (var db = new StockDataContext())
{
var result = db.stockData.Where(x => (x.symbol == symbol) && (x.series == "EQ" || x.series == "BE"))
.Select(x => new StockHistory
{
change = x.change,
deliverableQty = x.deliverableQty,
deliveryPercentage = x.deliveryPercentage,
totalTrades = x.totalTrades,
date = x.date.Date,
ltp = x.close,
openPrice = x.open
})
.OrderBy(x => x.date)
.ToList();
result = result.Where(x => (x.date.Year == year) && (x.date.Month == month)).ToList();
if (result.Count() > 3)
{
for (int i = 1; i < result.Count(); i++)
{
if (result[i - 1].deliverableQty != 0)
{
result[i].volumeChange = (decimal)Math.Round(1.0 * (result[i].deliverableQty - result[i - 1].deliverableQty) / result[i - 1].deliverableQty, 2);
}
}
}
return(result.OrderByDescending(x => x.date).ToList());
}
}
public int AddCompaniesToList(List <CompanyInformation> list)
{
using (var db = new StockDataContext())
{
db.companyInformation.AddRange(list);
// Return the number of companies added to list
return(db.SaveChanges());
}
}
public List <StockMonthlyStats> GetStockMonthlyStatsFromTable(int year)
{
var mapping = getSymbolToIndustryMapping();
using (var db = new StockDataContext())
{
var result = db.monthlyStockStats.Where(x => x.year == year).OrderBy(x => x.symbol).ToList();
result.ForEach(x => x.sector = mapping.TryGetValue(x.symbol, out string industry) ? industry : ConstValues.defaultIndustry);
return(result);
}
}
public List <DailyStockData> GetLTP(DateTime date)
{
var symSectorMapping = getSymbolToIndustryMapping();
using (var db = new StockDataContext())
{
var ltp = db.stockData.Where(x => (x.date.CompareTo(date.Date) == 0) && (x.series == "EQ" || x.series == "BE" || x.series == "SM")).ToList();
ltp.ForEach(x => x.industry = symSectorMapping.TryGetValue(x.symbol, out string industry) ? industry : ConstValues.defaultIndustry);
return(ltp);
}
}
private Dictionary <string, string> getSymbolToIndustryMapping()
{
using (var db = new StockDataContext())
{
Dictionary <string, string> dict = new Dictionary <string, string>();
db.companyInformation.Select(x => new { x.symbol, x.industry })
.ToList()
.ForEach(x => dict.TryAdd(x.symbol, x.industry));
return(dict);
}
}
public List <SectorChange> GetSectorChange(int day = 0)
{
var lastTradedDate = GetLastTradeDate(day);
var Date5ago = GetLastTradeDate(6);
using (var db = new StockDataContext())
{
return(db.sectorInformation.Where(x => x.date.CompareTo(Date5ago.Date) >= 0)
.GroupBy(x => new { x.industry })
.Select(x => new SectorChange(x.Key.industry, x.OrderByDescending(y => y.date)))
.OrderByDescending(x => x.change)
.ToList());
}
}
public List <StockMonthlyStats> GetStockMonthlyStats(int year)
{
var mapping = getSymbolToIndustryMapping();
using (var db = new StockDataContext())
{
var result = db.stockData.Where(x => (x.series == "EQ" || x.series == "BE") && (x.date.Year == year))
.GroupBy(x => new { x.symbol })
.Select(x => GetStockMonthlyStats(x.Key.symbol, x.AsQueryable(), year))
.OrderBy(x => x.symbol)
.ToList();
result.ForEach(x => x.sector = mapping.TryGetValue(x.symbol, out string industry) ? industry : ConstValues.defaultIndustry);
return(result);
}
}
// 0 refers to the last trading day and positive number (day) refers to
// last trading day - day
public DateTime GetLastTradeDate(int day = 0)
{
if (day < 0)
{
throw new Exception(@"Day ${day} should be greater or equal to 0");
}
using (var db = new StockDataContext())
{
var trading_days = db.stockData.Select(x => x.date)
.Distinct()
.OrderByDescending(x => x)
.ToList();
return((trading_days.Count() > day) ? trading_days[day] : trading_days.Last());
}
}
public List <CompanyInfo> getCompanyList()
{
using (var db = new StockDataContext())
{
var result = db.companyInformation.Select(x => new CompanyInfo()
{
companyName = x.companyName,
symbol = x.symbol,
series = x.series,
industry = x.industry
})
.ToList();
return(result);
}
}
public List <StockStats> GetStockStats()
{
var date = GetLastTradeDate(0);
var date300 = GetLastTradeDate(300);
var mapping = getSymbolToIndustryMapping();
using (var db = new StockDataContext())
{
var result = db.stockData.Where(x => ((x.series == "BE" || x.series == "EQ") && (x.date.CompareTo(date300) > 0)))
.GroupBy(x => new { x.symbol })
.OrderBy(x => x.Key.symbol)
.Select(x => getStockStats(x.Key.symbol,
mapping,
x.OrderByDescending(y => y.date).Take(300).ToList(),
date))
.ToList();
return(result.Where(x => x.ltp != 0).ToList());
}
}
public List <SectorStats> GetSectorMonthlyStats()
{
using (var db = new StockDataContext())
{
var result = db.sectorInformation
.GroupBy(x => new { x.industry })
.OrderBy(x => x.Key.industry)
.Select(x => new SectorStats()
{
industry = x.Key.industry,
stats = x.GroupBy(y => new { y.date.Year, y.date.Month })
.Select(y => new MonthlyStats(y.Key.Month, y.Key.Year, y.Average(a => a.change)))
.OrderByDescending(y => y.year)
.ThenByDescending(y => y.month)
.ToList()
}
)
.ToList();
return(result);
}
}
public int AddDailyStockData(List <DailyStockData> stockData, List <CircuitBreaker> circuitBreaker, DateTime date)
{
var symIndMapping = getSymbolToIndustryMapping();
using (var db = new StockDataContext())
{
db.stockData.AddRange(stockData);
stockData.ForEach(x => x.industry = symIndMapping.TryGetValue(x.symbol, out string industry) ? industry : ConstValues.defaultIndustry);
var sectorChange = stockData.GroupBy(x => x.industry)
.Select(x => new SectorInformation(date, x.Key, x.Average(y => y.change)))
.OrderBy(x => x.industry);
db.sectorInformation.AddRange(sectorChange);
if (circuitBreaker != null)
{
db.circuitBreaker.AddRange(circuitBreaker);
}
var count = db.SaveChanges();
Console.WriteLine("Added {0} rows while updating companies Stock Data for {1} ", count, date.ToString());
return(count);
}
}
