Bitcoinチャートのデータベース化

MySQLサーバでBitcoinチャートのデータベース化をしてみました。

 

準備

MySQLサーバをインストールします。

Binanceのサイトでユーザ登録をしAPIキーを取得します。APIキーはjson形式で保存します。

Pythonの環境設定をします。

mkdir environments
cd environments
python3 -m venv test_env
source test_env/bin/activate

Python3のBinance APIをインストールします。

pip3 install python-binance

 

Binanceからデータを取得

BinanceからBTC/USDTチャートデータをダウンロードします。今回の例では日足で1年分でデーターをダウンロードしてCSV出力します。

get_binance_data.py

from binance.client import Client
import os
import json
import time
import csv

binance_key_json = open('../config/binance_account.json', 'r')
binance_key = json.load(binance_key_json)
api_key = binance_key["key"]
api_secret = binance_key["secret"]
client = Client(api_key, api_secret)
#filename = "binance_1m_1yr.csv"
#filename = "binance_5m_1yr.csv"
#filename = "binance_1h_1yr.csv"
filename = "binance_1d_1yr.csv"

def candlesticks():
#    klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_1MINUTE, "1 year ago UTC")
#    klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_5MINUTE, "1 year ago UTC")
#    klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_15MINUTE, "1 year ago UTC")
#    klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_1HOUR, "1 year ago UTC")
    klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_1DAY, "1 year ago UTC")
    return klines

data = candlesticks()
with open(filename, 'w') as csvfile:
    fieldnames = ['timestamp', 'open', 'high', 'low', 'close', 'volume']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    for n in data:
        timestamp = str(int(n[0] / 1000))
        writer.writerow({'timestamp' : timestamp, 'open' : n[1], 'high' : n[2], 'low' : n[3], 'close' : n[4], 'volume' : n[5]})

このスクリプトを走らせるとbinance_1h_1yr.csvというファイルができます。

ますMySQLでデータベース作成とユーザ登録をします。

sudo mysql -u root -p
Enter password:

mysql> CREATE DATABASE Binance;
mysql> use Binance;
Database changed

mysql> CREATE TABLE chart_1d (timestamp INT UNSIGNED NOT NULL UNIQUE, open FLOAT NOT NULL, high FLOAT NOT NULL, low FLOAT NOT NULL, close FLOAT NOT NULL, volume FLOAT NOT NULL);
...

mysql> SHOW tables;
+-------------------+
| Tables_in_Binance |
+-------------------+
| chart_15m         |
| chart_1d          |
| chart_1h          |
| chart_1m          |
| chart_5m          |
+-------------------+
5 rows in set (0.00 sec)

