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.

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: Fetch Data from Binance

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

  1. Select Binance from the drop-down list of applications
    binance-application
  2. 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
  3. 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. When you create a custom request, you add your complete URL into the request URL field.

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: Get Private Binance Account Data

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

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

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

It will produce a sheet of data from YOUR account, like this:

binance-results-account

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

To resolve, you can try the following:

  • If you’re running a custom API request, 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, 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. You can see a list of free crypto APIs here.

Part 6: 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!

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

79 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. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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.

  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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)

Leave a Reply to Joe Cancel reply

Table of Contents