ビットフライヤーの証拠金（損益）をデータベースに保存して一定秒ごとに保存して描画するスクリプト

こんにちは．スナフキンです．ビットフライヤーのwebページをひらけない状況（クソつまらない会議とかでノーパソをいじっているふりをしながらbotの収益を確認したい時など）でbot取引などによる証拠金変動を確認したい時のために，証拠金変動をSQLiteデータベースに保存し，matplotlibでグラフ化するスクリプトを書きました．コードを読めば何をやっているかわかる方は自由に使ってください．

import matplotlib. pyplot as plt
import pybitflyer
import configparser
import sqlite3
from datetime import datetime, timedelta
from pytz import timezone
from time import sleep
import pandas as pd
from dateutil import parser


class CapitalManager:
    """
    損益管理クラス
    """
    def __init__(self):
        inifile = configparser.ConfigParser()
        inifile.read('../configs/config.ini', 'UTF-8-sig')
        key = inifile.get('user', 'api_key')
        secret = inifile.get('user', 'api_secret')
        self.api = pybitflyer.API(key, secret)

    def save_to_sql(self, term):
        """
        証拠金残高をsqlに保存する．日付，証拠金額+評価損益.

        :param term: 保存間隔
        :return:
        """
        while True:
            conn = sqlite3.connect('./database/capital_hist.db')
            cur = conn.cursor()
            cur.execute('''CREATE TABLE IF NOT EXISTS capital_history
                         (date text, capital int)''')

            col = self.api.getcollateral()
            print(col)
            col_val = int(col["collateral"] + col["open_position_pnl"])
            now = datetime.now().astimezone(timezone('Asia/Tokyo'))
            t = (now, col_val)

            cur.execute('INSERT INTO capital_history VALUES(?,?)', t)
            conn.commit()
            conn.close()

            sleep(term)

    def describe_graph(self, start_dt=None, end_dt=None):
        """
        資産グラフを表示．（損益ではない）startとendを指定．各々指定しないことも可能．
        :param start_dt: timezone awareなdatetimeで指定する．
        :param end_dt: timezone awareなdatetimeで指定する．
        :return:
        """

        conn = sqlite3.connect('./database/capital_hist.db')
        cur = conn.cursor()
        df = pd.read_sql("select * from capital_history", conn, index_col="date", parse_dates=True)
        df.index = df.index.map(parser.parse)
        conn.close()

        # 両方Noneの場合，全てのデータを描画
        if start_dt == None and end_dt == None:
            pass
        # startだけNoneの場合，endまでの資産曲線を全て描画．
        elif start_dt == None and end_dt != None:
            df = df[df.index <= end_dt]
        # endだけNone
        elif start_dt != None and end_dt == None:
            df = df[df.index >= start_dt]
        # 両方とも指定があるとき
        else:
            df = df[(df.index >= start_dt) & (df.index <= end_dt)]
        plt.title("The capital curve")
        plt.xlabel("Date")
        plt.ylabel("Capital(JPY)")
        plt.plot(df.index, df.capital)
        plt.show()

    def realtime_describe(self,term, minutes):
        """
         資産グラフのリアルタイムプロット．term(秒)ごとに描画する．過去minutes分前までのデータはDBから読み込み
        :return:
        """
        fig, ax = plt.subplots(1, 1)
        #各リストの初期化
        col_hist = []
        col = []
        index = []

        #過去データをSQLiteDBから読み込み
        conn = sqlite3.connect('./database/capital_hist.db')
        df = pd.read_sql("select * from capital_history", conn, index_col="date", parse_dates=True)
        df.index = df.index.map(parser.parse)
        conn.close()


        start_dt = datetime.now().astimezone(timezone('Asia/Tokyo')) - timedelta(minutes=minutes)
        # その日最初の証拠金額を取得
        now = datetime.now()
        today_df = df[df.index >= datetime(now.year, now.month, now.day, 0, 0, 0)]
        start_capital = today_df[today_df.index == today_df.index.min()].capital
        print(f"Start capital:{start_capital.values[0]}")
        df = df[df.index >= start_dt]
        for i,r in df.iterrows():
            index.append(i)
        col += df.capital.values.tolist()

        col_hist.append(self.api.getcollateral())
        col.append(col_hist[-1]["collateral"] + col_hist[-1]["open_position_pnl"])

        index.append(datetime.now().astimezone(timezone('Asia/Tokyo')))
        line, = ax.plot(index, col, c="blue")
        ax.set_title("The capital curve")
        ax.set_xlabel("datetime")
        ax.set_ylabel("Capital(JPY)")
        while True:
            dt = datetime.now().astimezone(timezone('Asia/Tokyo'))
            index.append(dt)
            col_hist.append(self.api.getcollateral())
            print("Info: {}".format(col_hist[-1]))
            #日次損益
            print("Daily PL: {}".format(col_hist[-1]["collateral"] + col_hist[-1]["open_position_pnl"] - start_capital.values[0]))
            col.append(col_hist[-1]["collateral"] + col_hist[-1]["open_position_pnl"])

            ax.set_xlim(min(index), max(index))
            ax.set_ylim(min(col) - 1000, max(col) + 1000)
            line.set_data(index, col)
            plt.pause(term)


