building a crypto trading bot - part 1 - requesting historical data and database storage

If you're interested in checking out the motivations for this project, you can checkout the video I made on YouTube:

If you want to see the code, it can be found here:

Keep in mind that it is neither well written nor well documented. I'll do some refactoring fairly soon.

The aim of this article is to act as a technical support document for the video I created on my YouTube channel. I'll try to keep from regurgitating general knowledge you can easily find on Wikipedia or some other how-to guide (I'll just link it in the article), and stick to the ideas I found/think are difficult or essential to understanding. Obviously I'm not really an expert on anything I'm going to write about, so there might be some mistakes, but I'll try my best. If there is anything painfully off, you can contact me via email, until I figure out how to allow comments.

For this first part, we'll be focusing on how to request historical candle data from Binance, and then store in our Postgres database. All of this will be done using the Clojure programming language. Although I'm aware it would make more sense to do this using Python because of the extensive materials and community that exists for trading, the lack of similar resources for Clojure is why I think this approach will be more interesting and beneficial.

If you want a general explanation of Clojure checkout its Wikipedia article, because I'll go into what I think is difficult to undertand about it. First off, Clojure is homoiconic, which basically means that code can be manipulated as data. This example from Wikipedia demonstrates it pretty well.

;; call a function (code) (+ 1 1) ;; => 2 ;; quote the function call ;; (turning code into data, which is a list of symbols) (quote (+ 1 1)) ;; => (+ 1 1) ;; get the first element on the list ;; (operating on code as data) (first (quote (+ 1 1))) ;; => + ;; get the last element on the list ;; (operating on code as data) (last (quote (+ 1 1))) ;; => 1 ;; get a new list by replacing the symbols on the original list ;; (manipulating code as data) (map (fn [form] (case form 1 'one + 'plus)) (quote (+ 1 1))) ;; => (plus one one)

You can easily manipulate any code you write thanks to the quote or ' function or symbol. What does this mean? Well let's say you wrote a bunch of functions with print calls to debug your code. If you want to keep the printing as default but not have it run in specific scenarios, you could create a macro for calling those functions and alter the prints to print to nothing (example of this macro can be found here under macros). It's a trivial example, but you could see how this can be handy, especially when combined with the fact that you can use a REPL to interact with a running process in real time. Once you get that key concept down, it's the real first step towards harnessing the power of Clojure. Other than that, the syntax is pretty simple, and Bob Martin has written a couple of articles that explain most of it.

If you want to dive straight into playing around with Clojure, it's very easy. This guide pretty much covered all the basics for me.

So the first step I chose to go with was just trying to get a request sent to Binance and get a response back. In order to do this, we need to take a look at the Binance API documentation to see firstly what the endpoint for the request is, and secondly what kind of information we need to send/what we'll receive. These are the parts in the beginning of the documentation that I think are most important, because they tell us the base endpoint, what endpoints return, how the data is sorted, what you need to send to the endpoint (and in what form), and potential limits we can run into if we overload the system with requests.

General API information for binance, including The base endpoint is:, All endpoints return either a JSON object or array, All endpoints return either a JSON object or array, All time and timestamp related fields are in milliseconds.
General endpoint information for binance, including For GET endpoints, parameters must be sent as a query string. For POST, PUT, and DELETE endpoints, the parameters may be sent as a query string or in the request body with content type application/x-www-form-urlencoded. You may mix parameters between both the query string and request body if you wish to do so. Parameters may be sent in any order. If a parameter sent in both the query string and request body, the query string parameter will be used.
Information about the IP limits for Binance.
Information for Binance PING request.

Now all of this info is specific to the RESTful API (HTTP requests to GET, PUT, POST and DELETE data). Binance also has a websocket API, which we'll probably end up using in the future for real time information, but for now, we'll focus on the RESTful side because that's how we're going to get historical data. We'll also use the endpoint in the last image to test our HTTP setup.

