How to build a Trading bot with Python and SQlite.
Tags: python, cryptocurrencies, sql, datascience
Published: 2022-02-26 18:14:58


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


In the **[ previous article](https://dev.to/shadyshafik/algorithmic-trading-a-beginners-guide-series-26km)** 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hmzmftzqy827bhq2fvjm.jpg)

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

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'

```

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

```


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

`{'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

```

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)

```

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

---

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

```


---


![BTC Price plot ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/56kp52h573gl2fiybsfj.png)

---



## 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
```

---

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.