private void ChartForm_Load(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" p.StockCode ");
sb.AppendLine(" , p.CompanyName ");
sb.AppendLine(" , p.IndustriesCategory ");
sb.AppendLine(" , p.MarketName ");
sb.AppendLine(" , p.ClosingMonth ");
sb.AppendLine(" , s.StockDate ");
sb.AppendLine(" , s.OpeningPrice ");
sb.AppendLine(" , s.LowPrice ");
sb.AppendLine(" , s.HighPrice ");
sb.AppendLine(" , s.ClosingPrice ");
sb.AppendLine(" , s.TradeVolume ");
sb.AppendLine(" FROM ");
sb.AppendLine(" stockprice s ");
sb.AppendLine(" INNER JOIN profile p ");
sb.AppendLine(" ON s.StockCode = p.StockCode ");
sb.AppendLine(" WHERE s.StockDate >= :BeginDate ");
sb.AppendLine(" ORDER BY ");
sb.AppendLine(" s.StockCode ");
sb.AppendLine(" , s.StockDate ");
_listStockData = new List <Utility.StockPriceProfile>();
using (Utility.DbUtil db = new Utility.DbUtil())
{
// データを取得してインスタンス変数に保持
_listStockData = db.DBSelect <Utility.StockPriceProfile>(sb.ToString(), new { BeginDate = DateTime.Now.AddMonths(-3) });
}
}
private List <StockPriceEntity> GetStockPriceList(int stockCode)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
return(db.DBSelect <Utility.StockPriceEntity>("SELECT * FROM stockprice WHERE StockCode = :StockCode", new { StockCode = stockCode }));
}
}
private void btnDividend_Click(object sender, EventArgs e)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// テーブルを作成
List <decimal> tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'dividend'");
if (tblLst[0] > 0)
{
db.DBExecuteSQL("DROP TABLE dividend ");
}
string sql = @"CREATE TABLE dividend
(
OrderNo NUMERIC
,StockCode NUMERIC
,Market TEXT
,CompanyName TEXT
,Dividend NUMERIC
,DividendYield NUMERIC
,DetailUrl TEXT
) ";
db.DBExecuteSQL(sql);
List <Utility.DividendEntity> list = new List <Utility.DividendEntity>();
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetDividendEntityList();
string insertSql = @"INSERT INTO dividend
(
OrderNo
,StockCode
,Market
,CompanyName
,Dividend
,DividendYield
,DetailUrl
) VALUES (
:OrderNo
,:StockCode
,:Market
,:CompanyName
,:Dividend
,:DividendYield
,:DetailUrl
)";
db.DBInsert(insertSql, list);
List <Utility.DividendEntity> dividend = db.DBSelect <Utility.DividendEntity>("SELECT * FROM dividend ");
this.dgvStockPrice.DataSource = dividend;
}
}
private async Task UpdateDividend()
{
Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
this.txtUpdateStatus.Text = "";
this.txtUpdateStatus.Text += "配当利回り取得開始" + Environment.NewLine;
// 情報の取得
List <Utility.DividendEntity> list = new List <Utility.DividendEntity>();
await Task.Run(() =>
{
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetDividendEntityList();
});
sw.Stop();
this.txtUpdateStatus.Text += "配当利回り取得終了 " + sw.Elapsed.ToString() + Environment.NewLine;
sw.Restart();
this.txtUpdateStatus.Text += "配当利回り更新開始" + Environment.NewLine;
await Task.Run(() =>
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// 削除
db.DBExecuteSQL("DELETE FROM dividend");
// 登録
string insertSql = @"INSERT INTO dividend
(
OrderNo
,StockCode
,Market
,CompanyName
,Dividend
,DividendYield
,DetailUrl
) VALUES (
:OrderNo
,:StockCode
,:Market
,:CompanyName
,:Dividend
,:DividendYield
,:DetailUrl
)";
db.DBInsert(insertSql, list);
db.DBExecuteSQL("DELETE FROM dividend WHERE StockCode NOT IN (SELECT TargetStockCode From targetcode) ");
}
});
sw.Stop();
this.txtUpdateStatus.Text += "配当利回り更新終了 " + sw.Elapsed.ToString() + Environment.NewLine;
}
private async Task UpdateStockCode()
{
Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
this.txtUpdateStatus.Text = "";
this.txtUpdateStatus.Text += "証券コード取得開始" + Environment.NewLine;
// 情報の取得
List <StockPriceEntity> list = new List <StockPriceEntity>();
await Task.Run(() =>
{
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetStockCodeList();
});
sw.Stop();
this.txtUpdateStatus.Text += "証券コード取得終了 " + sw.Elapsed.ToString() + Environment.NewLine;
sw.Restart();
this.txtUpdateStatus.Text += "証券コード更新開始" + Environment.NewLine;
await Task.Run(() =>
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// 削除
db.DBExecuteSQL("DELETE FROM stockcode");
// 登録
string insertSql = @"INSERT INTO stockcode
(
StockCode
) VALUES (
:StockCode
)";
db.DBInsert(insertSql, list);
}
});
sw.Stop();
this.txtUpdateStatus.Text += "証券コード更新終了 " + sw.Elapsed.ToString() + Environment.NewLine;
}
private void btnStockPrice_Click(object sender, EventArgs e)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// テーブルを作成
List <decimal> tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'stockprice'");
if (tblLst[0] >= 0)
{
db.DBExecuteSQL("DROP TABLE stockprice ");
}
string sql = @"create table stockprice
(
StockCode NUMERIC
,CompanyName TEXT
,StockDate TEXT
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,TradeVolume NUMERIC
,AdjustmentClosingPrice NUMERIC
) ";
db.DBExecuteSQL(sql);
List <Utility.StockPriceEntity> list = new List <Utility.StockPriceEntity>();
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetStockPriceEntityList(Convert.ToInt32(txtStockCode.Text), DateTime.Now.AddMonths(-3), DateTime.Now);
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, list);
List <Utility.StockPriceEntity> stockprice = db.DBSelect <Utility.StockPriceEntity>("SELECT * FROM stockprice ");
this.dgvStockPrice.DataSource = stockprice;
// チャート生成
CreateStockChart(stockprice);
}
}
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 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 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 void btnNumbering_Click(object sender, EventArgs e)
{
// 情報の取得
List <Utility.StockPriceEntity> listStock = new List <Utility.StockPriceEntity>();
List <Utility.NumberingPriceEntity> listNum = new List <Utility.NumberingPriceEntity>();
using (Utility.DbUtil db = new Utility.DbUtil())
{
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 ");
db.DBExecuteSQL("DELETE FROM pricenumbering");
}
StringBuilder sb = new StringBuilder();
sb.Length = 0;
sb.AppendLine(" SELECT ");
sb.AppendLine(" StockCode ");
sb.AppendLine(" ,CompanyName ");
sb.AppendLine(" ,StockDate ");
sb.AppendLine(" ,OpeningPrice ");
sb.AppendLine(" ,HighPrice ");
sb.AppendLine(" ,LowPrice ");
sb.AppendLine(" ,ClosingPrice ");
sb.AppendLine(" ,TradeVolume ");
sb.AppendLine(" ,AdjustmentClosingPrice ");
sb.AppendLine(" ,(SELECT COUNT(*) + 1 ");
sb.AppendLine(" FROM stockprice ");
sb.AppendLine(" WHERE StockCode = :StockCode ");
sb.AppendLine(" AND StockDate >= :StockDate ");
sb.AppendLine(" AND (StockDate < t.StockDate OR (StockDate = t.StockDate AND StockCode < t.StockCode )) ");
sb.AppendLine(" ) AS RowNum ");
sb.AppendLine(" FROM stockprice t ");
sb.AppendLine("WHERE t.StockCode = :StockCode ");
sb.AppendLine(" AND t.StockDate >= :StockDate ");
foreach (StockPriceEntity r in listStock)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
listNum = db.DBSelect <NumberingPriceEntity>(sb.ToString(), new { StockCode = r.StockCode, StockDate = DateTime.Now.AddMonths(-3).Date });
string insertSql = @"INSERT INTO pricenumbering
(
StockCode
,CompanyName
,StockDate
,RowNum
,OpeningPrice
,HighPrice
,LowPrice
,ClosingPrice
,TradeVolume
,AdjustmentClosingPrice
) VALUES (
:StockCode
,:CompanyName
,:StockDate
,:RowNum
,:OpeningPrice
,:HighPrice
,:LowPrice
,:ClosingPrice
,:TradeVolume
,:AdjustmentClosingPrice
)";
if (listNum.Count() > 0)
{
db.DBInsert(insertSql, listNum);
}
}
}
}
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;
}
}
private void ListForm_Load(object sender, EventArgs e)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" WITH PRICE AS ( ");
sb.AppendLine(" SELECT stockprice.* ");
sb.AppendLine(" FROM ");
sb.AppendLine(" stockprice ");
sb.AppendLine(" INNER JOIN ( ");
sb.AppendLine(" SELECT ");
sb.AppendLine(" StockCode ");
sb.AppendLine(" ,MAX(StockDate) StockDate ");
sb.AppendLine(" FROM ");
sb.AppendLine(" stockprice ");
sb.AppendLine(" GROUP BY ");
sb.AppendLine(" StockCode ");
sb.AppendLine(" ) stockmax ");
sb.AppendLine(" ON stockmax.StockCode = stockprice.StockCode ");
sb.AppendLine(" AND stockmax.StockDate = stockprice.StockDate ");
sb.AppendLine(" ) ");
sb.AppendLine(" SELECT ");
sb.AppendLine(" profile.StockCode ");
sb.AppendLine(" ,profile.CompanyName ");
sb.AppendLine(" ,profile.Feature ");
sb.AppendLine(" ,profile.ConcatenationBusiness ");
sb.AppendLine(" ,profile.HeadquartersLocation ");
sb.AppendLine(" ,profile.IndustriesCategory ");
sb.AppendLine(" ,profile.FoundationDate ");
sb.AppendLine(" ,profile.MarketName ");
sb.AppendLine(" ,profile.ListedDate ");
sb.AppendLine(" ,profile.ClosingMonth ");
sb.AppendLine(" ,profile.UnitShares ");
sb.AppendLine(" ,profile.EmployeeNumberSingle ");
sb.AppendLine(" ,profile.EmployeeNumberConcatenation ");
sb.AppendLine(" ,profile.AvarageAnnualIncome ");
sb.AppendLine(" ,dividend.Dividend ");
sb.AppendLine(" ,round(cast(dividend.Dividend as real) / PRICE.ClosingPrice * 100,2) DividendYield ");
sb.AppendLine(" ,PRICE.ClosingPrice ");
sb.AppendLine(" ,PRICE.TradeVolume ");
sb.AppendLine(" FROM ");
sb.AppendLine(" profile ");
sb.AppendLine(" LEFT JOIN PRICE ");
sb.AppendLine(" ON PRICE.StockCode = profile.StockCode ");
sb.AppendLine(" LEFT JOIN dividend ");
sb.AppendLine(" ON dividend.StockCode = profile.StockCode ");
sb.AppendLine(" ORDER BY profile.StockCode ");
_ListDetail = db.DBSelect <ListEntity>(sb.ToString());
}
this.listEntityBindingSource.DataSource = _ListDetail;
var query = (from q in _ListDetail
orderby q.IndustriesCategory
select q.IndustriesCategory).Distinct();
foreach (string s in query)
{
this.cboCategory.Items.Add(s);
}
var q2 = (from q in _ListDetail
orderby q.MarketName
select q.MarketName).Distinct();
foreach (string s in q2)
{
this.cboMarketName.Items.Add(s);
}
}
private void btnUpDown_Click(object sender, EventArgs e)
{
List <ListEntity> listDetail = new List <ListEntity>();
using (Utility.DbUtil db = new Utility.DbUtil())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" profile.StockCode ");
sb.AppendLine(" ,profile.CompanyName ");
sb.AppendLine(" ,profile.Feature ");
sb.AppendLine(" ,profile.ConcatenationBusiness ");
sb.AppendLine(" ,profile.HeadquartersLocation ");
sb.AppendLine(" ,profile.IndustriesCategory ");
sb.AppendLine(" ,profile.FoundationDate ");
sb.AppendLine(" ,profile.MarketName ");
sb.AppendLine(" ,profile.ListedDate ");
sb.AppendLine(" ,profile.ClosingMonth ");
sb.AppendLine(" ,profile.UnitShares ");
sb.AppendLine(" ,profile.EmployeeNumberSingle ");
sb.AppendLine(" ,profile.EmployeeNumberConcatenation ");
sb.AppendLine(" ,profile.AvarageAnnualIncome ");
sb.AppendLine(" ,dividend.Dividend ");
sb.AppendLine(" ,round(cast(dividend.Dividend as real) / PRICE.AdjustmentClosingPrice * 100,2) DividendYield ");
sb.AppendLine(" ,PRICE.AdjustmentClosingPrice AS ClosingPrice ");
sb.AppendLine(" ,PRICE.TradeVolume ");
sb.AppendLine(" FROM ");
sb.AppendLine(" profile ");
sb.AppendLine(" LEFT JOIN stockprice PRICE ");
sb.AppendLine(" ON PRICE.StockCode = profile.StockCode ");
sb.AppendLine(" LEFT JOIN dividend ");
sb.AppendLine(" ON dividend.StockCode = profile.StockCode ");
sb.AppendLine(" WHERE PRICE.StockDate BETWEEN :BeginDate AND :EndDate ORDER BY profile.StockCode,PRICE.StockDate ");
sb.AppendLine(" ORDER BY profile.StockCode ");
listDetail = db.DBSelect <ListEntity>(sb.ToString(), new { BeginDate = DateTime.Now.AddMonths(-3), EndDate = DateTime.Now.Date });
int breakStockCode = 0;
decimal previousPrice = 0;
if (listDetail.Count != 0)
{
breakStockCode = listDetail[0].StockCode;
previousPrice = listDetail[0].ClosingPrice;
}
Dictionary <string, int> dic = new Dictionary <string, int>();
foreach (ListEntity list in listDetail)
{
if (breakStockCode == list.StockCode)
{
// 同一証券コード
if (list.ClosingPrice >= previousPrice * 1.05m)
{
// 5%上昇
if (dic.ContainsKey("5%上昇" + list.MarketName))
{
dic["5%上昇" + list.MarketName] = dic["5%上昇" + list.MarketName] + 1;
}
else
{
dic.Add("5%上昇" + list.MarketName, 1);
}
}
else if (list.ClosingPrice <= previousPrice * 0.95m)
{
// 5%下落
if (dic.ContainsKey("5%下落" + list.MarketName))
{
dic["5%下落" + list.MarketName] = dic["5%下落" + list.MarketName] + 1;
}
else
{
dic.Add("5%下落" + list.MarketName, 1);
}
}
}
else
{
// 証券コードブレイク
breakStockCode = list.StockCode;
}
// 前回株価
previousPrice = list.ClosingPrice;
}
this.dataGridView2.DataSource = dic;
}
}