取引所のデータをpythonで加工してスプレッドシートで見たかった話 #pandas

はじめに

仮想通貨の自動取引を作ってみたい、けどその前に自分のロジックで勝てるのか確かめたい
データを格納するためにデータベースなんて建てたくない
まさか画面（フロントエンド）なんて作りたくない
使い慣れたExcelみたいなのでデータをいじったりグラフにしたりしたい → googleスプレッドシートに書き込みたい

という要件で作ってみました。仮想通貨取引所のデータ取得にはCoincheckさんのAPIを利用させていただきます。

環境

実行環境はたまたま手元にあった Raspberry Pi 4 を使用、python環境はraspbian os 付属のものを使用しました。
※pythonが動けばWindowsでもmacでもAnacondaでもminicondaでもなんでも大丈夫とおもいます。

python用ライブラリのインストール

pip install gspread oauth2client ccxt pandas

google スプレッドシートのAPI利用設定

こちらのサイトを参考に秘密鍵（jsonファイル）の取得までを実施します。

このようなファイルができます。

書き込み用のスプレッドシートの設定

引き続き先ほどのリンクを参考に、新規スプレッドシートに共有の設定をします。

カラムの設定

この後の処理でpandasデータフレームとしてこのスプレッドシートを読み込ませることになるので、あらかじめ先頭の行にカラム名（pandasだとseries名）を設定していきます。

今回はccxtで取得するfetch_trades(約定データ)の形式に合わせてこのように入力します。

id	amount	rate	pair	order_type	created_at

入力した状態

ファイル構成

py-coinというディレクトリを作ってその中にファイルを配置していきます。

mkdir py-coin
cd py-coin
# セキュリティな情報はここに入れます。
touch .env
# 設定値を入れるファイルを用意します。
touch config.py
# スクリプト本体を書き込むファイルを用意します。
touch py-coin.py

先ほどのGoogle Sheets APIで有効にした手順でサービスアカウントキーのjsonファイルをservice_account.jsonにリネームして同フォルダに格納します。
ここでファイルの構成は下記のようになりました。

py-coin
├── .env
├── config.py
├── py-coin.py
└── service_account.json

.envを編集

対象のスプレッドシートのキーを書き込みます。
スプレッドシートのキーはそれほどセキュアでもないですが、後々取引処理を書く時などに取引所のAPIキー、シークレットを格納したりするのに.env形式のほうが便利なので高のようにしています。

スプレッドシートのキーとはスプレッドシートのURLの一部
https://docs.google.com/spreadsheets/d/この部分/edit#gid=0

.env

SPREADSHEET_KEY = 'スプレッドシートのキー'

config.py

先ほどの.envの読み込みと設定値を入力します。設定値には

スプレッドシートに書き込む行数の最大値
取引所から取得する取引件数の設定
を設定しておきます。

config.py

from dotenv import load_dotenv
load_dotenv()
import os
# .envから取得
SPREADSHEET_KEY = os.getenv('SPREADSHEET_KEY')
# スプレッドシートに保持する最大行数
SHEET_MAX_ROWS = 200
# 取引所から取得する行数
COINCK_TRADE_ROWS = 50

py-coin.py

スクリプトの本体を記入していきます

py-coin.py

import ccxt
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import gspread
import datetime
import config

def main():
    # 最初にccxtでcoincheckを指定
    coincheck = ccxt.coincheck()
    # パラメーターにconfigから取引取得件数を指定
    param = {"limit":config.COINCK_TRADE_ROWS}
    # 約定取引一覧を取得
    trades = coincheck.fetch_trades('BTC/JPY',params=param)
    # 取引結果を格納する配列を初期化
    list_info = []
    # 所得した取引一覧を加工
    for row in trades:
        try:
            # 行ごとの辞書をつくります
            dict_tradeinfo = row['info']
            # それぞれ型を指定して辞書に格納
            dict_tradeinfo['id'] = int(dict_tradeinfo['id'])
            dict_tradeinfo['rate'] = float(dict_tradeinfo['rate'])
            dict_tradeinfo['amount'] = float(dict_tradeinfo['amount'])
            # UTC日時をJSTに変換します
            dict_tradeinfo['created_at'] = utc2jst(dict_tradeinfo['created_at'])
            # 行ごとの辞書を配列に追加します
            list_info.append(dict_tradeinfo)
        except KeyError:
            # 'info'がない場合の処理
            print('infoないのでスルー')
    # 配列をスプレッドシートに追加する処理
    insert_gspread(list_info)

