SQLで株価の分析環境をローカルに構築する
機械学習であったり、統計の勉強をしているときに必要になるのがデータ。
アイリスだったり、ボストンの住居価格などサンプルデータとしてライブラリにデフォルトで用意されてものはあるものの、身の回りに役立つものではないので、「ふーん。」という感じで終わってしまう。
このこととは関係なしに最近株を始めたので、これを分析・予測することでもっと自分ごと化できて、学習が捗るのではないかと。
というわけで、株価を自由に扱うための分析環境をローカルに構築しました。
最終ゴールとしては、SQLで株価を自由に操作できる状況を作ることとしました。
前提条件は 日経225に登録されている銘柄, 2018年のデータ です。
株式市場はいくつもあり、データ量が非常に多くなりそうなので、日経平均で知られる日経225に登録されている企業を対象としました。 また、日経225は日本を代表する企業で構成され、一度は聞いたことがあるような銘柄が多いので、これもまた自分ごと化されやすいという理由で選びました。
そして、取得データの期間としては一旦一年分あればいいかなということで、2018年分のデータを対象とします。
今回行う手順については下記の通りです。
1. DBスキーマの設定
まずはデータを入れる箱を用意する必要があります。 企業と株価をそれぞれ保つ必要があるので下記のようにシンプルに設計しました。
WITH句を使いたいことや最近MySQLを使う機会が減ったので、DBとしてはPostgeSQLを使用します。
-- 企業テーブル
dev=> CREATE TABLE company (
code INT NOT NULL, -- コード
name VARCHAR(45) NULL, -- 社名
traiding_name VARCHAR(45) NULL, -- 銘柄名
PRIMARY KEY (code),
UNIQUE (code));
-- 株価
dev=> CREATE TABLE price (
date DATE NOT NULL, -- 日付
code INT NOT NULL, -- コード
open FLOAT NULL, -- 始値
close FLOAT NULL, -- 終値
high FLOAT NULL, -- 高値
low FLOAT NULL, -- 安値
turnover FLOAT NULL, -- 出来高
adjustment_close FLOAT NULL, -- 終値調整
PRIMARY KEY (date, code),
CONSTRAINT code
FOREIGN KEY (code)
REFERENCES company (code)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
--
2. 日経225の銘柄のスクレイピング
pythonを使ってスクレイピングしました。 こちらを参考にしました。
※スクレイピングによるアクセスでWEBサービスに影響がでると、罪に問われることがあるので気をつけましょう。
まずは元となるデータですが、日経225についてはこちらにまとめられています。
divタグを探してパースすることになるのですが、対象のdivタグにid等が埋め込まれていないので、cssで用いられてるであろうclass属性を使って、HTMLをパースします。
3. 各銘柄の2018年の株価を取得してDBに保存
各銘柄の株価の情報については、こちらの株式投資メモ・株価データベース様がまとめておられるので、こちらを利用しました。
株価のダウンロードページに進んでいくと https://kabuoji3.com/stock/file.php というエンドポイントが叩かれてCSVがダウンロードされるので、こちらのyearとcodeのパラメータを変更させながらcsvを取得し、パースしてデータを入れていきます。
上記のスクリプトを実行すれば、ローカル環境の完成です。
試しに先月の出来高の多い銘柄のTOP5を出してみます。
dev=> SELECT p.code, name, SUM(turnover) AS turnover FROM price AS p JOIN company AS c ON p.code = c.code WHERE date BETWEEN '2018-09-01' AND '2018-09-30' GROUP BY p.code, name ORDER BY turnover DESC LIMIT 5; code | name | turnover ------+------------------------------------------+------------ 8411 | （株）みずほフィナンシャルグループ | 2049223000 8306 | （株）三菱ＵＦＪフィナンシャル・グループ | 1027620200 4689 | ヤフー（株） | 960981700 8604 | 野村ホールディングス（株） | 299399400 7201 | 日産自動車（株） | 270645100 (5 rows)
みずほや三菱UFJなど銀行が株の取引額の大半を占めてるということがわかり、ちょっとびっくり。
これで分析基盤ができたので、もっと勉強して行くぞーとな。
