ビットコイン自動売買の損益計算はプログラムで自動集計している
【BTC自動売買】として毎日追加している記事は
記事内に書いている通り、
システムトレードで自動売買した結果を
プログラムで集計して損益計算しています。
その結果を記事として自動追加しています。
損益の自動集計とメール送信を行う処理を
実装して稼働させています。
今回はこの集計プログラムをご紹介します。
計算の元になっている損益額は、
bitFlyer APIにより
証拠金残高の変動履歴から取得しています。
証拠金残高の変動履歴を得られれば、
そこから勝ちトレードと負けトレードが
それぞれどれほどあったか取得できます。
運用している集計プログラムのソースコードを紹介
では実際に運用しているプログラムを
ご紹介しますので参考にされてください。
import bitflyerApi
import pymysql
import datetime
import configparser
import os
import sys
import time
from decimal import Decimal, ROUND_HALF_UP
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.utils import formatdate
ADDRESS = "SMTPサーバー接続ID"
PASSWARD = "パスワード"
# SMTPサーバの設定
SMTP = "SMTPサーバー"
PORT = SMTPサーバーのポート番号
charset = 'ISO-2022-JP'
from_address = '送信元アドレス'
to_address = '送信先アドレス'
to_address_arr = ['送信先アドレス']
config = configparser.ConfigParser()
config.read(os.path.dirname(os.path.abspath(__file__)) + "/config.ini")
dbh = pymysql.connect(
host=config.get('db', 'host'),
user=config.get('db', 'user'),
password=config.get('db', 'pass'),
db=config.get('db', 'name'),
charset='utf8',
cursorclass=pymysql.cursors.DictCursor
)
argvs = sys.argv
account_id = argvs[1]
if len(argvs) >= 3:
target_date = argvs[2]
target_date = datetime.datetime.strptime(target_date, '%Y-%m-%d')
else:
target_date = datetime.datetime.now() - datetime.timedelta(days=1)
subject = u'【BTC自動売買】%s取引結果' % target_date.strftime('%Y-%m-%d')
def sort_collaterals(collateralhistories):
stmt = dbh.cursor()
sql = "CREATE TEMPORARY TABLE `tmp_collateralhistories` ( \
`account_id` bigint(20) unsigned DEFAULT NULL, \
`collateral_id` bigint(20) unsigned DEFAULT NULL, \
`currency_code` varchar(10) DEFAULT NULL, \
`change_amount` decimal(10,2) DEFAULT NULL, \
`amount` decimal(12,2) DEFAULT NULL, \
`reason_code` varchar(20) DEFAULT NULL, \
`collateral_date` varchar(30) DEFAULT NULL \
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
stmt.execute(sql)
for collateral in collateralhistories:
sql = "INSERT INTO tmp_collateralhistories \
(account_id, collateral_id, currency_code, change_amount, amount, reason_code, collateral_date) \
VALUES \
(%s, %s, '%s', %s, %s, '%s', '%s')" \
% (account_id, collateral['id'], collateral['currency_code'], collateral['change'], \
collateral['amount'], collateral['reason_code'], collateral['date'])
stmt.execute(sql)
sql = "SELECT * FROM tmp_collateralhistories ORDER BY collateral_id ASC"
stmt.execute(sql)
results = stmt.fetchall()
stmt.close()
return results
def get_collaterals():
stmt = dbh.cursor()
sql = "SELECT max(collateral_id) AS max_collateral_id FROM collateralhistories WHERE account_id = %s " % (account_id)
stmt.execute(sql)
result = stmt.fetchone()
stmt.close()
return result['max_collateral_id']
def insert_collateral(collateral):
conversion_date = collateral['collateral_date'].replace('T', ' ')
conversion_date = conversion_date[0:19]
conversion_date = datetime.datetime.strptime(conversion_date, '%Y-%m-%d %H:%M:%S')
conversion_date = conversion_date + datetime.timedelta(hours=9)
conversion_date = conversion_date.strftime('%Y-%m-%d %H:%M:%S')
sql = "INSERT INTO collateralhistories \
(account_id, collateral_id, currency_code, change_amount, amount, reason_code, collateral_date, conversion_date) \
VALUES \
(%s, %s, '%s', %s, %s, '%s', '%s', '%s')" \
% (account_id, collateral['collateral_id'], collateral['currency_code'], collateral['change_amount'], \
collateral['amount'], collateral['reason_code'], collateral['collateral_date'], conversion_date)
stmt = dbh.cursor()
stmt.execute(sql)
dbh.commit()
stmt.close()
def get_last_profit():
stmt = dbh.cursor()
sql = "SELECT * FROM daily_profits WHERE account_id = %s ORDER BY id DESC LIMIT 1" \
% (account_id)
stmt.execute(sql)
result = stmt.fetchone()
stmt.close()
if result is None or result['final_deposit'] is None:
return 50000
else:
return result['final_deposit']
def get_profits(from_date, to_date):
stmt = dbh.cursor()
sql = "SELECT * FROM collateralhistories WHERE account_id = %s AND reason_code = 'CLEARING_COLL' AND \
conversion_date >= '%s' AND conversion_date < '%s' ORDER BY collateral_id ASC" \
% (account_id, from_date, to_date)
stmt.execute(sql)
results = stmt.fetchall()
stmt.close()
return results
def insert_profit(insert_data):
sql = "INSERT INTO daily_profits \
(account_id, profit_date, total_trades, initial_deposit, total_net_profit, \
gross_profit, gross_loss, final_deposit, yield_rate, \
profit_factor, expected_payoff, profit_rate, profit_trades, loss_trades, \
largest_profit_trade, largest_loss_trade, average_profit_trade, average_loss_trade) \
VALUES \
(%s, '%s', %s, %s, %s, \
%s, %s, %s, %s, \
%s, %s, %s, %s, %s, \
%s, %s, %s, %s)" \
% (account_id, insert_data['profit_date'], insert_data['total_trades'], insert_data['initial_deposit'], insert_data['total_net_profit'], \
insert_data['gross_profit'], insert_data['gross_loss'], insert_data['final_deposit'], insert_data['yield_rate'], \
insert_data['profit_factor'], insert_data['expected_payoff'], insert_data['profit_rate'], insert_data['profit_trades'], insert_data['loss_trades'], \
insert_data['largest_profit_trade'], insert_data['largest_loss_trade'], insert_data['average_profit_trade'], insert_data['average_loss_trade'])
stmt = dbh.cursor()
stmt.execute(sql)
dbh.commit()
stmt.close()
def sendmail(body):
msg = MIMEText(body.encode(charset), 'plain', charset)
msg['Subject'] = Header(subject, charset)
msg['From'] = from_address
msg['To'] = to_address
msg['Date'] = formatdate(localtime=True)
smtp = smtplib.SMTP(SMTP, PORT)
# smtp.connect()
smtp.ehlo()
smtp.starttls()
smtp.ehlo()
smtp.login(ADDRESS, PASSWARD)
smtp.sendmail(from_address, to_address_arr, msg.as_string())
smtp.close()
def getmailtext(data):
text = u'''本記事はプログラムによる自動投稿により作成しています。
%s のBTC自動売買トレード結果です。
■トレード結果
取引前証拠金 %s 円
総損益 %s 円
純利益 %s 円
純損失 %s 円
取引後証拠金 %s 円
■トレードデータ
利益率 %s ％
プロフィットファクター %s
期待利得 %s
■トレード内訳
総トレード回数 %s 回
勝トレード回数 %s 回
負トレード回数 %s 回
勝率 %s ％
最大勝ちトレード %s 円
最大負けトレード %s 円
平均勝ちトレード %s 円
平均負けトレード %s 円
''' % (data['profit_date'], "{:,.0f}".format(data['initial_deposit']), "{:,.0f}".format(data['total_net_profit']), \
"{:,.0f}".format(data['gross_profit']), "{:,.0f}".format(data['gross_loss']), "{:,.0f}".format(data['final_deposit']), \
"{:,.2f}".format(data['yield_rate']), "{:,.2f}".format(data['profit_factor']), "{:,.2f}".format(data['expected_payoff']), \
data['total_trades'], data['profit_trades'], data['loss_trades'], "{:,.2f}".format(data['profit_rate']), \
"{:,.0f}".format(data['largest_profit_trade']), "{:,.0f}".format(data['largest_loss_trade']), \
"{:,.2f}".format(data['average_profit_trade']), "{:,.2f}".format(data['average_loss_trade']), \
)
return text
if __name__ == '__main__':
api = bitflyerApi2.bitflyerApi(account_id)
collateralhistories = api.get_collateralhistory()
while 'Message' in collateralhistories:
collateralhistories = api.get_collateralhistory()
time.sleep(10)
collaterals = sort_collaterals(collateralhistories)
max_collateral_id = get_collaterals()
for collateral in collaterals:
if max_collateral_id is None or collateral['collateral_id'] > max_collateral_id:
insert_collateral(collateral)
from_date = target_date.strftime('%Y-%m-%d 00:00:00')
to_date = (target_date + datetime.timedelta(days=1)).strftime('%Y-%m-%d 00:00:00')
profits = get_profits(from_date, to_date)
total_trades = 0
total_net_profit = Decimal("0.00")
gross_profit = Decimal("0.00")
gross_loss = Decimal("0.00")
max_drawdown = Decimal("0.00")
profit_trades = 0
loss_trades = 0
largest_profit_trade = Decimal("0.00")
largest_loss_trade = Decimal("0.00")
max_consecutive_wins = Decimal("0.00")
max_consecutive_losses = Decimal("0.00")
for profit in profits:
total_trades = total_trades + 1
total_net_profit = total_net_profit + profit['change_amount']
if profit['change_amount'] > 0:
gross_profit = gross_profit + profit['change_amount']
profit_trades = profit_trades + 1
if largest_profit_trade < profit['change_amount']:
largest_profit_trade = profit['change_amount']
else:
gross_loss = gross_loss + profit['change_amount']
loss_trades = loss_trades + 1
if largest_loss_trade > profit['change_amount']:
largest_loss_trade = profit['change_amount']
collateral = api.get_collateral()
insert_data = {}
insert_data['profit_date'] = target_date.strftime('%Y-%m-%d')
insert_data['total_trades'] = total_trades
insert_data['initial_deposit'] = get_last_profit()
insert_data['total_net_profit'] = total_net_profit
insert_data['gross_profit'] = gross_profit
insert_data['gross_loss'] = gross_loss
insert_data['profit_trades'] = profit_trades
insert_data['loss_trades'] = loss_trades
insert_data['largest_profit_trade'] = largest_profit_trade
insert_data['largest_loss_trade'] = largest_loss_trade
insert_data['final_deposit'] = collateral['collateral']
while insert_data['final_deposit'] is None:
time.sleep(10)
collateral = api.get_collateralhistory()
insert_data['final_deposit'] = collateral
insert_data['yield_rate'] = round(Decimal(total_net_profit / insert_data['initial_deposit'] * 100), 2)
insert_data['profit_rate'] = 0
if total_trades != 0:
insert_data['profit_rate'] = round(Decimal(profit_trades / total_trades * 100), 2)
insert_data['profit_factor'] = 0
if gross_loss != 0:
insert_data['profit_factor'] = round(Decimal(gross_profit / gross_loss * -1), 2)
insert_data['expected_payoff'] = 0
if total_trades != 0:
insert_data['expected_payoff'] = round(Decimal(total_net_profit / total_trades), 2)
insert_data['average_profit_trade'] = 0
if profit_trades != 0:
insert_data['average_profit_trade'] = round(Decimal(gross_profit / profit_trades), 2)
insert_data['average_loss_trade'] = 0
if loss_trades != 0:
insert_data['average_loss_trade'] = round(Decimal(gross_loss / loss_trades), 2)
insert_profit(insert_data)
body = getmailtext(insert_data)
sendmail(body)
