Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@neomi(南 澤鎭)

Raspberry PIでBITCOINのLTP情報取得

Last updated at Posted at 2021-01-09

#概要
最近BITCOINの売買価格が高くなっていたので、BITCOINの売買情報をデータ化するため、BitflyerのAPIを通して情報を収集しようと思って今回のコードを作成しました。

#事前環境構築
・Python
・MySql
※バージョンによって大きい影響はないと思います。
下記のコマンドでインストールしました。

■基本設定
sudo apt-get install python2.7-dev python3-dev
sudo apt-get install mariadb-server-10.0
sudo apt-get install python-mysqldb
■その他設定
sudo apt-get install python-pandas
sudo apt-get install jq
※ネットのサンプルを試すため、インストールしていたパッケージです。

#テーブル作成
各項目のデータ型は仮で下記のように設定しましたので、各自変更してみてください。これでテーブルが作成できます。

create table coindata(
product_code varchar(50),
state varchar(50),
timestamp varchar(50),
tick_id varchar(50),
best_bid varchar(50),
best_ask varchar(50),
best_bid_size varchar(50),
best_ask_size varchar(50),
total_bid_depth varchar(50),
total_ask_depth varchar(50),
market_bid_size varchar(50),
market_ask_size varchar(50),
ltp varchar(50),
volume varchar(50),
volume_by_product varchar(50)
);

#テーブル作成結果
下記の操作で正しくテーブルが作成されます。

pi@RPI4-DEV:~/work/python $ sudo mysql -uroot -A
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 58
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database bitcoin;
Query OK, 1 row affected (0.00 sec)

MariaDB [bitcoin]> use bitcoin;
Database changed
MariaDB [bitcoin]> create table coindata(
    -> product_code varchar(50),
    -> state varchar(50),
    -> timestamp varchar(50),
    -> tick_id varchar(50),
    -> best_bid varchar(50),
    -> best_ask varchar(50),
    -> best_bid_size varchar(50),
    -> best_ask_size varchar(50),
    -> total_bid_depth varchar(50),
    -> total_ask_depth varchar(50),
    -> market_bid_size varchar(50),
    -> market_ask_size varchar(50),
    -> ltp varchar(50),
    -> volume varchar(50),
    -> volume_by_product varchar(50)
    -> );
Query OK, 0 rows affected (0.06 sec)

MariaDB [bitcoin]>

#ソースコード
次のコードをコピーしてファイルを作成します。

#!/usr/bin/python
# -*- coding: utf-8 -*-
import requests
import sys
import MySQLdb

###############
#### const ####
###############
host="localhost"
user="bitcoin"
passwd="bitcoin"
db="bitcoin"
###############

