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.
・ 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.
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)
);
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]>
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 $
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 $
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]>
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
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]>
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