In [1]:
import datetime
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.sql import text
In [2]:
import sys
print (sys.version_info)
ユーザー名とパスワード
In [3]:
MARIADB_USER="ENTER USER NAME HERE"
MARIADB_PASSWD="ENTER PASSWORD HERE"
データーベースはmariaDB
ホストはlocalhost
データーベース名はstockdb
In [4]:
engine = create_engine(f"mysql+pymysql://{MARIADB_USER}:{MARIADB_PASSWD}@localhost:59603/stockdb")
8306-T, 三菱UFJフィナンシャル・グループ
を対象にする。
In [5]:
TICKERSYMBOL="TSTYO 8306"
日足を対象にする。
In [6]:
TIMEFRAME="TF1d"
In [7]:
query=" ".join (
[ "SELECT CONVERT_TZ(`at`, '+00:00','+9:00') AS 'jst', `open`, `high`, `low`, `close`, `volume`, `turnover`"
, "FROM `ohlcvt`"
,f"WHERE `ticker`='{TICKERSYMBOL}' AND `tf`='{TIMEFRAME}'"
, "ORDER BY `at` DESC"
, ";"
])
print (query)
In [8]:
df=pd.read_sql(query, engine)
df
Out[8]:
In [9]:
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.jst)
iplot(fig)
In [10]:
import matplotlib.pyplot as plt
from matplotlib.finance import candlestick_ohlc, candlestick2_ohlc
fig = plt.figure(figsize=(60,20))
ax = plt.subplot()
candlestick2_ohlc(ax, df.open, df.high, df.low, df.close, width=0.7, alpha=1, colorup='g', colordown='r')
counts = df.shape[0]
ax.grid()
ax.set_xlim(counts,0)
ax.set_xticks(range (0,counts,10))
ax.set_xticklabels([(df.jst[int(x)].date() if x <= df.shape[0] else x) for x in ax.get_xticks()], rotation=60)
plt.show()
In [11]:
query=" ".join (
[ "SELECT CONVERT_TZ(`at`, '+00:00','+9:00') AS 'jst', `val`, `ind`"
, "FROM `ohlcvt`"
, "INNER JOIN `tech_inds` ON `ohlcvt`.`id`=`tech_inds`.`ohlcvt`"
,f"WHERE `ticker`='{TICKERSYMBOL}' AND `tf`='{TIMEFRAME}'"
, "ORDER BY `at` DESC"
])
ti=pd.read_sql(query, engine)
ti
Out[11]:
In [12]:
ti=ti.pivot('jst','ind','val')
ti
Out[12]:
In [13]:
import matplotlib.dates as mdates
dateIdx = df.jst.map(mdates.date2num)
df_ohlc = pd.DataFrame( {'Date':dateIdx
,'Open':df.open.values
,'High':df.high.values
,'Low':df.low.values
,'Close':df.close.values}
,columns=['Date','Open','High','Low','Close'])
In [14]:
fig = plt.figure(figsize=(60,20))
ax = plt.subplot(111)
counts = df_ohlc.shape[0]
ax.grid()
ax.xaxis_date()
ax.set_xlim(dateIdx[counts-1],dateIdx[0])
candlestick_ohlc(ax, df_ohlc.values, width=0.7, alpha=1, colorup='g', colordown='r')
tiIdx = ti.reset_index()["jst"].map(mdates.date2num)
ax.plot(tiIdx, ti['TISMA 5'])
ax.plot(tiIdx, ti['TISMA 25'])
ax.plot(tiIdx, ti['TISMA 75'])
ax.legend()
plt.show()
In [15]:
fig = plt.figure(figsize=(60,20))
ax = plt.subplot(111)
counts = df_ohlc.shape[0]
ax.grid()
ax.xaxis_date()
ax.set_xlim(dateIdx[counts-1],dateIdx[0])
candlestick_ohlc(ax, df_ohlc.values, width=0.7, alpha=1, colorup='g', colordown='r')
tiIdx = ti.reset_index()["jst"].map(mdates.date2num)
ax.plot(tiIdx, ti['TIEMA 5'])
ax.plot(tiIdx, ti['TIEMA 25'])
ax.plot(tiIdx, ti['TIEMA 75'])
ax.legend()
plt.show()
In [16]:
fig = plt.figure(figsize=(60,20))
ax = plt.subplot(111)
counts = df_ohlc.shape[0]
ax.grid()
ax.xaxis_date()
ax.set_xlim(dateIdx[counts-1],dateIdx[0])
candlestick_ohlc(ax, df_ohlc.values, width=0.7, alpha=1, colorup='g', colordown='r')
tiIdx = ti.reset_index()["jst"].map(mdates.date2num)
ax.plot(tiIdx, ti['TIBBLOW3 25'])
ax.plot(tiIdx, ti['TIBBLOW2 25'])
ax.plot(tiIdx, ti['TIBBLOW1 25'])
ax.plot(tiIdx, ti['TIBBMIDDLE 25'])
ax.plot(tiIdx, ti['TIBBUP1 25'])
ax.plot(tiIdx, ti['TIBBUP2 25'])
ax.plot(tiIdx, ti['TIBBUP3 25'])
ax.legend()
plt.show()
In [17]:
fig = plt.figure(figsize=(60,20))
ax = plt.subplot(211)
counts = df_ohlc.shape[0]
ax.grid()
ax.xaxis_date()
ax.set_xlim(dateIdx[counts-1],dateIdx[0])
candlestick_ohlc(ax, df_ohlc.values, width=0.7, alpha=1, colorup='g', colordown='r')
tiIdx = ti.reset_index()["jst"].map(mdates.date2num)
rsi = plt.subplot(212)
rsi.grid()
rsi.xaxis_date()
rsi.set_xlim(dateIdx[counts-1],dateIdx[0])
rsi.plot(tiIdx, ti['TIRSI 9'])
rsi.plot(tiIdx, ti['TIRSI 14'])
rsi.plot(tiIdx, ti['TIPSYCHOLO 12'])
rsi.legend()
plt.show()
In [18]:
fig = plt.figure(figsize=(60,20))
ax = plt.subplot(211)
counts = df_ohlc.shape[0]
ax.grid()
ax.xaxis_date()
ax.set_xlim(dateIdx[counts-1],dateIdx[0])
candlestick_ohlc(ax, df_ohlc.values, width=0.7, alpha=1, colorup='g', colordown='r')
tiIdx = ti.reset_index()["jst"].map(mdates.date2num)
macd = plt.subplot(212)
macd.grid()
macd.xaxis_date()
macd.set_xlim(dateIdx[counts-1],dateIdx[0])
macd.plot(tiIdx, ti['TIMACD 12 26'])
macd.plot(tiIdx, ti['TIMACDSIG 12 26 9'])
macd.legend()
plt.show()
In [19]:
fig = plt.figure(figsize=(60,20))
ax = plt.subplot(211)
counts = df_ohlc.shape[0]
ax.grid()
ax.xaxis_date()
ax.set_xlim(dateIdx[counts-1],dateIdx[0])
candlestick_ohlc(ax, df_ohlc.values, width=0.7, alpha=1, colorup='g', colordown='r')
tiIdx = ti.reset_index()["jst"].map(mdates.date2num)
dmi = plt.subplot(212)
dmi.grid()
dmi.xaxis_date()
dmi.set_xlim(dateIdx[counts-1],dateIdx[0])
dmi.plot(tiIdx, ti['TIDIPOS 14'])
dmi.plot(tiIdx, ti['TIDINEG 14'])
dmi.plot(tiIdx, ti['TIADX 14'])
dmi.plot(tiIdx, ti['TIADX 9'], '--')
dmi.legend()
plt.show()
In [ ]: