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