Search API Connector Documentation

Print

Import Binance Data to Google Sheets

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 article is only about accessing Binance’s open, unauthenticated endpoints. If you are looking to get your private account data, please see this sheet instead.

The Binance API is, in my opinion, a bit confusing 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 as long as you’re aware of that, it isn’t too hard to get used to. Also, the nice thing about Binance is that it provides some detailed data that other APIs don’t, like open orders and candlestick chart data.

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

Contents

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 request URLs in this article can be accessed from within API Connector by entering “Binance” into the URL field. This will search the built-in API library so you can simply click and load the URL you’re interested in.

We’ll first follow the Binance API documentation to access 24 hour price change statistics for all currency pair symbols.

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

Note: If experiencing problems with the above API root, please use an alternate as outlined in the general API information section of the official Binance documentation.

Putting it together, we get the full API Request URL:

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

Part 2: Pull Binance API Data into Sheets

Now let’s paste that URL into API Connector.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the Request URL we just created. The screenshots show api.binance.com, but I recommend using the alternate domains like api1.binance.com instead.
    binance-img1
  3. Leave the Headers section empty. Binance’s market data endpoints are public, so we don’t need any headers here. We don’t need extra Authentication either so leave that set to None.
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see 24 hour rolling window price change statistics for all Binance’s supported currency pairs:
    binance-img2

Part 3: 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 just want to jump in and get a feel for it, play around with the URLs you enter into the API URL path field. Try the following (one at a time).

Note 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
    binance-img3
  • recent trade orders for specified currency pair
    https://api1.binance.com/api/v3/trades?symbol=BTCUSDT
    binance-img4
  • order book data (list of open orders for specified currency pair). Select the “compact” report style for this one to avoid all the data lining up on a single row.
    https://api1.binance.com/api/v3/depth?symbol=BTCUSDT
  • Kline/candlestick chart data. Again select the “compact” report style.
    https://api1.binance.com/api/v3/klines?symbol=BTCUSDT&interval=30m
  • historical trades data requires an API key. For this you’ll need an account at Binance. You can retrieve your API key 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 = X-MBX-APIKEY, and Value = your API key
    https://api1.binance.com/api/v3/historicalTrades?symbol=BTCUSDT
    binance-img5
To convert Binance’s timestamp to a human readable date, use the following formula in Sheets:

=({Sheet!Cell}/1000)/86400+date(1970,1,1)

Part 4: Binance Limits

If you use Binance for a little while, you’ll almost certainly run into an error message from their server.

There are a few different errors you might see:

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

You can see some information about these errors in Binance’s docs, but basically Binance is really sensitive to rate limits. And when you run your requests through API Connector / Google Sheets, you’re more likely to hit these rate limits because all requests running through Google Sheets share the same pool of IP addresses from Google’s 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, use one of their other base domains: https://api1.binance.com, https://api2.binance.com, and https://api3.binance.com.
  • reduce the number of requests you’re calling at once. DON’T smash their servers with thousands of requests as it will make the problem worse for everyone.
  • wait 5 minutes and try again

If you continue to have issues, you can also try pulling from a crypto API that limits by your own API key rather than the shared pool of IP addresses, e.g. CoinMarketCap or CryptoCompare.

Appendix: Binance Template

In this template, everything is configured for you to simply type in whatever coins you’re interested in and get a dashboard like below:
binance-img7

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

P.S. For dozens of other step-by-step guides for connecting crypto (and other) APIs to Google Sheets, click here: Knowledge Base

Previous Import BigQuery Data to Google Sheets
Next Import BscScan Data to Google Sheets