mysql> DESCRIBE chart_1d;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| Timestamp | int(10) unsigned | NO   | PRI | NULL    |       |
| Open      | float            | NO   |     | NULL    |       |
| High      | float            | NO   |     | NULL    |       |
| Low       | float            | NO   |     | NULL    |       |
| Close     | float            | NO   |     | NULL    |       |
| Volume    | float            | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> SELECT USER,HOST FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (1.14 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON Binance.* TO 'user'@'localhost' IDENTIFIED BY 'pass';
Query OK, 0 rows affected, 1 warning (0.56 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (1.48 sec)

mysql> exit

先程のbinance_1h_1yr.csvというファイルをchart_1d.csvにリネームしてMySQLに読み込ませます。

sudo -s
# csvfile='chart_1d.csv'
# mysqlimport --ignore-lines=1\
             --fields-terminated-by=,\
             --local -u root -p\
             Binance \
             $csvfile

ユーザー名でMySQLにログインします。

mysql -u user -p
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Binance            |
+--------------------+
2 rows in set (0.12 sec)

mysql> USE Binance;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database change

mysql> SHOW tables;
+-------------------+
| Tables_in_Binance |
+-------------------+
| chart_15m         |
| chart_1d          |
| chart_1h          |
| chart_1m          |
| chart_5m          |
+-------------------+
5 rows in set (0.03 sec)

mysql> DESCRIBE chart_1d;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| Timestamp| int(10) unsigned | NO   |     | NULL    |       |
| Open     | float            | NO   |     | NULL    |       |
| High     | float            | NO   |     | NULL    |       |
| Low      | float            | NO   |     | NULL    |       |
| Close    | float            | NO   |     | NULL    |       |
| Volume   | float            | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
6 rows in set (0.13 sec)

mysql> SELECT * FROM chart_1d;
+------------+---------+---------+---------+---------+---------+
| Timestamp  | Open    | High    | Low     | Close   | Volume  |
+------------+---------+---------+---------+---------+---------+
| 1519084800 | 11147.1 |   11786 | 11100.6 |   11201 | 48153.4 |
...

...
| 1550275200 | 3602.49 |  3648.2 | 3597.91 | 3618.41 |   19566 |
| 1550361600 | 3617.22 | 3700.11 |  3604.4 | 3667.58 | 25690.2 |
| 1550448000 | 3667.62 |    3925 |    3655 |  3898.6 | 64042.7 |
| 1550534400 | 3897.35 | 3974.98 |    3856 |  3912.9 |   18106 |
+------------+---------+---------+---------+---------+---------+
365 rows in set (0.16 sec)

チャート情報が出力できていれば完了です。

 

mysql-connector-pythonのインストール

pipでインストールします。

pip3 install mysql-connector-python

簡単なスクリプトを使ってテストしてみます。去年の3月のチャート情報を出力してみます。
test.py

import time
import datetime
import mysql.connector
from contextlib import closing

dbname  = 'Binance'
table   = 'chart_1d'
t_from  = (2018,3,1)
t_to    = (2018,3,31)

_d = datetime.date(t_from[0], t_from[1], t_from[2])
utime_from = time.mktime(_d.timetuple())
_d = datetime.date(t_to[0], t_to[1], t_to[2])
utime_to = time.mktime(_d.timetuple())

def db_read(dbname):
    with closing(mysql.connector.connect(user = 'user', password = 'pass', host = 'localhost', database = dbname)) as conn:
        c = conn.cursor()
        query = "select * from " + str(table) + " where Unixtime > " + str(utime_from) + " and Unixtime < " + str(utime_to) + ";"
        c.execute(query)
        for row in c.fetchall():
            print(row[0], row[1], row[2], row[3], row[4], row[5])

db_read(dbname)

結果:

1519948800 10923.4 11200.0 10770.0 11039.0 23910.7
1520035200 11039.0 11544.0 11015.0 11464.5 21287.2
1520121600 11464.5 11565.0 11050.0 11515.0 17295.9
1520208000 11515.0 11710.0 11415.0 11454.0 15144.2
1520294400 11455.0 11455.0 10555.5 10716.5 29515.6
1520380800 10716.5 10899.0 9389.31 9910.0 50647.7
1520467200 9910.0 10099.0 9060.0 9271.64 41109.5
1520553600 9267.07 9410.0 8329.0 9227.0 64112.3
1520640000 9230.0 9490.0 8667.07 8770.22 37180.0
1520726400 8770.22 9740.0 8450.0 9533.57 44326.0
1520812800 9533.57 9888.88 8780.0 9131.34 42230.8
1520899200 9131.34 9474.0 8823.0 9150.0 40191.4
1520985600 9151.92 9333.78 7900.28 8170.0 49708.1
1521072000 8184.01 8430.0 7650.0 8240.98 52291.0
1521158400 8240.98 8611.64 7900.0 8260.0 38815.4
1521244800 8260.0 8348.62 7721.99 7824.8 33110.2
1521331200 7824.01 8317.4 7322.0 8189.99 59488.2
1521417600 8189.0 8705.23 8088.4 8600.0 55297.1
1521504000 8595.01 9050.0 8280.0 8909.98 44865.1
1521590400 8909.96 9177.01 8750.6 8885.0 39972.4
1521676800 8884.82 9100.0 8465.1 8722.9 40617.6
1521763200 8720.0 8909.0 8269.0 8898.03 39991.0
1521849600 8898.04 8999.95 8491.0 8546.86 35466.6
1521936000 8531.25 8669.85 8365.77 8470.15 29001.8
1522022400 8470.14 8514.89 7831.0 8134.23 44033.6
1522108800 8134.22 8215.94 7730.0 7795.51 37427.6
1522195200 7795.51 8109.0 7728.0 7949.3 26401.3
1522281600 7949.3 7975.0 6941.11 7090.14 54620.9
1522368000 7090.16 7292.43 6600.1 6840.23 65306.0

 

デーベースのアップデート

調べながら作ってみました。
update_binance_database.py

#!/usr/bin/env python3

from binance.client import Client
import os
import sys
import re
import json
import time
import datetime
import mysql.connector
from contextlib import closing
from config.parm_binance import parm_binance, parm_binance_db

def candlesticks(client, tables):
    dict = {}
    for k in tables:
        if tables[k] == 'chart_1d':
            klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_1DAY, "2 day ago UTC")
        elif tables[k] == 'chart_4h':
            klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_4HOUR, "2 day ago UTC")
        elif tables[k] == 'chart_1h':
            klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_1HOUR, "1 day ago UTC")
        elif tables[k] == 'chart_15m':
            klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_15MINUTE, "1 day ago UTC")
        elif tables[k] == 'chart_5m':
            klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_5MINUTE, "1 day ago UTC")
        elif tables[k] == 'chart_1m':
            klines = client.get_historical_klines("BTCUSDT", Client.KLINE_INTERVAL_1MINUTE, "1 day ago UTC")
        dict.update({k : klines})
    return dict

def update(dbname, tables, db_user, db_pass, key, value):
    with closing(mysql.connector.connect(user = db_user, password = db_pass, host = 'localhost', database = dbname)) as conn:
        c = conn.cursor()
        for n in value:
            unixtime = str(int((n[0] / 1000)))
            query = str(unixtime) + ',' + str(n[1]) + ',' + str(n[2]) + ',' + str(n[3]) + ',' + str(n[4]) + ','  + str(n[5])
            query2 = "Open=" + str(n[1]) + ',' + "High=" + str(n[2]) + ',' + "Low=" + str(n[3]) + ',' + "Close=" + str(n[4]) + ','  + "Volume=" + str(n[5])
            action = ("INSERT INTO " + str(tables[key]) + " (Unixtime, Open, High, Low, Close, Volume) VALUES (" + str(query) +
                     ") ON DUPLICATE KEY UPDATE " + str(query2) + ";")
            c.execute(action)
        conn.commit()