if __name__ == '__main__':
    tz = timezone('Asia/Tokyo')
    start = tz.localize(datetime(2018, 5, 21, 0, 00, 00))
    end = tz.localize(datetime(2018, 5, 21, 19, 00, 00))
    manager = CapitalManager()
    #manager.describe_graph(end_dt=end)
    manager.realtime_describe(term=30, minutes=1440)

    #SQLite
    #manager.save_to_sql(30)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

import matplotlib. pyplot as plt

import pybitflyer

import configparser

import sqlite3

from datetime import datetime, timedelta

from pytz import timezone

from time import sleep

import pandas as pd

from dateutil import parser

class CapitalManager:

"""

損益管理クラス

"""

def __init__(self):

inifile = configparser.ConfigParser()

inifile.read('../configs/config.ini', 'UTF-8-sig')

key = inifile.get('user', 'api_key')

secret = inifile.get('user', 'api_secret')

self.api = pybitflyer.API(key, secret)

def save_to_sql(self, term):

"""

証拠金残高をsqlに保存する．日付，証拠金額+評価損益.

:param term: 保存間隔

:return:

"""

while True:

conn = sqlite3.connect('./database/capital_hist.db')

cur = conn.cursor()

cur.execute('''CREATE TABLE IF NOT EXISTS capital_history

(date text, capital int)''')

col = self.api.getcollateral()

print(col)

col_val = int(col["collateral"] + col["open_position_pnl"])

now = datetime.now().astimezone(timezone('Asia/Tokyo'))

t = (now, col_val)

cur.execute('INSERT INTO capital_history VALUES(?,?)', t)

conn.commit()

conn.close()

sleep(term)

def describe_graph(self, start_dt=None, end_dt=None):

"""

資産グラフを表示．（損益ではない）startとendを指定．各々指定しないことも可能．

:param start_dt: timezone awareなdatetimeで指定する．

:param end_dt: timezone awareなdatetimeで指定する．

:return:

"""

conn = sqlite3.connect('./database/capital_hist.db')

cur = conn.cursor()

df = pd.read_sql("select * from capital_history", conn, index_col="date", parse_dates=True)

df.index = df.index.map(parser.parse)

conn.close()

# 両方Noneの場合，全てのデータを描画

if start_dt == None and end_dt == None:

pass

# startだけNoneの場合，endまでの資産曲線を全て描画．

elif start_dt == None and end_dt != None:

df = df[df.index <= end_dt]

# endだけNone

elif start_dt != None and end_dt == None:

df = df[df.index >= start_dt]

# 両方とも指定があるとき

else:

df = df[(df.index >= start_dt) & (df.index <= end_dt)]

plt.title("The capital curve")

plt.xlabel("Date")

plt.ylabel("Capital(JPY)")

plt.plot(df.index, df.capital)

plt.show()

def realtime_describe(self,term, minutes):

"""

資産グラフのリアルタイムプロット．term(秒)ごとに描画する．過去minutes分前までのデータはDBから読み込み

:return:

"""

fig, ax = plt.subplots(1, 1)

#各リストの初期化

col_hist = []

col = []

index = []

#過去データをSQLiteDBから読み込み

conn = sqlite3.connect('./database/capital_hist.db')

df = pd.read_sql("select * from capital_history", conn, index_col="date", parse_dates=True)

df.index = df.index.map(parser.parse)

conn.close()

start_dt = datetime.now().astimezone(timezone('Asia/Tokyo')) - timedelta(minutes=minutes)

# その日最初の証拠金額を取得

now = datetime.now()

today_df = df[df.index >= datetime(now.year, now.month, now.day, 0, 0, 0)]

start_capital = today_df[today_df.index == today_df.index.min()].capital

print(f"Start capital:{start_capital.values[0]}")

df = df[df.index >= start_dt]

for i,r in df.iterrows():

index.append(i)

col += df.capital.values.tolist()

col_hist.append(self.api.getcollateral())

col.append(col_hist[-1]["collateral"] + col_hist[-1]["open_position_pnl"])

index.append(datetime.now().astimezone(timezone('Asia/Tokyo')))

line, = ax.plot(index, col, c="blue")

ax.set_title("The capital curve")

ax.set_xlabel("datetime")

ax.set_ylabel("Capital(JPY)")

while True:

dt = datetime.now().astimezone(timezone('Asia/Tokyo'))

index.append(dt)

col_hist.append(self.api.getcollateral())

print("Info: {}".format(col_hist[-1]))

#日次損益

print("Daily PL: {}".format(col_hist[-1]["collateral"] + col_hist[-1]["open_position_pnl"] - start_capital.values[0]))

col.append(col_hist[-1]["collateral"] + col_hist[-1]["open_position_pnl"])

ax.set_xlim(min(index), max(index))

ax.set_ylim(min(col) - 1000, max(col) + 1000)

line.set_data(index, col)

plt.pause(term)

if __name__ == '__main__':

tz = timezone('Asia/Tokyo')

start = tz.localize(datetime(2018, 5, 21, 0, 00, 00))

end = tz.localize(datetime(2018, 5, 21, 19, 00, 00))

manager = CapitalManager()

#manager.describe_graph(end_dt=end)

manager.realtime_describe(term=30, minutes=1440)

#SQLite

#manager.save_to_sql(30)