82 thoughts on “Import Binance Data to Google Sheets”

  1. The API works pretty well but also has certain limitations.
    If You have too many cells importing data through API, You will quickly hit the error like this one:

    Exception: Request failed for https://api.binance.com returned code 418.
    Truncated server response: {“code”:-1003,”msg”:”Way too much request weight used; IP banned until 1611528105765.
    Please use the websocket for live updates to avoid bans.”}
    (use muteHttpExceptions option to examine full response)
    (line 127).

    Do you know any way to use websockets from google sheet?

    Reply
    • I don’t, but maybe you can try an API like CoinMarketCap instead, that way you’ll get your own API key. Part of the issue with Binance is that everyone using Google’s IPs is sharing the same public quota.

      Reply
    • What’s your full URL? Please make sure your URL is formed correctly, e.g. multiple query strings should be separated with ampersands, not question marks. I tested https://api.binance.com/api/v3/historicalTrades?symbol=BTCUSDT&limit=1000 and it worked for me, try that and let me know if it fixes your error.

      Reply
  2. thanks man!

    i`m keep getting error code 403 using the google apps script.
    i want to get all the bids & asks from any coin.

    is there any solotion to error 403 ? or there will allways be a problem with binance api using google apps script?

    Reply
    • The Binance faq says I’m receiving an HTTP 403 error when using the API. Why?
      You have violated the WAF (Web Application Firewall) rules, most likely by violating a rate limit or sending a malicious request (as outlined in the limits section of this document). Typically, these bans last a period of 5 minutes.
      So in these cases I think you just need to wait and try again.
      If you continue to have issues, you could also try pulling data from other crypto APIs, e.g. CoinMarketCap or CoinGecko.

      Reply
  3. Good day
    How can I pull in the transactions only for the coins I have, in order to automatically calculate the cost I paid for the coins vs current market price?

    Reply
  4. Hey Ana,

    Great article. Thank you so much for the information and the file. It really helps.

    Is there a way to automatically refresh the data every minute or 5 minute without the need of clicking “Refresh All Now” every time.

    Thanks again.

    Reply
  5. CAKE doesn’t work because there is no CAKEUSDT pair. I can manually add CAKEBUSD to the sheet but it breaks any other pair. Otherwise, awesome.

    Reply
  6. Hello! An incredibly helpful article! You explained the sequence of actions very easily, thank you!
    I have a few questions:
    1) is it possible to make the data updated? Perhaps by a button that will start the update. Because it helped me only to restart the API (that is, to do everything again according to your instructions). I need to update such a table two or three times a day. When I try to update the table via the “Refresh All Now” button, the system shows me the error “ERROR can’t find sheet undefined”

    2) I also need to save the previous data of this table so that I can compare the “updated data” and “previous data”.
    I’m only interested in the price of the cryptocurrency to USDT

    Reply
    • Hey Renne, you can update the data by opening the request in the sidebar and clicking Run. If it says ERROR can’t find the sheet, that just means you changed or deleted your output sheet and need to re-select one. You can select a new output sheet when you open your request.
      As for saving previous data from your table, you can check out my other add-on as it does exactly that (and it’s free): Archive Data
      Hope that helps 🙂

      Reply
  7. Dear Ana, Thanks a bunch, super nice to work with.

    I can only add up to 23 coins in the “Inputs” tab. Adding more outputs no value in the “Summary” tab.

    I tried changing the formula value B8:B25 to B8:B100 in columns C8-CL but this did not allow me to add more coin.

    Would love to see your feedback Ana, thanks!

    Reply
    • Sorry, I don’t totally understand the question, is ‘USDT Coin’ a currency pair?
      But if you only want the price of a single coin you can use a URL like this, just substitute in the pair that you’re interested in:
      https://api.binance.com/api/v3/ticker/price?symbol=BTCUSDT

      Reply
      • Yes that’s a coin and a base pair for prices.
        How can I use this in Microsoft excel?
        I can convert but I don’t know how I can update prices in excel?
        Thanks

  8. Hi Ana,
    First of all, thank you so much for your articles – I learnt a ton from them. Don’t have any technical background, and these are super well written, so thank you!

    I was super happy when I managed to connect to the binance API 🙂 I used the /v3/ticker/price endpoint and it was working great for a couple of weeks but today I got the 418 error message:

    {“code”:-1003,”msg”:”Way too much request weight used; IP banned until 1617596675170. Please use the websocket for live updates to avoid bans.”}

    Sounds like binance have blocked my IP but I was only making 8 calls per day! Am I missing something? And do I need to use the websocket for occasional updates?

    Any help would be super appreciated!

    Reply
    • Hey Antoine, thanks for the message, I’m glad you like the articles :). For the 418 error, unfortunately this is a common problem with Binance’s API. Please check the section above called “Binance Limits” for a couple of suggestions to resolve it.

      Reply
    • Agreed, it’s really frustrating.
      Switching to their alternate hostnames usually helps, but it might be better to just use other crypto APIs that don’t limit by IP address.

      Reply
  9. Hi Ana,
    I am an end-user and find your google sheet quite interesting.
    Can you show me how to update the Binance Update sheet and also add more data to it. I am not too familiar with Google sheets. A detailed instruction will be well appreciated.
    Thanks
    Eddy
    p.s You did a good job preparing the template. Thumbs up!

    Reply
    • Hi Eddy, thanks for the nice comment. The instructions for using the template are in the Information tab. Basically you need to make a copy of the template, import in the request file, and then fill out the Inputs tab with the coins you want to track. Does that answer your question?

      Reply
    • As far as I know, Binance doesn’t provide any endpoint that returns only volume, so you have to use the /api/v3/ticker/24hr endpoint and pull it out yourself. There are 2 ways to do this:
      1) Use standard Google Sheets functions like HLOOKUP or MATCH to extract the metric you want into a second sheet.
      2) If you’re using the pro version of API Connector, you can enter a JMESPath expression to display only the volume field. For example this expression would pull in just the symbol and volume: [].{symbol:symbol,volume:volume}

      Reply
  10. Bonjour, je souhaiterais pouvoir choisir une date et une heure bien précise dans un url comme celui-ci :

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

    Je demande de l’aide merci

    Reply
  11. Hi! I just recently installed your add-ons to test it out. I’m very interested in your add-ons so I try get the binance data buy running it manually and thru scheduling

    Google Sheet: testSheet
    API URL path: https://api.binance.com/api/v3/ticker/24hr
    Destination sheet: Sheet1
    NAME AND SAVE REQUEST – Name: binanceData

    Upon testing the status error below always show up.

    Status
    Completed with errors
    – Server responded with an error (403)
    – Server responded with an error (418)

    But when I try to go to
    https://api.binance.com/api/v3/ticker/24hr
    the binance raw data is there.

    Is there wrong in my settings?
    Are there any work around with this?
    Hope you can help me out in evaluating and testing your product.
    thanks.

    Reply
    • Hi Richard, you can see information on this issue in this article (check the section called “Binance Limits”).

      Basically, Binance rate limits requests by IP address, and when you run requests through Google Sheets you’re using the same IP address pool as everyone else using Google Sheets. When you put their request URL into the browser, you’re using your own personal IP address instead, so you don’t hit their rate limits.

      To resolve, try using Binance’s other hostnames, e.g. https://api2.binance.com/api/v3/ticker/24hr. That usually helps. Otherwise you can try other crypto APIs that don’t limit by IP address.

      Reply
  12. Hi Ana,

    Thank you for the clear tutorial, however, i am trying to get my user data from the binance api using myTrades or Allorders. I have to set a timestamp, but i have no idea what this should be. Can you maybe explain how to extract my user data to google sheets?

    Reply
    • Hello! Sorry, I believe Binance only lets you specify a single symbol at a time, so you’d need to set up multiple requests to pull trade orders for multiple currency pairs.

      Reply
    • Binance is always updating its supported trading pairs, so the best way to check is just run a request for the trading pairs you’re interested in. Or you can view them all with a request like https://api2.binance.com/api/v3/ticker/24hr.

      Reply
      • No, I mean that Binance in addition to classic trading also has a kind of trading called “CONVERT” where although you are limited to exchanging only 2-3 currencies between them it is totally free. Here I want to try to use “CONVERT” to trade with 0 fees, is it possible?
        For example on CONVERT I can change: Matic in VET; VET in Doge, Doge in BNB.

        Thank you

  13. Hi I was wondering if it is at all possible to extract data using Mixed Analytics for USD-M Futures. I’m been trying to figure it out but nothing I try is working.

    Reply
    • Sure, you can check here for a link to Binance’s documentation on getting futures data.
      Based on that, here are a couple example Futures API request URLs:

      • https://fapi.binance.com/fapi/v1/trades?symbol=DEFIUSDT
      • https://fapi.binance.com/fapi/v1/klines?symbol=BTCUSDT&interval=1d
      Reply
    • Binance’s API only lets you get candlestick data for one pair at a time. So you would need to use API Connector’s multi-request feature (paid) to loop through and run the request for different cryptos. If you were using that, you’d set up your request like this:
      Request URL
      https://api.binance.com/api/v3/klines?symbol=BTCUSDT&interval=30m
      https://api.binance.com/api/v3/klines?symbol=ETHUSDT&interval=30m
      Report style: compact
      Add request URL: ☑ (this will let you see which results match to which symbol)

      Reply
  14. Hi,

    I would like to get the trading, deposit & withdrawal data from my binance account, but facing difficulty in finding the correct api endpoint.

    Can anyone share, what the correct api endpoint would be and where to use my API_KEY and SECRET_KEY?

    Reply
  15. Hi!
    Thanks for sharing this infos.
    I am trying to set the sheet to display my spot wallet. I followed the instructions but when I hit run it says:
    1) SPOT: Completed with errors
    – We received an error from the API server (400) show response
    {“code”:-1021,”msg”:”Timestamp for this request is outside of the recvWindow.”}
    Can you help me on this?

    Reply
    • The recvWindow sets how much of a buffer you have over the timestamp set in your signed URL, so it seems like you may have waited too long (or there’s some timestamp sync issue). Can you please check the following?
      1) make sure to leave recvWindow=60000
      2) click the checkbox to produce a fresh timestamp right before you hit Run, to help ensure that the timestamp is within the window.

      Reply
    • Sorry, we can’t change the data from the API, you would need to change it after it’s in your sheet.
      For importing a single column, you can check out JMESPath filtering. Another method is to just create a second sheet that pulls in the column you want with a Sheets function like =query(Sheet1!A:Z,"select C") to get column C.

      Reply
      • Hello, could you do a explanative youtube video on how to pull all binance coins and order them by marketcap either using cmc or coingecko api? It is a simple request but im having trouble figuring how.

        Thanks

      • You can enter this URL to see coins sorted by market cap in CMC: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?sort=market_cap. This article has more info on the CMC API, and there’s a video tutorial for it here.

  16. Thanks, i am only interested in binance coins. Is there a simple way to sustract the binance coins from the CmC list? maybe using two excels?

    Reply
    • You can pull the coin list from each exchange so you have one sheet for each exchange. Then do a VLOOKUP to mark which coins on CMC are also on Binance, and delete the ones that don’t get a mark. If you aren’t sure about VLOOKUP there are a lot of resources online, like here’s a video about comparing lists (seems similar to what you want to do): https://www.youtube.com/watch?v=GdrOFBBEMQ8

      Reply

Leave a Comment

Table of Contents