def check_db(dbname, tables, db_user, db_pass, line_num):
    line_num = line_num + 2
    with closing(mysql.connector.connect(user = db_user, password = db_pass, host = 'localhost', database = dbname)) as conn:
        c = conn.cursor()
        for key, tb in tables.items():
            action = 'SELECT * FROM ( SELECT * FROM ' + str(tb) + ' ORDER BY Unixtime DESC LIMIT ' + str(line_num) + ') sub ORDER BY Unixtime ASC;'
            c.execute(action)
            _diff1 = int()
            _diff2 = int()
            _t_row0 = int()
            print("\n^" + str(tb))
            for row in c.fetchall():
                if _diff1 != 0 and _diff2 != 0:
                    print(_t_row0, time.ctime(row[0]), 'BTC CLOSE:', row[4])
                _diff2 = _diff1
                _diff1 = row[0]
                _t_row0 = _diff1 - _diff2
    print('')

def read_cmdline():
    arg = str()
    if len(sys.argv) > 1:
        for n in range(1, len(sys.argv)):
            if re.match( r'check|help', sys.argv[n], re.M|re.I):
                arg = sys.argv[n]
        else:
            pass
    else:
        pass
    if arg == 'help':
        help()
        exit(0)
    return arg

def help():
    print("")
    print("Command : python3 update_binance_database.py [arg]")
    print("")
    print("    check      ---   Display latest 25 lines")
    print("    help       ---   Display this help and exit")
    print("")

### MAIN ###
def main():
    binance_config    = parm_binance()
    binance_config_db = parm_binance_db()
    os.environ['TZ']  = binance_config['timezone']
    time.tzset()
    binance_key_json  = open('./config/binance_account.json', 'r')
    binance_key       = json.load(binance_key_json)
    api_key           = binance_key["key"]
    api_secret        = binance_key["secret"]
    client            = Client(api_key, api_secret)
    dbname            = binance_config_db['database']
    tables            = binance_config_db['tables']
    db_user           = binance_config_db['db_user']
    db_pass           = binance_config_db['db_pass']
    line_num          = 25

    arg = read_cmdline()
    if arg == '':
        data = candlesticks(client, tables)
        for key, value in data.items():
            print('{:>5}'.format(key), 'updating...', end='')
            update(dbname, tables, db_user, db_pass, key, value)
            print('[done]')

        print("\n", 'Update completed successfully.', "\n")
    elif arg == 'check':
        check_db(dbname, tables, db_user, db_pass, line_num)

if __name__ == "__main__":
    main()

 

その他

Backup

mysqldump -u root -p --add-drop-table Binance > Binance.sql

Restore

mysql -u root -p Binance < Binance.sql

Passwordの設定

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.03 sec)

mysql> SET GLOBAL validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 4     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

以上です。

チャートのデータベース化のメリット

アルゴリズムの計算に大量のデータをサイトから引張ってきましたが、データベースの更新に必要なだけで済むのでネットワーク負荷がかなり押さえられます。しかし最大のメリットはシュミレーションが出来るようになります。試しに10日分をシュミレーションしてみた結果です。

./report_all.py
^
 DB name      : binance_test0.db
 Action       : profit
 From         : 00:02:00 01/01/19 JST
 TO           : 21:33:00 01/09/19 JST
 Transactions :   202
 Price(high)  :  4017.55
 Price(low)   :  3671.58
 Income       :  -329.13
 Fee          :   582.72
 Profit       :  -911.85

昨日までの一日分

./report_all.py
^
 DB name      : binance_test0.db
 Action       : profit
 From         : 00:12:00 02/19/19 JST
 TO           : 23:45:00 02/19/19 JST
 Transactions :    15
 Price(high)  :  3936.03
 Price(low)   :  3801.62
 Income       :    90.41
 Fee          :    43.73
 Profit       :    46.68

一月分

^
 DB name      : binance_test0.db
 Action       : profit
 From         : 00:03:00 01/19/19 JST
 TO           : 23:45:00 02/19/19 JST
 Transactions :   665
 Price(high)  :  3936.03
 Price(low)   :  3383.00
 Income       :   -25.25
 Fee          :  1769.37
 Profit       : -1794.62

部分的には利益がだせますがトータルではかなりの損失です。

追記:

チャートのデータベース化してシュミレーションをするとかなりCPUパワーとリソースを使います。メモリーが十分(16GB以上)あれば幾分緩和されますがそれでも不具合が生じます。MySQLで次のようなエラーがでました。

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

このエラーは何らかの原因で接続に問題が起こりタイムアウトエラーを起こしたときのエラーです。解決方法は2つあり、ひとつはMySQLの設定ファイルに次のように追加します。

[mysqld]
connect_timeout = 100

もう一つは/etc/hosts.allowに次のように追加します。

mysql: ALL: allow