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