Sorry We Cant Export Your Visual Data Right Now Please Try Again Later

Import Binance Data to Google Sheets

Last Updated On April 10, 2022

In this guide, we'll walk through how to pull cryptocurrency market data from the Binance API directly into Google Sheets, using the API Connector add-on for Sheets.  This commodity is only about accessing Binance'southward open up, unauthenticated endpoints. If yous are looking to get your private account information, please see this canvas instead.

The Binance API is, in my stance, a bit disruptive because it returns data in currency pairs (e.g. BTCAUD, BTCBBTC, BTCBIDR, BTCBKRW, BTCBRL, BTCBUSD, etc.). Other crypto APIs would just return "BTC", either in USD or in the conversion currency of your choice. But every bit long as you're enlightened of that, it isn't too hard to get used to. Also, the dainty matter about Binance is that it provides some detailed data that other APIs don't, like open orders and candlestick chart information.

If you want to skip the details, you can jump correct to the end and grab a re-create of the Binance crypto tracking template. Or if you are more than of a video person, head over to our YouTube video.

Contents

• Before You Begin
• Part 1: Create your API Asking URL
• Role 2: Pull Binance API Data into Sheets
• Part 3: More than Case Binance API URLs
• Part four: Handle Timestamps
• Part v: Get Private Binance Account Data
• Part half-dozen: Binance Limits
• Part vii: API Documentation
• Appendix: Binance Template

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Create Your API Request URL

All the asking URLs in this commodity can be accessed from within API Connector past entering "Binance" into the URL field. This volition search the built-in API library and then you can simply click and load the URL you're interested in.

For our first request, we'll beginning access 24 hour price change statistics for all currency pair symbols.

• API root: https://api1.binance.com
• Endpoint: /api/v3/ticker/24hr

Putting information technology together, we get the full API Asking URL:

          https://api1.binance.com/api/v3/ticker/24hr        

Office ii: Pull Binance API Data into Sheets

Now let'southward paste that URL into API Connector.

1. Open up up Google Sheets and click Extensions > API Connector > Open up.
2. In the Create tab, enter the Asking URL nosotros merely created.
   
3. Leave OAuth ready to none. Exit the Headers section empty as well.
4. Create a new tab and click Set electric current to utilize that tab every bit your data destination.
5. Proper name your request and click Run. A moment afterward you'll see 24 hour rolling window price change statistics for all Binance'due south supported currency pairs:
   

Part three: More Example Binance API URLs

Experiment with endpoints and query strings as described in the documentation to see other types of crypto data from the API. If you lot just want to jump in and go a experience for it, play effectually with the URLs you enter into the API URL path field. Endeavor the post-obit (ane at a time).

Annotation that for futures data, the base endpoint is https://fapi.binance.com.

• latest price of BTC in USD

                https://api1.binance.com/api/v3/ticker/price?symbol=BTCUSDT            

  
• recent trade orders for specified currency pair

                https://api1.binance.com/api/v3/trades?symbol=BTCUSDT            

  
• order volume information (list of open orders for specified currency pair). Select the "compact" report style for this one to avoid all the data lining upwards on a single row.

                https://api1.binance.com/api/v3/depth?symbol=BTCUSDT            

• Kline/candlestick chart data. This is great for seeing the price of a pair at a recurring interval. For example, this request URL will prove the daily cost of Bitcoin:

                https://api1.binance.com/api/v3/klines?symbol=BTCUSDT&interval=1d            

  Binance doesn't render column names for this asking, and so open the visual field editor (click Edit Fields) to manually add these field names in across the top: Open Time, Open, High, Low, Close, Volume, Close Time, Quote Asset Volume, Number of Trades, Taker purchase base of operations asset volume, Taker buy quote nugget volume, Ignore

• historical trades data requires an API key. For this you'll demand an account at Binance. You tin can retrieve your API fundamental by navigating to the API Management section of your account (located under Settings). Enter your key into the Headers section of API Connector, where Key = Ten-MBX-APIKEY, and Value = your API primal

                https://api1.binance.com/api/v3/historicalTrades?symbol=BTCUSDT            

  

Part 4: Handle Binance Timestamps

For most endpoints, Binance returns dates as UNIX timestamps .

To convert these timestamps to a homo readable appointment, set your data destination to cell B1, and add the following formula to prison cell A2:
=arrayformula((B2:B/1000)/86400+date(1970,1,1))

B2:B reflects the column in which the timestamp is located, so just modify it to match the column in which yous desire to convert timestamps.

Office v: Get Individual Binance Account Data

The above endpoints deal with public market data from Binance's API.

If you lot are instead a Binance customer looking to get API data from your own account, they crave a more complex authentication process that includes generating a "signature".

To access your private account information, please copy this sheet and follow the instructions inside:
https://docs.google.com/spreadsheets/d/1C0VtAloDF9CIbknd_-vE8kE4PdqZXE7_Ut8JyP7xVkk/re-create#gid=1253466820

Information technology will produce a canvas of data from YOUR account, like this:

Part 6: Binance Limits

If you apply Binance for a niggling while, you'll about certainly run into an error message from their server.

In that location are a few different errors y'all might run into:

• Server responded with an error (418) {"code":-1003,"msg":"Fashion also much request weight used; IP banned until 1616682165041. Delight use the websocket for live updates to avoid bans."}
• Server responded with an mistake (403). <Request blocked. We can't connect to the server for this app or website at this time. There might exist too much traffic or a configuration error.>

Y'all can come across some information almost these errors in Binance's docs, but basically Binance is sensitive to rate limits. And when you lot run your requests through API Connector / Google Sheets, you're more probable to hitting these rate limits because all requests running through Google Sheets share the aforementioned pool of IP addresses from Google'south servers. Unfortunately Binance doesn't seem to have increased their rate limits enough for Google.

To resolve, you can try the following:

• Instead of calling https://api.binance.com, utilize i of their other base domains: https://api1.binance.com, https://api2.binance.com, and https://api3.binance.com.
• reduce the number of requests y'all're calling at once. DON'T smash their servers with thousands of requests every bit it volition make the problem worse for everyone.
• wait five minutes and endeavor once more

If you proceed to take issues, I recommend switching to a crypto API that limits by your own API central rather than the shared puddle of IP addresses for Google Apps Script. You can see a list of complimentary crypto APIs here.

Office 7: API Documentation

Official API documentation: https://binance-docs.github.io/apidocs/spot/en/#full general-info

Interactive asking builder: https://binance.github.io/binance-api-swagger/

Appendix: Binance Template

In this template, everything is configured for yous to simply blazon in whatever coins you're interested in and get a dashboard similar below:

You can jump right to a copy of the template here. Happy data grabbing!

thomastheract.blogspot.com

Source: https://mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/

Share this post