public void SecurityControllerTest()
{
//http://www.davepaquette.com/archive/2014/03/18/seeding-entity-framework-database-from-csv.aspx
//http://adrianmejia.com/blog/2011/07/18/cs-getmanifestresourcestream-gotcha
Assembly assembly = Assembly.GetExecutingAssembly();
string resourceName = "ShareWealth.Web.Test.SeedData.stockPrice.csv";
ApplicationDbContext context = new ApplicationDbContext();
using (Stream stream = assembly.GetManifestResourceStream(resourceName))
{
using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
{
CsvReader csvReader = new CsvReader(reader);
csvReader.Configuration.WillThrowOnMissingField = false;
var stockPrice = csvReader.GetRecords<StockPrice>().ToArray();
for (int i = 1; i < 4 ; i++)
{
var sp = new StockPrice {
Id = i,
TradingDate = new DateTime(2013,12,5),
SecurityId = stockPrice[i].SecurityId,
Open = stockPrice[i].Open,
High = stockPrice[i].High,
Low = stockPrice[i].Low,
Close = stockPrice[i].Close,
};
context.StockPrices.Add(sp);
}
context.SaveChanges();
}
}
}
public static void UpdateStockPrice(StockPrice stockPrice)
{
try
{
StockPriceDAO stockPriceDAO = new StockPriceDAO();
stockPriceDAO.UpdateStockPrice(stockPrice);
}
catch (ApplicationException)
{
throw;
}
catch (Exception ex)
{
// log this exception
log4net.Util.LogLog.Error(ex.Message, ex);
// wrap it and rethrow
throw new ApplicationException(SR.BusinessUpdateStockPriceException, ex);
}
}
public virtual void CreateStockPrice(StockPrice stockPrice)
{
try
{
Database database = DatabaseFactory.CreateDatabase("CoreSecurityServiceConnection");
DbCommand dbCommand = database.GetStoredProcCommand("spStockPriceInsert");
database.AddInParameter(dbCommand, "@TradingDate", DbType.AnsiString, stockPrice.TradingDate);
database.AddInParameter(dbCommand, "@StockCode", DbType.AnsiString, stockPrice.StockCode);
database.AddInParameter(dbCommand, "@StockNo", DbType.Int32, stockPrice.StockNo);
database.AddInParameter(dbCommand, "@StockType", DbType.AnsiStringFixedLength, stockPrice.StockType);
database.AddInParameter(dbCommand, "@BoardType", DbType.AnsiStringFixedLength, stockPrice.BoardType);
database.AddInParameter(dbCommand, "@OpenPrice", DbType.Decimal, stockPrice.OpenPrice);
database.AddInParameter(dbCommand, "@ClosePrice", DbType.Decimal, stockPrice.ClosePrice);
database.AddInParameter(dbCommand, "@BasicPrice", DbType.Decimal, stockPrice.BasicPrice);
database.AddInParameter(dbCommand, "@CeilingPrice", DbType.Decimal, stockPrice.CeilingPrice);
database.AddInParameter(dbCommand, "@FloorPrice", DbType.Decimal, stockPrice.FloorPrice);
database.AddInParameter(dbCommand, "@AveragePrice", DbType.Decimal, stockPrice.AveragePrice);
database.AddInParameter(dbCommand, "@TransactionDate", DbType.DateTime, stockPrice.TransactionDate);
database.AddInParameter(dbCommand, "@TotalRoom", DbType.Decimal, stockPrice.TotalRoom);
database.AddInParameter(dbCommand, "@CurrentRoom", DbType.Decimal, stockPrice.CurrentRoom);
database.AddInParameter(dbCommand, "@Suspension", DbType.AnsiStringFixedLength, stockPrice.Suspension);
database.AddInParameter(dbCommand, "@Delisted", DbType.AnsiStringFixedLength, stockPrice.Delisted);
database.AddInParameter(dbCommand, "@Halted", DbType.AnsiStringFixedLength, stockPrice.Halted);
database.AddInParameter(dbCommand, "@Split", DbType.AnsiStringFixedLength, stockPrice.Split);
database.AddInParameter(dbCommand, "@Benefit", DbType.AnsiStringFixedLength, stockPrice.Benefit);
database.AddInParameter(dbCommand, "@Meeting", DbType.AnsiStringFixedLength, stockPrice.Meeting);
database.AddInParameter(dbCommand, "@Notice", DbType.AnsiStringFixedLength, stockPrice.Notice);
database.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
// log this exception
log4net.Util.LogLog.Error(ex.Message, ex);
// wrap it and rethrow
throw new ApplicationException(SR.DataAccessCreateStockPriceException, ex);
}
}
public Task <IEnumerable <StockPrice> > GetStocksFor(string ticker)
{
ThreadPool.QueueUserWorkItem(_ =>
{
try
{
var prices = new List <StockPrice>();
var lines = File.ReadAllLines(@"StockPrices_Small.csv");
foreach (var line in lines.Skip(1))
{
var segments = line.Split(',');
for (var i = 0; i < segments.Length; i++)
{
segments[i] = segments[i].Trim('\'', '"');
}
var price = new StockPrice
{
Ticker = segments[0],
TradeDate = DateTime.ParseExact(segments[1], "M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture),
Volume = Convert.ToInt32(segments[6], CultureInfo.InvariantCulture),
Change = Convert.ToDecimal(segments[7], CultureInfo.InvariantCulture),
ChangePercent = Convert.ToDecimal(segments[8], CultureInfo.InvariantCulture),
};
prices.Add(price);
}
}
catch (Exception ex)
{
}
});
// TODO: Change this
return(Task.FromResult <IEnumerable <StockPrice> >(null));;
}
private StockScreeningViewModel BuildStockScreeningModel(StockPrice x, string lastTrendBar)
{
return(new StockScreeningViewModel()
{
Date = x.Date,
StockID = x.StockID,
Open = x.Open,
Close = x.Close,
High = x.High,
Low = x.Low,
Support = x.Support,
Resistance = x.Resistance,
CloseToSupport = (x.Close - x.Support) / x.Support * 100,
CloseToResistance = (x.Close - x.Resistance) / x.Resistance * 100,
GSLineDirection = x.GSLineDirection,
Trend = Screening.DetermineTrend(x),
NormalRange = Screening.DetermineNormalRange(x),
BigWave = Screening.DetermineBigWave(x),
RiskProfile = Screening.DetermineRiskProfile(x),
TradingPlan = Screening.DetermineTradingPlan(x),
BuyLimit = Screening.DetermineBuyLimit(x),
LastTrendBar = lastTrendBar
});
}
public static string DetermineTradingPlan(StockPrice x)
{
//if (x.High > x.Resistance && x.High <= (decimal)1.03 * x.Resistance) //If Highest Price > Resist (0% < X <= +3%) | X = Closing price
if (x.Close > x.Resistance && x.Close <= (decimal)1.03 * x.Resistance && x.High > x.Resistance)
{
return("Buy");
}
else if (x.Close > (decimal)1.03 * x.Resistance) //If Closing Price > Resist (X > +3%) | X = Closing price
{
return("Hold");
}
else if (x.Close <= x.Resistance && x.Close >= (decimal)0.97 * x.Resistance) //If Closing Price < Resist (-3% <= X <= 0% ) | X = Closing price
{
return("Watch");
}
else if (x.Low < x.Support && x.Low >= (decimal)0.925 * x.Support) //If Lowest Price < Support ( -7.5% <= X < 0%)
{
return("Sell");
}
else
{
return("");
}
}
public void PeriodFoundWhenAddingNewStockPrice()
{
// Arrange
var mockRepository = new Mock <IRepository>();
var domainRepository = new StockPriceRepository(mockRepository.Object);
mockRepository.Setup(x => x.Get <Period>(It.IsAny <Expression <Func <Period, bool> > >())).Returns(new Period {
Id = 69
});
var entity = new StockPrice
{
Stock = new Stock {
},
Period = new Period {
}
};
// Act
domainRepository.AddStockPrice(entity);
// Assert
mockRepository.Verify(x => x.SaveOrUpdate <StockPrice>(It.Is <StockPrice>(sp => sp.Period.Id == 69)), Times.Once());
mockRepository.VerifyAll();
}
private Decimal GetAverageVolume(StockPrice stock, int days, out string lastTrendBar)
{
//*Fungsi ini menghitung rata2 volume selama (days) terakhir sekaligus mencari last trend bar yang muncul
var avgdata = _context.StockPrice.Where(m => m.Date <= stock.Date && m.Date >= stock.Date.AddDays(-days) && m.StockID == stock.StockID).OrderByDescending(m => m.Date).AsNoTracking();
long temp = 0; int count = 0;
var lastTrend = "";
foreach (var item in avgdata)
{
temp += item.Volume;
count++;
if (lastTrend == "")
{
if (item.TrendHigh > 0)
{
lastTrend = "H;" + item.TrendHigh;
if (item.TrendLow > 0)
{
lastTrend += ";L" + item.TrendLow;
}
}
else if (item.TrendLow > 0)
{
lastTrend = "L;" + item.TrendLow;
}
}
}
lastTrendBar = lastTrend;
var avg = (Decimal)temp / count;
//var avg = (Decimal) avgdata.Average(s => s.Volume);
return(avg);
}
public virtual StockPrice CreateStockPriceFromReader(IDataReader reader)
{
StockPrice item = new StockPrice();
try
{
if (!reader.IsDBNull(reader.GetOrdinal("TradingDate"))) item.TradingDate = (string)reader["TradingDate"];
if (!reader.IsDBNull(reader.GetOrdinal("StockCode"))) item.StockCode = (string)reader["StockCode"];
if (!reader.IsDBNull(reader.GetOrdinal("StockNo"))) item.StockNo = (int)reader["StockNo"];
if (!reader.IsDBNull(reader.GetOrdinal("StockType"))) item.StockType = (string)reader["StockType"];
if (!reader.IsDBNull(reader.GetOrdinal("BoardType"))) item.BoardType = (string)reader["BoardType"];
if (!reader.IsDBNull(reader.GetOrdinal("OpenPrice"))) item.OpenPrice = (decimal)reader["OpenPrice"];
if (!reader.IsDBNull(reader.GetOrdinal("ClosePrice"))) item.ClosePrice = (decimal)reader["ClosePrice"];
if (!reader.IsDBNull(reader.GetOrdinal("BasicPrice"))) item.BasicPrice = (decimal)reader["BasicPrice"];
if (!reader.IsDBNull(reader.GetOrdinal("CeilingPrice"))) item.CeilingPrice = (decimal)reader["CeilingPrice"];
if (!reader.IsDBNull(reader.GetOrdinal("FloorPrice"))) item.FloorPrice = (decimal)reader["FloorPrice"];
if (!reader.IsDBNull(reader.GetOrdinal("AveragePrice"))) item.AveragePrice = (decimal)reader["AveragePrice"];
if (!reader.IsDBNull(reader.GetOrdinal("TransactionDate"))) item.TransactionDate = (DateTime)reader["TransactionDate"];
if (!reader.IsDBNull(reader.GetOrdinal("TotalRoom"))) item.TotalRoom = (decimal)reader["TotalRoom"];
if (!reader.IsDBNull(reader.GetOrdinal("CurrentRoom"))) item.CurrentRoom = (decimal)reader["CurrentRoom"];
if (!reader.IsDBNull(reader.GetOrdinal("Suspension"))) item.Suspension = (string)reader["Suspension"];
if (!reader.IsDBNull(reader.GetOrdinal("Delisted"))) item.Delisted = (string)reader["Delisted"];
if (!reader.IsDBNull(reader.GetOrdinal("Halted"))) item.Halted = (string)reader["Halted"];
if (!reader.IsDBNull(reader.GetOrdinal("Split"))) item.Split = (string)reader["Split"];
if (!reader.IsDBNull(reader.GetOrdinal("Benefit"))) item.Benefit = (string)reader["Benefit"];
if (!reader.IsDBNull(reader.GetOrdinal("Meeting"))) item.Meeting = (string)reader["Meeting"];
if (!reader.IsDBNull(reader.GetOrdinal("Notice"))) item.Notice = (string)reader["Notice"];
}
catch (Exception ex)
{
// log this exception
log4net.Util.LogLog.Error(ex.Message, ex);
// wrap it and rethrow
throw new ApplicationException(SR.DataAccessCreateStockPriceFromReaderException, ex);
}
return item;
}
private async Task <IList <StockPrice> > GetStockPrices()
{
var prices = new List <StockPrice>();
using (var stream =
new StreamReader(File.OpenRead(@"D:\Demo\StockPrices.csv")))
{
await stream.ReadLineAsync(); // Skip headers
CultureInfo cultureInfo = CultureInfo.InvariantCulture;
string line;
while ((line = await stream.ReadLineAsync()) != null)
{
var segments = line.Split(',');
DateTime dateTime10 = DateTime.Now;
for (var i = 0; i < segments.Length; i++)
{
segments[i] = segments[i].Trim('\'', '"');
}
bool isSuccess = DateTime.TryParse(segments[1], out dateTime10);
var price = new StockPrice
{
Ticker = segments[0],
TradeDate = isSuccess? dateTime10: DateTime.Now,
Volume = Convert.ToInt32(segments[6]),
Change = Convert.ToDouble(segments[7], cultureInfo),
ChangePercent = Convert.ToDouble(segments[8], cultureInfo),
};
prices.Add(price);
}
}
return(prices);
}
public IList <StockPrice> ImportStock(string filepath)
{
FileInfo file = new FileInfo(filepath);
string filename = file.Name;
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];
int TotalRows = worksheet.Dimension.Rows;
StockPrice stockPrice = new StockPrice();
int len = _db.StockPrices.ToList().Count();
for (int i = 2; i <= TotalRows; i++)
{
stockPrice = new StockPrice
{
DataID = (i - 1).ToString() + worksheet.Cells[i, 1].Value.ToString().Trim(),
CompanyCode = worksheet.Cells[i, 1].Value.ToString().Trim(),
CompanyName = worksheet.Cells[i, 2].Value.ToString().Trim(),
PricePerShare = worksheet.Cells[i, 3].Value.ToString().Trim(),
Date = DateTime.Parse(worksheet.Cells[i, 4].Value.ToString().Trim()),
Time = worksheet.Cells[i, 5].Value.ToString().Trim()
};
if (_db.StockPrices.Contains(stockPrice))
{
continue;
}
_db.StockPrices.Add(stockPrice);
}
_db.SaveChanges();
return(_db.StockPrices.ToList());
}
}
public bool addStockPrice(AddStockPriceModel sp)
{
var cmpany = _db.Companies.Where(c => c.CompanyID == sp.CompanyCode).FirstOrDefault();
StockPrice stockPrice = new StockPrice
{
CompanyCode = sp.CompanyCode,
Company = cmpany,
Exchange = sp.Exchange,
price = sp.price,
Date = sp.Date,
Time = sp.Time
};
var result = _db.StockPrices.Add(stockPrice);
_db.SaveChanges();
if (result != null)
{
return(true);
}
else
{
return(false);
}
}
public void UpdateStockPrice(StockPrice value)
{
db.StockPrice.Update(value);
db.SaveChanges();
}
public void AddStockPrice(StockPrice value)
{
db.StockPrice.Add(value);
db.SaveChanges();
}
public Tuple <int, string, string> UploadExcel(string filePath)
{
var list = new List <StockPrice>();
FileInfo info = new FileInfo(filePath);
string fileExtension = info.Extension;
string excelConString = "";
//Get connection string using extension
switch (fileExtension)
{
// If uploaded file is Excel 1997 - 2007.
case ".xls":
excelConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
break;
//If uploaded file is Excel 2007 and above
case ".xlsx":
excelConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
break;
}
// Read data from first sheet of excel into datatable
DataTable dt = new DataTable();
excelConString = string.Format(excelConString, filePath);
Tuple <int, string, string> summary;
using (OleDbConnection excelOledbConnection = new OleDbConnection(excelConString))
{
using (OleDbCommand excelDbCommand = new OleDbCommand())
{
using (OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter())
{
excelDbCommand.Connection = excelOledbConnection;
excelOledbConnection.Open();
// Get schema from excel sheet
DataTable excelSchema = excelOledbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); // Get sheet name
string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
excelOledbConnection.Close();
// Read Data from First Sheet.
excelOledbConnection.Open();
excelDbCommand.CommandText = "SELECT * From [" + sheetName + "]";
excelDataAdapter.SelectCommand = excelDbCommand;
//Fill datatable from adapter
excelDataAdapter.Fill(dt);
excelOledbConnection.Close();
int count = dt.Rows.Count;
string cName;
string sName;
foreach (DataRow r in dt.Rows)
{
// int i = Int32.Parse(r[1].ToString().Trim());
//You can try single() method instead of where.
//StockExchangeId = context.StockExchanges.Where(s=> s.StockExchangeName == r[1].ToString().Trim())
//list.Add(
// new StockPrice()
// {
// CompanyId = int.Parse(r[0].ToString().Trim()),
// StockExchangeId = int.Parse(r[1].ToString().Trim()),
// CurrentPrice = Convert.ToDouble(r[2].ToString().Trim()),
// Date = r[3].ToString().Trim(),
// Time = r[4].ToString().Trim()
// }) ;
DateTime t = (DateTime)r[3];
var sp = new StockPrice()
{
CompanyId = int.Parse(r[0].ToString().Trim()),
StockExchangeId = int.Parse(r[1].ToString().Trim()),
CurrentPrice = Convert.ToDouble(r[2].ToString().Trim()),
Date = t.ToString("d").Trim(),
//Date = r[3].ToString().Trim(),
Time = r[4].ToString().Trim()
};
sp.Company = context.Companies.Find(sp.CompanyId);
sp.StockExchange = context.StockExchanges.Find(sp.StockExchangeId);
list.Add(sp);
// context.Add(sp);
// var x= context.SaveChanges();
// Console.WriteLine(x);
}
cName = context.Companies.Find(list.First().CompanyId).CompanyName;
sName = context.StockExchanges.Find(list.First().StockExchangeId).StockExchangeName;
summary = new Tuple <int, string, string>(count, cName, sName);
context.StockPrices.AddRange(list); //insert list of rows to table
var x = context.SaveChanges();
Console.WriteLine(x);
return(summary);
//summary tuple returns no. of entries, company name and stock exchange name.
}
}
}
}
/// <summary>
/// 從網路直接下載上市櫃股票價格檔並解析
/// </summary>
/// <param name="stock"></param>
/// <param name="fetchDate"></param>
/// <param name="type">1: 上市, 2: 上櫃</param>
/// <returns></returns>
private List <MyStockAnalyzer.Classes.StockPrice> downloadStockPriceData(MyStockAnalyzer.Classes.StockData stock,
DateTime fetchDate, string type)
{
List <MyStockAnalyzer.Classes.StockPrice> result = new List <MyStockAnalyzer.Classes.StockPrice>();
string downloadUrl = String.Format(type == "1" ? ConfigHelper.StockPriceUrl1 : ConfigHelper.StockPriceUrl2,
type == "1" ? fetchDate.Year.ToString() : (fetchDate.Year - 1911).ToString("000"),
fetchDate.Month.ToString("00"), stock.StockId);
WebClient wc = getNewWebClient();
string csvText = wc.DownloadString(downloadUrl);
string[] lines = csvText.Split(new string[] { "\r", "\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string line in lines.Skip(type == "1" ? 2 : 5))
{
// 上櫃檔案最後一行跳過
if (type == "2" && line.Equals(lines.Last()))
{
continue;
}
string[] fields = CSVHelper.ParseCSVLine(line, ",");
if (fields.Length == 9)
{
if (fields[3].Equals("--") || fields[4].Equals("--") || fields[5].Equals("--") ||
fields[6].Equals("--"))
{
continue;
}
string[] strDate =
(fields[0].Trim().StartsWith("1")
? fields[0].Trim().Substring(0, 9)
: fields[0].Trim().Substring(0, 8)).Split(new string[] { "/" }, StringSplitOptions.None);
int year = Convert.ToInt32(strDate[0]);
if (year < 1000)
{
year += 1911;
}
DateTime date = new DateTime(year, Convert.ToInt32(strDate[1]), Convert.ToInt32(strDate[2]));
// date = date.AddYears(1911);
StockPrice priceData = new StockPrice();
priceData.StockId = stock.StockId;
priceData.Date = date;
int amount = Convert.ToInt32(fields[1].Replace(",", ""));
if (amount != 0)
{
if (type == "1")
{
priceData.Amount = amount / 1000;
}
else if (type == "2") // 上櫃欄位直接是千股
{
priceData.Amount = amount;
}
priceData.Open = Convert.ToDecimal(fields[3].Replace(",", ""));
priceData.High = Convert.ToDecimal(fields[4].Replace(",", ""));
priceData.Low = Convert.ToDecimal(fields[5].Replace(",", ""));
priceData.Close = Convert.ToDecimal(fields[6].Replace(",", ""));
result.Add(priceData);
}
}
}
return(result);
}
public async Task CanWorkWithRollupTimeSeries2()
{
using (var store = GetDocumentStore())
{
var raw = new RawTimeSeriesPolicy(TimeSpan.FromHours(24));
var p1 = new TimeSeriesPolicy("By6Hours", TimeSpan.FromHours(6), raw.RetentionTime * 4);
var p2 = new TimeSeriesPolicy("By1Day", TimeSpan.FromDays(1), raw.RetentionTime * 5);
var p3 = new TimeSeriesPolicy("By30Minutes", TimeSpan.FromMinutes(30), raw.RetentionTime * 2);
var p4 = new TimeSeriesPolicy("By1Hour", TimeSpan.FromMinutes(60), raw.RetentionTime * 3);
var config = new TimeSeriesConfiguration
{
Collections = new Dictionary <string, TimeSeriesCollectionConfiguration>
{
["Users"] = new TimeSeriesCollectionConfiguration
{
RawPolicy = raw,
Policies = new List <TimeSeriesPolicy>
{
p1, p2, p3, p4
}
},
},
PolicyCheckFrequency = TimeSpan.FromSeconds(1)
};
await store.Maintenance.SendAsync(new ConfigureTimeSeriesOperation(config));
await store.TimeSeries.RegisterAsync <User, StockPrice>();
var database = await Databases.GetDocumentDatabaseInstanceFor(store);
var now = DateTime.UtcNow;
var baseline = now.AddDays(-12);
var total = TimeSpan.FromDays(12).TotalMinutes;
using (var session = store.OpenSession())
{
session.Store(new User {
Name = "Karmel"
}, "users/karmel");
var ts = session.TimeSeriesFor <StockPrice>("users/karmel");
var entry = new StockPrice();
for (int i = 0; i <= total; i++)
{
entry.Open = i;
entry.Close = i + 100_000;
entry.High = i + 200_000;
entry.Low = i + 300_000;
entry.Volume = i + 400_000;
ts.Append(baseline.AddMinutes(i), entry, "watches/fitbit");
}
session.SaveChanges();
}
await database.TimeSeriesPolicyRunner.RunRollups();
await database.TimeSeriesPolicyRunner.DoRetention();
await TimeSeries.VerifyPolicyExecutionAsync(store, config.Collections["Users"], 12, rawName: "StockPrices");
using (var session = store.OpenSession())
{
var query = session.Query <User>()
.Select(u =>
RavenQuery.TimeSeries <StockPrice>(u, "StockPrices").Select(x =>
new {
First = x.First(),
Last = x.Last(),
Min = x.Min(),
Max = x.Max(),
Sum = x.Sum(),
Count = x.Count(),
Avg = x.Average()
})
.ToList());
var result = query.Single();
Assert.Equal(1, result.Results.Length);
var r = result.Results[0];
Assert.NotNull(r.First);
Assert.NotNull(r.Last);
Assert.NotNull(r.Min);
Assert.NotNull(r.Max);
Assert.NotNull(r.Sum);
Assert.NotNull(r.Count);
Assert.NotNull(r.Average);
}
using (var session = store.OpenSession())
{
var query = session.Query <User>()
.Select(u =>
RavenQuery.TimeSeries <StockPrice>(u, "StockPrices")
.GroupBy(x => x.Hours(3))
.Select(x =>
new
{
First = x.First(),
Last = x.Last(),
Min = x.Min(),
Max = x.Max(),
Sum = x.Sum(),
Count = x.Count(),
Avg = x.Average()
})
.ToList());
var result = query.Single();
var r = result.Results[0];
Assert.NotNull(r.First);
Assert.NotNull(r.Last);
Assert.NotNull(r.Min);
Assert.NotNull(r.Max);
Assert.NotNull(r.Sum);
Assert.NotNull(r.Count);
Assert.NotNull(r.Average);
}
using (var session = store.OpenSession())
{
var ts1 = session.TimeSeriesRollupFor <StockPrice>("users/karmel", p1.Name);
var r = ts1.Get().First();
Assert.NotNull(r.First);
Assert.NotNull(r.Last);
Assert.NotNull(r.Min);
Assert.NotNull(r.Max);
Assert.NotNull(r.Sum);
Assert.NotNull(r.Count);
Assert.NotNull(r.Average);
}
}
}
public async Task CanWorkWithRollupTimeSeries()
{
using (var store = GetDocumentStore())
{
var raw = new RawTimeSeriesPolicy(TimeSpan.FromHours(24));
var p1 = new TimeSeriesPolicy("By6Hours", TimeSpan.FromHours(6), raw.RetentionTime * 4);
var p2 = new TimeSeriesPolicy("By1Day", TimeSpan.FromDays(1), raw.RetentionTime * 5);
var p3 = new TimeSeriesPolicy("By30Minutes", TimeSpan.FromMinutes(30), raw.RetentionTime * 2);
var p4 = new TimeSeriesPolicy("By1Hour", TimeSpan.FromMinutes(60), raw.RetentionTime * 3);
var config = new TimeSeriesConfiguration
{
Collections = new Dictionary <string, TimeSeriesCollectionConfiguration>
{
["Users"] = new TimeSeriesCollectionConfiguration
{
RawPolicy = raw,
Policies = new List <TimeSeriesPolicy>
{
p1, p2, p3, p4
}
},
},
PolicyCheckFrequency = TimeSpan.FromSeconds(1)
};
await store.Maintenance.SendAsync(new ConfigureTimeSeriesOperation(config));
await store.TimeSeries.RegisterAsync <User, StockPrice>();
var database = await Databases.GetDocumentDatabaseInstanceFor(store);
var now = DateTime.UtcNow;
var nowMinutes = now.Minute;
now = now.AddMinutes(-nowMinutes);
database.Time.UtcDateTime = () => DateTime.UtcNow.AddMinutes(-nowMinutes);
var baseline = now.AddDays(-12);
var total = TimeSpan.FromDays(12).TotalMinutes;
using (var session = store.OpenSession())
{
session.Store(new User {
Name = "Karmel"
}, "users/karmel");
var ts = session.TimeSeriesFor <StockPrice>("users/karmel");
var entry = new StockPrice();
for (int i = 0; i <= total; i++)
{
entry.Open = i;
entry.Close = i + 100_000;
entry.High = i + 200_000;
entry.Low = i + 300_000;
entry.Volume = i + 400_000;
ts.Append(baseline.AddMinutes(i), entry, "watches/fitbit");
}
session.SaveChanges();
}
await database.TimeSeriesPolicyRunner.RunRollups();
await database.TimeSeriesPolicyRunner.DoRetention();
await TimeSeries.VerifyPolicyExecutionAsync(store, config.Collections["Users"], 12, rawName: "StockPrices");
using (var session = store.OpenSession())
{
var query = session.Advanced.RawQuery <TimeSeriesRawResult <StockPrice> >(@"
declare timeseries out()
{
from StockPrices
between $start and $end
}
from Users as u
select out()
")
.AddParameter("start", baseline.AddDays(-1))
.AddParameter("end", now.AddDays(1));
var result = query.Single();
var count = result.Results.Length;
Assert.Equal(5, result.Results[count - 1440].Values.Length);
foreach (var res in result.Results)
{
Assert.Equal(5, res.Values.Length);
}
}
using (var session = store.OpenSession())
{
// test the same query using linq
var query = session.Query <User>()
.Select(u =>
RavenQuery.TimeSeries <StockPrice>(u, "StockPrices", baseline.AddDays(-1), now.AddDays(1))
.ToList());
var result = query.Single();
var count = result.Results.Length;
Assert.Equal(5, result.Results[count - 1440].Values.Length);
foreach (var res in result.Results)
{
Assert.Equal(5, res.Values.Length);
}
}
now = DateTime.UtcNow;
using (var session = store.OpenSession())
{
var ts = session.TimeSeriesRollupFor <StockPrice>("users/karmel", p1.Name);
var a = new TimeSeriesRollupEntry <StockPrice>(DateTime.Now)
{
Max = new StockPrice
{
Close = 1
}
};
ts.Append(a);
session.SaveChanges();
}
using (var session = store.OpenSession())
{
var ts = session.TimeSeriesRollupFor <StockPrice>("users/karmel", p1.Name);
var res = ts.Get(from: now.AddMilliseconds(-1)).ToList();
Assert.Equal(1, res.Count);
Assert.Equal(1, res[0].Max.Close);
}
}
}
public IEnumerable <StockPrice> GetPriceNumberDays(string id3)
{
int num = Convert.ToInt32(id3);
return(StockPrice.ReadLastNumberDays(num));
}
//private bool StockPriceExists(string id, DateTime date)
//{
// return _context.StockPrice.Any(e => e.StockID == id && e.Date == date);
//}
#region "FORMULA"
//private async Task<bool> InitIndicator()
//{
// foreach (Stock stock in await _context.Stock.Where(s=>s.StockID == "AALI").ToListAsync())
// //foreach (Stock stock in _context.Stock.Where(m => m.StockID == "AALI" || m.StockID == "BBCA" || m.StockID == "PTBA"))
// {
// //Console.Write("TEST " + stock.StockID);
// System.Diagnostics.Debug.WriteLine("TEST " + stock.StockID);
// //*cari semua histori harga
// var data = _context.StockPrice.Where(c => c.StockID == stock.StockID).OrderBy(c => c.Date).ToList();
// var count = data.Count();
// if (count > 0)
// {
// for(int i=0; i<count-1; i++)
// {
// var price = data[0];
// var counter = i;
// //foreach (StockPrice price in data.ToList().Last())
// //{
// price.MA20 = Calculation.MA(counter, data.ToArray(), 20);
// price.MA60 = Calculation.MA(counter, data.ToArray(), 60);
// price.Support = Calculation.Support(counter, data.ToArray());
// price.Resistance = Calculation.Resistance(counter, data.ToArray());
// dynamic t3 = Calculation.HighestLowest(counter, data.ToArray(), 3);
// price.Highest3Months = (t3 == null ? null : t3.Highest);
// price.Lowest3Months = (t3 == null ? null : t3.Lowest);
// dynamic t6 = Calculation.HighestLowest(counter, data.ToArray(), 6);
// price.Highest6Months = (t6 == null ? null : t6.Highest);
// price.Lowest6Months = (t6 == null ? null : t6.Lowest);
// dynamic t12 = Calculation.HighestLowest(counter, data.ToArray(), 12);
// price.Highest12Months = (t12 == null ? null : t12.Highest);
// price.Lowest12Months = (t12 == null ? null : t12.Lowest);
// price.TrendHigh = Calculation.TrendHigh(counter, data.ToArray(), price.Highest3Months, price.Highest6Months, price.Highest12Months);
// price.TrendLow = Calculation.TrendLow(counter, data.ToArray(), price.Lowest3Months, price.Lowest6Months, price.Lowest12Months);
// //dynamic highlow = BreakHighLowCalc(counter, data.ToArray());
// //price.TrendHigh = highlow.TrendHigh;
// //price.TrendLow = highlow.TrendLow;
// price.EMA12 = Calculation.EMA(counter, data.ToArray(), 12);
// price.EMA26 = Calculation.EMA(counter, data.ToArray(), 26);
// if (price.EMA12 == null || price.EMA26 == null)
// price.MACD = null;
// else
// price.MACD = price.EMA12 - price.EMA26;
// price.SignalLine = Calculation.SignalLine(counter, data.ToArray(), 9);
// if (price.MACD == null || price.SignalLine == null)
// price.GSLine = null;
// else
// price.GSLine = (Decimal)(price.MACD + price.SignalLine) / 2;
// price.SD20 = Calculation.SD(counter, data.ToArray(), 20);
// price.BBUpper = price.MA20 + 2 * price.SD20;
// price.BBLower = price.MA20 - 2 * price.SD20;
// }
// //*ambil tanggal terbaru
// //var price = await data.LastAsync();
// //counter++;
// //}
// }
// }
// await _context.SaveChangesAsync();
// return true;
//}
private async Task <bool> CalculateIndicator2(string stockID, StockPrice price)
{
//foreach (Stock stock in await _context.Stock.ToListAsync())
//foreach (Stock stock in _context.Stock.Where(m => m.StockID == "AALI" || m.StockID == "BBCA" || m.StockID == "PTBA"))
{
//Console.Write("TEST " + stock.StockID);
System.Diagnostics.Debug.WriteLine("TEST " + stockID);
var prices = _context.StockPrice.Where(c => c.StockID == stockID).AsNoTracking();
DateTime last_date;
if (prices.Count() > 0)
{
last_date = prices.Max(m => m.Date);
}
else
{
last_date = price.Date;
}
//*cari semua histori harga
StockPrice[] data = prices.Where(c => c.Date >= last_date.AddMonths(-13)).OrderBy(c => c.Date).AsNoTracking().ToArray();
//*tambahkan data terbaru dalam memory
data = data.Append(price).ToArray();
var countx = data.Count();
//StockPrice dataPrev = null;
//var indexPrev = 0;
if (countx >= 5)
{
//*Update harga 3 hari sebelumnya
for (int i = countx - 4; i <= countx - 2; i++)
{
//dataPrev = data[i];
StockPrice dataPrev = await _context.StockPrice.SingleOrDefaultAsync(m => m.Date == data[i].Date && m.StockID == stockID);
//indexPrev = countx - 3;
dataPrev.Support = Calculation.Support(i, data);
data[i].Support = dataPrev.Support;
dataPrev.Resistance = Calculation.Resistance(i, data);
data[i].Resistance = dataPrev.Resistance;
//dataPrev.Support = 60;
//dataPrev.Resistance = 28000;
await _context.SaveChangesAsync();
}
}
var count = data.Count();
if (count > 0)
{
//for(int i=0; i<data.Length-1; i++)
//*ambil tanggal terbaru
//var price = data.Last();
var counter = count - 1;
//foreach (StockPrice price in data.ToList().Last())
//{
price.MA20 = Calculation.MA(counter, data, 20);
price.MA60 = Calculation.MA(counter, data, 60);
price.Support = Calculation.Support(counter, data);
price.Resistance = Calculation.Resistance(counter, data);
dynamic t3 = Calculation.HighestLowest(counter, data, 3);
price.Highest3Months = (t3 == null ? null : t3.Highest);
price.Lowest3Months = (t3 == null ? null : t3.Lowest);
dynamic t6 = Calculation.HighestLowest(counter, data, 6);
price.Highest6Months = (t6 == null ? null : t6.Highest);
price.Lowest6Months = (t6 == null ? null : t6.Lowest);
dynamic t12 = Calculation.HighestLowest(counter, data, 12);
price.Highest12Months = (t12 == null ? null : t12.Highest);
price.Lowest12Months = (t12 == null ? null : t12.Lowest);
price.TrendHigh = Calculation.TrendHigh(counter, data, price.Highest3Months, price.Highest6Months, price.Highest12Months);
price.TrendLow = Calculation.TrendLow(counter, data, price.Lowest3Months, price.Lowest6Months, price.Lowest12Months);
//dynamic highlow = BreakHighLowCalc(counter, data);
//price.TrendHigh = highlow.TrendHigh;
//price.TrendLow = highlow.TrendLow;
price.EMA12 = Calculation.EMA(counter, data, 12);
price.EMA26 = Calculation.EMA(counter, data, 26);
if (price.EMA12 == null || price.EMA26 == null)
{
price.MACD = null;
}
else
{
price.MACD = price.EMA12 - price.EMA26;
}
price.SignalLine = Calculation.SignalLine(counter, data, 9);
if (price.MACD == null || price.SignalLine == null)
{
price.GSLine = null;
}
else
{
price.GSLine = (Decimal)(price.MACD + price.SignalLine) / 2;
}
if (counter >= 1)
{
if (price.GSLine != null && data[counter - 1].GSLine != null)
{
if (price.GSLine > data[counter - 1].GSLine)
{
price.GSLineDirection = "U";
}
else if (price.GSLine < data[counter - 1].GSLine)
{
price.GSLineDirection = "D";
}
else
{
price.GSLineDirection = "N";
}
}
else
{
price.GSLineDirection = "";
}
}
else
{
price.GSLineDirection = "";
}
price.SD20 = Calculation.SD(counter, data, 20);
price.BBUpper = price.MA20 + 2 * price.SD20;
price.BBLower = price.MA20 - 2 * price.SD20;
if (price.Frequency > 0)
{
price.BigWave = (Decimal)price.Volume / (Decimal)(price.Frequency * price.Frequency * price.Frequency);
}
else
{
price.BigWave = 0;
}
price.AverageBigWave = Calculation.AverageBigWave(counter, data, 3);
//counter++;
//}
}
}
//await _context.SaveChangesAsync();
return(true);
}
private void MapStockPriceModel(StockPrice response)
{
try
{
StockPriceModel.SpecialPriceTypes = MapSepcialPrices(response.SpecialPriceTypes);
}
catch (Exception)
{
}
finally
{
StockPriceModel.Description = string.Format("{0} (#{1})", response.Description, StockCode);
StockPriceModel.AvailableQuantity = response.AvailableQuantity;
StockPriceModel.ToDate = string.IsNullOrEmpty(response.ToDate) ? DateTimeOffset.Now :
Convert.ToDateTime(response.ToDate, CultureInfo.InvariantCulture);
StockPriceModel.FromDate = string.IsNullOrEmpty(response.FromDate) ? DateTimeOffset.Now :
Convert.ToDateTime(response.FromDate, CultureInfo.InvariantCulture);
StockPriceModel.RegularPriceText = response.RegularPriceText;
StockPriceModel.IsAddButtonVisible = response.IsAddButtonVisible && IsEditButtonPressed;
StockPriceModel.IsChangePriceEnable = response.IsChangePriceEnable;
StockPriceModel.IsRemoveButtonVisible = response.IsRemoveButtonVisible && IsEditButtonPressed;
StockPriceModel.IsToDateVisible = response.IsToDateVisible && response.IsSpecialPricingVisible;
StockPriceModel.TaxExemptPrice = response.TaxExemptPrice;
StockPriceModel.TaxExemptAvailable = response.TaxExemptAvailable;
StockPriceModel.IsTaxExemptVisible = response.IsTaxExemptVisible;
StockPriceModel.VendorId = response.VendorId;
StockPriceModel.AvailableQuantity = response.AvailableQuantity;
_defaultSelectedSpecialPrice = StockPriceModel.PriceTypeText = response.PriceTypeText;
SelectedPriceIndex = string.IsNullOrEmpty(response.PriceTypeText) ? -1 :
response.SpecialPriceTypes.IndexOf(response.PriceTypeText);
ControlUIElementVisibility();
if (response.XForPrice.Columns > 0)
{
MapPriceTypeModelWithReponse(response.XForPrice);
}
else if (response.IncrementalPrice.Columns > 0)
{
MapPriceTypeModelWithReponse(response.IncrementalPrice);
}
else if (response.FirstUnitPrice.Columns > 0)
{
MapPriceTypeModelWithReponse(response.FirstUnitPrice);
}
else if (response.SalePrice.Columns > 0)
{
MapPriceTypeModelWithReponse(response.SalePrice);
}
else
{
ResetPriceTypeList();
}
if (response.IsPerDollarChecked)
{
SelectedPriceUnitIndex = PriceUnits.IndexOf("$");
}
else if (response.IsPerPercentageChecked)
{
SelectedPriceUnitIndex = PriceUnits.IndexOf("%");
}
}
}
private void button4_Click(object sender, EventArgs e)
{
SqlConnection Connection = new SqlConnection(Con.ConnectionString());
if (Cat.SelectedIndex == -1)
{
toolStripStatusLabel1.Text = "Please Select a Catagory";
Cat.Focus();
}
else if (SubCat.SelectedIndex == -1)
{
toolStripStatusLabel1.Text = "Please Select Sub Catagory ";
SubCat.Focus();
}
else if (ItemNo.Text.ToString().Trim(' ') == "")
{
toolStripStatusLabel1.Text = "Please Enter Item No";
ItemNo.Text = "";
ItemNo.Focus();
}
else if (Units.Text.ToString().Trim(' ') == "")
{
toolStripStatusLabel1.Text = "Please Enter Units";
Units.Text = "";
Units.Focus();
}
else if (StockPrice.Text.ToString().Trim(' ') == "")
{
toolStripStatusLabel1.Text = "Please Enter Stock Price";
StockPrice.Text = "";
StockPrice.Focus();
}
else if (SalesPrice.Text.ToString().Trim(' ') == "")
{
toolStripStatusLabel1.Text = "Please Enter Sales Price";
SalesPrice.Text = "";
SalesPrice.Focus();
}
else if (UnitsInStock.Text.ToString().Trim(' ') == "")
{
toolStripStatusLabel1.Text = "Please Enter how many Units have in Stock";
UnitsInStock.Text = "";
UnitsInStock.Focus();
}
else if (ROL.Text.ToString().Trim(' ') == "")
{
toolStripStatusLabel1.Text = "Please Enter ReOrder Level";
ROL.Text = "";
ROL.Focus();
}
else if (Convert.ToDecimal(Profit.Text) < 0)
{
MessageBox.Show("Your Stock price larger than Sales Price" + Environment.NewLine + "Enter Correct Value or Change the Discount Rate", "Data Entry Problem", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);;
SalesPrice.Focus();
}
else
{
try
{
Connection.Open();
if (button4.Text.ToString() == "Save")
{
SqlCommand INSERT = new SqlCommand("INSERT INTO ItemList(ItemNo,Descriptions,Units,Catagory,SubCatagory,StockPrice,SalesPrice,Discount,UnitsInStock,ROL) VALUES(@ItemNo,@Descriptions,@Units,@Catagory,@SubCatagory,@StockPrice,@SalesPrice,@Discount,@UnitsInStock,@ROL)", Connection);
INSERT.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
INSERT.Parameters.AddWithValue("@Descriptions", Des.Text.ToString());
INSERT.Parameters.AddWithValue("@Units", Units.Text.ToString());
INSERT.Parameters.AddWithValue("@Catagory", Cat.SelectedItem.ToString());
INSERT.Parameters.AddWithValue("@SubCatagory", SubCat.SelectedItem.ToString());
INSERT.Parameters.AddWithValue("@StockPrice", Convert.ToDecimal(StockPrice.Text));
INSERT.Parameters.AddWithValue("@SalesPrice", Convert.ToDecimal(SalesPrice.Text));
INSERT.Parameters.AddWithValue("@Discount", Convert.ToInt16(Discount.Text));
INSERT.Parameters.AddWithValue("@UnitsInStock", Convert.ToInt64(UnitsInStock.Text));
INSERT.Parameters.AddWithValue("@ROL", Convert.ToInt32(ROL.Text));
if (INSERT.ExecuteNonQuery() == 1)
{
toolStripStatusLabel1.Text = "Sucessfully Saved";
ItemNo2.Items.Add(ItemNo.Text.ToString());
ItemNo.Text = "";
Des.Text = "";
Units.Text = "";
StockPrice.Clear();
SalesPrice.Text = "";
Discount.Text = "";
UnitsInStock.Text = "";
ROL.Text = "";
Profit.Text = "0.0";
ItemNo.Focus();
}
else
{
toolStripStatusLabel1.Text = "Error Item Not Saved";
}
}
else
{
SqlCommand UPDES = new SqlCommand("UPDATE ItemList SET Descriptions=@Descriptions WHERE ItemNo=@ItemNo", Connection);
UPDES.Parameters.AddWithValue("@Descriptions", Des.Text.ToString());
UPDES.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPUNIT = new SqlCommand("UPDATE ItemList SET Units=@Units WHERE ItemNo=@ItemNo", Connection);
UPUNIT.Parameters.AddWithValue("@Units", Units.Text.ToString());
UPUNIT.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPCAT = new SqlCommand("UPDATE ItemList SET Catagory=@Catagory WHERE ItemNo=@ItemNo", Connection);
UPCAT.Parameters.AddWithValue("@Catagory", Cat.SelectedItem.ToString());
UPCAT.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPSUBCAT = new SqlCommand("UPDATE ItemList SET SubCatagory=@SubCatagory WHERE ItemNo=@ItemNo", Connection);
UPSUBCAT.Parameters.AddWithValue("@SubCatagory", SubCat.SelectedItem.ToString());
UPSUBCAT.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPSTOCKPRICE = new SqlCommand("UPDATE ItemList SET StockPrice=@StockPrice WHERE ItemNo=@ItemNo", Connection);
UPSTOCKPRICE.Parameters.AddWithValue("@StockPrice", Convert.ToDecimal(StockPrice.Text));
UPSTOCKPRICE.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPSALESPRICE = new SqlCommand("UPDATE ItemList SET SalesPrice=@SalesPrice WHERE ItemNo=@ItemNo", Connection);
UPSALESPRICE.Parameters.AddWithValue("@SalesPrice", Convert.ToDecimal(SalesPrice.Text));
UPSALESPRICE.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPDISCOUNT = new SqlCommand("UPDATE ItemList SET Discount=@Discount WHERE ItemNo=@ItemNo", Connection);
UPDISCOUNT.Parameters.AddWithValue("@Discount", Convert.ToInt16(Discount.Text));
UPDISCOUNT.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPUNITSINSTOCK = new SqlCommand("UPDATE ItemList SET UnitsInStock=@UnitsInStock WHERE ItemNo=@ItemNo", Connection);
UPUNITSINSTOCK.Parameters.AddWithValue("@UnitsInStock", Convert.ToInt64(UnitsInStock.Text));
UPUNITSINSTOCK.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
SqlCommand UPROL = new SqlCommand("UPDATE ItemList SET ROL=@ROL WHERE ItemNo=@ItemNo", Connection);
UPROL.Parameters.AddWithValue("@ROL", Convert.ToInt32(ROL.Text));
UPROL.Parameters.AddWithValue("@ItemNo", ItemNo.Text.ToString());
int a, b, c, d, ei, f, g, h, i;
a = UPDES.ExecuteNonQuery();
b = UPUNIT.ExecuteNonQuery();
c = UPSTOCKPRICE.ExecuteNonQuery();
d = UPSALESPRICE.ExecuteNonQuery();
ei = UPDISCOUNT.ExecuteNonQuery();
f = UPUNITSINSTOCK.ExecuteNonQuery();
g = UPROL.ExecuteNonQuery();
h = UPCAT.ExecuteNonQuery();
i = UPSUBCAT.ExecuteNonQuery();
if ((a == 1) && (b == 1) && (c == 1) && (d == 1) && (ei == 1) && (f == 1) && (g == 1) && (h == 1) && (i == 1))
{
toolStripStatusLabel1.Text = "Update Sucessfully";
ItemNo.Text = "";
Des.Text = "";
Units.Text = "";
StockPrice.Text = "";
SalesPrice.Text = "";
Discount.SelectedIndex = 0;
UnitsInStock.Text = "";
ROL.Text = "";
Profit.Text = "0.0";
Cat.Focus();
}
else
{
toolStripStatusLabel1.Text = "Error, Item Not Updated";
}
}
Connection.Close();
}
catch
{
}
}
}
public void UpdateStockPrice(StockPrice value)
{
stockRepo.UpdateStockPrice(value);
}
public void AddNewStockData(StockPrice stockPrice)
{
context.Add(stockPrice);
}
private async void Search_Click(object sender, RoutedEventArgs e)
{
#region Before loading stock data
var watch = new Stopwatch();
watch.Start();
StockProgress.Visibility = Visibility.Visible;
StockProgress.IsIndeterminate = true;
#endregion
var loadLinesTask = Task.Run(() =>
{
var lines = File.ReadAllLines(@"C:\GIT\Erbium\Data\StockPrices_Small.csv");
return(lines);
});
var processStocksTask = loadLinesTask.ContinueWith(t =>
{
var lines = t.Result;
var data = new List <StockPrice>();
foreach (var line in lines.Skip(1))
{
var segments = line.Split(',');
for (var i = 0; i < segments.Length; i++)
{
segments[i] = segments[i].Trim('\'', '"');
}
var price = new StockPrice
{
Ticker = segments[0],
TradeDate = DateTime.ParseExact(segments[1], "M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture),
Volume = Convert.ToInt32(segments[6], CultureInfo.InvariantCulture),
Change = Convert.ToDecimal(segments[7], CultureInfo.InvariantCulture),
ChangePercent = Convert.ToDecimal(segments[8], CultureInfo.InvariantCulture),
};
data.Add(price);
}
try
{
// Dispatcher enables us to call UI Controls from Main UI Thread without Exception.
Dispatcher.Invoke(() =>
{
Stocks.ItemsSource = data.Where(p => p.Ticker == Ticker.Text);
});
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}, TaskContinuationOptions.OnlyOnRanToCompletion);
loadLinesTask.ContinueWith(t =>
{
Dispatcher.Invoke(() =>
{
Notes.Text = t.Exception.InnerException.Message;
});
}, TaskContinuationOptions.OnlyOnFaulted);
processStocksTask.ContinueWith(_ =>
{
#region After stock data is loaded
Dispatcher.Invoke(() =>
{
StocksStatus.Text = $"Loaded stocks for {Ticker.Text} in {watch.ElapsedMilliseconds}ms";
StockProgress.Visibility = Visibility.Hidden;
});
#endregion
});
}
public IHttpActionResult GetPriceDay(string id1)
{
StockPrice sValue = StockPrice.ReadLastData();
return(Ok(sValue));
}
public async Task <IActionResult> DailyUploadNonAjax(UploadViewModel model)
{
//var files = Request.Form.Files;
//if (files == null)
// Json("E-" + "File not uploaded");
//var date = Request.Form["date"];
DateTime currentDate = Convert.ToDateTime(model.date);
//long size = files.Sum(f => f.Length);
//long size = files.Length;
int count = 0;
// full path to file in temp location
var filePath = Path.GetTempFileName();
//var uploads = Path.Combine(_environment.WebRootPath, "uploads");
//foreach (var formFile in model.files)
var formFile = model.files;
{
if (formFile.Length > 0)
{
//using (var fileStream = new FileStream(Path.Combine(uploads, file.FileName), FileMode.Create))
using (var stream = new FileStream(filePath, FileMode.Create))
{
await formFile.CopyToAsync(stream);
}
var stream2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
try
{
using (var streamReader = new StreamReader(stream2, Encoding.UTF8))
{
string line = "";
while ((line = streamReader.ReadLine()) != null)
{
// process the line
//Stock Code, Stock Name, High, Low, Close, Change, Volume, Value, Frequency
string[] result = line.Split('|');
if (result[0].ToUpper() != "STOCK CODE")
{
count++;
if (!_context.Stock.Any(m => m.StockID == result[0]))
{
_context.Stock.Add(new Stock()
{
StockID = result[0], Name = result[1]
});
await _context.SaveChangesAsync();
}
if (_context.StockPrice.Any(m => m.StockID == result[0] && m.Date == currentDate))
{
var stock = await _context.StockPrice.SingleOrDefaultAsync(m => m.StockID == result[0] && m.Date == currentDate);
stock.High = Convert.ToDecimal(result[3]);
stock.Low = Convert.ToDecimal(result[4]);
stock.Close = Convert.ToDecimal(result[5]);
//stock.Open = stock.Close + Convert.ToDecimal(result[2]);
stock.Open = Convert.ToDecimal(result[2]);
stock.Volume = (long)Convert.ToDecimal(result[6]);
stock.Frequency = (long)Convert.ToDecimal(result[7]);
await CalculateIndicator2(result[0].ToUpper(), stock);
await _context.SaveChangesAsync();
}
else
{
StockPrice stock = new StockPrice();
stock.StockID = result[0];
stock.Date = currentDate;
stock.High = Convert.ToDecimal(result[3]);
stock.Low = Convert.ToDecimal(result[4]);
stock.Close = Convert.ToDecimal(result[5]);
//stock.Open = stock.Close + Convert.ToDecimal(result[2]);
stock.Open = Convert.ToDecimal(result[2]);
stock.Volume = (long)Convert.ToDecimal(result[6]);
stock.Frequency = (long)Convert.ToDecimal(result[7]);
//_context.StockPrice.Add(stock);
await CalculateIndicator2(result[0].ToUpper(), stock);
_context.StockPrice.Add(stock);
await _context.SaveChangesAsync();
//*Refresh 1-3 Days Previous Prices
//*Get 5 days latest data, refresh only 3 days ago data
//var prevPrices = (_context.StockPrice.Where(m => m.StockID == result[0]).OrderByDescending(m=>m.Date).Take(5)).OrderBy(m=>m.Date);
//*If there is less than 5 data, then skip
//if(prevPrices.Count() == 5)
//{
// var counter = 0;
// foreach (var price in prevPrices)
// {
// if (counter == 1)
// {
// price.Support = Support(1, prevPrices.ToArray());
// price.Resistance = Resistance(1, prevPrices.ToArray());
// await _context.SaveChangesAsync();
// }
// counter++;
// }
//}
}
}
}
//_context.SaveChangesAsync();
}
}
catch (Exception ex)
{
return(Json("E-" + ex.InnerException));
}
}
}
//var t = await CalculateIndicator();
// process uploaded files
// Don't rely on or trust the FileName property without validation.
//return Content("Success");
//return Ok(new { count = files.Count, size, filePath });
return(Json("I-" + count.ToString() + " stock(s) have been updated successfuly"));
}
internal static StockPrice FilerTheStockpriceFromRediff(string httpResposeMessage)
{
TimeZoneInfo INDIAN_ZONE = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");
DateTime tm = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, INDIAN_ZONE);
DateTime dt = DateTime.Today;
StockPrice sp = new StockPrice();
var configJson = @"
{
'price':'//span[2]',
'LastTradedDate':'//span[6]',
'LastTradedTime':'//span[7]'
}";
// var configJson = @"
// {
// 'title1': '//h1',
// 'title': '//script',
// 'price':'//span[2]',
// 'LastTradedDate':'//span[6]',
// 'LastTradedTime':'//span[7]',
//'body': '//div[contains(@class, \'article\')]'
// }
// ";
// var html = "<html><body><h1>Article title</h1><div class='article'>Article contents</div></body></html>";
// html = httpResposeMessage;
try
{
var config = StructuredDataConfig.ParseJsonString(configJson);
var openScraping = new StructuredDataExtractor(config);
var scrapingResults = openScraping.Extract(httpResposeMessage);
System.Diagnostics.Debug.WriteLine(JsonConvert.SerializeObject(scrapingResults, Formatting.Indented));
var thePrice = scrapingResults["price"];
var theDate = scrapingResults["LastTradedDate"];
var theTime = scrapingResults["LastTradedTime"];
sp.Price = double.Parse(thePrice.ToString().Trim());
//DateTime dt = DateTime.ParseExact(theDate.ToString().Trim(), "dd MMM", CultureInfo.InvariantCulture);
//DateTime tm = DateTime.ParseExact(theTime.ToString().Trim(), "HH:mm:ss", CultureInfo.InvariantCulture);
//** precaution in case missing date & time**//
if (!string.IsNullOrEmpty(theDate.ToString()))
{
if (theDate.ToString().IsDateType())
{
// dt = (DateTime)Convert.ChangeType(theDate, typeof(DateTime));
dt = DateTime.ParseExact(theDate.ToString().Trim(), "dd MMM", CultureInfo.InvariantCulture);
}
if (theTime.ToString().IsDateType())
{
tm = (DateTime)Convert.ChangeType(theTime, typeof(DateTime));
}
}
else
{
var DataNTime = theTime.ToString().Split(',', StringSplitOptions.RemoveEmptyEntries);
if (DataNTime.Length == 2) //has date and time
{
if (DataNTime[0].IsDateType())
{
//dt = (DateTime)Convert.ChangeType(DataNTime[0], typeof(DateTime));
dt = DateTime.ParseExact(DataNTime[0], "dd MMM", CultureInfo.InvariantCulture);
}
if (DataNTime[1].ToString().IsDateType())
{
tm = (DateTime)Convert.ChangeType(DataNTime[1], typeof(DateTime));
}
}
else //has time only
{
if (DataNTime[0].ToString().IsDateType())
{
tm = (DateTime)Convert.ChangeType(DataNTime[0], typeof(DateTime));
}
}
}
//** adjust the date if date in missing in the downloaded time stamp**//
var currectedDate = dt.Date.Add(tm.TimeOfDay);
currectedDate = currectedDate > TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, INDIAN_ZONE) ?
currectedDate.AddYears(-1) : currectedDate;
sp.ValueOn = currectedDate;
}
catch (Exception ex)
{
throw ex;
}
return(sp);
}
//StockPriceRepository stockRepo = new StockPriceRepository();
public void AddStockPrice(StockPrice value)
{
stockRepo.AddStockPrice(value);
}
public StockPricePrediction Prediction([FromBody] StockPrice input)
{
return(_predictor.Predict(input));
}
internal static StockPrice FilerTheStockpriceFromYahoo(string httpResposeMessage)
{
StockPrice sp = new StockPrice();
TimeZoneInfo INDIAN_ZONE = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");
DateTime tm = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, INDIAN_ZONE);
DateTime dt = DateTime.Today;
var configJson = @"
{
'price':'//div[1]/div/div/div[1]/div/div[2]/div/div/div[4]/div/div/div/div[3]/div/div/span[1]',
'Closing':'//div[1]/div/div/div[1]/div/div[2]/div/div/div[4]/div/div/div/div[3]/div/div/div/span',
'DK':'//span[7]'
}";
try
{
var config = StructuredDataConfig.ParseJsonString(configJson);
var openScraping = new StructuredDataExtractor(config);
var scrapingResults = openScraping.Extract(httpResposeMessage);
System.Diagnostics.Debug.WriteLine(JsonConvert.SerializeObject(scrapingResults, Formatting.Indented));
//Closing >> At close: May 24 3:29PM IST
//Opening >> As of 9:23AM IST. Market open.
//As of May 27 9:30AM IST. Market open.
//As of May 27 9:26AM IST. Market open.
//As of May 24 3:52PM IST. Market open.
var thePrice = scrapingResults["price"];
var splits = scrapingResults["Closing"].ToString().Trim().Split(' ', StringSplitOptions.RemoveEmptyEntries);
var index = Array.FindIndex(splits, x => x.ToUpper().TrimEnd(new char[] { '.', ',' }) == "IST");
string sDate = string.Empty, sTime = string.Empty;
if (index > -1)
{
if (index - 1 > -1)
{
sTime = $"{splits[index - 1]}";
}
if (index - 3 > -1)
{
sDate = $"{splits[index - 2]} {splits[index - 3]}";
}
}
//** precaution in case missing date & time**//
if (sDate.IsDateType())
{
dt = (DateTime)Convert.ChangeType(sDate, typeof(DateTime));
}
if (sTime.IsDateType())
{
tm = (DateTime)Convert.ChangeType(sTime, typeof(DateTime));
}
//** adjust the date if date in missing in the downloaded time stamp**//
var currectedDate = dt.Date.Add(tm.TimeOfDay);
currectedDate = currectedDate > TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, INDIAN_ZONE) ?
currectedDate.AddDays(-1) : currectedDate;
//** Convert the downloaded time into last business day**//
if (currectedDate.IsWeekend())
{
currectedDate = currectedDate.PreviousWorkDay();
}
sp.Price = double.Parse(thePrice.ToString().Trim());
sp.ValueOn = currectedDate;
}
catch (Exception ex)
{
throw ex;
}
return(sp);
}
private string ToString(StockPrice stockPrice)
{
return($"({stockPrice.DateTime.Day}, {stockPrice.Price})");
}
public async Task CanIncludeTypedTimeSeries_Rollup()
{
using (var store = GetDocumentStore())
{
var raw = new RawTimeSeriesPolicy(TimeSpan.FromHours(24));
var p1 = new TimeSeriesPolicy("By6Hours", TimeSpan.FromHours(6), raw.RetentionTime * 4);
var p2 = new TimeSeriesPolicy("By1Day", TimeSpan.FromDays(1), raw.RetentionTime * 5);
var p3 = new TimeSeriesPolicy("By30Minutes", TimeSpan.FromMinutes(30), raw.RetentionTime * 2);
var p4 = new TimeSeriesPolicy("By1Hour", TimeSpan.FromMinutes(60), raw.RetentionTime * 3);
var config = new TimeSeriesConfiguration
{
Collections = new Dictionary <string, TimeSeriesCollectionConfiguration>
{
["Users"] = new TimeSeriesCollectionConfiguration
{
RawPolicy = raw,
Policies = new List <TimeSeriesPolicy>
{
p1, p2, p3, p4
}
},
},
PolicyCheckFrequency = TimeSpan.FromSeconds(1)
};
await store.Maintenance.SendAsync(new ConfigureTimeSeriesOperation(config));
await store.TimeSeries.RegisterAsync <User, StockPrice>();
var database = await Databases.GetDocumentDatabaseInstanceFor(store);
var now = DateTime.UtcNow;
var nowMinutes = now.Minute;
now = now.AddMinutes(-nowMinutes);
database.Time.UtcDateTime = () => DateTime.UtcNow.AddMinutes(-nowMinutes);
var baseline = now.AddDays(-12);
var total = TimeSpan.FromDays(12).TotalMinutes;
using (var session = store.OpenSession())
{
session.Store(new User {
Name = "Karmel"
}, "users/karmel");
var ts = session.TimeSeriesFor <StockPrice>("users/karmel");
var entry = new StockPrice();
for (int i = 0; i <= total; i++)
{
entry.Open = i;
entry.Close = i + 100_000;
entry.High = i + 200_000;
entry.Low = i + 300_000;
entry.Volume = i + 400_000;
ts.Append(baseline.AddMinutes(i), entry, "watches/fitbit");
}
session.SaveChanges();
}
await database.TimeSeriesPolicyRunner.RunRollups();
await database.TimeSeriesPolicyRunner.DoRetention();
await TimeSeries.VerifyPolicyExecutionAsync(store, config.Collections["Users"], 12, rawName : "StockPrices");
using (var session = store.OpenSession())
{
var user = session.Query <User>()
.Include(x => x.IncludeTimeSeries($"StockPrices@{p1.Name}"))
.First();
// should not go to server
var ts = session.TimeSeriesRollupFor <StockPrice>(user.Id, p1.Name);
var res = ts.Get().ToList();
Assert.Equal(16, res.Count);
Assert.Equal(1, session.Advanced.NumberOfRequests);
}
}
}
ObservableCollection <Series> GetSeriesCollection()
{
// [ open, high, low, close ]
var data = new double[][] {
new [] { 15.99, 16.07, 15.80, 15.94 },
new [] { 15.93, 16.03, 15.89, 15.97 },
new [] { 15.97, 16.43, 15.94, 16.40 },
new [] { 16.36, 16.52, 16.34, 16.46 },
new [] { 16.44, 16.47, 16.23, 16.38 },
new [] { 16.38, 17.08, 16.37, 17.02 },
new [] { 17.01, 17.32, 16.93, 17.27 },
new [] { 17.24, 17.36, 17.14, 17.32 },
new [] { 17.28, 17.44, 17.08, 17.41 },
new [] { 17.40, 17.47, 17.21, 17.38 },
new [] { 17.34, 17.59, 17.32, 17.50 },
new [] { 17.51, 17.85, 17.14, 17.15 },
new [] { 17.16, 17.48, 17.16, 17.45 },
new [] { 17.41, 17.62, 17.27, 17.47 },
new [] { 17.46, 17.56, 17.16, 17.27 },
new [] { 17.22, 17.32, 17.10, 17.24 },
new [] { 17.15, 17.47, 17.14, 17.28 },
new [] { 17.03, 18.30, 17.02, 17.74 },
new [] { 17.72, 17.90, 17.61, 17.72 },
new [] { 17.71, 17.83, 17.51, 17.75 },
new [] { 17.74, 18.46, 17.74, 18.26 },
new [] { 18.21, 18.80, 18.17, 18.69 },
new [] { 18.70, 19.88, 18.65, 19.68 },
new [] { 19.72, 21.12, 19.66, 20.40 },
new [] { 20.29, 20.44, 19.52, 19.92 },
new [] { 19.87, 20.49, 19.84, 20.07 },
new [] { 20.06, 20.25, 19.45, 19.66 },
new [] { 19.65, 20.20, 19.17, 20.19 },
new [] { 20.30, 20.66, 20.09, 20.27 },
new [] { 20.26, 20.51, 19.95, 20.13 },
new [] { 20.10, 20.46, 19.88, 20.32 },
new [] { 20.31, 20.54, 19.99, 20.28 },
new [] { 20.28, 20.32, 19.98, 20.07 },
new [] { 20.11, 20.22, 19.69, 20.01 },
new [] { 19.98, 20.05, 19.79, 19.89 },
new [] { 19.87, 19.97, 19.31, 19.39 },
new [] { 19.36, 19.86, 19.20, 19.76 },
new [] { 19.76, 19.93, 19.55, 19.63 },
new [] { 19.63, 19.70, 19.29, 19.55 },
new [] { 19.48, 19.73, 19.42, 19.61 },
new [] { 19.57, 20.39, 19.51, 20.33 },
new [] { 20.32, 20.50, 20.09, 20.13 },
new [] { 20.13, 20.39, 19.95, 20.33 },
new [] { 20.30, 20.63, 20.28, 20.43 },
new [] { 20.44, 20.77, 20.32, 20.58 },
new [] { 20.59, 20.71, 20.36, 20.38 },
new [] { 20.37, 20.50, 20.04, 20.34 },
new [] { 20.33, 20.42, 19.68, 19.69 },
new [] { 19.49, 19.85, 19.48, 19.72 },
new [] { 19.70, 19.86, 19.57, 19.83 },
new [] { 19.83, 20.48, 19.83, 20.11 },
new [] { 20.08, 20.31, 19.87, 19.95 },
new [] { 19.95, 20.21, 19.64, 19.69 },
new [] { 19.74, 20.03, 19.69, 19.79 },
new [] { 19.80, 20.10, 19.70, 19.78 },
new [] { 19.72, 19.88, 19.35, 19.66 },
new [] { 19.63, 19.92, 19.60, 19.71 },
new [] { 19.72, 19.77, 19.22, 19.42 }
};
var dataList = new List <StockPrice>();
for (var i = 0; i < data.GetLength(0); i++)
{
var d = data[i];
var open = d[0];
var close = d[3];
var low = d[2];
var high = d[1];
var date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
var dataItem = new StockPrice(open, close, low, high, date.AddDays(i));
dataList.Add(dataItem);
}
var series = new MyStockSeries(dataList);
series.DateTimeFormat = DateTimeFormat.CustomDateTime;
series.CustomDateTimeFormat = "dd";
var result = new ObservableCollection <Series>();
result.Add(series);
return(result);
}
