DEV Community

shady shafik
shady shafik

Posted on • Edited on

How to build a Trading bot with Python and SQlite.

#python #cryptocurrencies #sql #datascience

Hello there and welcome to another article about algorithmic trading with python.

In the previous article I gave a light introduction to algorithmic trading, and we developed a simple trading bot using Binance API to get data and execute trades.

Today we will develop another trading bot, with the use of SQlite to save data and process this data to execute trades.

We Start By connecting with Binance and Socket Manager

Websocket connection

A WebSocket is a communication protocol which is a bidirectional connection full duplex communication channel between a client's web browser and a server.

I am working with Jupyter notebook, python and sqlite.

import pandas as pd !pip install python-binance from binance import Client from binance import BinanceSocketManager
Enter fullscreen mode Exit fullscreen mode

Import pandas, install python binance, import client and socket manager.

APIKEY= 'Your API Key' SECRETKEY = 'Your Secret Key' client = Client(APIKEY,SECRETKEY) client.API_URL = 'https://testnet.binance.vision/api'
Enter fullscreen mode Exit fullscreen mode

get an API KEY and SECRET KEY, provide the two to the client function to establish a connection.

*client.API_URL = *, here we provide the Binance Testnet url to work with test net environment.

to generate an API key and secret key go to Binance Spot Test Network sign with GitHub and generate HMAC SHA256 key.
finally, replace APIKEY and SECRETKEY variables in the code with actual keys.

from sqlalchemy import create_engine engine = create_engine('sqlite:///SqlDb.db')
Enter fullscreen mode Exit fullscreen mode

import create_engine from sqlalchemy ( Python SQL toolkit and Object Relational Mapper ), then define new database sqlDB.db.

Getting Data with Binance socket manager

I'll start by testing binance socket manager connection by print the received data.

 bsm = BinanceSocketManager(client) ts = bsm.trade_socket('BTCUSDT') await ts.__aenter__() msg = await ts.recv() print(msg)
Enter fullscreen mode Exit fullscreen mode

{'e': 'trade', 'E': 1645890024494, 's': 'BTCUSDT', 't': 1271100538, 'p': '39317.92000000', 'q': '0.00051000', 'b': 9560827435, 'a': 9560827538, 'T': 1645890024493, 'm': True, 'M': True}

Now we can see what we'll receive, it's a dictionary with 's' as
a currency symbol, 'p' as price and 'E' as time ( in unix format), these three are what we care about.

Next step is getting a stream of real time data, then send these data to a function in order to produce a dataFrame, and we'll start with the latter.

def dframe(msg): df = pd.DataFrame([msg]) df = df.loc[:,['s','E','p']] df.columns = ['Symbol','Time','Price'] df.Price = df.Price.astype(float) df.set_index('Time') return df
Enter fullscreen mode Exit fullscreen mode

I defined a function wich will take the received dictionary, convert it to pandas dataframe slice the Symbol,Time and price.

while True: bsm = BinanceSocketManager(client) ts = bsm.trade_socket('BTCUSDT') await ts.__aenter__() msg = await ts.recv() frame = dframe(msg) frame.to_sql('BTCUSDT',engine,if_exists='append',index=False) print(frame)
Enter fullscreen mode Exit fullscreen mode

in order to get a stream of data, I'll request data in an endless loop, after getting the msg dictionary it will be sent to *dframe * function to update the data frame.

Then to_sql() Write records stored in a DataFrame to a SQL database.

and that's this is the output of the endless loop, a dataframe updated every second with recieved data, and write records to BTCUSDT table in SQl database.

 Symbol Time Price 0 BTCUSDT 1645891574400 39268.11 Symbol Time Price 0 BTCUSDT 1645891576796 39268.89 Symbol Time Price 0 BTCUSDT 1645891578340 39262.33 Symbol Time Price 0 BTCUSDT 1645891579856 39262.33 Symbol Time Price 0 BTCUSDT 1645891581334 39262.32 Symbol Time Price 0 BTCUSDT 1645891582679 39250.4 Symbol Time Price 0 BTCUSDT 1645891584140 39250.4 Symbol Time Price 0 BTCUSDT 1645891585365 39250.39 Symbol Time Price 0 BTCUSDT 1645891586707 39258.25 Symbol Time Price 0 BTCUSDT 1645891587986 39262.4 Symbol Time Price 0 BTCUSDT 1645891589897 39262.4 Symbol Time Price 0 BTCUSDT 1645891591257 39262.39 Symbol Time Price 0 BTCUSDT 1645891593257 39253.68 Symbol Time Price 0 BTCUSDT 1645891594699 39253.68 Symbol Time Price 0 BTCUSDT 1645891596403 39253.68 Symbol Time Price 0 BTCUSDT 1645891597752 39253.68 Symbol Time Price 0 BTCUSDT 1645891599107 39253.69 Symbol Time Price
