Pandasピボットテーブルを使ってBinance US取引履歴Excelデータから利益計算

こんにちはICHIKENです。

Pythonで仮想通貨自動取引を開始して約2ヶ月。（自動取引Pythonスクリプトは現時点未公開です）

アルゴリズムの検証を兼ねて、Binance USの取引履歴を集計して通貨ごとに売買の差分と取引回数を計算しました。よければコピペで使ってください。

ツイート時点では、履歴のFee列がBNB数量だということに気づかず、誤った結果でつぶやいていました。手数料安すぎw

うちの #ラズパイ #Python ちゃん#仮想通貨自動取引 おつかい日記

ちゃんとおつかいできてるか結果検証

BinanceUS取引履歴ExcelをDataFrame取込
↓
各通貨の売買を合計
↓
売買差分をmatplotlibグラフ出力

7月の売買差分合計 +$295
まさかおつかいできてる、だと...っ！#プログラミング pic.twitter.com/n5BBABPpXk

— ICHIKEN@🇺🇸エンジニアキャンパー🏕 (@IchikenUsa) August 8, 2021

この記事でわかること

PythonでBinance USの取引履歴EXCELデータをpandasで取り込み、ピボットテーブルで通貨ごとに売買集計し収益をグラフ化する方法がわかります。

Excelだとついついその時限りのデータになりがちなので、同じことを繰り返すならPandasで再現性を確保しちゃいましょう。

• Binance USの取引履歴ExcelデータをDataFrame取り込み
• Pandasピボットテーブルで通貨ごとに売買の合計算出
• Pandasピボットテーブルに通貨ごとの売買差分の列追加
• 列の合計算出でトータルの損益計算
• Pandasピボットテーブルで通過ごとの売買回数と合計算出
• 差分列をmatplotlib棒グラフ出力

前提条件

まず前提条件となる私の環境です。仮想環境はvenv、IDEはVSCodeを使用しています。

• M1 MacBook Air
• macOS 11.4
• VSCode 1.55.2
• Python 3.8.2
• pandas 1.3.0.dev0+1761.ga811c9694a
• matplotlib 3.4.2
• openpyxl 3.0.7

ライブラリインストール

正式対応するまでの期間限定対応ですが、M1にpandasとmatplotlibをインストールする方法はこちら

M1Macにnumpy | matplotlib | pandasをインストール

ExcelをPandas DataFrameとして読み込むためにopenpyxlを使用します。

pip3 install openpyxl

Binance US取引履歴ダウンロード

1ヶ月分のデータで検証します。まずはBinance USのWebページから期間指定で取引履歴をダウンロードします。

[Orders] → [Trade History] → [Export Recent Trade History]

ダウンロードしたExcelファイルの中身はこんな感じです。

ダウンロードしたデータは適当な場所に保存します。サンプルではVSCode用フォルダのvenv仮想環境に保存しています。
./python_envs/trade/Data/Trade_History-202107.xlsx

取引履歴の集計

ではさっそく準備したものを使って売買の差分、取引回数などを算出してみましょう。

サンプルコード

import pandas as pd
import matplotlib.pyplot as plt
# Excelの取り込みはopenpyxl

# Excelデータ読み込み
dir = './python_envs/trade/Data/Trade_History-202107.xlsx'
df = pd.read_excel(dir, index_col=[0])
print('--- 取引履歴Excelファイルの中身 ---')
print(df)

# 通貨ごとに売買の合計を算出
df_pivot_sum = pd.pivot_table(df, index='Market', columns='Type', values='Total', aggfunc='sum')
print('--- 通貨ごとの売買合計ピボット ---')
print(df_pivot_sum)

# SELLとBUYの差分計算
df_pivot_sum['DIFF'] = df_pivot_sum['SELL'] - df_pivot_sum['BUY']
print('--- 売りと買いの差分列を追加 ---')
print(df_pivot_sum)

