Load Binance Data using API and Python for SQL Server Analysis

By: Levi Masonde   |   Updated: 2023-05-30   |   Comments   |   Related: > Python

Problem

If you want to create trading strategies you can easily download a CSV/Excel sheet from websites like Yahoo Finance. But this data is static and requires constant downloads of new data and uploading it to your trading strategy. Is there another way to do this?

Solution

In this article, we look at how to use the Binance API to access a live stream of Crypto trades. The trade information can be stored in a SQL database to run your strategies against the database instead of static data.

Prerequisites

• Binance account
• Python 3.10.9
• SQL Server Instance
• Windows 10/ Server 2019
• Visual Studio Code

Preparing the Database

Create a database to store trade information such as trade times, closes, openings, lows, and highs. We will use SQLCMD to run the SQL scripts, but you can use SSMS or any other tool.

Open a Windows command prompt and connect to your SQL Server instance using SQLCMD and create a database:

sqlcmd -S PC_NAME\SQL_INSTANCE -E
CREATE DATABASE BinanceDB;
GO

After creating the database, use the following commands to create a table:

Use BinanceDB;
GO
CREATE TABLE TradesTable(ID int NOT NULL PRIMARY KEY);
GO

Great work! You have prepared a database to store data you will obtain from the Binance API.

Python Code

You will need to create a Python connection string using SqlAlchemy to connect to the database.

Use the pip command, pip install sqlalchemy, to install SQLAlchemy to your environment.

Create a Python file named ConnString.py and add the code shown below and update as needed.:

import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
 
conn = urllib.parse.quote_plus(
    'Data Source Name=KNITNETDS;'
    'Driver={SQL Server};'
    'Server=PC_NAME\SQL_INSTANCE'
    'Database=BinanceDB;'
    'Trusted_connection=yes;'
)
 
try:
   
    connEngine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
    print("Passed")
 
except:
    print("failed!")

Binance API Keys

To get a Binance API key, follow these steps:

Step 1: Log in to your Binance account.

Step 2: Click on your profile icon in the top right corner of the page and select "API Management" from the dropdown menu.

Step 3: Click on "Create API" to create a new API key.

Step 4: Enter a name for your API key and click on "Next."

Step 5: You will be prompted to enter your two-factor authentication code.

Step 6: After verifying your two-factor authentication, you will be shown your API key and secret key. Copy and save these keys in a safe and secure place.

Note: API keys grant full access to your Binance account, including the ability to trade and withdraw funds. It's essential to keep your API keys safe and secure. Additionally, be cautious when granting access to third-party applications that request your API keys.

Build Software and Store Your Data Using Python

Once you have your API keys, you can build the software to store data in your SQL instance using Python.

First, create a Python file named BinanceApiKeys.py and store your API and secret keys as strings.

API_Key     = "YourApiKey"
Secret_Key  = "YourSecretKey"

The Binance API can be accessed using the Binance Python client. The Binance Python client is a wrapper around the Binance API, making interacting with Binance's cryptocurrency trading platform easy.

To use the Binance Python client, you need to install the python-binance library. You can install it using pip:

pip install python-binance

After installing the module, create a Python file named MainApp.py and add the code shown below:

import BinanceApiKeys
from binance.client import Client
import pandas as pd
import asyncio
from asyncio.windows_events import SelectorEventLoop
from pandas.core.indexes.timedeltas import timedelta_range
import numpy as np
Import talib
from datetime import datetime
import datetime
import pandas as pd
import asyncio
from binance import AsyncClient, DepthCacheManager, BinanceSocketManager
from ConnString import connEngine

 
#Binance client
client                          = Client (BinanceApiKeys.API_Key,BinanceApiKeys.Secret_Key)

 
#getting BTCUSDT trade data for the past day.
candlesticks                    = client.get_historical_klines("BTCUSDT", AsyncClient.KLINE_INTERVAL_1MINUTE, "1 day ago UTC")

 
#Declaring Arrays to be used
processed_closed_candlesticks   = []
processed_time_candlesticks     = []
open_candlestick                = []
low_candlestick                 = []
high_candlestick                = []

 
for data in candlesticks:
        candlestick             = { 
            "time": data[0] /1000, 
            "open": data[1],
            "high": data[2], 
            "low": data[3], 
            "close": data[4]
        }
        open_candlestick.append(candlestick["open"])
        low_candlestick.append(candlestick["low"])
        high_candlestick.append(candlestick["high"])
        processed_closed_candlesticks.append(candlestick["close"])
        processed_time_candlesticks.append(candlestick["time"])

 