Enter fullscreen mode Exit fullscreen mode

let's make a plot to visualize btc price.

import matplotlib.pyplot as plt df = pd.read_sql('BTCUSDT',engine) plt.figure(figsize=(15, 8)) plt.plot(df.Price,color='red',label = "Price", linewidth = 2, marker='*', markerfacecolor='black',markersize=9) plt.title('BTC Price', color='black',size=20) plt.ylabel('Price', color='blue',size=16) plt.xlabel('no of records', color='blue',size=16) plt.legend()
Enter fullscreen mode Exit fullscreen mode

BTC Price plot

Trend Following Strategy

I'll define a simple enough trend following strategy, which is Buy if the price increased with a percentage(0.1%), and Sell if certain percentage up or down (+0.2% , -0.2%) is reached.

def strategy(in_position=False): percen = 0.001 periods = 100 while True: df = pd.read_sql('BTCUSDT', engine) df1 = df.tail(periods) cumret = (df1.Price.pct_change() + 1).cumprod() - 1 if not in_position: if cumret[cumret.last_valid_index()] > percen: buyOrder = client.create_order(symbol='BTCUSDT', side='BUY', type='MARKET' , quantity=0.001) print(buyOrder) in_position = True break if in_position: while True: df = pd.read_sql('BTCUSDT', engine) orderPrice = df.loc[df.Time > buyOrder['transactTime']] if len(orderPrice) > 1 : postionRet = (orderPrice.Price.pct_change() + 1).cumprod() - 1 lastRet = postionRet[postionRet.last_valid_index()] if lastRet > 0.002 or lastRet < -0.002: sellOrder = client.create_order(symbol='BTCUSDT' , side='SELL' , type='MARKET' , quantity=0.001 ) print(sellOrder) break
Enter fullscreen mode Exit fullscreen mode

start with calculating the last no of records to calculate the cumulative return, and when the cumulative return exceeds certain percentage then Buy order will be triggered, then break the while loop.

The buy order will return a dictionary with transaction Time so, we'll slice the dataframe from this time and wait till a cumulative return of the sliced dataframe exceeds or go below sell percentage, then a sell order will be triggered.

Time To Execute a Trade...

it's time to call the strategy function.

strategy()

we get a Buy order filled at price 39166.91

Buy order

{'symbol': 'BTCUSDT', 'orderId': 10940581, 'orderListId': -1, 'clientOrderId': 'XCAVbax0xaQxRs0erpds5P', 'transactTime': 1645895549187, 'price': '0.00000000', 'origQty': '0.00100000', 'executedQty': '0.00100000', 'cummulativeQuoteQty': '39.16691000', 'status': 'FILLED', 'timeInForce': 'GTC', 'type': 'MARKET', 'side': 'BUY', 'fills': [{'price': '39166.91000000', 'qty': '0.00100000', 'commission': '0.00000000', 'commissionAsset': 'BTC', 'tradeId': 3562682}]}

we get a Sell order filled at price 39097.37

Sell order

{'symbol': 'BTCUSDT', 'orderId': 10945890, 'orderListId': -1, 'clientOrderId': 'txnRsAuc1gcMs3aRiQ6YGc', 'transactTime': 1645896773538, 'price': '0.00000000', 'origQty': '0.00100000', 'executedQty': '0.00100000', 'cummulativeQuoteQty': '39.09737000', 'status': 'FILLED', 'timeInForce': 'GTC', 'type': 'MARKET', 'side': 'SELL', 'fills': [{'price': '39097.37000000', 'qty': '0.00100000', 'commission': '0.00000000', 'commissionAsset': 'USDT', 'tradeId': 3564175}]}

We have a trade with about 0.18 loss, the strategy is working and of course if you know about trading you won't be surprised about the loss because you can't win each time you just try to have a good winning ratio and the overall will result a profit.

What's next ?!

You can optimize this bot by changing the parameters or manipulate the strategy which will make it better.

I will share more in the upcoming articles

and that's it for this article, I hope you the best.

Top comments (0)