def utc2jst(str_datetime):
    # UTCに単純に9時間足してJSTにします
    date_jst = datetime.datetime.fromisoformat(str_datetime.replace('Z', '')) + datetime.timedelta(hours=9)
    date_jst = datetime.datetime.isoformat(date_jst)
    return date_jst

def insert_gspread(list_info):
    # spreadsheet認証情報
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
    # service_account.jsonのパスを指定
    SERVICE_ACCOUNT_FILE = './service_account.json'
    credentials = ServiceAccountCredentials.from_json_keyfile_name(SERVICE_ACCOUNT_FILE, SCOPES)
    gs = gspread.authorize(credentials)
    SPREADSHEET_KEY = config.SPREADSHEET_KEY
    # ワークシートを指定　この場合sheet1を指定
    worksheet = gs.open_by_key(SPREADSHEET_KEY).sheet1
    try:
        # 最初にスプレッドシートの内容を取得してdfにします
        df = pd.DataFrame(worksheet.get_all_records())
        # 挿入するデータを辞書からdfに変換します
        df_insert = pd.json_normalize(list_info)
        # スプレッドシートのデータと挿入するデータをくっつけdf_newとします
        df_new = pd.concat([df, df_insert])
        # df_newから重複する行を削除
        df_new.drop_duplicates(inplace=True)
        # df_newからNan値を削除
        df_new.fillna(0,inplace=True)
        # 最大値を超過する行数を取得 （元の行数 ＋ 追加する行数） - 最大値
        offset = len(df_new) - int(config.SHEET_MAX_ROWS)
        # offsetが0より小さい場合は0にする
        offset = max(0, offset)
        # offset分繰り上げて書き込み
        worksheet.update([df_new[offset:].columns.values.tolist()] + df_new[offset:].values.tolist())
    except gspread.exceptions.APIError as e:
        # APIエラーが時々出るのでその場合の処理
        print(e)

if __name__ == "__main__":
    main()

処理内容について

python coin.py

この処理を複数繰り返せば最新の情報をどんどん取り込んでスプレッドシートを更新してくれます。繰り返し実行する方法についてはcronを使用するか、このcoin.pyをモジュール化してwhileで回すなど自由に設計できるようにしています。

繰り返しの実行頻度、回数についてはスプレッドシートのAPI、取引所のAPI、それぞれの利用規約等を確認のうえ設定する必要があります

処理はpy-coin.pyを一回実行するごとにCOINCK_TRADE_ROWSで指定した件数の取引データを取得してスプレッドシートに追加していきます。
何度か実行してSHEET_MAX_ROWSの行数を超えた分は古いデータからスプレッドシートから削除されていきます。
こうすることでスプレッドシート上で時間軸ごとの価格グラフなどを作成した場合でもデータが時間軸で流れるような表現が可能になります。

※行の削除⇒追加で同様の処理を書くと、グラフの参照範囲が削除の度にどんどん狭くなってしまいます（最初これで結構悩みました）

まとめ

仮想通貨バブル期の数年前にphp,mySQLで同じようなものを作ったことがありましたが、結局データベースに格納してもエクスポートしてexcelでいろいろいじってみたり、とにかく日々の検証作業に手間がかかっていた記憶があります。現代なら当時よりもっといいやり方があるだろうと思い、今回調べて作ってみて本当に簡単にできたので時代を感じました。
また、何か使えそうなものができたら投稿してみようと思います。