Below code just expands the width of the cells
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:95% !important; }</style>"))
bin_keys is a .py file that stores the API and security key
import psycopg2
import time
from binance.client import Client
import bin_keys
from datetime import datetime, timedelta
import sys
#Connects to the Binance API using the security key
client = Client(bin_keys.p_key, bin_keys.s_key)
Gets the symbol or cryptos which have the status 'Trading' and excludes the ones that are on break and stores it in the symbol_list
symbol_info = client.get_exchange_info()['symbols']
symbol_list = [i['symbol'] for i in symbol_info if i['status'] == 'TRADING']
The Cursor class allows Python code to execute PostgreSQL command in a database session. Cursors are created by the connection.cursor() method: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.
#Enter the password setup by you for the user
conn = psycopg2.connect("dbname=trading user=postgres password=*******")
cur = conn.cursor()
The below function rounds up the time to the next starting time of the candlestick. It rounds up the minute part. For example, if its 54minutes then the next start time for the candlestick is 55 minutes hence the round up time has the minute set to 55 minutes.
def nearest_minute_rounder(current_time):
minute = current_time.minute
minute_tens_position = int(minute/10)
minute_unit_position = minute % 10
if minute_unit_position in (0,1,2,3,4):
minute_unit_position = 0
else:
minute_unit_position = 5
new_minute = str(minute_tens_position) + str(minute_unit_position)
return datetime.strptime((datetime.strftime(current_time,'%d-%m-%Y %H:') + new_minute + ':00'),'%d-%m-%Y %H:%M:%S')
Below code block checks for the time interval of the preceding candlestick to be passed so that as soon as the candlestick closing time has passed we get the candlestick values for all the symbols in the symbol_list.
to_and_fro_UTC: This variable is used to convert a given time in UTC while getting the start_raw value. It is used to convert the end_raw time, which gets converted to UTC for the values within the loop, to be converted back to the original time for the first if condition to be satisfied.
start_raw & end_raw: Both are datetime objects variables.But cannot be used to extract the candlestick values as the function client.get_historical_klines() requires the time to be inserted in form of a string.
start_time & end_time : Variables converted to string using the datetime object variables 'start_raw' & 'end_raw'.
serial_id : It is a variable assigned to give a unique integer to each of candlestick values retreived for all the symbols. For example : for a given 5 minutes timeframe we get values of a 5 minute candlestick for each of the symbols in symbol_list. Hence for each of these symbols the serial_id will be the same. The next same symbols in the next 5 minute timeframe will have the next integer value assigned as the serial_id.This is to easily access the blocks or number of candlesticks retrieved.
historical_klines: Its a list that stores the candlestick values for a symbol.
conn.commit(): Makes the changes into the database.
to_and_fro_UTC = timedelta(hours = 5,minutes = 30)
start_raw = nearest_minute_rounder(datetime.now()) - to_and_fro_UTC
end_raw = start_raw + timedelta(minutes=4 ,seconds=59)
while True:
#the condition checks if the current time has crossed the closing time for the previous candlestick
if (datetime.now() > end_raw + to_and_fro_UTC):
start_time = datetime.strftime(start_raw,'%m-%d-%Y %H:%M:%S')
end_time = datetime.strftime(end_raw,'%m-%d-%Y %H:%M:%S')
klines = []
#Check for the maximum serial_id assigned in the database
cur.execute("SELECT MAX(serial_id) FROM price")
serial_id_raw = (cur.fetchall())[0][0]
if serial_id_raw is None:
serial_id = 1
else:
serial_id = int(serial_id_raw) + 1
for symbol in symbol_list:
#Get the candlestick values for 5minute intervals
historical_klines =(client.get_historical_klines(symbol,"5m",start_time, end_time))
if historical_klines == []:
count.append(symbol)
continue
else:
historical_klines = historical_klines[0]
klines.append(historical_klines)
#Enter the values into the respective columns in the database
sql_string = "INSERT INTO price(serial_id,symbol,taim,opan,high,low,claus,volume) "
sql_string += "VALUES('{}','{}','{}','{}','{}','{}','{}','{}')".format(serial_id,symbol,datetime.fromtimestamp(historical_klines[0]/1000),float(historical_klines[1]),float(historical_klines[2]),float(historical_klines[3]),float(historical_klines[4]),float(historical_klines[8]))
cur.execute(sql_string)
conn.commit()
end_raw += timedelta(minutes = 5)
start_raw += timedelta(minutes = 5)
#below code closes the cursor and the connection
cur.close()
conn.close()