From why research, it looks like the fundamental library for HTTP server abstraction in Clojure is ring. It has pretty much everything you need, and a lot of other libraries are built on top of it or inspired by it. That's why I'd recommend reading their spec page or concepts pages. Although I'm pretty familiar with how HTTP servers work, I still found them fairly informative. For this part, I chose to use clj-http instead. Why? Because it's a little more lightweight than ring (50 KB smaller repo) and the syntax looked easier to me. It's also inspired by ring, so the information in those pages I linked earlier is still relevant.

The great thing about Clojure is if you have a REPL running and want to test a library, you just type

(require '[library-you-want :as alias-you-want-to-use])

and you're ready to go. So to use clj-http, we just type

(require '[clj-http.client :as client])

and we can now use the client part of the library from the REPL. To test it and the Binance endpoint, we can type

(client/get "")

And we'll the REPL will automatically print the whole response, since it prints to console whatever the last evaluation is. If you just want to see the response body, you can type

(:body (client/get ""))

since the response is a map. Pretty cool that we can do it that quickly right? Anyway so now we're going to try to get some candlestick information. If we go to the candlestick data section of the API, all of the important information we need to know is there.

Binance API information for candlestick data requesting

So we know we need to hit the /api/v3/klines endpoint, and at least provide a symbol and an interval. Symbols are just trading pairs like BTCUSDT or ETHBTC or whatever other pair you can find on the Binance website and needs to be in string format. Interval is the candlestick interval, which is listed in the enum section. The startTIme and endTime will become relevant when we start cycling back through time to get all of the historical data we need.

Enum definitions for candlestick intervals

We also know that the response is going to be a JSON array. To test it, we can use our REPL. The general information on endpoints from the Binance API tells us that any GET parameters have to be sent as a query string. In clj-http, query-parameters automatically get parsed into a query string, so we can add those. An example can be found of how to do it here. It's basically the same as the request before, but we add a map with a query-params key and another map as its value with the relevant parameters.

(client/get "" {:query-params {:symbol "BTCUSDT" :interval "5m"}})

Boom, you should see a stringified JSON array of the 500 most recent 5 minute candle values for the BTCUSDT trading pair in your terminal window. Easy-peasy-lemon-squeezy. The General API Information from Binance told us before that all time fields are in milliseconds, so if we want to find the data for a specific time period, we have to convert the startTime and endTime for the period we want into milliseconds, then just add on those parameters into the query-params map. From my testing, it seems that if you don't hit the candlestick start time right on the head with the startTime parameter, it'll just start at the nearest one. This is not really important though.

Anyway, now that we know we can request data from Binance, we have to store it somewhere. I played with the idea of using a .csv file to store it, but then thought I might as well pull the bandaid off fast and jump straight into getting a database setup. Because I know how to use Postgres, that's what I'm going to choose to use for this part.

Java has this thing called the Java Database Connectivity API (JDBC), which is a standard API in the Java environment. Its purpose is to provide an "all Java" solution to creating database connections. It's useful because it's database independent and platform independent, so should work pretty much everywhere. Clojure has a new equivalent called next-jdbc. It's low level, but I'm pretty comfortable writing SQL, so that's fine for me.

So first thing first, we should make sure we have Postgres running. Personally I have it running in a docker container, which you can learn about here, but there shouldn't be any difference if you have it in a container or running locally. Just make sure your container is actually active (sometimes I forget!). Once that's up and going, we need to add two new dependencies. Obviously we need the next.jdbc library, but we're also going to need the Postgres specific JDBC driver. A list of the drivers that next.jdbc has been tested against can be found here. In order to add them, we need to edit our :dependencies in the project.clj file in the root folder to have

[seancorfield/next.jdbc "1.0.409"] [org.postgresql/postgresql "42.2.10"]

then restart the REPL and you should be able to load in the library like before/

(require '[next.jdbc :as jdbc]) Try connecting to your database (all the functions we use are outlined here) (jdbc/get-datasource {:dbtype "postgresql" :dbname "trader" :user "postgres" :password "postgres"})

The above assumes your database is called trader and the default postgres user has access to it (which they probably do). This is going to change in the future for security reasons, but for now it shouldn't be a problem. If you got no errors, you got no problems! Next we want to create a table that'll store the candlestick values for specific pairs and intervals. You can do this in your a Postgres session in a separate terminal or put it in a function like I did below

(jdbc/execute! (jdbc/get-datasource {:dbtype "postgresql" :dbname "trader" :user "postgres" :password "postgres"}) [(str "CREATE TABLE IF NOT EXISTS " symbol interval " ( open_time bigint PRIMARY KEY, open numeric(14,8) not null, high numeric(14,8) not null, low numeric(14,8) not null, close_time bigint not null, volume numeric(16,8) not null, close numeric(14,8) not null, quote_asset_volume numeric(16,8) not null, number_of_trades int not null, taker_buy_base_asset_volume numeric(16,8) not null, taker_buy_quote_asset_volume numeric(16,8) not null, ignore numeric(16,8) not null );")])

Where symbol and interval stand for the values you want to lookup. I chose to make the open_time the primary key, because it'll always be unique, but it could just as easily be the close_time as well. All the other value types I just kind of eyeballed for the minimum I could get away with. For about 280,000 rows (which is all the BTCUSDT 5 minute candlestick data) it takes up 43 MB. I don't know if that's especially good, but it's manageable for me.

Now we have the database setup, we need to format the data we get into something the database can ingest. Remember how our response was a stringified JSON array of arrays? Well conveniently that can be tranformed to a Clojure vector of vectors, using the edn library, which can look something like this in REPL

(require '[clojure.edn :as edn]) (edn/read-string ((client/get "" {:query-params {:symbol "BTCUSDT" :interval "5m"}}) :body))

which should print out a vector with 500 vectors full of string values. See any potential issues with that? We'll get to it in a bit.

So now we have 500 rows worth of data that we want to insert into the database. Rooting around in the SQL section of next.jdbc, I found a pretty cool function for inserting lots of data at once called insert-multi!. This naughty little function lets us provide a sequence (can be a vector or a list or any iterable) of column names and a vector of vectors of data, and it generates a single SQL statement that is then sent to the database (in our case 500 insert statements). The connectable is a database object, which is what was returned from the jdbc/get-datasource function, and the table is the name of the table we're connecting to. So it would look something like this

(require '[next.jdbc.sql :as sql]) (sql/insert-multi! (connect-to-db) (str symbol interval) ["open_time" "open" "high" "low" "close" "volume" "close_time" "quote_asset_volume" "number_of_trades" "taker_buy_base_asset_volume" "taker_buy_quote_asset_volume" "ignore"] data {:suffix "ON CONFLICT DO NOTHING"})

where (connect-to-db) is a function that calls the get-datasource function, (str symbol interval) creates a string of the table name, the vector is our column names, data is the edn/read-stringified data, and the options map at the end tells the function to add DO NOTHING ON CONFLICT to the end of each INSERT because I was getting an error saying that I was trying to insert data that had the same primary key as a row in the table. The latter was a quick fix because my recursive candlestick data retrieval function wasn't perfect. This works for us because the data provided by Binance always has the column values in the same order according to the API.

But hold on! You probably get an error trying to run the above function talking about being unable to cast something or types mismatching. That was the problem I asked you about earlier. edn/read-string turns string into a vector of vectors, but the values inside the vectors remain as strings. This is going to be a problem got Postgres, since all of the column types are numeric or int. So before inserting it, you're going to have to cast it to a Clojure number type. I'm not going to cover that in detail here, so you can have a crack at that yourself. Because of Clojure's nature, it's pretty simple, but if you get stuck you can checkout the source code.

Ah who am I kidding, I like you so I'll give you a hint. You can use the bigdec function. But that's all I'll say!

Anyway that's it for this post! I'll be working on improving the source code and adding more features. The next article will likely be about creating and testing strategies on the data we get, and might include some genetic machine learning algorithms. If you have any questions, feel free to email me at alex dot zdanov at gmail dot com. If you want to get a better idea of my motivations for doing this, I'd recommend checking out the YouTube video I made that pairs with this article.