Get BITCOIN LTP information with Raspberry PI

Overview

Since the trading price of BITCOIN has been high recently, I created this code with the intention of collecting information through the API of Bitflyer in order to digitize the trading information of BITCOIN.

Pre-environment construction

・ Python ・ MySql

 ■ Basic settings
sudo apt-get install python2.7-dev python3-dev
sudo apt-get install mariadb-server-10.0
sudo apt-get install python-mysqldb
 ■ Other settings
sudo apt-get install python-pandas
sudo apt-get install jq
 * This is the package that was installed to try the net sample.

Create table

The data type of each item is tentatively set as follows, so please change it yourself. Now you can create the table.

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)
);

Table creation result

The table will be created correctly by the following operation.

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]>

Source code

Copy the following code to create the file.

!/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 $

Execution result

When you execute the command, the current trading information will be displayed as shown below. Each displayed item is registered in the 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 $

Check table information in DB

You can check the registered information by the following command operation.

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 setting

Next, set CRON to execute the above trading information at 1-minute intervals and register it in the DB. The command to set is as follows.

crontab -e

First, check the absolute path of the program.

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
 * This screen will be displayed when you execute for the first time. Choose your favorite editor.

 * When the editor screen is displayed, add the following line at the bottom.
 m h  dom mon dow   command
*/1 * * * * /usr/bin/python /home/pi/work/python/pybitflyer.py

Check registration data

You can check the registered data at 1-minute intervals with the following command.

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]>

At the end

Today, I was able to get BITCOIN trading information at 1-minute intervals, so I would like to create automatic trading and virtual simulations based on this data. Today is up to here. Thank you very much. ^^

Recommended Posts

Get BITCOIN LTP information with Raspberry PI
Get CPU information of Raspberry Pi with Python
GPGPU with Raspberry Pi
DigitalSignage with Raspberry Pi
Get temperature and humidity with DHT11 and Raspberry Pi
Get Alembic information with Python
[Raspberry Pi] Stepping motor control with Raspberry Pi
Get video file information with ffmpeg-python
Use vl53l0x with Raspberry Pi (python)
Servo motor control with Raspberry Pi
Serial communication with Raspberry Pi + PySerial
OS setup with Raspberry Pi Imager
Try L Chika with raspberry pi
VPN server construction with Raspberry Pi
Try moving 3 servos with Raspberry Pi
Using a webcam with Raspberry Pi
Get weather information with Python & scraping
Get US stock price from Python with Web API with Raspberry Pi
Get weather information using Yahoo! Open Local Platform (YOLP) and let Raspberry Pi talk with AquesTalkPi
Get property information by scraping with python
Measure SIM signal strength with Raspberry Pi
Pet monitoring with Rekognition and Raspberry pi
Hello World with Raspberry Pi + Minecraft Pi Edition
Build a Tensorflow environment with Raspberry Pi [2020]
Try fishing for smelt with Raspberry Pi
Programming normally with Node-RED programming on Raspberry Pi 3
Improved motion sensor made with Raspberry Pi
Try Object detection with Raspberry Pi 4 + Coral
Power SG-90 servo motor with raspberry pi
Working with sensors on Mathematica on Raspberry Pi
Use PIR motion sensor with raspberry Pi
Make a wash-drying timer with a Raspberry Pi
Infer Custom Vision model with Raspberry Pi
Operate an oscilloscope with a Raspberry Pi
[Python] Get Python package information with PyPI API
Create a car meter with raspberry pi
Get coincheck virtual currency information with API ♪
Inkbird IBS-TH1 value logged with Raspberry Pi
Working with GPS on Raspberry Pi 3 Python
Get GrovePi + sensor value with Raspberry Pi and store it in kintone
Discord bot with python raspberry pi zero with [Notes]
Get the latest AMI information with the AWS CLI
Media programming with Raspberry Pi (preparation for audio)
I tried L-Chika with Raspberry Pi 4 (Python edition)
Raspberry Pi backup
Enjoy electronic work with GPIO on Raspberry Pi
MQTT RC car with Arduino and Raspberry Pi
Power on / off your PC with raspberry pi
Use Majoca Iris elongated LCD with Raspberry Pi
CSV output of pulse data with Raspberry Pi (CSV output)
Observe the Geminids meteor shower with Raspberry Pi 4
Play with your Ubuntu desktop on your Raspberry Pi 4
Stock investment analysis app made with Raspberry Pi
Logging Inkbird IBS-TH1 mini values with Raspberry Pi
Connect to MySQL with Python on Raspberry Pi
Python script to get note information with REAPER
GPS tracking with Raspberry Pi 4B + BU-353S4 (Python)
Measure CPU temperature of Raspberry Pi with Python
Record temperature and humidity with systemd on Raspberry Pi
Machine learning with Raspberry Pi 4 and Coral USB Accelerator
Run LEDmatrix interactively with Raspberry Pi 3B + on Slackbot