This was an excellent intro duction to API, I figured it out. However.....the data is not live? What needs to be set so the price on the quotes is updating in near realtime?
I received this error message when trying to get latest quotes. I've re-entered my API key multiple times and double checked the header key is correct. 1) Latest Quotes 1: Completed with errors - We received an error from the API server (401) show response { "status": { "timestamp": "2021-08-25T22:45:56.169Z", "error_code": 1002, "error_message": "API key missing.", "elapsed": 0, "credit_count": 0 } }
Hi Chris! Wow, based on what you've said, I tried to replicate the bug and can't get it. You could try alternatively: pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?CMC_PRO_API_KEY=yourapikey Just take the X-CMC-API-KKEY out of the headers and add it as a query parameter like above. Let me know if that works for you! Maybe you can try posting over on our Reddit community, our mods and users are pretty active there.
Hey CnC, It's a little complicated to share sheets with API Connector if you also want to get the same API Connector setup I am using (Google purposefully does this to protect user data). But I already built a publicly-facing dashboard that monitors FCAS data if you want to use that. Follow the instructions in the first tab to see what I mean: docs.google.com/spreadsheets/d/1cG2B04hnLNnOVVeTVR-ywL1gTBp4IgtgJ0H-mgo_-Ng/edit#gid=1743165485
Hi, good stuff :-) I have one question though, I want all prices/ quotes and insteed of using the quote ending I picked the lists, but it still just give me the first hundred (as if I use the normal IMPORTHTML function directly in google sheet). Is there anyway to get all? It also looks like my free version ends in some days, it this it or is there a unlimited time basis version as well, look like that on your homepage?
also interested in a response to this, i only had 1000 data points come up when i ran it and cant work out how to get more quotes. would also like to know what happens when the free trial ends
Hi Jesse and Johanna! So first thing, you can try adding '?limit=5000' to the end of your API request to return the maximum allowable limit in a single call (by CMC's side). However, we've found this can produce errors, so it may be better to just use 1000 and just paginate. You can either paginate manually, or with the trial version, you have access to the pagination feature available to pro users. You can read more about both here in our docs: mixedanalytics.com/knowledge-base/import-coinmarketcap-data-to-google-sheets/#num5 Lemme know if that helps!
There's a few ways to do this: You can look into JMES Path filtering: mixedanalytics.com/knowledge-base/filter-specific-fields-values/ or sometimes you have query parameters you can use to filter results a bit more (i.e. you can select specific tokens/time periods, etc.
this is great! IDs worked PERFECTLY but got an error "latest quotes" Status Completed with errors: - Server responded with an error (400) show response{"status":{"timestamp":"2021-02-11T17:37:00.364Z","error_code":400,"error_message":"\"\" is not allowed","elapsed":0,"credit_count":0}} How do you correct this!
Thanks for the info. Tell me, to update data, for example, the value of a cryptocurrency, you need to press RUN or you can automate this process so that the data is updated automatically when you enter a Google table.
Hey! So yes normally you will have to press Run every time. For an automation work around, check out this article we wrote up. mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/#num6 Let me know if that helps!
What happens when a coin changes rank order? While Bitcoin will likely always stay #1 and Ethereum #2, the other coins change ranking frequently. How can this setup follow the coins as they go up and down in the rankings?
I think for CoinMarketCap this is not an issue. I've definitely seen the problem you are talking about in other crypto APIs, but this quotes end point just returns them in the order you list in the query parameter (i.e. 1,10 = BTC, FRC)
Hi Jomel! You can refer to the docs here for the info: coinmarketcap.com/api/documentation/v1/#operation/getV2CryptocurrencyOhlcvHistorical. For what you are looking for, the request would probably look something like: pro-api.coinmarketcap.com/v2/cryptocurrency/ohlcv/historical?start_time=2018-09-19 {check the start_time format in the documentation, not 100% sure) Do note this is not on the free plan, so you'll need to upgrade your CoinMarketCap account to access this information.
For sure! You'd have to wire it all up yourself (match everything to your holdings) but could definitely be done in a dashboard style Sheet or even Data Studio. I'll have some cool vids coming soon on that, so stay tuned ;)
Hi! Check this one out: coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesHistorical It looks like this one is not available on the free plan though just FYI
Hi Qwerty! Please check out our new Application integration to check for these kinds of endpoints. You can also reference our knowledge base and/or CoinMarketCap's API docs: coinmarketcap.com/api/
I'm getting a 403 error. My API subscrption doesn't support this endpoint. Does that mean it's not free? Then I get a 400 error for the second one. Thanks!
Hello! So the 403 probably means you don't have the endpoint in your subscription. As I understand, CMC prices there endpoints differently for different plans. And the 400 I think is just in response to that initial 403. Which endpoint are you trying to call?
Hello! This is a very valid concern and I do the same thing when investigating new services! You are welcome to check over our privacy policy yourself and determine yourself if it's reasonable for your use case: mixedanalytics.com/knowledge-base/filter-specific-fields-values/ I can tell you in a nutshell, the only potentially sensitive data we collect is the top-level domain and your email address to help with debugging (so for example, I would see your email + pro-api.coinmarketcap.com, and that's it). We collect ZERO of the actual data that passes through to your sheet. I hope this clears up things, let me know if you have any other concerns :)
Hi Ikhsan, no it's not. You'll need to press "Run" everytime you want to fetch live data, or alternatively with our pro subscription you can setup scheduling to handle that for you
Thanks for the video! Question - after running the API connector I can't see the data for Market Cap, Price, Circulating Supply, or Volume. How do I pull those in? Thanks!
Hey Christopher! The Latest Quotes endpoint I demonstrated returns price, volume (24hr) and circulating supply. If you want market cap, read here and try this endpoint coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesLatest
Thak you for this tutorial! Can you help me with the API Connector? I have correctly installed API Connector in my google account, and i can find it in "ADD-ONS". I can open correctly the API Connector interface but when I click somewhere nothing happens.
Hi there, this is likely an issue with how your browser handles cookies. You can view instructions for resolving this in this article: mixedanalytics.com/knowledge-base/troubleshooting-api-requests/. Please check the first section, called "Add-on Buttons & Menus Don’t Work".
Hey Kurtzy! So the price is inside of the API response. It's inside the sheet, you just gotta scroll to it. If you want to just show the price, I would use =SheetName!A:A where "A" is the column you want to pull over into another sheet. Let me know if that helps!
Hello Mikaela! You can manually re-run API Connector sidebar. If you want to automate it, check out our scheduling feature here: mixedanalytics.com/knowledge-base/api-connector-scheduling/ As for listing in rows, I'm not sure what you mean. You could try messing around with the TRANSPOSE() function or using the pivot table functionality. Generally with API Connector, it's good practice to have your raw data sheet, and then pipe that data over to another sheet and manipulate it into the shape you want there. For inspiration, you can check out this dashboard we built: docs.google.com/spreadsheets/d/1yO_5bvczvt1pghy1TSj36WQdDBfbWQQxXRct43p2m0U/edit#gid=1460402550 Side-note: This dashboard requires a pro-account, just a heads up :) Let me know if that helps!
@@MikaelaHard We only offer 8 requests/day with the free account unfortunately :(, and yeh the scheduling/automation through our app requires the pro-account. Your only option is to manually update it by pressing run OR you could try implementing IMPORTAPI() function here: mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/ and linking it to another auto-updating function (this is buggy and prone to issues from Google's server side), but I'd be careful with this (8 request limit mentioned above). Let me know if that helps!
Awesome video thanks! one question tho, how do you put in only the price of for example btc, in a sheet that is already made but I just want to adjust the current prices of the crypto coins from my spreadsheet
Good question! You'll want to use API Connector to make a "raw data" sheet, then use something like =QUERY() or =VLOOKUP() to move that data over to your existing sheet (or some other function to port the data over).
How come "CoinMarketCap ID Map" doesn't display current market price? How would I export ALL coins with their current market price, not just specific coins from "Latest Quotes" ?? Goal: 1 - Export ALL coins + current market price into sheet called "Current CMC" that I can run an API request for 2 - Use my personal "Trading" sheet to reference specific data cells in "Current CMC" based on keywords 2a - AKA under my ETH holdings on "Trading" I can reference that keyword "ETH" from the "Current CMC" sheet to input the current price
@@DrDwizz Sorry didn't catch this comment! To get ALL coins, you would want to use this endpoint: /v1/cryptocurrency/listings/latest. Then for your keyword referencing, just make sure you use the same symbols/names that CMC uses and you should be good to go! You can read in the docs more here: coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyListingsLatest
Not sure, I checked the docs here: coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesLatest and it looks like for the "convert" param you are limited to 120. Maybe it's the same for ids? I would just scale down you request until you find the right amount that is able to run. Otherwise, it could be on our side and you may want to implement our JMESpath filter to lighten the request up a bit (pro-feature): mixedanalytics.com/knowledge-base/filter-specific-fields-values/
Hey Carlos, Hmm, sounds like you need to reset the sheet/tab name. Navigate to the tab you want the data to pull to, then in the side bar scroll to the "output settings" section, and click "Set current". You should see the sheet update to match the one you are currently viewing. Try that and let me know if it works!
Hey PepitoCoin! Try to append ?limit=200 onto the end of the URL. Should work. You are talking about the ids endpoint? I would not suggest limiting this one (in other words, just use the raw endpoint without query parameters)
Thanks for the video ! It works ! We can also use the symbol instead of the ID. But with the symbols the result appears in one line even selecting "compact". Do you know a method to track (daily and weekly) RSI on Sheets ?
Hey MegaProyk, Yep you can use symbols, but they strong suggest using ID in their documentation as their are some blockchains that use the same symbol (which can lead to duplicates being returned, so they probably hard-coded the JSON to account for this). You can read more here: coinmarketcap.com/api/documentation/v1/#section/Best-Practices I don't know any methods for RSI, but this looks like a fun future project for me! :)
@@mixedanalytics Yes that is true there are duplicates. It would be very nice if you share a method to track RSI ! Especially since it seems there are no videos on it. I found taapi but I think it does not work with Sheets, only with coding languages.
@@mixedanalytics Hey Mixed analytics, yes it works but I don't know if it is possible to run it for multiple symbols at a time... I tried comma-separated like this : symbol=BTC/USDT,ETH/USDT and &-separated and it did not work... Any idea ?? Thank in advance !
@@MegaProyk Hmm, seems like their API doesn't support multiple symbols in one request. If you only want to track two, it's probably easier to have their own sheet. Otherwise you could use the IMPORTAPI function to setup all the symbols you want, then run the request separately for each (read more here) mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/
Liked and subscribed! Very clear information - I do however have a question. I think it should auto update/refresh the prices. Is this true? If yes, how often does it do this?
@@marlesdk Correct. So each time you press "run", it consumes 1 API request on our side, as well as from the domain you call. With our pro version, you can do auto update hourly, but you would still want to keep in mind hitting rate limits on the domain you are using (in the case of CMC, I think its 333/day, don't remember exactly though). Also thanks for subscribing! Got some more videos in the works :)
Awesome video. Question, is there a way to maintain formatting when refreshing the data in sheets? Do you make a new sheet and reference the cell data? What is the best way to do it?
Hi Abel! Yes, so if you purchased our pro version, I suggest using JMES filtering for faster and cleaner results: Otherwise you can use something like this dashboard I setup: docs.google.com/spreadsheets/d/1hwsK5UkSqLhMmKbz4KFcFc1qk-EavhHSxhuV96wNe_Y/edit#gid=91435068. If you look at the "Template" sheet and unhide rows 16-19, you will see the formula =QUERY(Parsed_Out_Data!$1:$10000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH(B17,Parsed_Out_Data!$1:$1,0),4),1,""),1). Basically, you can reference your sheet that pipes in data from APIC and use the above formula to search for the header by name. This is necessary because sometimes the API jumbles the order of these columns, so rather than referencing the column directly (Sheet1!A:A), it's better to track down the name and just return it's contents.
@@mixedanalytics It's ok right now but the problem was that when I installed the add on I couldn't launch it because there was one help button (reference)
@@Crossfire-fd8gy Hmmm, if you are still having issues you can post a screenshot/Loom of your issue over on our Reddit community for potentially resolving the issue: www.reddit.com/r/api_connector/
Hey Chris, sorry to hear that! Is it throwing some kind of error in the side panel? You can also try following our KB article as well: mixedanalytics.com/knowledge-base/import-coinmarketcap-data-to-google-sheets/ You can also drop a more detailed question w/ screenshots + Loom over on our Reddit page for more help: www.reddit.com/r/api_connector/
@@mixedanalytics PLEASE! We must bring the bid price and ask price, and compare each other! I think that we should connect one by one! What do you think? Thanks!
@@Fernitano Hmm, I think CMC doesn't give out that data on their free plan. If you get the startup package, it looks like you can access OHLCV. Otherwise, you may wanna use a different service (coingecko or another).
![BLACK BAG - Official Trailer [HD] - Only in Theaters March 14](http://i.ytimg.com/vi/Du0Xp8WX_7I/mqdefault.jpg)
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
This was an excellent intro duction to API, I figured it out. However.....the data is not live? What needs to be set so the price on the quotes is updating in near realtime?
I received this error message when trying to get latest quotes. I've re-entered my API key multiple times and double checked the header key is correct.
1) Latest Quotes 1: Completed with errors
- We received an error from the API server (401) show response
{ "status": { "timestamp": "2021-08-25T22:45:56.169Z", "error_code": 1002, "error_message": "API key missing.", "elapsed": 0, "credit_count": 0 } }
Hi Chris!
Wow, based on what you've said, I tried to replicate the bug and can't get it. You could try alternatively:
pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?CMC_PRO_API_KEY=yourapikey
Just take the X-CMC-API-KKEY out of the headers and add it as a query parameter like above. Let me know if that works for you!
Maybe you can try posting over on our Reddit community, our mods and users are pretty active there.
can you share your google sheet with the list of coin and updated with the connector? it avoid us rewrite an excel sheet
Hey CnC,
It's a little complicated to share sheets with API Connector if you also want to get the same API Connector setup I am using (Google purposefully does this to protect user data). But I already built a publicly-facing dashboard that monitors FCAS data if you want to use that. Follow the instructions in the first tab to see what I mean: docs.google.com/spreadsheets/d/1cG2B04hnLNnOVVeTVR-ywL1gTBp4IgtgJ0H-mgo_-Ng/edit#gid=1743165485
Thank you! You are a legend! Happy new year!
Happy new year! Thanks :)
I realized 3' in that you guys were actually mixed analytics : successful marketing!
We are doing our best ;)
how do we get API data for "watchlist" within Coinmarketcap?
For example Bitcoin: "On 3,6Million watchlist"?
Thanks a lot, video TOP!
Do you know how to change the currency displayed in the field "data » quote » USD » price" in EURO?
Yep! Just add &convert=EUR to the end of your URL.
@@mixedanalytics Epic. I salute you.
@@marlesdk Cheers :)
How can I get the crypto princes in BTC (satoshi)? It only provides the prices in fiat currencies
Hmmm, as I can tell, I don't think you can return as Satoshi, but you could just multiply the returned results by 100000000
That´s what I was looking for! Good explanation, simple and clear. Gracias amigo!
De nada Armando! Stay tuned for more :)
Hi, good stuff :-) I have one question though, I want all prices/ quotes and insteed of using the quote ending I picked the lists, but it still just give me the first hundred (as if I use the normal IMPORTHTML function directly in google sheet). Is there anyway to get all? It also looks like my free version ends in some days, it this it or is there a unlimited time basis version as well, look like that on your homepage?
also interested in a response to this, i only had 1000 data points come up when i ran it and cant work out how to get more quotes.
would also like to know what happens when the free trial ends
Hi Jesse and Johanna!
So first thing, you can try adding '?limit=5000' to the end of your API request to return the maximum allowable limit in a single call (by CMC's side). However, we've found this can produce errors, so it may be better to just use 1000 and just paginate. You can either paginate manually, or with the trial version, you have access to the pagination feature available to pro users. You can read more about both here in our docs: mixedanalytics.com/knowledge-base/import-coinmarketcap-data-to-google-sheets/#num5
Lemme know if that helps!
And how do you filter some data.. .so that you do not get all the details just some out of the available
There's a few ways to do this: You can look into JMES Path filtering: mixedanalytics.com/knowledge-base/filter-specific-fields-values/
or sometimes you have query parameters you can use to filter results a bit more (i.e. you can select specific tokens/time periods, etc.
this is great! IDs worked PERFECTLY but got an error "latest quotes" Status
Completed with errors:
- Server responded with an error (400) show response{"status":{"timestamp":"2021-02-11T17:37:00.364Z","error_code":400,"error_message":"\"\" is not allowed","elapsed":0,"credit_count":0}}
How do you correct this!
I think you ran out of API calls for the day. You can check on your API dashboard inside CMC
Sounds right to me! :)
Thanks for the info. Tell me, to update data, for example, the value of a cryptocurrency, you need to press RUN or you can automate this process so that the data is updated automatically when you enter a Google table.
Hey!
So yes normally you will have to press Run every time. For an automation work around, check out this article we wrote up. mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/#num6 Let me know if that helps!
What happens when a coin changes rank order? While Bitcoin will likely always stay #1 and Ethereum #2, the other coins change ranking frequently. How can this setup follow the coins as they go up and down in the rankings?
I think for CoinMarketCap this is not an issue. I've definitely seen the problem you are talking about in other crypto APIs, but this quotes end point just returns them in the order you list in the query parameter (i.e. 1,10 = BTC, FRC)
wonderful tutorial! Is there anything like this for altcoins on the XRPL?
No idea.... maybe this? xrpl.org/http-websocket-apis.html
Hi can you do tutorial on like getting the 90days value of a coin ? (DATE, OPEN, HIGH, LOW, CLOSE, VOLUME )
Hi Jomel!
You can refer to the docs here for the info: coinmarketcap.com/api/documentation/v1/#operation/getV2CryptocurrencyOhlcvHistorical. For what you are looking for, the request would probably look something like: pro-api.coinmarketcap.com/v2/cryptocurrency/ohlcv/historical?start_time=2018-09-19 {check the start_time format in the documentation, not 100% sure)
Do note this is not on the free plan, so you'll need to upgrade your CoinMarketCap account to access this information.
Very good thank you . Is it possible to access to the overall portfolio value of coinmarketcap via API?
For sure! You'd have to wire it all up yourself (match everything to your holdings) but could definitely be done in a dashboard style Sheet or even Data Studio. I'll have some cool vids coming soon on that, so stay tuned ;)
@@mixedanalytics thanks!!
Hi, I am getting this error: Server responded with an error (400). Could you advise on this please?
Hey Benjamin!
Usually when I get a 400, it's a spelling error. Double check your URL and let me know if there are still issues, thanks!
Hello
How to import historical data of single coin from last one year.
Hi! Check this one out: coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesHistorical
It looks like this one is not available on the free plan though just FYI
I was looking for a good explanation, excellent video
Glad it was helpful!
How give data low52 and high52 price from coinmarcetcap? Show please.🙏
Hi Qwerty! Please check out our new Application integration to check for these kinds of endpoints. You can also reference our knowledge base and/or CoinMarketCap's API docs:
coinmarketcap.com/api/
Pressing "Run" again doesn't update it for me 😣
Oh No! Is it giving some kind of error message? You could also try running in a private browser to see if that helps
I'm getting a 403 error. My API subscrption doesn't support this endpoint. Does that mean it's not free? Then I get a 400 error for the second one. Thanks!
Hello! So the 403 probably means you don't have the endpoint in your subscription. As I understand, CMC prices there endpoints differently for different plans. And the 400 I think is just in response to that initial 403. Which endpoint are you trying to call?
Do you happen to know if there is a way to import my entire portfolio to google sheets or excel?
Depends on which service you want to pull the data from. Can you be a little more specific?
how i get more ALL coins listed in coinmarketcap with this api? i archived just 100.
Hi Alexandre!
Add &limit=5000 or whatever number you need to the end of your request. Let me know if that works for you!
@@mixedanalytics thanks its work!!
Is there a way to get this info without giving up my entire financial portfolio on my google sheets? The permissions sounds very invasive to me.
Hello! This is a very valid concern and I do the same thing when investigating new services! You are welcome to check over our privacy policy yourself and determine yourself if it's reasonable for your use case: mixedanalytics.com/knowledge-base/filter-specific-fields-values/
I can tell you in a nutshell, the only potentially sensitive data we collect is the top-level domain and your email address to help with debugging (so for example, I would see your email + pro-api.coinmarketcap.com, and that's it). We collect ZERO of the actual data that passes through to your sheet.
I hope this clears up things, let me know if you have any other concerns :)
Is there any way to find out the ID of a random coin?
coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyMap
is that show live data from coin market cap?
Hi Ikhsan, no it's not. You'll need to press "Run" everytime you want to fetch live data, or alternatively with our pro subscription you can setup scheduling to handle that for you
Hi well done. Question, how do I know how much I spend on my crypto in total if I have multiple transaction on the portfolio’s of CoinMarketCap
I didn't realize you can trade on CMC! Um, I have no idea to be honest haha! I can't find anything in the documentation about trading
Thanks for the video! Question - after running the API connector I can't see the data for Market Cap, Price, Circulating Supply, or Volume. How do I pull those in? Thanks!
Hey Christopher!
The Latest Quotes endpoint I demonstrated returns price, volume (24hr) and circulating supply. If you want market cap, read here and try this endpoint
coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesLatest
didn't work for me -- still getting error when I put in my api key
What kind of error is it throwing back? Can you provide details? Also feel free to drop a comment over on our Reddit page.
where can I found the data id for a cryptocurrency?
Nico check this: coinmarketcap.com/api/documentation/v1/#operation/getV1ExchangeMap
Thak you for this tutorial!
Can you help me with the API Connector?
I have correctly installed API Connector in my google account, and i can find it in "ADD-ONS". I can open correctly the API Connector interface but when I click somewhere nothing happens.
Hi there, this is likely an issue with how your browser handles cookies. You can view instructions for resolving this in this article: mixedanalytics.com/knowledge-base/troubleshooting-api-requests/. Please check the first section, called "Add-on Buttons & Menus Don’t Work".
@@mixedanalytics I LOVE YOU! Fucking cookies!
Thank you so much!
@@ChristianEvangelistaEV Hahaha I'm glad that worked. We love you too! :D
Thank you. great video! I followed all of your instructions, but I don't have the price of the coin in me sheet :-(
okay, so say i want to just show an updated current price?
Hey Kurtzy!
So the price is inside of the API response. It's inside the sheet, you just gotta scroll to it. If you want to just show the price, I would use =SheetName!A:A where "A" is the column you want to pull over into another sheet. Let me know if that helps!
is there a way to keep that thing updating every miunte automatically? and without running out of credits on the api key site?
What's up Dude Man!
Sounds like you need to look into web scraping: www.parsehub.com/blog/web-scraping-vs-api/
That was very easy! Thanks for the video. Hello from Ukraine!
You are welcome! Love your country :)
Now my search is end good explanation with simple language
Hi.How often will the price be updated? And also: Is it not possible to have the coins in a list in rows?? It's very messy having them in columns.
Hello Mikaela!
You can manually re-run API Connector sidebar. If you want to automate it, check out our scheduling feature here: mixedanalytics.com/knowledge-base/api-connector-scheduling/
As for listing in rows, I'm not sure what you mean. You could try messing around with the TRANSPOSE() function or using the pivot table functionality. Generally with API Connector, it's good practice to have your raw data sheet, and then pipe that data over to another sheet and manipulate it into the shape you want there. For inspiration, you can check out this dashboard we built:
docs.google.com/spreadsheets/d/1yO_5bvczvt1pghy1TSj36WQdDBfbWQQxXRct43p2m0U/edit#gid=1460402550
Side-note: This dashboard requires a pro-account, just a heads up :)
Let me know if that helps!
@@mixedanalytics Is there no way to have it updated without paying for your services? I don't have much money right now :(
@@MikaelaHard We only offer 8 requests/day with the free account unfortunately :(, and yeh the scheduling/automation through our app requires the pro-account. Your only option is to manually update it by pressing run OR you could try implementing IMPORTAPI() function here: mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/ and linking it to another auto-updating function (this is buggy and prone to issues from Google's server side), but I'd be careful with this (8 request limit mentioned above).
Let me know if that helps!
Awesome video thanks! one question tho, how do you put in only the price of for example btc, in a sheet that is already made but I just want to adjust the current prices of the crypto coins from my spreadsheet
Good question! You'll want to use API Connector to make a "raw data" sheet, then use something like =QUERY() or =VLOOKUP() to move that data over to your existing sheet (or some other function to port the data over).
@@mixedanalytics Alright thanks a lot! Ima tryyy :D
How come "CoinMarketCap ID Map" doesn't display current market price? How would I export ALL coins with their current market price, not just specific coins from "Latest Quotes" ??
Goal:
1 - Export ALL coins + current market price into sheet called "Current CMC" that I can run an API request for
2 - Use my personal "Trading" sheet to reference specific data cells in "Current CMC" based on keywords
2a - AKA under my ETH holdings on "Trading" I can reference that keyword "ETH" from the "Current CMC" sheet to input the current price
@@DrDwizz Sorry didn't catch this comment!
To get ALL coins, you would want to use this endpoint: /v1/cryptocurrency/listings/latest. Then for your keyword referencing, just make sure you use the same symbols/names that CMC uses and you should be good to go!
You can read in the docs more here: coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyListingsLatest
Nice video Mo Salah!!! Seriously helpful
Cheers! Glad it helped :)
Amazing! Thanks for this :)
My pleasure!
Great vid. How do you change the currency from USD to AUD?
You can add &convert=AUD to the end of your request URL.
@@mixedanalytics awesome thanks bud
how
@@ai_dividend_daily_1 wut?
Thanks for the explanation. I tried to display the data of 140 ids, and it's been loading for 5 mins. Is there a limit of IDS ?
Not sure, I checked the docs here: coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesLatest and it looks like for the "convert" param you are limited to 120. Maybe it's the same for ids? I would just scale down you request until you find the right amount that is able to run. Otherwise, it could be on our side and you may want to implement our JMESpath filter to lighten the request up a bit (pro-feature): mixedanalytics.com/knowledge-base/filter-specific-fields-values/
how do you know the ids?
Why the video is not showed properly?
What's wrong? Is it a quality issue?
How can I find the ID number of each cryptocurrency?
ruclips.net/video/n84bsqaer-g/видео.html This endpoint returns the id in the sheet, or maybe I don't understand your question 😅
I got destination sheet does not exist error.
Hey Carlos,
Hmm, sounds like you need to reset the sheet/tab name. Navigate to the tab you want the data to pull to, then in the side bar scroll to the "output settings" section, and click "Set current". You should see the sheet update to match the one you are currently viewing. Try that and let me know if it works!
Is Excel or Google Sheets best for importing CMC data ?
I can only speak for Google Sheets, as I haven't used Excel in a while.
Thanks buddy but it retrieves only the first 100 coins, how can we extend to 200 or more? thxxx
Hey PepitoCoin!
Try to append ?limit=200 onto the end of the URL. Should work. You are talking about the ids endpoint? I would not suggest limiting this one (in other words, just use the raw endpoint without query parameters)
Is it possible to do this on a Excel File? Not in Google Sheets?
I'm sure it is, but I don't know anything about Excel! :P Also our tool doesn't work there.
Nice, thanks brother!
No problem 👍
Esto sigue funcionando?
que yo sepa si :)
How to Refresh Data automatically bro?
Hi!
Check out our scheduling feature: mixedanalytics.com/knowledge-base/api-connector-scheduling/
Hi! Thank you for the video 🙏🏻 Anyway, is there any API for downloading historical data from coinmarketcap?
You CAN, but it's not free check here: coinmarketcap.com/api/features
Thank you for this!
Glad it was helpful!
Thanks for the video ! It works ! We can also use the symbol instead of the ID. But with the symbols the result appears in one line even selecting "compact".
Do you know a method to track (daily and weekly) RSI on Sheets ?
Hey MegaProyk,
Yep you can use symbols, but they strong suggest using ID in their documentation as their are some blockchains that use the same symbol (which can lead to duplicates being returned, so they probably hard-coded the JSON to account for this). You can read more here: coinmarketcap.com/api/documentation/v1/#section/Best-Practices
I don't know any methods for RSI, but this looks like a fun future project for me! :)
@@mixedanalytics Yes that is true there are duplicates.
It would be very nice if you share a method to track RSI ! Especially since it seems there are no videos on it. I found taapi but I think it does not work with Sheets, only with coding languages.
@@MegaProyk Hmm, I don't see why taapi wouldn't work! You could try messing with any of these indicators and see: taapi.io/indicators/
@@mixedanalytics Hey Mixed analytics, yes it works but I don't know if it is possible to run it for multiple symbols at a time... I tried comma-separated like this : symbol=BTC/USDT,ETH/USDT and &-separated and it did not work...
Any idea ??
Thank in advance !
@@MegaProyk Hmm, seems like their API doesn't support multiple symbols in one request. If you only want to track two, it's probably easier to have their own sheet. Otherwise you could use the IMPORTAPI function to setup all the symbols you want, then run the request separately for each (read more here)
mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/
Liked and subscribed!
Very clear information - I do however have a question.
I think it should auto update/refresh the prices.
Is this true?
If yes, how often does it do this?
Ahh.. I have to click "run" in the API connector!
@@marlesdk Correct. So each time you press "run", it consumes 1 API request on our side, as well as from the domain you call. With our pro version, you can do auto update hourly, but you would still want to keep in mind hitting rate limits on the domain you are using (in the case of CMC, I think its 333/day, don't remember exactly though).
Also thanks for subscribing! Got some more videos in the works :)
@@mixedanalytics I apreciate the answer :)
Awesome video. Question, is there a way to maintain formatting when refreshing the data in sheets? Do you make a new sheet and reference the cell data? What is the best way to do it?
Hi Abel!
Yes, so if you purchased our pro version, I suggest using JMES filtering for faster and cleaner results:
Otherwise you can use something like this dashboard I setup: docs.google.com/spreadsheets/d/1hwsK5UkSqLhMmKbz4KFcFc1qk-EavhHSxhuV96wNe_Y/edit#gid=91435068.
If you look at the "Template" sheet and unhide rows 16-19, you will see the formula =QUERY(Parsed_Out_Data!$1:$10000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH(B17,Parsed_Out_Data!$1:$1,0),4),1,""),1). Basically, you can reference your sheet that pipes in data from APIC and use the above formula to search for the header by name. This is necessary because sometimes the API jumbles the order of these columns, so rather than referencing the column directly (Sheet1!A:A), it's better to track down the name and just return it's contents.
Nice! Thank u bro!
But... Is the data automatically updated?
Nope, you would need to look into scheduling: mixedanalytics.com/knowledge-base/api-connector-scheduling/
Man thank you, so clear so easy message!!!
Glad it was helpful!
why it doesn't work?
Hmmm, can you share some details about the error messages or behavior?
@@mixedanalytics It's ok right now but the problem was that when I installed the add on I couldn't launch it because there was one help button (reference)
@@Crossfire-fd8gy Hmmm, if you are still having issues you can post a screenshot/Loom of your issue over on our Reddit community for potentially resolving the issue: www.reddit.com/r/api_connector/
Rad, thanks!
Glad you liked it! 🏄♂️
Was so excited to find this video but I followed the video exactly and it didn't work for me
Hey Chris, sorry to hear that! Is it throwing some kind of error in the side panel? You can also try following our KB article as well: mixedanalytics.com/knowledge-base/import-coinmarketcap-data-to-google-sheets/
You can also drop a more detailed question w/ screenshots + Loom over on our Reddit page for more help:
www.reddit.com/r/api_connector/
Guys!! Can yo do the same video for yahoo finance Ali
Sure thing Michael, I'll add it to the backlog :)
perfecto ejemplo para uso de una api. gracias
de nada! :)
And where is the price of each crypto? :(
Hey Michael,
This particular endpoint is pretty heavy with data, so just make sure you scroll right :)
You save me, thank you
Happy to help!
Thank u bro!
Thank you sir :)
Thanks !
Very interesting !
You bet!
you are hero
Cheers :)
Hi! Great video! Could you make an arbitraje dashboard? Thanks!
Hey Fernando,
I'll keep it in mind haha, that would be a neat project! Thanks for the suggestion :)
@@mixedanalytics PLEASE! We must bring the bid price and ask price, and compare each other! I think that we should connect one by one! What do you think? Thanks!
@@Fernitano Hmm, I think CMC doesn't give out that data on their free plan. If you get the startup package, it looks like you can access OHLCV. Otherwise, you may wanna use a different service (coingecko or another).
You can check through the endpoints in their documentation: coinmarketcap.com/api/documentation/v1/#tag/cryptocurrency
Thanks muhamed salah
You're welcome!
muy buena información
gracias por el comentario :)
THANKSSSSSSSSSSSSSSSSSSSSSSSSSSSSS
No prob :D
thanks
Welcome
Отличное видео! Ни одного слова на русском языке! Спасибо! Я ничего не понял! Было очень интересно!
Что ты имеешь в виду? Все видео было на русском;)
@@mixedanalytics Наверное не воспринимаю информацию на слух. Может быть ты мне напишешь как изложенной в видео информацией воспользоваться?)
@@dzmitryslabkouski2093 В будущем я бы хотел добавить субтитры, чтобы текст было легче переводить. Спасибо за напоминание! :)
no price!!!!!!!
For Latest Quotes endpoint, check the column data -> quote -> USD -> PRice