private async Task UpdateStockPriceTarget() { Stopwatch sw = new System.Diagnostics.Stopwatch(); this.txtUpdateStatus.Text += "株価更新開始" + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); // 情報の取得 List <Utility.ProfileEntity> listProfile = new List <Utility.ProfileEntity>(); List <Utility.StockPriceEntity> listStockPrice = new List <Utility.StockPriceEntity>(); List <Utility.StockPriceEntity> listStock = new List <Utility.StockPriceEntity>(); Utility.StockPriceEntity sp; int code; for (int line = 0; line < this.txtStockCodes.Lines.Length; line++) { if (int.TryParse(this.txtStockCodes.Lines[line], out code)) { sp = new Utility.StockPriceEntity(); sp.StockCode = code; listStock.Add(sp); } } foreach (StockPriceEntity r in listStock) { sw.Restart(); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); //listStockPrice = finance.GetStockPriceEntityList(r.StockCode,DateTime.Now.AddYears(-10).Date,DateTime.Now.Date); listStockPrice = finance.GetStockPriceEntityList(r.StockCode, DateTime.Now.AddMonths(-60).Date, DateTime.Now.Date); }); await Task.Run(() => { if (listStockPrice.Count == 0) { // 株価取得できない場合はスルー } else { using (Utility.DbUtil db = new Utility.DbUtil()) { // 削除 var query = from q in listStockPrice where q.StockCode == r.StockCode select q; db.DBUpdate("DELETE FROM stockprice WHERE StockCode = :StockCode AND StockDate BETWEEN :BeginDate AND :EndDate ", new { StockCode = r.StockCode, BeginDate = query.Min(stock => stock.StockDate), EndDate = query.Max(stock => stock.StockDate) }); // 登録 string insertSql = @"INSERT INTO stockprice ( StockCode ,CompanyName ,StockDate ,OpeningPrice ,HighPrice ,LowPrice ,ClosingPrice ,TradeVolume ,AdjustmentClosingPrice ) VALUES ( :StockCode ,:CompanyName ,:StockDate ,:OpeningPrice ,:HighPrice ,:LowPrice ,:ClosingPrice ,:TradeVolume ,:AdjustmentClosingPrice )"; db.DBInsert(insertSql, listStockPrice); } } }); sw.Stop(); this.txtUpdateStatus.Text += r.StockCode.ToString().PadLeft(4, '0') + " データ更新 " + sw.Elapsed.ToString() + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); } this.txtUpdateStatus.Text += "株価更新終了" + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); // 日経平均、ドル円 sw.Restart(); List <DollarYenEntity> listDY = new List <DollarYenEntity>(); List <NikkeiAverageEntity> listN = new List <NikkeiAverageEntity>(); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); listDY = finance.GetDollarYenEntityList(); listN = finance.GetNikkeiAverageEntityList(); }); await Task.Run(() => { if (listDY.Count == 0) { // ドル円が取得できない場合はスルー } else { using (Utility.DbUtil db = new Utility.DbUtil()) { // 削除 var query = from q in listDY select q; db.DBUpdate("DELETE FROM dollaryen WHERE ExchangeDate BETWEEN :BeginDate AND :EndDate ", new { BeginDate = query.Min(dollerYen => dollerYen.ExchangeDate), EndDate = query.Max(dollerYen => dollerYen.ExchangeDate) }); // 登録 string insertSql = @"INSERT INTO dollaryen ( ExchangeDate ,OpeningPrice ,HighPrice ,LowPrice ,ClosingPrice ) VALUES ( :ExchangeDate ,:OpeningPrice ,:HighPrice ,:LowPrice ,:ClosingPrice )"; db.DBInsert(insertSql, listDY); } } if (listN.Count == 0) { // 日経平均が取得できない場合はスルー } else { using (Utility.DbUtil db = new Utility.DbUtil()) { // 削除 var query = from q in listN select q; db.DBUpdate("DELETE FROM nikkeiaverage WHERE StockDate BETWEEN :BeginDate AND :EndDate ", new { BeginDate = query.Min(nikkei => nikkei.StockDate), EndDate = query.Max(nikkei => nikkei.StockDate) }); // 登録 string insertSql = @"INSERT INTO nikkeiaverage ( StockDate ,OpeningPrice ,HighPrice ,LowPrice ,ClosingPrice ) VALUES ( :StockDate ,:OpeningPrice ,:HighPrice ,:LowPrice ,:ClosingPrice )"; db.DBInsert(insertSql, listN); } } }); this.txtUpdateStatus.Text += "日経平均・ドル/円更新終了" + sw.Elapsed.ToString() + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); }
private async Task UpdateStockPrice(int months) { Stopwatch sw = new System.Diagnostics.Stopwatch(); this.txtUpdateStatus.Text += "株価更新開始" + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); // 情報の取得 List <Utility.ProfileEntity> listProfile = new List <Utility.ProfileEntity>(); List <Utility.StockPriceEntity> listStockPrice = new List <Utility.StockPriceEntity>(); List <Utility.StockPriceEntity> listStock = new List <Utility.StockPriceEntity>(); using (Utility.DbUtil db = new Utility.DbUtil()) { db.DBUpdate("DELETE FROM stockcode "); db.DBUpdate("INSERT INTO stockcode (StockCode) SELECT TargetStockCode FROM targetcode "); listProfile = db.DBSelect <Utility.ProfileEntity>("SELECT * FROM profile "); listStock = db.DBSelect <StockPriceEntity>(@"SELECT stockcode.StockCode ,ifnull(profile.CompanyName,'') CompanyName FROM stockcode LEFT JOIN profile ON stockcode.StockCode = profile.StockCode ORDER BY stockcode.StockCode "); } foreach (StockPriceEntity r in listStock) { sw.Restart(); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); listStockPrice = finance.GetStockPriceEntityList(r.StockCode, months); }); await Task.Run(() => { if (listStockPrice.Count == 0) { // 株価取得できない場合はスルー } else { using (Utility.DbUtil db = new Utility.DbUtil()) { // 削除 var query = from q in listStockPrice where q.StockCode == r.StockCode select q; db.DBUpdate("DELETE FROM stockprice WHERE StockCode = :StockCode AND StockDate BETWEEN :BeginDate AND :EndDate ", new { StockCode = r.StockCode, BeginDate = query.Min(stock => stock.StockDate), EndDate = query.Max(stock => stock.StockDate) }); // 登録 string insertSql = @"INSERT INTO stockprice ( StockCode ,CompanyName ,StockDate ,OpeningPrice ,HighPrice ,LowPrice ,ClosingPrice ,TradeVolume ,AdjustmentClosingPrice ) VALUES ( :StockCode ,:CompanyName ,:StockDate ,:OpeningPrice ,:HighPrice ,:LowPrice ,:ClosingPrice ,:TradeVolume ,:AdjustmentClosingPrice )"; db.DBInsert(insertSql, listStockPrice); } } }); sw.Stop(); this.txtUpdateStatus.Text += r.StockCode.ToString().PadLeft(4, '0') + r.CompanyName + " データ更新 " + sw.Elapsed.ToString() + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); } this.txtUpdateStatus.Text += "株価更新終了" + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); }
private async Task UpdateStockPriceAndProfile() { Stopwatch sw = new System.Diagnostics.Stopwatch(); this.txtUpdateStatus.Text += "企業情報取得・更新開始" + Environment.NewLine; // 情報の取得 List <Utility.ProfileEntity> listProfile = new List <Utility.ProfileEntity>(); List <Utility.StockPriceEntity> listStockPrice = new List <Utility.StockPriceEntity>(); List <Utility.TargetEntity> targetCode; using (Utility.DbUtil db = new Utility.DbUtil()) { targetCode = db.DBSelect <Utility.TargetEntity>("SELECT * FROM targetcode ORDER BY TargetStockCode "); db.DBUpdate("DELETE FROM profile "); } foreach (TargetEntity r in targetCode) { sw.Restart(); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); listProfile = finance.GetProfileEntityList(r.TargetStockCode); }); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); listStockPrice = finance.GetStockPriceEntityList(r.TargetStockCode); }); await Task.Run(() => { using (Utility.DbUtil db = new Utility.DbUtil()) { // 削除 db.DBUpdate("DELETE FROM profile WHERE StockCode = :StockCode ", new { StockCode = r.TargetStockCode }); var query = from q in listStockPrice where q.StockCode == r.TargetStockCode select q; db.DBUpdate("DELETE FROM stockprice WHERE StockCode = :StockCode AND StockDate BETWEEN :BeginDate AND :EndDate ", new { StockCode = r.TargetStockCode, BeginDate = query.Min(stock => stock.StockDate), EndDate = query.Max(stock => stock.StockDate) }); // 登録 string insertSql = @"INSERT INTO profile ( StockCode ,CompanyName ,Feature ,ConcatenationBusiness ,HeadquartersLocation ,IndustriesCategory ,FoundationDate ,MarketName ,ListedDate ,ClosingMonth ,UnitShares ,EmployeeNumberSingle ,EmployeeNumberConcatenation ,AvarageAnnualIncome ) VALUES ( :StockCode ,:CompanyName ,:Feature ,:ConcatenationBusiness ,:HeadquartersLocation ,:IndustriesCategory ,:FoundationDate ,:MarketName ,:ListedDate ,:ClosingMonth ,:UnitShares ,:EmployeeNumberSingle ,:EmployeeNumberConcatenation ,:AvarageAnnualIncome )"; db.DBInsert(insertSql, listProfile); string insertSql2 = @"INSERT INTO stockprice ( StockCode ,CompanyName ,StockDate ,OpeningPrice ,HighPrice ,LowPrice ,ClosingPrice ,TradeVolume ,AdjustmentClosingPrice ) VALUES ( :StockCode ,:CompanyName ,:StockDate ,:OpeningPrice ,:HighPrice ,:LowPrice ,:ClosingPrice ,:TradeVolume ,:AdjustmentClosingPrice )"; db.DBInsert(insertSql2, listStockPrice); } }); sw.Stop(); this.txtUpdateStatus.Text += r.TargetStockCode.ToString().PadLeft(4, '0') + " データ更新 " + sw.Elapsed.ToString() + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); } this.txtUpdateStatus.Text += "企業情報取得・更新終了" + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); }
private async Task UpdateProfile() { Stopwatch sw = new System.Diagnostics.Stopwatch(); this.txtUpdateStatus.Text += "企業情報取得・更新開始" + Environment.NewLine; // 情報の取得 List <Utility.ProfileEntity> listProfile = new List <Utility.ProfileEntity>(); List <Utility.StockPriceEntity> listStockPrice = new List <Utility.StockPriceEntity>(); List <Utility.TargetEntity> targetCode; using (Utility.DbUtil db = new Utility.DbUtil()) { targetCode = db.DBSelect <Utility.TargetEntity>("SELECT * FROM targetcode ORDER BY TargetStockCode "); db.DBUpdate("DELETE FROM profile "); } await Task.Run(() => { using (Utility.DbUtil db = new Utility.DbUtil()) { // 全削除する db.DBUpdate("DELETE FROM profile "); } }); foreach (TargetEntity r in targetCode) { sw.Restart(); await Task.Delay(1000); await Task.Run(() => { Utility.FinanceUtil finance = new Utility.FinanceUtil(); listProfile = finance.GetProfileEntityList(r.TargetStockCode); }); await Task.Run(() => { using (Utility.DbUtil db = new Utility.DbUtil()) { // 登録 string insertSql = @"INSERT INTO profile ( StockCode ,CompanyName ,Feature ,ConcatenationBusiness ,HeadquartersLocation ,IndustriesCategory ,FoundationDate ,MarketName ,ListedDate ,ClosingMonth ,UnitShares ,EmployeeNumberSingle ,EmployeeNumberConcatenation ,AvarageAnnualIncome ) VALUES ( :StockCode ,:CompanyName ,:Feature ,:ConcatenationBusiness ,:HeadquartersLocation ,:IndustriesCategory ,:FoundationDate ,:MarketName ,:ListedDate ,:ClosingMonth ,:UnitShares ,:EmployeeNumberSingle ,:EmployeeNumberConcatenation ,:AvarageAnnualIncome )"; db.DBInsert(insertSql, listProfile); } }); sw.Stop(); this.txtUpdateStatus.Text += r.TargetStockCode.ToString().PadLeft(4, '0') + " データ更新 " + sw.Elapsed.ToString() + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); } this.txtUpdateStatus.Text += "企業情報取得・更新終了" + Environment.NewLine; this.txtUpdateStatus.SelectionStart = this.txtUpdateStatus.TextLength; this.txtUpdateStatus.ScrollToCaret(); await Task.Run(() => { using (Utility.DbUtil db = new Utility.DbUtil()) { // 全削除する db.DBUpdate("DELETE FROM stockcode WHERE stockcode.StockCode NOT IN (SELECT StockCode FROM profile)"); } }); }
private void btn5UpDown_Click(object sender, EventArgs e) { List <StockPriceEntity> listDetail = new List <StockPriceEntity>(); List <StockPriceEntity> listUp = new List <StockPriceEntity>(); List <StockPriceEntity> listDown = new List <StockPriceEntity>(); List <PriceUpDownEntity> listUpDown = new List <PriceUpDownEntity>(); StockPriceEntity previousPrice = new StockPriceEntity(); using (Utility.DbUtil db = new Utility.DbUtil()) { StringBuilder sb = new StringBuilder(); sb.Length = 0; sb.AppendLine(" SELECT"); sb.AppendLine(" StockCode"); sb.AppendLine(" ,0 AS UpCnt3M "); sb.AppendLine(" ,0 AS DownCnt3M "); sb.AppendLine(" ,0 AS UpCnt2M "); sb.AppendLine(" ,0 AS DownCnt2M "); sb.AppendLine(" ,0 AS UpCnt1M "); sb.AppendLine(" ,0 AS DownCnt1M "); sb.AppendLine(" FROM"); sb.AppendLine(" stockprice "); sb.AppendLine(" GROUP BY StockCode "); listUpDown = db.DBSelect <PriceUpDownEntity>(sb.ToString()); // 一旦削除 db.DBUpdate("DELETE FROM priceupdown "); sb.Length = 0; sb.AppendLine(" SELECT "); sb.AppendLine(" stockprice.* "); sb.AppendLine(" FROM "); sb.AppendLine(" stockprice "); sb.AppendLine(" WHERE stockprice.StockDate BETWEEN :BeginDate AND :EndDate "); sb.AppendLine(" ORDER BY stockprice.StockCode,stockprice.StockDate "); listDetail = db.DBSelect <StockPriceEntity>(sb.ToString(), new { BeginDate = DateTime.Now.AddMonths(-3), EndDate = DateTime.Now.Date }); int breakStockCode = 0; if (listDetail.Count != 0) { previousPrice = listDetail[0]; } Dictionary <string, int> dic = new Dictionary <string, int>(); foreach (StockPriceEntity list in listDetail) { if (breakStockCode == list.StockCode) { // 同一証券コード if (list.ClosingPrice >= previousPrice.ClosingPrice * 1.05m) { // 5%上昇(前後データ追加) var priceup = from up in listUpDown where up.StockCode == previousPrice.StockCode select up; foreach (PriceUpDownEntity up in priceup) { up.UpCnt3M = up.UpCnt3M + 1; if (DateTime.Now.AddMonths(-2) < previousPrice.StockDate) { up.UpCnt2M = up.UpCnt2M + 1; } if (DateTime.Now.AddMonths(-1) < previousPrice.StockDate) { up.UpCnt1M = up.UpCnt1M + 1; } } } else if (list.ClosingPrice <= previousPrice.ClosingPrice * 0.95m) { // 5%下落(前後データ追加) var pricedown = from down in listUpDown where down.StockCode == previousPrice.StockCode select down; foreach (PriceUpDownEntity down in pricedown) { down.DownCnt3M = down.DownCnt3M + 1; if (DateTime.Now.AddMonths(-2) < previousPrice.StockDate) { down.DownCnt2M = down.DownCnt2M + 1; } if (DateTime.Now.AddMonths(-1) < previousPrice.StockDate) { down.DownCnt1M = down.DownCnt1M + 1; } } } } else { // 証券コードブレイク breakStockCode = list.StockCode; } // 前回株価 previousPrice = list; } this.dataGridView2.DataSource = listUpDown; //this.dataGridView3.DataSource = listDown; } }