PostgreSQL に投入した約定履歴からMatplotlibでローソク図を描画する

前の記事で約定履歴が手に入ったので、OHLCを求めてローソク図を描画してみる。

team-6.hatenablog.jp

from datetime import datetime, timedelta

import pandas as pd

import matplotlib.pyplot as plt
import matplotlib.finance as mpf
from matplotlib import ticker
import matplotlib.dates as mdates

import psycopg2
import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative


POSTGRES_HOST='HOST'
POSTGRES_PORT=5432
POSTGRES_USER='user'
POSTGRES_PASSWORD='password'

connection_config = {
    'host': POSTGRES_HOST,
    'port': '5432',
    'database': POSTGRES_USER,
    'user': POSTGRES_USER,
    'password': POSTGRES_PASSWORD,
}

connection = psycopg2.connect(**connection_config)

end = datetime.utcnow() # ここはテキトーに
period = 48 * timedelta(minutes=60) # 1時間足で48本
start = end - period

executions = pd.read_sql(sql=f"SELECT * FROM executions WHERE market='bitbank_btc_jpy' AND '{start}' < timestamp  AND timestamp <= '{end}' ORDER BY timestamp ASC;", con=connection)

df = executions[['timestamp', 'price']].set_index('timestamp')
ohlc = df.resample('60T').ohlc(); # ここは60T = 60分
ohlc['price']['open'].head()

fig, ax = plt.subplots()

mpf.candlestick2_ohlc(ax, opens=ohlc['price']['open'], closes=ohlc['price']['close'], lows=ohlc['price']['low'], highs=ohlc['price']['high'], width=0.6, colorup='r', colordown='b')

こんな感じ。

f:id:katoken-0215:20180414014204p:plain