🤑🤑🤑For creating your own balance sheet based on your Binance account, you can check out a solution one of our users came up with here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068 🚧🚧🚧If you are having 403 issues: Try changing the domain from api1.binance.com to api2.binance.com or api3.binance.com. If the 403 persists, you can try: 1) to decrease the size of your requests 2) create an account and use an API key Read more on their website here: www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
Hey there! Can you help me to figure out why BTCCNY, USDTCNY etc is not able to get? Probably any tickers with CNY (Chinese yuan) I can't find where binance mention all available tickers except get list (ticker/price) I want to get tickers
Thank you, was helpful! Just as a tip- in some cases is hard to follow you when you were in the Binance menu as you were jumping around all bur Thanks again!
1. Can i get my trade history, order history, open orders in Google Sheet? 2. Can I put formulas to check if any scrip is in profit or loss as per current market price? 3. Can i import data from multiple exchanges for my executed orders and keep in 1 sheet? Sync after every interval
Hey Nimesh! 1. We had one of our users come up with a cool trick for getting their Binance data in: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068 2. You could wire this up yourself by refreshing and referencing current market price from another sheet 3. Depends on if those exchanges have API access, you'd have to check their website. You can sync every X times/day with our pro subscription yes :)
I found this video very useful because I now get the data I want from Binance but my challenge is when ever I try to pull in multiple cryptocurrencies even with as intervals set to just 2, I get a timeout response after five minutes stating that the data seems large for the API CONNECTOR to handle at once. Meanwhile, am seeking to pull in a minimum of 30 intervals for about 400 cryptocurrencies at once So 1. How can the one limit the number of columns to just OHLC and jettison the remaining columns after CLOSE in order to reduce data size? 2. How can API CONNECTOR be configured to receive very large data sets because I want to retrieve lots of history prices for multiple coins just from one click of the run button? Help!!! Thanks
Hi ib, 1. If you have one of the higher pricing subscriptions, you can access JMES Path to lighten the request and speed it hopefully to hit within your time-limitation: mixedanalytics.com/knowledge-base/filter-specific-fields-values/ 2. It's a little hard for me to understand based on a YT comment, but I would think in this use case you may want create an API request for each crypto. You could test to see if it can handle a call that big by testing on a few to see if it can reach back that far. Then, you'd just duplicate the request and run each request seperately via scheduling. The downside to this is that you'd have to have a unique sheet for each crypto, but you could always just recombine them later with something like =QUERY({'DOGE'!A2:D7;'BTC'!A2:D7},"select * where Col1 ''") in another sheet to combine all of them.
Hi Devashish! So Google limits the amount of requests you can make in a day across your entire Google account, you can read more in our write-up here: mixedanalytics.com/knowledge-base/api-connector-scheduling/#num6. We also do, you can read more about our limits for different subscription levels: mixedanalytics.com/api-connector-pricing/. If you need a faster refresh rate, I would suggest looking into webhooks (a different protocol from API), you can read more about them here: www.mparticle.com/blog/apis-vs-webhooks
Bonjour, y'a t'il une solution pour ne supprimer que les ordres d'achats posés sur Binance ? J'aimerais pouvoir toutes les 30 minutes par exemple que mon robot de trading sur google sheets supprime les ordres d'achats (uniquement) qui ne sont plus utile car la crypto est montée. Merci
How to list the data from /api/v3/ticker/24hr in excel as 1 or 5 minutes; that is, how can I keep the old data sequentially, while the data is refreshed every 1 or 5 minutes?
Thanks for sharing, it's very helpful. Can I filter the list of coins before import? For instance, if I want a list of only coins that pairs with USDT? Thanks
It is possible, but not easy. One of our users wrote up an interesting solution for getting your account balance, you can check it out here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
I'm having this error message after I hit the Run button. "We received an error from API server (403) You may have hit a rate limit..." Please help. Thanks in advance!
Try changing the domain from api1.binance.com to api2.binance.com or api3.binance.com. If the 403 persists, you can try: 1) to decrease the size of your requests 2) create an account and use an API key Read more on their website here: www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
Sure thing! You'll want to append &startTime={Unix timestamp milliseconds} and same for &endTime={Unix timestamp milliseconds}. For converting from unix to human time, check out this page: www.unixtimestamp.com/index.php but note, you will need to add 3 zeros on the end for it to work (most websites don't allow ms as input, so you will need to add them manually). So a sample call to klines would look like this: api1.binance.com/api/v3/klines?symbol=ETHBTC&interval=1h&startTime=1614528000000&endTime=1614960000000
Sup, my homie Wanna understand your ideas on PolkaCover. Best insurance system and looks they will go to the moon soon. Do you think I can make some money with them?
i get continuously this error Completed with errors - Server responded with an error (403) - You may have hit a rate limit. Please try using one of Binance's other base domains, for example api3.binance.com
@@juanjosemolina5501 Sorry about that, if you check the pinned comment on this channel, it's a pretty big issue that comes from Binance's side. I think you should be able to get around it if you make your own API key and use that to authenticate requests ( I haven't personally tried yet, you can check this link: www.binance.com/en/support/faq/360002502072)
Hi Pawan! Sure, you just need to switch over to the USD-M/Coin-M endpoints: Check this one out for example: binance-docs.github.io/apidocs/futures/en/#order-book
from where you open the api connector config? i ready installed but i cant see it how do you import transaction history ? to generate a portfolio manager sheet? thks
Sounds like there may be a few issues. You could try doing a hard refresh on your browser or running in private mode (sometimes API Connector doesn't play well with ad blockers, etc) As for transaction history and building a portfolio manager, you can check out a solution one of our users came up with here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
So at present there is no way (that I know of) to web hook into Sheets (if you aren't familiar with web development, a web hook is a kind of live updating API). With the pro version of API Connector, you could refresh hourly/daily (choose your refresh rate) to kind of simulate this, but you'll need to be careful of rate limits from Binance. Binance is notorious for throwing 403's at all Google extensions, so just something to watch out for :)
@@mixedanalytics Thank you for the response. I know that if you go into a crypto chart on crome and inspect say a price that updates every second. You can see in the code a purple pulse and if you drill down into that code, the price pulses and changes state every second or so. That is what I trying to extract but with no luck. I am not familiar with web development or any Java or python type programing language but I'm a fast learner. Is this web hook you are talking about able to import into sheets?
@@rbendzsuk You are already a web developer if you know how to Inspect a page hahaha ;) I'm a beginner web dev myself, but I just inspected that front page as well, and it looks like they use React (a JS framework) to render that number. In other words, they are likely just running the same API we are using but on their own back end so it's registered and they obviously wouldn't put a limit on their own servers. At present, I don't know any no/low code tools to accomplish a live dashboard due to technical barriers. Also I confused webhook for websocket lol. The websocket on binance is here: binance-docs.github.io/apidocs/spot/en/#websocket-market-streams It's similar to an API, but the difference is your application subscribes to that websocket and then gets live data from that stream. I haven't worked with either of them yet, only knowing about them from reading articles, so I won't admit to being an authority figure at all. But from what I can best tell, based on Google Sheets' limitations you would quickly hit a hard cap trying to grab that data and dump it directly into sheets.
Hi! Thanks for the video! I've done all the steps but it returns an error 400... {"code":-1102,"msg":"Mandatory parameter 'symbol' was not sent, was empty/null, or malformed."}. Do you have idea how to solve that? Thanks again!
Hi, thanks for this video, very useful to start learning about this!!! I have a question, once you connect the API and download the ticker price, is this static data? how often this updates?? Thanks
You're welcome Eduardo! And to answer, yes the data is static. If you want to update it, you will need to re-run the request. With our pro feature, you can automate this with triggers.
Hi! I was just wondering if you could please help me out in turning the data in the open time and close time into a human readable format such as year/month/day 21:00. Thanks
One of our users came up with a kinda working solution for getting your personal data, you can check the post here: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
Hi there! I want to have a dashboard kind of thing in Excel/Google Sheets that automatically captures my trades from Binance and shows the profits and loss. Can this be done? and How? Thanks in advance.
Hi Mustafa, I peeked into their documentation and I found a few endpoints that you could check out: Withdraw data: binance-docs.github.io/apidocs/spot/en/#withdraw-history-user_data Deposit data: binance-docs.github.io/apidocs/spot/en/#deposit-history-supporting-network-user_data This question is quite complex for a RUclips comment haha, but you could try posting your ideas in our Reddit/browsing to see if anyone else has created something similar www.reddit.com/r/api_connector/. While this is a little complicated, you could definitely wire up a dashboard that accomplishes what you would want. You'll also have to get an API key from Binance. Here's a basic dashboard you can check out for inspiration that we put together: docs.google.com/spreadsheets/d/1yO_5bvczvt1pghy1TSj36WQdDBfbWQQxXRct43p2m0U/edit#gid=1460402550 I'm also planning to do a tutorial soon on something similar, so hang tight! Hope that all helps!
@mixed analytics could you please do a video on how to show all one's Binance fiat and spot trades in googlesheets/excel. I follwed their documentation but i keep getting weird errors as recvwindow isnt large enough or timestamp is not passed, even though i pass it as parameter, alongside the HMC 256 signature but still no luck. Thank you!
@@salahouadi6343 You can check out this solution one of our users came up with (requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
Hi, thanks for the video. But something seems to be wrong with the data im getting. I am for an example getting the value 55268:20:00.000 for my ETHBTC pairing
@@mixedanalytics Ireally don't know. There isn't mutch to do different haha. I've tried several times now and even reinstalled mixed analytics. Do you have any idea
@@oscarschyum3962 On second thought, it sounds like to me it could be an issue with the actual sheet formatting. Select the column that is showing that data, then select Format->Number->Number, or try some other formats to see if that helps. Let me know if that worked :) We'll figure this out no sweat.
@@mixedanalytics Okej thanks man. Yeah so i had that thought too and tried all the different number formats but it dosn't show the correct numbers. I tried your new coinbase video too and i get the value 16666:40:00.000 istead of your 0.01 under the kolumn: data>>min_size. If i reformat it to a number i get the value 694,4444444 instead of 0.01. Really strange
@@oscarschyum3962 That is pretty weird! What browser are you using? Maybe try running in private/incognito as a last resort. If that doesn't work, you can post a screenshot/more details over in our subreddit and we can check it out: www.reddit.com/r/api_connector/
Order book: binance-docs.github.io/apidocs/spot/en/#order-book Older orders: binance-docs.github.io/apidocs/spot/en/#compressed-aggregate-trades-list Feel free to check out their documentation and try different endpoints!
I want to be able to work with each row using varying values. So is there a way to prevent the rows from rearranging every time I refresh the data ? That way my calculations don’t get messed up.
Hey Kris! So my go to method would actually to pipe that raw data over to another sheet using SORT() to ensure the order, or some similar idea. Let me know if that works for you!
Check out this article (scroll down a bit) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num3 To convert Binance’s timestamp to a human readable date, use the following formula in Sheets: =({Sheet!Cell}/1000)/86400+date(1970,1,1)
@@mixedanalytics In my case the following script worked: =({Sheet!Cell}/1000)/86400+DATA(1970;1;1). Instead of a " , " I used " ; ". I think it is connected with excel settings.
Thanks for this video! Can u make another with a signed endpoint example pls. For instance, to get C2c order history require a signature and timestamp; how would I write out the url expression with the timestamp and signature?
Hey Cyrus, I just found these links check them out! Pancakeswap: github.com/pancakeswap/pancake-info-api/blob/develop/documentation.md Burgerswap: Documentation sucks (basically useless, I would just email them directly or google around/check GitHub) but this is what I found: doc.burgerswap.org/ you could also try this one I found through Inspector(Ctrl+Shift+I): api.burgerswap.org/getGlobalInfo Let me know if that helps!
Hi Moshin! Sorry, we don't work with Excel. If you want to work with your own accounts data, the process a bit more convoluted but you can check it out here: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/
hello @mixed analytics.. I cannot pull in all the data of symbols. I am just in the first step but after RUN nothing happens. I'd tried all the api 1,2,3.. what might be the problem?
Do you see a message below the "run" button? You could also post a screenshot/Loom of your setup over on our Reddit for help as well. Here's the link: www.reddit.com/r/api_connector/
Hi I'm really struggling to get this too work, I've downloaded the add-on but it won't let me add a new API request, it always says i have a demo request saved but I can't delete this.
Hmmm, I'm gonna take a shot and say it's a cookies/extension issue. Have you tried running in an private browser/different browser to see if the same results occur?
Hello, I am getting the following ERROR: Status Completed with errors - Server responded with an error (403) The request could not be satisfied. 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. Try again later, or contact the app or website owner. If you provide content to customers through CloudFront, you can find steps to troubleshoot and help prevent this error by reviewing the CloudFront documentation. Any help would be appreciated
Hello, My go to trick for dealing with this is changing the base endpoint from just api to api1,api2, or api3. Read more here: binance-docs.github.io/apidocs/spot/en/#general-info For more information on this, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again. www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D You can try also: 1)to decrease the size of your requests as well, as this may help, or 2) create an account and use an API key or 3) try a new browser Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol). Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well. Let me know if all that helps!
Hi! That's pretty interesting, I'm not sure why they would come in horizontally... You are using the kline endpoint right? I think you can try dropping a message + screenshot over on our support Reddit to get quick technical help: www.reddit.com/r/api_connector/
You can check out this sheet we've put together. It's a bit technical, but give it a shot! docs.google.com/spreadsheets/d/1mA31i-4E9yKQJ9rWBaICQBPesGfaGpazX6rZBz9JHqs/edit?usp=sharing
Not sure what you mean here. It's good to call that first endpoint to get Binance's internal symbol system so you have a reference. Other endpoints would need to specify symbols, so you wouldn't be wasting bandwidth
Hello, From their website, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again. www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D You can try also: 1)to decrease the size of your requests as well, as this may help, or 2) create an account and use an API key or 3) try a new browser Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol). Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well.
Check out the /api/v3/avgPrice endpoint if you just want live prices. You can get the live price every time you hit "run" in the API connector sidebar. Please note the free plan of API Connector only allows 8 calls per day. As for Bollinger band values, you would have to setup a sheet to calculate that yourself as their API doesn't provide that data. Check out this Reddit post: www.reddit.com/r/BinanceExchange/comments/gsa8kz/how_to_calculate_bollinger_bands_from_api/ I imagine for a setup like that you would probably want to do a raw data sheet like I did in this video followed by =rawDataSheet!A:Z in another sheet to pull it over, then do your calculations there. Let me know if that helps!
I followed every step exactly as the video but I don't know why it doesn't work for me Completed with errors - Server responded with an error (403) This message keeps coming up
Hi Chad, From their website, they also mention that 403 bans generally last 5 minutes, so you could chill a minute then try again. www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D You can try also: 1)to decrease the size of your requests as well, as this may help, or 2) create an account and use an API key or 3) try a new browser Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses. Sorry I know this got super technical real quick, but yeh this is why I'm not a huge fan of Binance. Their documentation and usage is a bit advanced and confusing unfortunately :( and they are really picky about volume to their service. If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well. Let me know if all that helps!
Hi, what if I only wanted to pull daily revenue from Binance to Google sheets, is this possible? So essentially it will have a DATE and REVENUE collum.
Hi Gin! Not sure what you mean here, sounds like revenue would require you to have an API key. I didn't cover this in this video, but you can read more here and follow our template doc: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num4 Hope that helps!
Hi Agustin! Check out this article (scroll down a bit) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num3 To convert Binance’s timestamp to a human readable date, use the following formula in Sheets: =({Sheet!Cell}/1000)/86400+date(1970,1,1)
Hello, great video, it's been very useful, but I'm having a problem when the data arrives in the spreadsheet, right in the price issue. The values do not come 0.00000 they see all the numbers together with no dots or commas to separate them. I've tried to change the formation, remove formatting, but the values remain all together and it's not possible as far as I know to change that. You can help?
Hmmmm.... Can you please try switching the locale of the sheet to US? (click File > Settings > Locale). I think that might fix it. Let me know if that doesn't work!
Hello, is there any way to do this continously? instead of creating a mobile app or desktop app, i'd like my google sheet to continously be updated with the prices
Hey Wildheart! Due to limitations within Google Sheets itself, you can't "continuously" keep a cell refreshed within Google Sheets. There are also "rate limits" to keep in mind when using an API (oftentimes you can't call a particular service more than X times per minute/hour, particularly so with Binance. Our pro version allows for hourly updates automatically, but this doesn't guarantee you won't run into issues. You can read more on our docs here: mixedanalytics.com/knowledge-base/troubleshooting-api-requests/#num10
Excellent work! A Question: I need to import the latest price of a coin pair from Binance to a single cell in Google Sheets and keep it refreshing at 1 minute intervals. Can you please tell me how I can do this? Many thanks.
Hi Lou! From a technical standpoint, that's not going to be possible because of limits imposed by Google, but anyway we wrote up an article on doing basically this: mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/#num6
Hi, I'm constantly getting "multiple errors occurred running all requests".. this has been a smaller issue since the beginning (3 months ago), but lately I can barely get a refresh.. And I think these refreshes also count in my 8 free daily requests. Is there a fix for this?
Hey Jure, Yeh this is unfortunately an issue with Binance's API. We are all frustrated with Binance's API throttles! :( www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D Unfortunately existing workarounds suck. Also, those failed requests don't count against you so no need to worry :D
I'm not sure how you would do this in Excel. API Connector only works for Google Sheets, though I am sure there are some other extensions you could search for for Excel specifically.
Hi, I have to try this fuction witout apiconnector, because its trial. i try it with script editor. But i cant work it for binance. Can you do this with code editor?
Certainly this can be done. I'd suggest using Postman first, as it's similar to API Connector in terms of functionality to confirm your request is setup properly.
Hello sir, example above is only prev close and I want to get all the daily closes of btc eth ada dot bnb in the last 7 days in one api call like they have in yahoo for tradfi.
Hey Byron, You'll want to follow the documentation for klines as seen in the video: binance-docs.github.io/apidocs/spot/en/#kline-candlestick-data specify startTime -7 days (make sure you convert time to UNIX), then just set the ticker interval=1d to get daily for example. I guess with klines you would need to have a separate sheet for each crypto you want. Let me know if that helps!
Hello. Thank you for sharing. But I keep on getting this error " We received an error from the API server (403) - You may have hit a rate limit. " I've already tried to limit the query to one symbol. Please advise.
@@basantmailbox If you are having 403 issues: Try changing the domain from api1.binance.com to api2.binance.com or api3.binance.com. If the 403 persists, you can try: 1) to decrease the size of your requests 2) create an account and use an API key Read more on their website here: www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D 2
great video brother , i have a question , is it possible to capture the market data of to movers tab ( ie : Large Buy , large Sell , pullback ) using the binance API key
Hey Hemanth, great question! Browsing their docs, I didn't see anything like that, but check this one out: whale-alert.io/ I think this service has what you are looking for. Let me know if that helps!
@@mixedanalytics whale alert ,i think its good for checking the big transactions , i am more interested in the large transactions which DID NOT MOVE THE PRICE basically i am interested in finding out a good accumulation range , by tracking the SPLIT orders of whales ,
@@HemanthMukundan Hmm, this is a bit beyond my expertise (I'm more of a coder, not so much a trader). My guess is from googling quickly is that this would have to be manually/programmatically calculated. I can't find any endpoints that currently provide a single number for this. Sounds like a cool project though!
Hi Adi! Check out this article (scroll down a bit) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num3 To convert Binance’s timestamp to a human readable date, use the following formula in Sheets: =({Sheet!Cell}/1000)/86400+date(1970,1,1)
@@Cheshire2898 Ahh, you can try this one: =({cell}/1000)/86400+date(1970,1,1) + time(8,0,0), with that time part being adjust to UTC (I think). Let me know if that works for you!
It is possible, but not easy. One of our users wrote up an interesting solution, you can check it out here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
Thanks Prakhar! I don't know much about futures, but if you check the top of the Binance API docs page, there are two tabs for USD-M Futures and Coin-M Futures endpoints, like for example: binance-docs.github.io/apidocs/futures/en/#order-book Let me know if that helps :)
Hello, From their website, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again. www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D You can try also: 1)to decrease the size of your requests as well, as this may help, or 2) create an account and use an API key or 3) try a new browser Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol). Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well. Let me know if all that helps!
Not officially. I found some documentation of some people hacking a solution though: stackoverflow.com/questions/67793326/api-binance-p2p-i-only-access-a-part-only-the-buy-and-not-all-of-it-buy-and-s
Hi Guillaume, Yep, you can check out these endpoints: github.com/binance/binance-spot-api-docs/blob/master/rest-api.md#compressedaggregate-trades-list, this one you will want to specify the startTime and endTime Trade opened, I'm not sure what you mean by that. Can you clarify?
If you are talking about the HMAC stuff, you can check out a hack one of our users put together: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068 This should be towards having your own portfolio.
Hey ! thanks for this video. But i've got a problem. When i'm using the api connector, and click on "Run". i've got a message error type 403. Does someone got this problem to ?
Hello Pitou, From their website, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again. www.binance.com/en/support/faq/360004492232 binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D You can try also: 1)to decrease the size of your requests as well, as this may help, or 2) create an account and use an API key or 3) try a new browser Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol). Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well. Let me know if all that helps!
Hi Talmon, Private data is a little more tricky. You can check our article here: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num4
Hello! There are a few ways. If you just want to get all tickers, just call this endpoint: api1.binance.com/api/v3/ticker/bookTicker Inside of the Binance API, you can't comma separate tickers. It's basically all-or-nothing with respect to a given endpoint. Otherwise if you want the kline data like I mentioned in the video, you would need to setup an API request for each symbol. You can read more about it here: mixedanalytics.com/knowledge-base/api-connector-run-multiple-queries-single-request/ If you are using our free version, just be aware that we limit to 8 requests/day for free accounts.
Hi great video...thankyou!! I would like to import my portfolio with all the transactions....is it possibile? Could you explain how to do it? Is it possible also to create an automated trading? Thankyou for your answers
Hi Sara, I peeked into their documentation and I found a few endpoints that you could check out: Withdraw data: binance-docs.github.io/apidocs/spot/en/#withdraw-history-user_data Deposit data: binance-docs.github.io/apidocs/spot/en/#deposit-history-supporting-network-user_data This question is quite complex for a RUclips comment haha, but you could try posting your ideas in our Reddit/browsing to see if anyone else has created something similar www.reddit.com/r/api_connector/. I can tell you right now automated trading would be tricky, but definitely doable in Google Sheets (would need to use POST requests to send to Binance binance-docs.github.io/apidocs/spot/en/#test-new-order-trade). You'll also have to get an API key from Binance. Here's a basic dashboard you can check out for inspiration that we put together: docs.google.com/spreadsheets/d/1yO_5bvczvt1pghy1TSj36WQdDBfbWQQxXRct43p2m0U/edit#gid=1460402550 Hope that all helps!
@@mixedanalytics thankyouuuuuu and what about all transactions. Could you make a video for creating a spreadsheet with all transactions done in binance? Keep going with great work ;)
![Beginners Intro to Pulling API Data into Google Sheets [BONUS: Secret APIs]](http://i.ytimg.com/vi/9VBxRZgY8_4/mqdefault.jpg)
![Get crypto prices from MORE than 100 different exchanges with Python & CCXT [Arbitrage Series]](http://i.ytimg.com/vi/sj9cqfct4Ac/mqdefault.jpg)
🤑🤑🤑For creating your own balance sheet based on your Binance account, you can check out a solution one of our users came up with here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
🚧🚧🚧If you are having 403 issues:
Try changing the domain from api1.binance.com to api2.binance.com or api3.binance.com.
If the 403 persists, you can try:
1) to decrease the size of your requests
2) create an account and use an API key
Read more on their website here:
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
Hey there! Can you help me to figure out why BTCCNY, USDTCNY etc is not able to get? Probably any tickers with CNY (Chinese yuan)
I can't find where binance mention all available tickers except get list (ticker/price) I want to get tickers
Thank you, was helpful! Just as a tip- in some cases is hard to follow you when you were in the Binance menu as you were jumping around all bur Thanks again!
Thanks for the tip! I'm gonna be working on some better highlighting/zoom features for the upcoming ones :)
Thanks for the great video. Direct to the point, no time wasted.
Glad it was helpful!
Мужик! Ты мой герой) Спасибо, за крутое обучение
👍👍
1. Can i get my trade history, order history, open orders in Google Sheet?
2. Can I put formulas to check if any scrip is in profit or loss as per current market price?
3. Can i import data from multiple exchanges for my executed orders and keep in 1 sheet? Sync after every interval
Hey Nimesh!
1. We had one of our users come up with a cool trick for getting their Binance data in: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
2. You could wire this up yourself by refreshing and referencing current market price from another sheet
3. Depends on if those exchanges have API access, you'd have to check their website. You can sync every X times/day with our pro subscription yes :)
@@mixedanalytics Thank You
Finally some one explain this topic in great way!! Thx so much!! I want to get indicators data, can I get it from the API too??
Binance doesn't have any, but you could check out taapi.io/
I found this video very useful because I now get the data I want from Binance but my challenge is when ever I try to pull in multiple cryptocurrencies even with as intervals set to just 2, I get a timeout response after five minutes stating that the data seems large for the API CONNECTOR to handle at once. Meanwhile, am seeking to pull in a minimum of 30 intervals for about 400 cryptocurrencies at once
So
1. How can the one limit the number of columns to just OHLC and jettison the remaining columns after CLOSE in order to reduce data size?
2. How can API CONNECTOR be configured to receive very large data sets because I want to retrieve lots of history prices for multiple coins just from one click of the run button?
Help!!!
Thanks
Hi ib,
1. If you have one of the higher pricing subscriptions, you can access JMES Path to lighten the request and speed it hopefully to hit within your time-limitation: mixedanalytics.com/knowledge-base/filter-specific-fields-values/
2. It's a little hard for me to understand based on a YT comment, but I would think in this use case you may want create an API request for each crypto. You could test to see if it can handle a call that big by testing on a few to see if it can reach back that far. Then, you'd just duplicate the request and run each request seperately via scheduling. The downside to this is that you'd have to have a unique sheet for each crypto, but you could always just recombine them later with something like =QUERY({'DOGE'!A2:D7;'BTC'!A2:D7},"select * where Col1 ''") in another sheet to combine all of them.
Extremely Helpful. Thank you sir!!!
You are very welcome :):)
Great vid! How can we update the symbols data automatically every 30 seconds or so?
Hi Devashish!
So Google limits the amount of requests you can make in a day across your entire Google account, you can read more in our write-up here: mixedanalytics.com/knowledge-base/api-connector-scheduling/#num6. We also do, you can read more about our limits for different subscription levels: mixedanalytics.com/api-connector-pricing/.
If you need a faster refresh rate, I would suggest looking into webhooks (a different protocol from API), you can read more about them here: www.mparticle.com/blog/apis-vs-webhooks
Bonjour, y'a t'il une solution pour ne supprimer que les ordres d'achats posés sur Binance ? J'aimerais pouvoir toutes les 30 minutes par exemple que mon robot de trading sur google sheets supprime les ordres d'achats (uniquement) qui ne sont plus utile car la crypto est montée. Merci
i received a message 403 blocked due to excessive traffic on google and it said to switch to a websocket. How do I do that?
How to list the data from /api/v3/ticker/24hr in excel as 1 or 5 minutes; that is, how can I keep the old data sequentially, while the data is refreshed every 1 or 5 minutes?
Thanks for sharing, it's very helpful. Can I filter the list of coins before import? For instance, if I want a list of only coins that pairs with USDT? Thanks
how can i ask the api for my hole $ balance? i want to put how many $ i have on balance in my sheet, cheers !
It is possible, but not easy. One of our users wrote up an interesting solution for getting your account balance, you can check it out here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
I'm having this error message after I hit the Run button. "We received an error from API server (403) You may have hit a rate limit..." Please help. Thanks in advance!
Try changing the domain from api1.binance.com to api2.binance.com or api3.binance.com.
If the 403 persists, you can try:
1) to decrease the size of your requests
2) create an account and use an API key
Read more on their website here:
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
nice tutorial , but I receive : "1) symbols: Completed with errors
- We received an error from (403) " I do not know what to do
Thank you, I have been looking for this for a long time :D could you also let me know how can we add startTime and endTime?
Sure thing! You'll want to append &startTime={Unix timestamp milliseconds} and same for &endTime={Unix timestamp milliseconds}.
For converting from unix to human time, check out this page: www.unixtimestamp.com/index.php but note, you will need to add 3 zeros on the end for it to work (most websites don't allow ms as input, so you will need to add them manually).
So a sample call to klines would look like this:
api1.binance.com/api/v3/klines?symbol=ETHBTC&interval=1h&startTime=1614528000000&endTime=1614960000000
@@mixedanalytics Really appreciated, thank you for your time and solution
Sup, my homie
Wanna understand your ideas on PolkaCover. Best insurance system and looks they will go to the moon soon. Do you think I can make some money with them?
Thank you.. Can i do the same way to import the order book and the order history?
i get continuously this error
Completed with errors
- Server responded with an error (403) - You may have hit a rate limit. Please try using one of Binance's other base domains, for example api3.binance.com
So basically in the same spot in the URL Path that says api1.binance.com, you need to swap that with api2.binance.com or api3.binance.com
@@mixedanalytics try many time doesnt work
@@juanjosemolina5501 Sorry about that, if you check the pinned comment on this channel, it's a pretty big issue that comes from Binance's side. I think you should be able to get around it if you make your own API key and use that to authenticate requests ( I haven't personally tried yet, you can check this link: www.binance.com/en/support/faq/360002502072)
hi, can we get different price of future contract of crypto
hope to get some reply from you
Hi Pawan!
Sure, you just need to switch over to the USD-M/Coin-M endpoints:
Check this one out for example:
binance-docs.github.io/apidocs/futures/en/#order-book
Getting a 403 Error in google sheets, how should I resolve that?
from where you open the api connector config? i ready installed but i cant see it
how do you import transaction history ? to generate a portfolio manager sheet? thks
Sounds like there may be a few issues. You could try doing a hard refresh on your browser or running in private mode (sometimes API Connector doesn't play well with ad blockers, etc)
As for transaction history and building a portfolio manager, you can check out a solution one of our users came up with here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
Is there a way to have the coin prices that you input into sheets auto-update??
So at present there is no way (that I know of) to web hook into Sheets (if you aren't familiar with web development, a web hook is a kind of live updating API). With the pro version of API Connector, you could refresh hourly/daily (choose your refresh rate) to kind of simulate this, but you'll need to be careful of rate limits from Binance. Binance is notorious for throwing 403's at all Google extensions, so just something to watch out for :)
@@mixedanalytics
Thank you for the response. I know that if you go into a crypto chart on crome and inspect say a price that updates every second. You can see in the code a purple pulse and if you drill down into that code, the price pulses and changes state every second or so. That is what I trying to extract but with no luck. I am not familiar with web development or any Java or python type programing language but I'm a fast learner. Is this web hook you are talking about able to import into sheets?
@@rbendzsuk You are already a web developer if you know how to Inspect a page hahaha ;) I'm a beginner web dev myself, but I just inspected that front page as well, and it looks like they use React (a JS framework) to render that number. In other words, they are likely just running the same API we are using but on their own back end so it's registered and they obviously wouldn't put a limit on their own servers. At present, I don't know any no/low code tools to accomplish a live dashboard due to technical barriers.
Also I confused webhook for websocket lol. The websocket on binance is here: binance-docs.github.io/apidocs/spot/en/#websocket-market-streams It's similar to an API, but the difference is your application subscribes to that websocket and then gets live data from that stream. I haven't worked with either of them yet, only knowing about them from reading articles, so I won't admit to being an authority figure at all. But from what I can best tell, based on Google Sheets' limitations you would quickly hit a hard cap trying to grab that data and dump it directly into sheets.
@@mixedanalytics
I appreciate the insight.
nice, just what i needed
Glad it was helpful :)
Hi! Thanks for the video! I've done all the steps but it returns an error 400... {"code":-1102,"msg":"Mandatory parameter 'symbol' was not sent, was empty/null, or malformed."}. Do you have idea how to solve that? Thanks again!
Hey Felipe!
Just make sure you set the symbol=BTC or whichever one you want to pull. Cheers!
Hi, thanks for this video, very useful to start learning about this!!! I have a question, once you connect the API and download the ticker price, is this static data? how often this updates?? Thanks
You're welcome Eduardo! And to answer, yes the data is static. If you want to update it, you will need to re-run the request. With our pro feature, you can automate this with triggers.
@@mixedanalytics what is pro feature and how can i get it
@@sherifkordy190 Here check this one out: mixedanalytics.com/api-connector-pricing/
Hi! I was just wondering if you could please help me out in turning the data in the open time and close time into a human readable format such as year/month/day 21:00. Thanks
Sure thing, try this formula
=({Sheet!Cell}/1000)/86400+date(1970,1,1)
Thanks só much! Great vídeo. Infortunly is so hard find The average price, but o Will try with Excel formuls
You can do it!
Thank you a lot! Any idea on how could I connect via API and get my spot trades from Binance via API connector? Thank you again :)
One of our users came up with a kinda working solution for getting your personal data, you can check the post here: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
Does somebody know, how reload trade history from account binance to google sheets???
Stay tuned, it's coming! :D
Please do a video on how to set a bot or market condition in it
noted! ;)
Thank you...it has been helpful....but how can I draw the candlestick charts..and the weighed moving avarage charts
Hey Space Angel, I belive that's one of the default chart types in Sheets: support.google.com/docs/answer/9146870?hl=en
Thank you very much for this video. This is great info. Please make more instruction videos!
More to come! Thanks for the comment :)
Hi there! I want to have a dashboard kind of thing in Excel/Google Sheets that automatically captures my trades from Binance and shows the profits and loss. Can this be done? and How?
Thanks in advance.
Hi Mustafa, I peeked into their documentation and I found a few endpoints that you could check out:
Withdraw data: binance-docs.github.io/apidocs/spot/en/#withdraw-history-user_data
Deposit data: binance-docs.github.io/apidocs/spot/en/#deposit-history-supporting-network-user_data
This question is quite complex for a RUclips comment haha, but you could try posting your ideas in our Reddit/browsing to see if anyone else has created something similar www.reddit.com/r/api_connector/. While this is a little complicated, you could definitely wire up a dashboard that accomplishes what you would want. You'll also have to get an API key from Binance.
Here's a basic dashboard you can check out for inspiration that we put together: docs.google.com/spreadsheets/d/1yO_5bvczvt1pghy1TSj36WQdDBfbWQQxXRct43p2m0U/edit#gid=1460402550
I'm also planning to do a tutorial soon on something similar, so hang tight!
Hope that all helps!
@mixed analytics could you please do a video on how to show all one's Binance fiat and spot trades in googlesheets/excel. I follwed their documentation but i keep getting weird errors as recvwindow isnt large enough or timestamp is not passed, even though i pass it as parameter, alongside the HMC 256 signature but still no luck. Thank you!
@@salahouadi6343 You can check out this solution one of our users came up with (requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
Awesome man this worked perfectly well really big thanks.
Glad I could help! :)
Hi, thanks for the video. But something seems to be wrong with the data im getting. I am for an example getting the value 55268:20:00.000 for my ETHBTC pairing
What did you change in your query compared to the way I set it up?
@@mixedanalytics Ireally don't know. There isn't mutch to do different haha. I've tried several times now and even reinstalled mixed analytics. Do you have any idea
@@oscarschyum3962 On second thought, it sounds like to me it could be an issue with the actual sheet formatting. Select the column that is showing that data, then select Format->Number->Number, or try some other formats to see if that helps. Let me know if that worked :) We'll figure this out no sweat.
@@mixedanalytics Okej thanks man. Yeah so i had that thought too and tried all the different number formats but it dosn't show the correct numbers. I tried your new coinbase video too and i get the value 16666:40:00.000 istead of your 0.01 under the kolumn: data>>min_size. If i reformat it to a number i get the value 694,4444444 instead of 0.01. Really strange
@@oscarschyum3962 That is pretty weird! What browser are you using? Maybe try running in private/incognito as a last resort. If that doesn't work, you can post a screenshot/more details over in our subreddit and we can check it out:
www.reddit.com/r/api_connector/
Great video, just like ever. Could you bring bid and ask price? Wich are thoose camps? Thanks!
Thanks Fernando :)
Yep, you can try the /depth endpoint outlined here:
binance-docs.github.io/apidocs/spot/en/#order-book
nice
after thanks.. how can i import the order book and the order history or th market trades from binance
Order book: binance-docs.github.io/apidocs/spot/en/#order-book
Older orders: binance-docs.github.io/apidocs/spot/en/#compressed-aggregate-trades-list
Feel free to check out their documentation and try different endpoints!
I want to be able to work with each row using varying values. So is there a way to prevent the rows from rearranging every time I refresh the data ?
That way my calculations don’t get messed up.
Hey Kris!
So my go to method would actually to pipe that raw data over to another sheet using SORT() to ensure the order, or some similar idea.
Let me know if that works for you!
how i can replace dot / comma
Thanks for the tips.. it's really helpful.. btw how to convert the time to regular date and time (hours & minutes) ?
Check out this article (scroll down a bit) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num3
To convert Binance’s timestamp to a human readable date, use the following formula in Sheets:
=({Sheet!Cell}/1000)/86400+date(1970,1,1)
@@mixedanalytics In my case the following script worked: =({Sheet!Cell}/1000)/86400+DATA(1970;1;1). Instead of a " , " I used " ; ". I think it is connected with excel settings.
@@annalogarytm2205 Awesome I'm glad you figured it out :)
Thanks for this video! Can u make another with a signed endpoint example pls. For instance, to get C2c order history require a signature and timestamp; how would I write out the url expression with the timestamp and signature?
I'll put it in the pipeline. In the mean time, check out our new Application build-in integration!
thanks
You're welcome!
Hi! Can you please show how to get raw exchange data from pancakeswap and burgerswap? i.e. bnb-cake or bnb-burger
Hey Cyrus,
I just found these links check them out!
Pancakeswap: github.com/pancakeswap/pancake-info-api/blob/develop/documentation.md
Burgerswap: Documentation sucks (basically useless, I would just email them directly or google around/check GitHub) but this is what I found: doc.burgerswap.org/
you could also try this one I found through Inspector(Ctrl+Shift+I): api.burgerswap.org/getGlobalInfo
Let me know if that helps!
Thanks for the wonderful video. how can i connect my spot account of binance with google sheets or excel and trade simultaneously.
Hi Moshin!
Sorry, we don't work with Excel. If you want to work with your own accounts data, the process a bit more convoluted but you can check it out here: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/
Can you do short video on importing Binance futures wallet into google spreadsheets please
Sure thing! I'll add to the potential list of videos :)
hello @mixed analytics.. I cannot pull in all the data of symbols. I am just in the first step but after RUN nothing happens. I'd tried all the api 1,2,3.. what might be the problem?
Do you see a message below the "run" button? You could also post a screenshot/Loom of your setup over on our Reddit for help as well.
Here's the link:
www.reddit.com/r/api_connector/
Hi I'm really struggling to get this too work, I've downloaded the add-on but it won't let me add a new API request, it always says i have a demo request saved but I can't delete this.
Hmmm, I'm gonna take a shot and say it's a cookies/extension issue. Have you tried running in an private browser/different browser to see if the same results occur?
Hello,
I am getting the following ERROR:
Status
Completed with errors
- Server responded with an error (403)
The request could not be satisfied.
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. Try again later, or contact the app or website owner.
If you provide content to customers through CloudFront, you can find steps to troubleshoot and help prevent this error by reviewing the CloudFront documentation.
Any help would be appreciated
Hello,
My go to trick for dealing with this is changing the base endpoint from just api to api1,api2, or api3. Read more here: binance-docs.github.io/apidocs/spot/en/#general-info
For more information on this, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again.
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
You can try also:
1)to decrease the size of your requests as well, as this may help, or
2) create an account and use an API key or
3) try a new browser
Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol).
Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well.
Let me know if all that helps!
Thank you very much for the video, I see the prices horizontally separated by commas, how can I solve this problem?
Hi! That's pretty interesting, I'm not sure why they would come in horizontally... You are using the kline endpoint right?
I think you can try dropping a message + screenshot over on our support Reddit to get quick technical help: www.reddit.com/r/api_connector/
thank youuu very interesting
You are great, thank you!
You are awesome too :D
Thank you, so do you think there could be a balance sheets for Binance Earnings? To track only the Earnings aspect of Binance?
You can check out this sheet we've put together. It's a bit technical, but give it a shot!
docs.google.com/spreadsheets/d/1mA31i-4E9yKQJ9rWBaICQBPesGfaGpazX6rZBz9JHqs/edit?usp=sharing
What to do if I want to get specific symbols without consuming the internet for non useful symbols?
Not sure what you mean here. It's good to call that first endpoint to get Binance's internal symbol system so you have a reference. Other endpoints would need to specify symbols, so you wouldn't be wasting bandwidth
Nice API. Using on Excel.
Hi Eduardo,
Sorry we don't support Excel. We are a Sheets-only add-on. Sorry for any inconvenience!
Doug
Status
Completed with errors
- Server responded with an error (403)
Hello,
From their website, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again.
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
You can try also:
1)to decrease the size of your requests as well, as this may help, or
2) create an account and use an API key or
3) try a new browser
Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol).
Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well.
Hey can we fetch live prices too which refresh when we want ? And about about Bollinger band values at different intervelas can we grab them too ?
Check out the /api/v3/avgPrice endpoint if you just want live prices. You can get the live price every time you hit "run" in the API connector sidebar. Please note the free plan of API Connector only allows 8 calls per day.
As for Bollinger band values, you would have to setup a sheet to calculate that yourself as their API doesn't provide that data. Check out this Reddit post: www.reddit.com/r/BinanceExchange/comments/gsa8kz/how_to_calculate_bollinger_bands_from_api/
I imagine for a setup like that you would probably want to do a raw data sheet like I did in this video followed by =rawDataSheet!A:Z in another sheet to pull it over, then do your calculations there.
Let me know if that helps!
This video was awesome, thank you so much!
Glad you liked it!
I followed every step exactly as the video but I don't know why it doesn't work for me
Completed with errors
- Server responded with an error (403)
This message keeps coming up
Hi Chad,
From their website, they also mention that 403 bans generally last 5 minutes, so you could chill a minute then try again.
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
You can try also:
1)to decrease the size of your requests as well, as this may help, or
2) create an account and use an API key or
3) try a new browser
Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses.
Sorry I know this got super technical real quick, but yeh this is why I'm not a huge fan of Binance. Their documentation and usage is a bit advanced and confusing unfortunately :( and they are really picky about volume to their service. If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well.
Let me know if all that helps!
Hi, what if I only wanted to pull daily revenue from Binance to Google sheets, is this possible? So essentially it will have a DATE and REVENUE collum.
Hi Gin!
Not sure what you mean here, sounds like revenue would require you to have an API key. I didn't cover this in this video, but you can read more here and follow our template doc: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num4 Hope that helps!
Good job! Thank you.
You are welcome :)
Hi! Thanks for the tutorial! Worked great. How can I change the "open time" value to time format.
Hi Agustin!
Check out this article (scroll down a bit) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num3
To convert Binance’s timestamp to a human readable date, use the following formula in Sheets:
=({Sheet!Cell}/1000)/86400+date(1970,1,1)
Hello, great video, it's been very useful, but I'm having a problem when the data arrives in the spreadsheet, right in the price issue. The values do not come 0.00000
they see all the numbers together with no dots or commas to separate them. I've tried to change the formation, remove formatting, but the values remain all together and it's not possible as far as I know to change that. You can help?
Hmmmm.... Can you please try switching the locale of the sheet to US? (click File > Settings > Locale). I think that might fix it. Let me know if that doesn't work!
Great video! Thanks. Do you have a similar video that involves the use of API keys?
Not yet! Working on something for a dashboard. Binance is a bit of a headache for accessing secure data, but it is possible!
Hello, is there any way to do this continously? instead of creating a mobile app or desktop app, i'd like my google sheet to continously be updated with the prices
Hey Wildheart!
Due to limitations within Google Sheets itself, you can't "continuously" keep a cell refreshed within Google Sheets. There are also "rate limits" to keep in mind when using an API (oftentimes you can't call a particular service more than X times per minute/hour, particularly so with Binance. Our pro version allows for hourly updates automatically, but this doesn't guarantee you won't run into issues. You can read more on our docs here: mixedanalytics.com/knowledge-base/troubleshooting-api-requests/#num10
Awesome. Thanks a lot
Most welcome!
Excellent work! A Question: I need to import the latest price of a coin pair from Binance to a single cell in Google Sheets and keep it refreshing at 1 minute intervals. Can you please tell me how I can do this? Many thanks.
Hi Lou!
From a technical standpoint, that's not going to be possible because of limits imposed by Google, but anyway we wrote up an article on doing basically this: mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/#num6
Many thanks for your reply. I'll take a look, try it out, and get back to you. Keep up the great work !
Hi, I'm constantly getting "multiple errors occurred running all requests".. this has been a smaller issue since the beginning (3 months ago), but lately I can barely get a refresh.. And I think these refreshes also count in my 8 free daily requests. Is there a fix for this?
Hey Jure,
Yeh this is unfortunately an issue with Binance's API. We are all frustrated with Binance's API throttles! :(
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
Unfortunately existing workarounds suck.
Also, those failed requests don't count against you so no need to worry :D
kral büyüksün...
teşekkür ederim :)
Hi, it was great.
2:15 GET symbols
How can I define in Excel that automatically updates the price of the symbols?
I'm not sure how you would do this in Excel. API Connector only works for Google Sheets, though I am sure there are some other extensions you could search for for Excel specifically.
How to set up automatic data updates for Google Sheets?
Hello! I think this may answer your question: mixedanalytics.com/knowledge-base/api-connector-scheduling/
Hi,
I have to try this fuction witout apiconnector, because its trial. i try it with script editor.
But i cant work it for binance. Can you do this with code editor?
Certainly this can be done. I'd suggest using Postman first, as it's similar to API Connector in terms of functionality to confirm your request is setup properly.
Hello sir, example above is only prev close and I want to get all the daily closes of btc eth ada dot bnb in the last 7 days in one api call like they have in yahoo for tradfi.
Hey Byron,
You'll want to follow the documentation for klines as seen in the video: binance-docs.github.io/apidocs/spot/en/#kline-candlestick-data
specify startTime -7 days (make sure you convert time to UNIX), then just set the ticker interval=1d to get daily for example. I guess with klines you would need to have a separate sheet for each crypto you want.
Let me know if that helps!
HOw do I get connected to Binance and retrieve live market data like 1mn or tickers? 24/7
Hi Philippe!
You can check out this one: binance-docs.github.io/apidocs/spot/en/#recent-trades-list
Hello. Thank you for sharing. But I keep on getting this error " We received an error from the API server (403) - You may have hit a rate limit. "
I've already tried to limit the query to one symbol. Please advise.
I am also facing same
@@basantmailbox If you are having 403 issues:
Try changing the domain from api1.binance.com to api2.binance.com or api3.binance.com.
If the 403 persists, you can try:
1) to decrease the size of your requests
2) create an account and use an API key
Read more on their website here:
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
2
great video brother , i have a question , is it possible to capture the market data of to movers tab ( ie : Large Buy , large Sell , pullback ) using the binance API key
Hey Hemanth, great question! Browsing their docs, I didn't see anything like that, but check this one out: whale-alert.io/ I think this service has what you are looking for.
Let me know if that helps!
@@mixedanalytics whale alert ,i think its good for checking the big transactions , i am more interested in the large transactions which DID NOT MOVE THE PRICE basically i am interested in finding out a good accumulation range , by tracking the SPLIT orders of whales ,
@@HemanthMukundan Hmm, this is a bit beyond my expertise (I'm more of a coder, not so much a trader). My guess is from googling quickly is that this would have to be manually/programmatically calculated. I can't find any endpoints that currently provide a single number for this. Sounds like a cool project though!
Thank you so much!!!!
Thanks for your support :)
Thanks
No problem
add-on button is not there?
Yep, Google changed their UI. Check under "Extensions" now.
How to convert the time to readable ?
Hi Adi!
Check out this article (scroll down a bit) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num3
To convert Binance’s timestamp to a human readable date, use the following formula in Sheets:
=({Sheet!Cell}/1000)/86400+date(1970,1,1)
@@mixedanalytics it only change to date instead of time and date. Do you know how to change it to date and time?
@@Cheshire2898 Ahh, you can try this one: =({cell}/1000)/86400+date(1970,1,1) + time(8,0,0), with that time part being adjust to UTC (I think).
Let me know if that works for you!
Will the addin refresh or work even if I'm using phone app Google sheets? Thank you.
Reply to self: no it doesnt because the addins needs to be run in web view.
Correct ;) But if you want to do automatic updating check out our pro version for scheduling functionality! mixedanalytics.com/api-connector-pricing/
Can you show how to authenticate via Excel to get the wallet balance for example? Thanks
Hey Miguel, sorry I don't know anything about Excel :( I'm a Google person!
how i get long short ratio indice by excel ?
Hi Ricardo, sorry I don't know anything about Excel. I'm a Sheets person!
Nice tutorial! One question, is it possible to get balances from my binance account?
It is possible, but not easy. One of our users wrote up an interesting solution, you can check it out here:(requires a custom Google App Script) mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
tell me how to import the current balance of binance into Google Sheets
Hi Nazurich, I'm working on a solution right now. In the meantime, check out the pinned comment, one of our users came up with a work around :D
Hey , great vedio , i have a question though , how can we download the future contract data for crypto from binance ? please help me out here
Thanks Prakhar!
I don't know much about futures, but if you check the top of the Binance API docs page, there are two tabs for USD-M Futures and Coin-M Futures endpoints, like for example: binance-docs.github.io/apidocs/futures/en/#order-book
Let me know if that helps :)
using your exact settings but keep getting error (403) from the server. any tips?
Hello,
From their website, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again.
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
You can try also:
1)to decrease the size of your requests as well, as this may help, or
2) create an account and use an API key or
3) try a new browser
Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol).
Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well.
Let me know if all that helps!
Thank you ...
You're welcome! :)
Great Post buddy, Thank you. Do you have any idea how we can change the time codes to real time format?
Hey AJ,
I think you are looking for this right? stackoverflow.com/questions/45227380/convert-unix-epoch-time-to-date-in-google-sheets
@@mixedanalytics Yes Yes, Appreciated your help bro 👌✌
this method does not work for me can you help
Hi Ali, can you be more specific about what you need? You can also join our community over at Reddit: www.reddit.com/r/api_connector/
Am i right that p2p is not supported?
Not officially. I found some documentation of some people hacking a solution though: stackoverflow.com/questions/67793326/api-binance-p2p-i-only-access-a-part-only-the-buy-and-not-all-of-it-buy-and-s
@@mixedanalytics thanks 🙏🙏
Thx ! Do you know if it is possible to have a list of all the trade opened, the history etc. ?
Hi Guillaume,
Yep, you can check out these endpoints:
github.com/binance/binance-spot-api-docs/blob/master/rest-api.md#compressedaggregate-trades-list, this one you will want to specify the startTime and endTime
Trade opened, I'm not sure what you mean by that. Can you clarify?
@@mixedanalytics thx. i d like to copy my orders in my google sheet and watch my portfolio
If you are talking about the HMAC stuff, you can check out a hack one of our users put together: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#comment-23068
This should be towards having your own portfolio.
@@mixedanalytics Access to this sheet is restricted to view only by user. Can u help?
hello. can someone help me. what is the IP address of the server used in this API connector?
when I try to get data from binance, an error 451 shows. what should I do? please help. thank you
Google uses a wide range of IP addresses, you can see the list here: www.gstatic.com/ipranges/goog.txt. I believe all are US-based.
Hey ! thanks for this video. But i've got a problem. When i'm using the api connector, and click on "Run". i've got a message error type 403. Does someone got this problem to ?
Hello Pitou,
From their website, they mention that 403 bans generally last 5 minutes, so you could chill a minute then try again.
www.binance.com/en/support/faq/360004492232
binance.zendesk.com/hc/en-us/articles/360004492232-API-Frequently-Asked-Questions-FAQ-#%E2%80%9Climits_waf%E2%80%9D
You can try also:
1)to decrease the size of your requests as well, as this may help, or
2) create an account and use an API key or
3) try a new browser
Binance rate limits based on IP address (your specific device/computer) and all requests through google sheets share Google’s pool of IP addresses. If the problem still persists after all these tricks, you can try contacting binance and ask to whitelist Google's IP addresses (I think if enough of us Google app users complain, they will have to fix this lol).
Sorry I know this got super technical real quick, but yeh this is one of the known problems with binance. It's hit or miss . If you google "403 Binance" you will see lots of people face this issue haha. But when it does work, it works well.
Let me know if all that helps!
@@mixedanalytics How to set a limit? I am also getting this error. I would like to get the values just for the cryptos I own.
@@danielxavier89 Hey Daniel, what do you mean by set a limit?
wow u have huge eyebrows !
How import Trade History from Binance Futures to Google Sheets? Who's know ?
Hello!
I am working on a solution for Google Sheets users at the moment. Stay tuned on the channel!
how do we do with portfolio API?
Hi Talmon,
Private data is a little more tricky. You can check our article here: mixedanalytics.com/knowledge-base/import-binance-data-to-google-sheets/#num4
how can i add a timeframe.
Hi Ali, I think this is what you are looking for: mixedanalytics.com/knowledge-base/add-a-data-refresh-timestamp/
how can I get bitcoin price data from from started date to now pls reply
That would be a pretty massive request, but maybe you can try this endpoint? /api/v3/aggTrades
Birden çok veri için nasıl çekebiliriz acaba ? Örnek "BCH and ETH and ONT) nasıl çekebiliriz?
Hello!
There are a few ways. If you just want to get all tickers, just call this endpoint: api1.binance.com/api/v3/ticker/bookTicker
Inside of the Binance API, you can't comma separate tickers. It's basically all-or-nothing with respect to a given endpoint.
Otherwise if you want the kline data like I mentioned in the video, you would need to setup an API request for each symbol. You can read more about it here: mixedanalytics.com/knowledge-base/api-connector-run-multiple-queries-single-request/
If you are using our free version, just be aware that we limit to 8 requests/day for free accounts.
Hi great video...thankyou!!
I would like to import my portfolio with all the transactions....is it possibile? Could you explain how to do it?
Is it possible also to create an automated trading?
Thankyou for your answers
Hi Sara, I peeked into their documentation and I found a few endpoints that you could check out:
Withdraw data: binance-docs.github.io/apidocs/spot/en/#withdraw-history-user_data
Deposit data: binance-docs.github.io/apidocs/spot/en/#deposit-history-supporting-network-user_data
This question is quite complex for a RUclips comment haha, but you could try posting your ideas in our Reddit/browsing to see if anyone else has created something similar www.reddit.com/r/api_connector/. I can tell you right now automated trading would be tricky, but definitely doable in Google Sheets (would need to use POST requests to send to Binance binance-docs.github.io/apidocs/spot/en/#test-new-order-trade). You'll also have to get an API key from Binance.
Here's a basic dashboard you can check out for inspiration that we put together: docs.google.com/spreadsheets/d/1yO_5bvczvt1pghy1TSj36WQdDBfbWQQxXRct43p2m0U/edit#gid=1460402550
Hope that all helps!
@@mixedanalytics
thankyouuuuuu and what about all transactions.
Could you make a video for creating a spreadsheet with all transactions done in binance?
Keep going with great work ;)
@@ioio7354 that's also what I want to see. Especially if it can be done in sheets or power query
@@ioio7354 I'll add to my to-do list, thanks for the tip!
@@jeffclassics Noted, thanks for the feedback!