def insertDB(data):
    try:
        database = MySQLdb.connect (host=host, user=user, passwd=passwd, db=db, charset="utf8")
        cursor = database.cursor()
        query = """INSERT INTO coindata (product_code, state, timestamp, tick_id, best_bid, best_ask, best_bid_size, best_ask_size, total_bid_depth, total_ask_depth, market_bid_size, market_ask_size, ltp, volume,volume_by_product) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
        values = (str(data['product_code']),str(data['state']),str(data['timestamp']),str(data['tick_id']),str(data['best_bid']),str(data['best_ask']),str(data['best_bid_size']),str(data['best_ask_size']),str(data['total_bid_depth']),str(data['total_ask_depth']),str(data['market_bid_size']),str(data['market_ask_size']),str(data['ltp']),str(data['volume']),str(data['volume_by_product']))
        cursor.execute(query, values)
        cursor.close()
        database.commit()
        database.close()
    except Exception as e:
        print("#######################")
        print("# data process error. #")
        print("#######################")
        print("# Error >>>>>>>>>>>>>>>")
        print(e)
        print("#######################")
        sys.exit(1)


def main():
    headers = {
        'Accept': 'application/json',
    }
    response = requests.get('https://api.bitflyer.com/v1/ticker?product_code=BTC_JPY', headers=headers, data={})
    #print(response.text)
    data = response.json()

    if response is not None:
        print("#######################")
        print("# Api response data.  #")
        print("#######################")
        print('product_code='+ str(data['product_code']))
        print('state='+ str(data['state']))
        print('timestamp='+ str(data['timestamp']))
        print('tick_id='+ str(data['tick_id']))
        print('best_bid='+ str(data['best_bid']))
        print('best_ask='+ str(data['best_ask']))
        print('best_bid_size='+ str(data['best_bid_size']))
        print('best_ask_size='+ str(data['best_ask_size']))
        print('total_bid_depth='+ str(data['total_bid_depth']))
        print('total_ask_depth='+ str(data['total_ask_depth']))
        print('market_bid_size='+ str(data['market_bid_size']))
        print('market_ask_size='+ str(data['market_ask_size']))
        print('ltp='+ str(data['ltp']))
        print('volume='+ str(data['volume']))
        print('volume_by_product='+ str(data['volume_by_product']))
        insertDB(data);
    else:
        print("reponse is null")


if __name__ == '__main__':
    main()
pi@RPI4-DEV:~/work/python $

#実行結果
コマンドを実行すると下記のように現在の売買情報が表示されます。
表示されている各項目はDBへ登録されます。

pi@RPI4-DEV:~/work/python $ python pybitflyer1.py
#######################
# Api response data.  #
#######################
product_code=BTC_JPY
state=RUNNING
timestamp=2021-01-09T01:36:12.597
tick_id=7241936
best_bid=4154675.0
best_ask=4156000.0
best_bid_size=0.38445428
best_ask_size=0.03
total_bid_depth=1206.39330272
total_ask_depth=603.2624046
market_bid_size=0.0
market_ask_size=0.0
ltp=4154675.0
volume=190283.400721
volume_by_product=17158.1161835
pi@RPI4-DEV:~/work/python $

#DBにてテーブル情報確認
次のコマンド操作で登録されている情報の確認ができます。

pi@RPI4-DEV:~/work/python $ sudo mysql -uroot -A
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 62
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use bitcoin
Database changed
MariaDB [bitcoin]> select * from coindata;
+--------------+---------+-------------------------+---------+-----------+-----------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+-----------+---------------+-------------------+
| product_code | state   | timestamp               | tick_id | best_bid  | best_ask  | best_bid_size | best_ask_size | total_bid_depth | total_ask_depth | market_bid_size | market_ask_size | ltp       | volume        | volume_by_product |
+--------------+---------+-------------------------+---------+-----------+-----------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+-----------+---------------+-------------------+
| BTC_JPY      | RUNNING | 2021-01-09T01:36:12.597 | 7241936 | 4154675.0 | 4156000.0 | 0.38445428    | 0.03          | 1206.39330272   | 603.2624046     | 0.0             | 0.0             | 4154675.0 | 190283.400721 | 17158.1161835     |
+--------------+---------+-------------------------+---------+-----------+-----------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+-----------+---------------+-------------------+
1 rows in set (0.00 sec)

MariaDB [bitcoin]>

#CRON設定
次は上記の売買情報を1分間隔で実行してDBへ登録するようにCRONに設定します。
設定するコマンドは以下の通りです。

crontab -e

まずはプログラムの絶対パスを確認します。
※CRONの設定について詳しい情報は各自で検索してみてください。

pi@RPI4-DEV:~/work/python $ pwd
/home/pi/work/python
pi@RPI4-DEV:~/work/python $ crontab -e
pi@RPI4-DEV:~/work/python $ crontab -e
no crontab for pi - using an empty one

Select an editor.  To change later, run 'select-editor'.
  1. /bin/nano        <---- easiest
  2. /usr/bin/vim.tiny
  3. /bin/ed

Choose 1-3 [1]: 2
※初めて実行する場合は、この画面が表示されます。好きなエディタを選びます。

※エディタ画面が表示されたら一番下に次の行を追加します。
# m h  dom mon dow   command
*/1 * * * * /usr/bin/python /home/pi/work/python/pybitflyer.py

#登録データを確認
次のコマンドで1分間隔で登録されているデータが確認できます。

pi@RPI4-DEV:~/work/python $ sudo mysql -uroot bitcoin -A
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 67
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [bitcoin]> select * from coindata;
+--------------+---------+-------------------------+---------+-----------+-----------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+-----------+---------------+-------------------+
| product_code | state   | timestamp               | tick_id | best_bid  | best_ask  | best_bid_size | best_ask_size | total_bid_depth | total_ask_depth | market_bid_size | market_ask_size | ltp       | volume        | volume_by_product |
+--------------+---------+-------------------------+---------+-----------+-----------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+-----------+---------------+-------------------+
| BTC_JPY      | RUNNING | 2021-01-09T01:36:12.597 | 7241936 | 4154675.0 | 4156000.0 | 0.38445428    | 0.03          | 1206.39330272   | 603.2624046     | 0.0             | 0.0             | 4154675.0 | 190283.400721 | 17158.1161835     |
| BTC_JPY      | RUNNING | 2021-01-09T02:00:01.737 | 7277223 | 4181830.0 | 4183521.0 | 0.1782        | 0.05          | 1209.05388127   | 607.47497788    | 0.0             | 0.0             | 4182870.0 | 188553.381385 | 16853.8598802     |
| BTC_JPY      | RUNNING | 2021-01-09T02:01:01.477 | 7278952 | 4185354.0 | 4189102.0 | 0.204         | 0.05          | 1202.80743073   | 609.78078502    | 0.0             | 0.0             | 4186504.0 | 188608.251493 | 16866.3219666     |
| BTC_JPY      | RUNNING | 2021-01-09T02:02:01.51  | 7280690 | 4184065.0 | 4186520.0 | 0.1           | 0.03          | 1201.72775047   | 610.64902502    | 0.0             | 0.0             | 4184065.0 | 188603.676283 | 16867.4278812     |
| BTC_JPY      | RUNNING | 2021-01-09T02:03:00.583 | 7282149 | 4182182.0 | 4185000.0 | 0.05          | 0.2           | 1203.15019458   | 605.12780202    | 0.0             | 0.0             | 4183657.0 | 188626.810728 | 16863.1595472     |
| BTC_JPY      | RUNNING | 2021-01-09T02:04:01.443 | 7283487 | 4186501.0 | 4188169.0 | 0.107         | 0.1762        | 1203.22910919   | 606.56515624    | 0.0             | 0.0             | 4188169.0 | 188611.955945 | 16854.4726057     |
| BTC_JPY      | RUNNING | 2021-01-09T02:05:01.99  | 7284970 | 4183318.0 | 4185000.0 | 0.03          | 0.02994       | 1203.19692324   | 612.95260845    | 0.0             | 0.0             | 4184364.0 | 188625.722582 | 16854.5922007     |
| BTC_JPY      | RUNNING | 2021-01-09T02:06:01.467 | 7286247 | 4182683.0 | 4184961.0 | 0.074         | 0.01          | 1205.67149056   | 618.09582191    | 0.0             | 0.0             | 4184961.0 | 188616.57201  | 16850.0963765     |
| BTC_JPY      | RUNNING | 2021-01-09T02:07:01.627 | 7287530 | 4186482.0 | 4188998.0 | 0.03          | 0.6           | 1199.76322728   | 614.5609498     | 0.0             | 0.0             | 4186483.0 | 188612.761309 | 16855.8516827     |
| BTC_JPY      | RUNNING | 2021-01-09T02:08:02.017 | 7289554 | 4208196.0 | 4210176.0 | 0.0296        | 0.02896875    | 1199.07365715   | 584.77633435    | 0.0             | 0.0             | 4210000.0 | 188746.858826 | 16885.9375454     |
| BTC_JPY      | RUNNING | 2021-01-09T02:09:01.2   | 7291398 | 4218089.0 | 4220000.0 | 0.202         | 0.5989905     | 1203.38397034   | 578.56932313    | 0.0             | 0.0             | 4220000.0 | 188808.063154 | 16888.2721248     |
+--------------+---------+-------------------------+---------+-----------+-----------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+-----------+---------------+-------------------+
13 rows in set (0.00 sec)

MariaDB [bitcoin]>

#終わりに
今日はこれで1分間隔でBITCOINの売買情報を取得することができましたので、このデータを基に自動売買及び仮想シミュレーションを作ってみたいですね。
今日はここまでです。ありがとうございます。＾＾

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?