RPI家庭内my株価データベース分割修正についてのメモ

data-get.comより株価データを買って、家庭内サーバに株価データベースを構築して遊んでいる。
最低限、4本値と、会社分割情報だけ入れられたらいいかなあと思っている。

mysql> describe tbl_dg_devide;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s     | char(4) | NO   | PRI |         |       |
| d     | char(8) | NO   | PRI |         |       |
| f     | float   | YES  |     | NULL    |       |
| t     | float   | YES  |     | NULL    |       |
| mul   | float   | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
5 rows in set (0.01 sec)

分割情報を保持するテーブルは↑のような感じ s はセキュリティコード（証券コード）
dは日付文字列にした。データタイム型にするとload data する時に面倒なのでやってない。
f は分割前～に当たり t ～個の株式を割り当てるみたいな。
f と t はあまり必要ない。

mul が重要で、分割修正乗数というべきもの。

とりあえず、Data get の devide.csv はそのままload data できる構造にした。

ところで、data-getの株価は分割情報は別テーブルだ。
これはこれで便利なのだけど。
普通バックテストや過去データの検証に必要なのは分割を調整した後の4本値だ。
だから

ふつうの4本値
＋
分割情報から

⇒ 分割を調整した後の4本値を記録した時系列データを

作らないといけない。

で、やり方にはいろいろあるが、SQLでできるのかどうかを考えてみる。
12.19.1 GROUP BY (集約) 関数
をみてうーんと考えてみたけどもどうにもできそうにない。

できない原因は集約関数にEXCELでいうPRODUCT的な動きをする関数や
Perlでいうmap 関数のようなものが無いからだと思う。
また集約関数を自作できそうな情報もなかったので、これはたぶん無理か難しいんだろうな。

でも、SQLで修正乗数のPRODUCTを返す数式なら返す事ができるなあと気が付いた。

まずソフトバンクの分割を調べてみる

mysql> select * from tbl_dg_devide where s="9984";
+------+----------+------+------+---------+
| s    | d        | f    | t    | mul     |
+------+----------+------+------+---------+
| 9984 | 19960326 |    1 |  1.4 | 0.71429 |
| 9984 | 19960925 |    1 |  1.4 | 0.71429 |
| 9984 | 19970326 |    1 |  1.3 | 0.76923 |
| 9984 | 20000425 |    1 |    3 | 0.33333 |
| 9984 | 20051228 |    1 |    3 | 0.33333 |
+------+----------+------+------+---------+
5 rows in set (0.01 sec)

ソフトバンクの1996年3月2X日の終値を表示

mysql> select d, s,c from tbl_dg_ohlc where s="9984" and d LIKE "1996032%";
+----------+------+-------+
| d        | s    | c     |
+----------+------+-------+
| 19960321 | 9984 | 26800 |
| 19960322 | 9984 | 26000 |
| 19960325 | 9984 | 26300 |
| 19960326 | 9984 | 19200 |
| 19960327 | 9984 | 19800 |
| 19960328 | 9984 | 20200 |
| 19960329 | 9984 | 21800 |
+----------+------+-------+
7 rows in set (0.01 sec)

ソフトバンクの1996年3月2X日の終値を表示↑の二つの表から修正値の数式を出力させてみる。

mysql> select a.s,tbl_dg_ohlc.d,GROUP_CONCAT(a.mul separator '*') 
from (select s,d,mul from tbl_dg_devide where s="9984" ) as a, 
tbl_dg_ohlc 
where a.s = tbl_dg_ohlc.s and a.d > tbl_dg_ohlc.d 
and tbl_dg_ohlc.d LIKE "1997032%" group by tbl_dg_ohlc.s,tbl_dg_ohlc.d ;
+------+----------+-----------------------------------+
| s    | d        | GROUP_CONCAT(a.mul separator '*') |
+------+----------+-----------------------------------+
| 9984 | 19970321 | 0.33333*0.76923*0.33333           |
| 9984 | 19970324 | 0.33333*0.33333*0.76923           |
| 9984 | 19970325 | 0.33333*0.33333*0.76923           |
| 9984 | 19970326 | 0.33333*0.33333                   |
| 9984 | 19970327 | 0.33333*0.33333                   |
| 9984 | 19970328 | 0.33333*0.33333                   |
+------+----------+-----------------------------------+
6 rows in set (0.44 sec)

どうだろう。なぜか、CONCATの順番が入れ替わったりしているが概ね機能してそうな気がする。

おしいところまではできるのだが、あと一押し eval()関数が欲しいなあ。

あ、これだと分割情報の適用されないところが出てくるからRIGHT JOIN した方がいいのか。

select b.s,b.d, IFNULL(GROUP_CONCAT(a.mul separator '*'),1) 
from 
  (select s,d,mul from tbl_dg_devide where s="9984" ) as a
     right join
         (select * from tbl_dg_ohlc where s="9984") as b
     on a.s = b.s and a.d > b.d  group by b.s,b.d ;