timestamps = []
for i in processed_time_candlesticks:
    timestamp = datetime.datetime.fromtimestamp(i)
    timestamps.append(timestamp)
timestamp_cleaned = []
for i in timestamps:
    timestamp_clean = i.strftime('%Y-%m-%d %H:%M:%S')
    timestamp_cleaned.append(timestamp_clean)
dataCom = list(zip(pd.DatetimeIndex(timestamp_cleaned),processed_closed_candlesticks,open_candlestick,low_candlestick,high_candlestick))
df = pd.DataFrame(data=dataCom,columns=["time","close","open","low","high"])
 
print( df.head()) 

The script above will use Binance to retrieve market data for Bitcoin and store it in a dataframe. The dataframe should print out data as shown below.

Using TA-LIB to Detect Patterns

After you acquire the data, you can use this data to detect trading patterns. To do this, you will use Technical Analysis Library (TA-LIB), a Python library used to make mathematics calculations and technical analyses with its predefined pattern recognition algorithms. This tutorial uses TA-Lib to detect the Morning Star Pattern from the data acquired.

To install TA-Lib, visit the IFD website and download a wheel file corresponding to your Python version. Once the file is downloaded, move the file to your Python code directory and use the following pip command:

pip install TA_Lib-0.4.24-cp310-cp310-win_amd64.whl

Since the Python version is 3.10 and the computer system is 64, the TA_Lib-0.4.24-cp310-cp310-win_amd64.whl file was used. For a more detailed guide on installing TA-LIB, visit this blog called Installation of Ta-Lib in Python: A Complete Guide for all Platforms.

After installing TA-LIB, add the following code at the end of your original mainApp.py:

#CreatingMorningStarPattern
morning_star = talib.CDLMORNINGSTAR(df['open'], df['high'], df['low'], df['close'])
 
#filtering data to not show zero values from the returned data set.
NotNull_MorningStar = morning_star[morning_star != 0]

 
print(NotNull_MorningStar)

The code above takes the dataframe and runs the TA-Lib's pattern recognition against your data. If all is setup correctly, you should see the data printed out on your terminal as shown below:

Keep an eye on the dates and times. The indicator is not guaranteed to always give reliable results. However, more often than not, you will get useful results. You can visit CoinGecko and compare the candle sticks to the Morning Star results below.

As you can see, some of the indicators were correct. This data will mainly help you find an entry point. You can add more indicators to filter Morning Star's results.

To avoid re-running the script each time you want to do analysis and limiting the results to your personal computer only, add the following code at the end of your mainApp.py file to store the results in a database:

try:
    df.to_sql('TradesTable',con=connEngine,if_exists='replace', index=False)
    print("added data to database")
except Exception as e:
    print("Failed!")
    print(e)

The code above stores the dataframe data to your SQL Server database. Storing the data in a database enables the data to be stored on your machine for as long as you want. This is useful considering that most historical data providers have limits to how far back the data goes, or you can get charged to retrieve historical data for last year, for example.

Storing the data in your database also enables other people to access the data remotely, and you can also run applications using the database data.

Conclusion

This article covered collecting trading data from Binance, then using TA-LIB to analyze the data and store the results in your SQL Server database. You can use BackTrader to test the trading strategy used here. It is recommended to look at TA-LIB's function list for more TA-LIB patterns that can be implemented in your code.

Next Steps

• Learn how to create Candlestick and Heikin Ashi Charts from Yahoo Finance and Stooq.com
• Interested in stocks? Learn Techniques for Collecting Stock Data with Python.

About the author

Levi Masonde is a developer passionate about analyzing large datasets and creating useful information from these data. He is proficient in Python, ReactJS, and Power Platform applications. He is responsible for creating applications and managing databases as well as a lifetime student of programming and enjoys learning new technologies and how to utilize and share what he learns.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips