はじめに

ビットコインの特徴の一つ、コインの値段は取引所によって違う。
それぞれの取引所の需要と供給によってコインの値段が決まるからだ。

仮説

取引所の中で最も安く売っている所でコインを購入し、
最も高く売買しているところへ送金し現金化すればアービトラージを得ることができるのではないか。

法的にOKなのか、NGなのかグレーなのかはここでは問わない。
とりあえず、データを参照し、差分を確認するところまでをここでのスコープとする。

方針

取引所で取り扱っている通貨の値段を知り、どれだけ乖離されているか知る事が何よりも重要である。
幸いビットコインの取引所の多くは通貨や取引APIを公開している。
それを定期的に取ってきて可視化した。

この作業は重要ではあるが、あまり時間をかけたくない。
なんとかうまくサックリできないかを考え、この作りになった。

まずはCoincheck。日本でメジャーな仮想通貨取引所である。
もう一つはBithumb。韓国で最も大きい仮想通貨取引所。
まずはこの２つで差分を取ることからはじめた。

データの保存先

データを格納する箱。
今回はMariaDBに格納することにした。

yum install mariadb mariadb-server -y
rpm -qa | grep maria

# MariaDBの起動
systemctl start mariadb
systemctl enable mariadb

# ログイン (ノーパスで入れる)
mysql -uroot -p
MariaDB [(none)]> 
MariaDB [(none)]> create database coin;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>

アクセス権

dockerでre:dashを動かし、サーバ上のmysqlに接続となると、
接続元(re:dash)のIPはDocker bridgeを経由する関係で
localhost(127.0.0.1)ではなくなる。

172.18.0.*はdockerのコンテナのデフォルトのセグメント。
パスワードはpasswordとする場合、ユーザーの設定手順は以下の通り

MariaDB [(none)]> SELECT PASSWORD('password');
+-------------------------------------------+
| PASSWORD('password')                      |
+-------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> GRANT USAGE ON *.* TO 'redash'@'172.18.0.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT, EXECUTE ON `coin`.* TO 'redash'@'172.18.0.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user,host from mysql.user;
+--------+------------+
| user   | host       |
+--------+------------+
| root   | 127.0.0.1  |
| redash | 172.18.0.% |
| root   | ::1        |
|        | instance-1 |
| root   | instance-1 |
|        | localhost  |
| root   | localhost  |
+--------+------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> show grants for 'redash'@'172.18.0.%';

テーブルの作成

MariaDB [(none)]> use coin;
Database changed
MariaDB [coin]>

COINCHECK_JPY_BTC用

名前	型	用途
price	int(10)	CoincheckでのBTCの値段
creation_time	timestamp	登録した時間

CREATE TABLE `COINCHECK_JPY_BTC` (
  `price` int(10) unsigned NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`price`,`creation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

BITHUMB_JPY_BTC用

Bitthumbは韓国の取引所なので、売買単価が韓国ウォンになる。
本テーブルに登録する前に日本円に変換したものを入れるようにする。

名前	型	用途
price	int(10)	BITHUMBでのBTCの値段(JPY)
creation_time	timestamp	登録した時間

CREATE TABLE `BITHUMB_JPY_BTC` (
  `price` int(10) unsigned NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`price`,`creation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

データの取得

ただ取ってくるだけだからシェルで良い。
Cronで定期的に最新の値を取得し、sql(insert文)を作る。

Coincheck用

coin/script/COINCHECK_JPY_BTC.sh

# !/bin/sh

TABLE=COINCHECK_JPY_BTC
PRICE=`curl -s https://coincheck.com/api/rate/btc_jpy | jq -r .rate | awk -F'.' '{print $1}'`

echo "insert into $TABLE(price) values($PRICE);" > sql/$TABLE.sql;

Bithumb用
DBに格納する通貨を日本円に統一するために、為替データを取得する必要がある。
クジラWeb APIさんが公開してくれていたので、そのデータを元に日本円に換算した。

coin/script/BITHUMB_JPY_BTC.sh

# !/bin/sh

TABLE=BITHUMB_JPY_BTC
ORIGINALPRICE=`curl -s https://api.bithumb.com/public/ticker/BTC | jq -r '.data.buy_price'`
RATE=`curl -s http://api.aoikujira.com/kawase/json/krw | jq -r '.JPY'`

PRICE=`echo "scale=0 ; $ORIGINALPRICE * $RATE" | /usr/bin/bc`


echo "insert into $TABLE(price) values(${PRICE%.*});" > sql/$TABLE.sql;

データの投入

MySQL(厳密にはMariaDB)に出来たsqlを定期的に実行し、
DBに最新データを格納する

jqコマンド,bcコマンドを利用するのでjqコマンドをインストールする

curl -o /usr/bin/jq -L https://github.com/stedolan/jq/releases/download/jq-1.5/jq-linux64 && chmod +x /usr/bin/jq

yum install bc -y

coin/master/exec.sh

echo "insert...."

for i in `ls sql`; do
	echo sql/$i
	mysql -uroot coin < sql/$i
	sleep 5;
done 
echo "insert....done"

当然パスワード入れるべきなのだが、とりあえずここではノンパスとした。

可視化

Dockerでre:dashコンテナを持ってきて可視化した。

# docker install
yum -y install docker-io
systemctl start docker
systemctl enable docker

# docker compose install
curl -L https://github.com/docker/compose/releases/download/1.18.0/docker-compose-`uname -s`-`uname -m` -o /usr/local/bin/docker-compose
chmod +x /usr/local/bin/docker-compose
docker-compose --version

# re:dash container 
git clone https://github.com/getredash/redash
cd redash/
docker-compose -f docker-compose.production.yml run --rm server create_db
docker-compose -f docker-compose.production.yml up -d

http://[サーバのIP]/setup
にアクセスし、re:dashのセットアップ

アクセスできない場合はFirewallやselinuxなどの設定を見直す。

データセットの登録

可視化するデータベースを登録する
注意：dockerでre:dashを動かしているサーバを、可視化対象のサーバが同一の場合でも
Docker bridgeを経由するためlocalhostで接続しても接続できない。

View の登録

Create queryで登録する

クエリを書く

Visualizationdes線グラフを描くようにする

上記で取引所のデータを元にグラフ化することが成功
同じように他の通貨も登録する

差分を計算する

取引所と取引所の差分を計算する

DBに格納されているデータBithumbとcoincheckを元に
同じ時間帯でどれだけ乖離されているか計算をする

select b1.price as BITHUMB_JPY_BTC , 
    c1.price as COINCHECK_JPY_BTC , 
    b1.price / c1.price as percent ,
    c1.creation_time
    
from BITHUMB_JPY_BTC as b1 
left join COINCHECK_JPY_BTC as c1
on b1.creation_time >= c1.creation_time
where b1.creation_time between '2018-01-11 10:55' and '2120-01-11 12:55'
order by creation_time desc 
limit 1000

(もっとうまくSQLを書けるはず・・・、いい書き方あればご教示ください)

Dashboardsを登録する

Dashboardsは複数のクエリを見やすく1枚のページにまとめたもの。
取引所のチャートと、乖離情報を描写する

乖離情報はローソク棒で表示した。
（もっとうまい方法があるはずだが、とりあえずここではこれで進めた）

17.5% ~ 19%程 CoincheckよりBithumbのが高い値段で売買されていると言える。
このようなグラフを複数登録し、
もっとも乖離の大きい通貨で他の取引所へ送金し、
もっとも乖離の少ない通貨で戻せば、理論上アービトラージを稼げるはず。

※実際に取引については自己責任でお願いします。

今後やりたいこと

現在はただ知るのみだが、
redashに備わっているAlert機能を使い、監視設定をすることができるはずなので、その機能も設定。
https://qiita.com/bitrinjani/items/3ed756da9baf7d171306
の記事のように自動で取引できるようにしたい