API Connector Documentation

Print

Import Binance Data to Google Sheets

Last Updated OnJanuary 3, 2024
byAna
< Knowledge Base

2023-10 update: Binance's API is now rejecting requests for data made through Google Sheets. The Binance integration has been removed from API Connector. Please check this list of alternative crypto APIs: https://mixedanalytics.com/knowledge-base/top-free-crypto-apis/.

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.

The nice thing about Binance is that it doesn't require any authentication (though this also makes it more likely to produce errors). It also provides some detailed data that other APIs don't, like open orders and candlestick chart data.

Contents

Before You Begin

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

Part 1: Pull Data from Binance to Sheets

The easiest way to get started with the Binance API is through API Connector’s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select Binance from the drop-down list of applications
    binance-application
  3. Select an endpoint.  These endpoints are all open so you don't need an API key. For this example we'll choose the /ticker/24hr endpoint, which provides 24hr price change statistics.
    binance-endpoints
  4. Choose a destination sheet, name your request, and hit Run. A moment later you'll see price change stats for all currency pairs in your sheet.
    binance-response

Part 2: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the API URLs shown in the API documentation. Here's an example request setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://data.binance.com/api/v3/ticker/24hr
    binance-custom

Part 3: Handle Binance Timestamps

For most endpoints, Binance returns dates as UNIX timestamps .

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

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

Part 4: Binance Error Messages

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:

