PythonでCSVファイルの株価データをデーターベースに入れて、ローソク足チャートを描いてみた。
Publish date: Jun 23, 2017
Last updated: Sep 16, 2019
Last updated: Sep 16, 2019
CSVファイルの株価データをデーターベースへ
データーベースから取り出した株価をJupyter Notebookで分析したくなったのでCSVファイルの株価をデーターベースに入れる処理を書いてみた。
テーブル定義
MariaDBを使う、超ええかげんの設計。問題があればその時に直す。元データがあるのでなんとかなるだろう。
# 始値,高値,安値,終値(1時間足)
CREATE TABLE `ohlc_1h` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`sec_code` SMALLINT(5) UNSIGNED NOT NULL,
`at` DATETIME NOT NULL,
`open` FLOAT NULL DEFAULT NULL,
`high` FLOAT NULL DEFAULT NULL,
`low` FLOAT NULL DEFAULT NULL,
`close` FLOAT NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 出来高,売買代金(1時間足)
CREATE TABLE `extra_1h` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`sec_code` SMALLINT(5) UNSIGNED NOT NULL,
`at` DATETIME NOT NULL,
`volume` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
`turnover` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 始値,高値,安値,終値(1日足)
CREATE TABLE `ohlc_1d` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`sec_code` SMALLINT(5) UNSIGNED NOT NULL,
`at` DATE NOT NULL,
`open` FLOAT NULL DEFAULT NULL,
`high` FLOAT NULL DEFAULT NULL,
`low` FLOAT NULL DEFAULT NULL,
`close` FLOAT NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 出来高,売買代金(1日足)
CREATE TABLE `extra_1d` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`sec_code` SMALLINT(5) UNSIGNED NOT NULL,
`at` DATE NOT NULL,
`volume` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
`turnover` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
株価をCSVファイルからデーターベースに格納する
株価データは http://k-db.com/ からダウンロードしてきたCSVファイルをデーターベースに入れる。
import datetime
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.sql import text
# 証券コードと日付時刻から主キーを生成する関数
def make_ID(sec_code, datetime):
return 10**13 * sec_code + int(datetime.timestamp())
# CSVファイルの1レコードに主キーと証券コードを追加する関数
def append_key(sec_code, row):
datetime = pd.to_datetime (row['at'])
row['id'] = make_ID (sec_code, datetime)
row['sec_code'] = sec_code
return row
# CSVファイルを読み取ってデーターベースのテーブルに格納する関数
def csv_to_DB(engine, sec_code, tnames, csvfilename):
# CSVファイルの1行目は列名なので無視して
# 無視した列名の代わりを与える
df_csv = pd.read_csv (csvfilename, na_values='', header=None, skiprows=1)
if len(df_csv.columns) == 7:
df_csv.columns = ['date','open','high','low','close','volume','turnover']
else:
df_csv.columns = ['date','time','open','high','low','close','volume','turnover']
# date, time列を統合してat列を作る関数
def combine_date_time(row):
date_and_time = row['date']
del row['date']
if 'time' in row:
date_and_time = date_and_time + " " + row['time']
del row['time']
datetime = pd.to_datetime (date_and_time)
row['at'] = str(datetime)
return row
df = df_csv.apply (combine_date_time, axis=1)
df = df.apply (lambda r:append_key(sec_code,r), axis=1)
# 4値(ohlc)とその他に分ける
df_ohlc = df.loc[:,['id', 'sec_code', 'at', 'open', 'high', 'low', 'close']]
df_extra = df.loc[:,['id', 'sec_code', 'at', 'volume', 'turnover']]
# 分けた物をそれぞれのテーブルへ格納する
df_ohlc.to_sql (tnames['ohlc'], engine, if_exists='append', index=False)
df_extra.to_sql (tnames['extra'], engine, if_exists='append', index=False)
return df_csv
# 1時間足を日足へ変換してDBへ
def batch_resample_1day(engine, sec_code, tname_hourly, tname_daily, strategy):
# 日足テーブルに登録されている最後の日を得る
sql="SELECT MAX(at) AS 'latest' FROM `{}`".format (tname_daily)
sql=sql+" WHERE `sec_code`='{}'".format (sec_code)
sql=sql+";"
with engine.connect() as conn:
r = conn.execute(sql).fetchall()
latest = r[0].latest
# 日足テーブルに無いデーターを1時間足から計算して日足テーブルへ
sql="SELECT at,{} FROM `{}`".format (",".join(strategy.keys()), tname_hourly)
sql=sql+" WHERE `sec_code`='{}'".format (sec_code)
if latest:
nextday = latest + datetime.timedelta(days=1)
sql=sql+" AND `at` > '{}'".format (nextday.strftime('%Y-%m-%d'))
sql=sql+";"
df = pd.read_sql(sql, engine, index_col='at')
if not df.empty:
df = df.resample('1D').agg(strategy)
df = df.reset_index().apply (lambda x:append_key(sec_code,x), axis=1)
df.to_sql (tname_daily, engine, if_exists='append', index=False)
データーベースに入れる処理はここまでで出来る。
データーベースから株価を取り出す
データーベースの株価(日足)はこんなふうに取り出す。
sql="SELECT at,open,high,low,close"
sql=sql+" FROM `{}`".format('ohlc_1d')
sql=sql+" WHERE `sec_code`='{}'".format(8306)
sql=sql+" AND `at` BETWEEN '{}' AND '{}'".format('2017-01-01','2017-12-31')
sql=sql+";"
df = pd.read_sql(sql, engine,index_col='at')
ローソク足チャート
Pythonでローソク足チャートの表示(Plotly編) を参考に日足データのローソク足チャートを表示
from plotly.offline import init_notebook_mode, iplot
from plotly import figure_factory as FF
init_notebook_mode(connected=True) # Jupyter notebook用設定
fig = FF.create_candlestick(df.open, df.high, df.low, df.close, dates=df.index)
iplot(fig)
いろいろ気になる所があるけど、いじるのはまた今度。
使い方はこのipynbを見てね