# 損益計算
order_diff = df_pivot_sum['DIFF'].sum()
buy_total = df_pivot_sum['BUY'].sum()
sell_total = df_pivot_sum['SELL'].sum()
fee = (buy_total + sell_total) * 0.075 * 0.01 # 手数料0.075%をBNB支払い
profit = order_diff - fee

# 各通貨ペアごとの取引回数
df_pivot_count = pd.pivot_table(df, index='Market', columns='Type', values='Total', aggfunc='count', margins=True, margins_name='TTL')
print('--- 各オーダー回数 ---')
print(df_pivot_count)

print('--- 評価 ---')
print(f'BUY TTL:    $ {buy_total}')
print(f'SELL TTL:   $ {sell_total}')
print(f'Diff:       $ {order_diff}')
print(f'Fee:        $ {fee}')
print(f'Profit:     $ {profit}')

# グラフ表示
plt.figure(figsize=(7,5))
plt.title('Trading balance of each cryptocurrency')
plt.ylabel('Profit ($)')
df_pivot_sum['DIFF'].plot(kind='bar')

plt.show()

解説

まずはExcelデータをPandas DataFrameとして取り込みます。

# Excelデータ読み込み
dir = './python_envs/trade/Data/Trade_History-202107.xlsx'
df = pd.read_excel(dir, index_col=[0])
print('--- 取引履歴Excelファイルの中身 ---')
print(df)

Excelデータを取り込むことができました。

次に取引履歴を行：Market（通貨ペア）と列：Type（BUYとSELL）の各項目に対し合計を算出します。aggfuncでsumを指定すれば数値合計を出せます。

# 通貨ごとに売買の合計を算出
df_pivot_sum = pd.pivot_table(df, index='Market', columns='Type', values='Total', aggfunc='sum')
print('--- 通貨ごとの売買合計ピボット ---')
print(df_pivot_sum)

各通貨に対しTypeごとの取引金額の合計が出ました。

SELLとBUYの差分を新しい列DIFFを追加します。

# SELLとBUYの差分計算
df_pivot_sum['DIFF'] = df_pivot_sum['SELL'] - df_pivot_sum['BUY']
print('--- 売りと買いの差分列を追加 ---')
print(df_pivot_sum)

各行のSELLからBUYを引いた計算結果が新しい列DIFFに追加されました。

それぞれのピボット列からオーダーの合計額と手数料を算出します。列名を指定してメソッドsum()で列合計が出ます。

# 損益計算
order_diff = df_pivot_sum['DIFF'].sum()
buy_total = df_pivot_sum['BUY'].sum()
sell_total = df_pivot_sum['SELL'].sum()
fee = (buy_total + sell_total) * 0.075 * 0.01 # 手数料0.075%をBNB支払い
profit = order_diff - fee

手数料支払にBNBを使っているためExcelのFeeだとBNB数量になっちゃってます。その時のBNB取引価格から払った手数料を算出するなんてめんどくさすぎるので、データの手数料は使わずSELLとBUYの取引額から計算してます。

ピボットテーブルですので項目のカウントもできます。aggfuncでcountを指定することにより、データ個数合計を出せます。margins=Trueで行と列の合計を追加できます。

# 各通貨ペアごとの取引回数
df_pivot_count = pd.pivot_table(df, index='Market', columns='Type', values='Total', aggfunc='count', margins=True, margins_name='TTL')
print('--- 各オーダー回数 ---')
print(df_pivot_count)

値の合計のときと違い、件数がカウントされてますね。合計の行と列もmarginsにより自動追加されています。

最後にグラフ出力です。PandasのDataFrameはそのままプロットできます。大きさ、タイトル、Y軸ラベル名を指定して、DIFF列を棒グラフとして出力しています。

# グラフ表示
plt.figure(figsize=(7,5))
plt.title('Trading balance of each cryptocurrency')
plt.ylabel('Profit ($)')
df_pivot_sum['DIFF'].plot(kind='bar')

plt.show()

そのままだと文字が隠れたりしてるので、UIから表示を調整してます。

最後に

いかがでしたか？

この記事が皆さんのお役に立てれば幸いです。