(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.
OR (418) {"code":-1003,"msg":"Way too much request weight used; IP banned until 1616682165041. Please use the websocket for live updates to avoid bans."}

You can see some information about these errors in Binance's docs, but basically Binance is sensitive to rate limits.

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 there isn't always enough capacity for Binance to handle the massive request volume from Google Sheets.

If you continue to have issues, I recommend switching to a crypto API that limits by your own API key rather than the shared pool of IP addresses for Google Apps Script. Please check this article for a list of alternative crypto APIs.

(451) { "code": 0, "msg": "Service unavailable from a restricted location according to 'b. Eligibility' in https://www.binance.com/en/terms.}
OR (403) Forbidden

As of Nov 2022, Binance began blocking API calls sent through Google Cloud, which includes requests through Google Sheets (more info). Originally, this could be worked around by using the data sub-domain, but this method stopped working in October 2023. Please check this article for a list of alternative crypto APIs.

Part 5: API Documentation

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

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

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!

103 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

  5. 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

  6. 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

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

  8. 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

  9. 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

    • 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

  11. 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

  13. 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) Use API Connector's visual field editor to display just the columns you want.

      Reply

  15. 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

  16. Hi !

    Thank you for this article, it's nice ! 🙂

    Quick question, I would like to obtain recent trade orders for 2 or more currency pairs, like this for example, but it doesn't work with a comma
    https://api1.binance.com/api/v3/trades?symbol=BTCUSDT,ETHUSDT

    Can you tell me how I should write it ?

    Thank you !

    jix

    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

  17. 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

  19. 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

    • 1) We can't change the dots for commas before importing, but you can convert them after with a function like =substitute(substitute(substitute(A1,",","#"),".",","),"#",".")
      2) To get just a single column, please use the field editor.

      Reply

  22. 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

    • Not really. Binance's API only lets you get data for all coins or a specific coin, not multiple coins.
      So I suggest using a different crypto API for this: CoinGecko, CryptoCompare and CoinMarketCap all provide that type of multi-coin endpoint.

      Reply

  24. Hey. Great post! Thank!
    I wanted to find out how to get prices data from certain dates - for example 1st of January 2020, 31st December 2020, etc.
    Is it possible at all?

    Reply

    • Sure, you can do the following:
      1. Use a URL like this to get daily data: https://api.binance.com/api/v3/klines?symbol=BTCUSDT&interval=1d
      2. Set your destination cell to B1
      3. (optional, but recommended) Click Edit fields and give your columns field names: 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.
      4. Save and run your request.
      5. In cell A2, add the following formula to convert their timestamps to regular dates: =arrayformula((B2:B/1000)/86400+date(1970,1,1))

      You should now have a list of dates with prices (and other data). Let me know if that works for you.

      Reply

      • Thank you very much for your answer!

        One more question - probably I wasn't clear enough before - we would like to import prices of all tokens (like you did in an example https://api1.binance.com/api/v3/ticker/24hr) - with specific date. For example:
        CELL A - here would be all pairs available
        CELL B - prices of all pairs on the first of January
        CELL C - prices of all pairs on the first of February
        CELL D - prices of all pairs on the first of March

        and so on.

        Is that possible?

      • Hmm, I don't think this is possible. I see endpoints for getting all pairs at the current date, and endpoints for getting a specific pair at a historical date, but I don't see any endpoints for getting all pairs at a historical date.
        You might want to check their API documentation or their API forum to confirm.

  26. Thank you for this very useful extension.
    Unfortunately I have a problem with number formats.

    For example I have to divide the Binance ticker results with 100000000.
    https://api1.binance.com/api/v3/ticker/price
    (3.120.000.000 should be 3,12)

    Or Gate.io results
    https://api.gateio.ws/api/v4/spot/tickers
    (3.827 should be 0,000003827)

    Is there any way to edit the number format? Or get the number directly from the raw response?

    Thanks.

    Reply

    • Can you please try switching the locale of the sheet to US? (click File > Settings > Locale). I think that might fix it.
      After you switch it, to see your data with commas instead of periods you could send your data into column B, and add a formula in column A to convert the data.
      This formula should work: =IF(REGEXMATCH(TO_TEXT(A1), "."), VALUE(REGEXREPLACE(TO_TEXT(A1), ".", ",")), VALUE(A1)) (source)

      Reply

  27. I just signed up with the paid version, and I have not yet been upgraded from the free version.

    How do I get oauth from binance so that API CONNECTOR can have access to my recently executed trades?

    Reply

    • This is the API I am trying to call https://api1.binance.com/api/v3/historicalTrades

      This is the response
      Status
      Completed with errors
      - We received an error from binance.com (401) show response{"code":-2014,"msg":"API-key format invalid."}

      Reply

    • Hi Ibiye, upgrades are processed instantly. If you aren't seeing your account updated, please contact support so we can help you there.

      Besides that, Binance doesn't currently use OAuth2 (I believe they are working on it but it's not yet available). They use a mix of different authentication methods like API keys and HMAC signatures. You can see more about it in our article on Binance. If you search that page for "/historicalTrades" you'll see information and a screenshot about how to access the endpoint you listed. There's also a link to a sheet you can use to access your private account info.

      Reply

  28. Hi,
    Nice platform.
    The issue I have right now is that I am trying to retrieve a large volume of data from Binance within every hour but it does not seem to work, so due to Binance limits, I broke down the large request into three smaller request and at the same time of every hour, the first of the three returns data but the second starts request starts and never completes while the third does not even start at all. and this happens on every hourly cycle.
    I wish there was a way to input the exact time when each schedule should begin just as it is on the daily schedule so as to separate the three requests by an interval of say 10 minutes apart to avoid the second request trying to load immediately after the first.

    Reply

    • Oh I forgot to mention that i used the API1, APT2 and API3 to help reduce the requests on one API but still come across the issue stated above.

      Reply

    • Hi Ibiye, triggers are managed by Google, and unfortunately they don't allow us to specify a specific start time. You can see more about that in our article on triggers, but basically they enforce various limits related to the frequency and quantity of data pulls.
      Can you please share some information about your request URL, either here or via support? Then I can try to replicate the issue and help you find a solution.

      Reply

  29. Also, the time that returns on the log is different from my time zone so I have to always try to do a mental adjustment every time I look in the log to figure out when the request was triggered. It would highly be appreciated if this can also be fixed.

    Thanks.

    Reply

    • The time returned in the log should match the time zone of the sheet, so it sounds like your sheet may be set wrong. In Google Sheets can you please click File > Settings > Time zone and make sure the value there matches your time zone?

      Reply

  30. Very nice info you put out there, i haven't started the process and i'm quite new to this but i want to know if i can specify the date of the data i need to grab like for example
    i only want to collect trading data on two tokens, higstreet/usdt and btc/usdt for lets say on the 18/12/2021. will this be possible?

    Reply

    • Sure, you can check the "Kline/candlestick chart data" example above for how to get daily btc/usdt prices, and then optionally add startTime and endTime parameters to specify a date. For example, this would get btc/usdt priceson 18/12/2021: https://api1.binance.com/api/v3/klines?symbol=BTCUSDT&interval=1d&startTime=1639699200000&endTime=1639785600000. (Note that Binance's API requires a timestamp in milliseconds.)

      Reply

    • Sorry, I'm not really familiar with futures but you can see all their futures endpoints here. An example working futures API call would be https://fapi.binance.com/fapi/v1/lvtKlines?symbol=BTCUP&interval=1d.

      Reply

  32. Hi Friends,

    I want use the Binance Trade history in my google sheets (Trade Journal)
    How can i connect binance in livetime with the google Sheets? ( symbol, entry time,entrydate, exit time, exit date, entry price ....)

    Thank you

    Reply

    • You can enter an endpoint like /api/v3/myTrades?symbol=BNBBTC into the private data sheet. Note that you can only fetch trades for a specific currency at one time, you can't automatically get all your trades (discussed here).

      Reply

  33. hello.
    how can i change automaticlly on the API price column from "." to "," so i can make formulas containing numbers.
    i can change it manually but every time the API runs it goes to "." again...
    thank you

    Reply

    • The API sends back data with a "." so it will always get replaced. Therefore you have 2 options:
      1) Send your data to cell B1 (instead of A1). Then in column A, you can add a formula to substitute commas with periods. This formula should work: =substitute(substitute(substitute(F1,",","#"),".",","),"#",".")

      2) change your sheet locale to US under File > Settings. Then the sheet will let you do calculations on decimal numbers (though you'll have to adjust yourself to get used to it :p)

      Reply

    • I mean how the link for the api connector should look so that I get the data of the makers and takers that sell on p2p.

      Reply

      • Seems there's no documented API for this, so I can't say too much, but this is how to access it:
        Method: POST
        Request URL: https://p2p.binance.com/bapi/c2c/v2/friendly/c2c/adv/search
        Headers: Key = Content-Type, Value = application/json
        Request body: {"page":1,"rows":10,"payTypes":[],"asset":"USDT","tradeType":"SELL","fiat":"LKR","publisherType":null,"transAmount":"2600"}
        (source)

  35. Hello, thanks for all your efforts, i just have a quick question ..
    will the prices update when i log from GoogleSheet on mobile (Sheet IOS App)?

    Reply

    • Right, unfortunately it won't work for getting account data since the "data" subdomain is only for fetching public data. To get account data, you will need to use Binance's "api.binance.com" subdomain, which still blocks Google's servers. I believe some Sheets extensions route your data through their own servers instead of using Google's, which is potentially less secure but allows them to work around this issue, so you can search Google's marketplace for alternative extensions to resolve this issue.

      Reply

  37. Hi, I m trying to use this formula "https://fapi.binance.com/fapi/v1/klines?symbol=BNBBUSD&interval=4h&startTime=1634616000000&endTime=1668801600000" in the API connector it shows the error code "451"

    However, I m not sure .. How to use the below information to get the required data. Can you please help me on the suitable code to get the required data from BNBBUSD from binance for 4 hr and 8 hr k-line?

    "Use the data sub-domain, e.g. https://data.binance.com/api/v3/ticker/24hr
    Use API Connector's preset integration instead of making a custom request. The preset integration automatically applies the data domain."

    Reply

    • I believe that futures data that was previously accessed via fapi.binance.com now needs to be accessed with the testnet.binancefuture.com domain, e.g.

      https://testnet.binancefuture.com/fapi/v1/klines?symbol=BNBBUSD&interval=4h&startTime=1634616000000&endTime=1668801600000

      Reply

  38. Hi! Can you tell me how I can set up an upload from my personal account on Binance? From the instructions is unclear, is it possible to enter the API key and gain access to the cabinet

    Reply

    • You can see this comment for a sheet that generates the signature required by Binance to access account details. However this no longer works through Google Sheets as Binance blocks Google's servers.

      Reply

    • You don't need to read any API documentation for this, just select "Binance" from the dropdown application menu, choose the /avgPrice endpoint (marked as "Current average price" ), and select BTCUSDT from the dropdown list of symbols.
      If you'd like to see which API URL that corresponds to, tick the "Add request URL" box before running.

      Reply

  42. Hello,
    Binance requests no longer working, im trying any public request and im always getting this:

    1) : Completed with errors
    - We received an error from binance.com (403) show response

    It became recently. About two weeks ago everything was working fine.

    Reply

    • 1) : Completed with errors
      - We received an error from binance.com (403) show response
      403 Forbidden 403 Forbidden

      this is full respond

      Reply

      • Thanks, I can confirm I'm getting 403 errors as well. Seems like they're no longer supporting the "data" subdomain, but I don't see anything in their documentation or change log relating to this. I suggest using a different API like CoinGecko or CoinMarketCap instead.

Leave a Comment

Jump To