API Connector Documentation
Import CoinMarketCap Data to Google Sheets
In this guide, we’ll walk through how to pull cryptocurrency data from the CoinMarketCap API directly into Google Sheets, using the API Connector add-on for Sheets.
We'll first get an API key from CoinMarketCap, and then set up a request to pull in crypto data to your spreadsheet.
Contents
- Before You Begin
- Part 1: Get your CoinMarketCap API Key
- Part 2: Pull Data from CoinMarketCap into Sheets
- Part 3: Create a Custom Request
- Part 4: Handle Pagination
- Part 5: API Documentation
- Appendix: CoinMarketCap Crypto Portfolio Template
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your CoinMarketCap API Key
- If you haven't already, navigate to https://coinmarketcap.com/api/ and click Get Your API Key Now
- Fill out your details and click Create My Account
- A moment later you'll receive an email. Click on the email to verify your account, and you'll be redirected into your new CoinMarketCap dashboard. Congrats! You now have access to the CoinMarketCap API. Hover over the API Key box in the top left and copy the key to your clipboard.
Part 2: Pull Data from CoinMarketCap into Sheets
The easiest way to get started with the CoinMarketCap API is through API Connector’s built-in integration.
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- Select CoinMarketCap from the drop-down list of applications
- Under Authorization, enter your API key.
- Choose an endpoint. We’ll start with
/v1/cryptocurrency/listings/latest
, which is the endpoint for fetching the latest market data. - In the parameters section, select auxiliary fields to retrieve.
- Optionally select other parameters, e.g.
limit
to get more than 100 records. - Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
Part 3: Create a Custom Request
Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the API URLs shown in the API documentation. Here's an example request setup:
- Application:
Custom
- Method:
GET
- Request URL:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?aux=cmc_rank
- OAuth:
None
- Headers:
X-CMC_PRO_API_KEY
:your_key
Part 4: Handle Pagination
- CoinMarketCap limits the number of records returned in each request. By default, only 100 records will be returned unless you set the
limit
parameter to 1000. (The documentation says you can specify a limit of up to 5000, but in my own tests limits > 1000 may produce an error). - With API Connector you can loop through multiple pages of 1000 with offset-limit pagination handling, like this:
- Pagination type:
offset-limit
- Offset parameter:
start
- Limit parameter:
limit
- Limit value:
1000
- Run until: choose when to stop running the request
- Pagination type:
Part 5: API Documentation
Official API documentation: https://coinmarketcap.com/api/documentation/v1/
Appendix: CoinMarketCap Template
In this template, everything is configured for you to simply add your API key along with whatever currencies you're interested in and get a dashboard like below:
Here is the template link.
When I load the data into google sheets it only loads 100 coins. is there a way to export all coins in order to filter on market cap, circulating supply, volume?
You can add the parameter
limit=5000
to get 5000 rows of data. If that's still not enough, you can combine it with thestart
parameter.I'll update the article to reflect this.
Thakns so muc! I successfully did it. However, there are many critical coins that are not being reported. For example; Aion, WAX and Peerplays are all not there, even after I had imported over 1000 coins.
Woat could be the issue?
I just tested a request to
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=5000
and found all 3 of those coins on the list. Which URL are you using?Good Article,Thank you Ana.
great article, i just wanted to know can we create forecast report using live price which will automatically update ?
Hi there, if you want data to automatically update you can either set up up scheduling (paid feature) or use the IMPORTAPI() function.
hi Ana, thanks for the article, very helpful. Can you provide more details on how to use the importapi in this context? thanks!
Hey Cris, please check this article. The section called "Fast Cell-Based Refresh" shows how you'd hook up your API call to a checkbox.
Hello Ana,
I am using the API based on your useful Article put here.
When I run the request the timestamp shown is 2020-08-29T06:34:36.879Z .But the Actual time I run the API was 1:38 PM JST.Due to this current Price is not shown(The Binance Exchane price was 17 USDT but my Google sheet is showing 15 USDT).Could you help to fix this issue?
The endpoint returns 3 different timestamps: 1) Timestamp (ISO 8601) of the last time this cryptocurrency's market data was updated, 2) Timestamp (ISO 8601) of when the conversion currency's current value was referenced, and 3) Current timestamp (ISO 8601) on the server. Are all of them looking off? In my own tests the timestamps don't differ from API Connector's own timestamps by more than about 30 seconds. Can you please add a timestamp (located at Output Options > Add timestamp) and compare the API Connector timestamp to the server timestamp?
Keep in mind that the API Connector timestamp will reflect the time zone of your sheet, which can be viewed by clicking File > Spreadsheet settings.
Hello Ana,
I had selected "Append" in the Output mode and due to that nothing gets updated(Date,Price),Once I changed to "Overwrite" then everything gets updated properly.
But I have some formulas put in the Page 1(limit 5000) and these formuals are lost when I select Overwrite.Could you let me know how to fix the update issue by having Append option as I don't want to loose my Customer formulas added in page1.
Hi Ragu, append mode should return all data except the headers from the first row, to avoid repeating headers within the sheet. Are you seeing something different?
Since overwrite mode overwrites everything in the sheet, I suggest using a formula like
=query({Sheet1!A1:Z10000})
to pull all your data into a second sheet. Then you can apply your formulas to that second sheet, and won't lose your formulas when the data refreshes.Update: you can now set a starting cell to avoid overwriting your formulas.
Hi!
I'm trying to load all of the metadata for all of the coins and im having problems with setting correct request.
https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?id=300
returns meta on 1 coin only.
How do I return URLS only for all of the crypto currencies ?
Thanks
I don't think CoinMarketCap provides an option to get all at once. You need to specify IDs in a comma separated list, like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?id=300,1,2
Alternately, you can list the slugs or symbols as described here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyInfo
Hello Ana
How can I build an endpoint to call, for example the BTC and ETH, symbols and logos at the same time?
I made this but I get errors:
api.coinmarketcap.com/v1/cryptocurrency/info?symbols="BTC,ETH"?logo="BTC,ETH"
It is based on what I've seen here: https://imgur.com/a/jMws1fV
That looks fine to me, I think you just need to remove the quotation marks, e.g.
https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?symbol=BTC,ETH
. Also it should be ?symbol=, not ?symbols=.Yes, Ana, it works well! Thanks a lot
(BTW I made a mistake with "symbols" parameter because it's singular, not plural as I wrote earlier")
My last question is how to get the logo of each one of these cryptos. Should I need to create a new endpoint and run it from another spreadsheet or can I run it in the same one?
I have this URL but due to 8 calls limits of the free API plan I'm not able to test today:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?symbol=BTC,ETH?logo=BTC,ETH
But I'm not sure, since "logo" belongs to the "aux" parameter https://imgur.com/a/jMws1fV
You don't need to do anything extra, as logo is already included as one of the fields from your original request URL. You should see links to the logos returned in the fields
data » BTC » logo
anddata » BTC » logo
.Also, if you want to actually see the images in your sheet, you can add a cell containing the IMAGE() function, e.g. =IMAGE(M2), and it will automatically display the image from the link.
Amazing! It worked pretty well
Thanks a lot Ana. Have a wonderful day 🙂
Hi Ana and thanks a lot for your article!
What if I just want USD price, 24h % and 7jd % for a list of coins defined by their ID (because symbol is sometimes the same for 2 coins)?
Hey David, you'd make a request like
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1,1027,825
, where you include whatever IDs you need in theid
parameter. Btw, I recommend using compact report style (under Output Options) to make the output easier to read.Thanks, it works fine but how can i filter columns?
I just need coin symbol, USD price, 24h % and 7d %
I think it's in JMESpath but i don't know the syntax...
Hi there, you can try this:
data.*[].{symbol:symbol,quote:quote}
. For more info, please check my JMESPath guide.Alternatively, you can just query the columns you want into a new sheet using Sheets functions. For example a function like this would get columns A, C, and E:
=QUERY(Data!A:Z, “select A, C, E”)
Update: you can now use API Connector's visual field editor to simply select the columns you want to see.
Hi Ana,
your article helped me a lot. Thank you.
I want to create a spreadsheet where i have the overview on my coins and their current values.
Therefore i need to build connections from the prices of the coins to other fields.
What about if the rank of the coins change, will the connection be wrong or invalid afterwards?
What is the best way to connect the fields?
sorry my technical english isn't so good...
Hey Florian! If I understand correctly, you have a list of coins in one sheet, and want to keep all the data lined up?
If so, you can just use Google Sheets functions for this, for example put all your coins in one column and then use VLOOKUP to pull associated data into associated columns. There are lots of tutorials online about how to use VLOOKUP, here's one: https://exceljet.net/excel-functions/excel-vlookup-function. With VLOOKUP, it doesn't matter if your coin data changes position, since you'll be matching on the coin name or ID.
Let me know if that answers your question, if not, feel free to message back with more detail about what you're looking for.
Hi Ana, could you maybe elaborate on this solution. I have tried the Multiple VLOOKUP Matches but could not get it to work.
I had difficulties as well since the data is ordered in a different way after importing. I have my personal data (coins) on one tab, and the data from CMC on the other.
I am not able to match on the coin name or ID and get the values in the second tab. Is there a wau to do that? Thank you very much for all the time and effort you put into this. Cheers, EC
I think I need to see your sheet to help troubleshoot your issue. Could you please contact support so I can take a look? Otherwise, how about using the preset template in this article?
Hi Ana,
one more question. My data does not update. What do i have to configure for automatic updates?
Unfortunately I don't understand your quote:
"If you’re looking for a more dynamic, ticker-like experience in Sheets, check out the API Connector custom function IMPORTAPI() for faster refresh rates."
Thanks for your help 🙂
Automatic updates are handled by scheduling. You can click on the Scheduling tab to set that up, and see this article for more info: https://mixedanalytics.com/knowledge-base/api-connector-scheduling/
As another option, you can look into API Connector's feature IMPORTAPI. It's a different feature from scheduling, but it has some data refresh capabilities. You can see this article for more info on that (check the section called Fast Cell-Based Refresh): https://mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/
Hello Ana, thank you for the wonderful detailed article! I was able to set this up in my spreadsheet in no time at all. 🙂
Right now all price quotes are in USD. Is there a way to modify the request URL so it displays prices in BTC? Thank you very much!
Thank you for the nice comment, I'm glad the article was useful!
CoinMarketCap's documentation says that you can convert currency in the quote object from USD to BTC by adding
?convert=BTC
to the end of your URL. Can you please try that and see how it goes?Hello! so i have done everything perfectly but i wanna add an extra something. I want to add convert for the usd prices that i receive from coinmarketcap via that method. Then i want a real time converter to change the prices to euro as well.
Is that possible via coinmarkecap's api? and if yes what's the exact code i should type in the app?
CoinMarketCap lets you convert currencies by adding the
convert
parameter to the end of your API request URL, e.g.https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?convert=EUR
.Sheets itself also has currency conversion functions, e.g. you can enter a function like
=GOOGLEFINANCE("CURRENCY:USDEUR")
directly into your cell to see a conversion rate.ahhh thanks a lot, i made a little bit of digging myself and found the googlefinance method!
I will need your help on another one though, it's the last thing missing to completely set up my excel. So i have one sheet in which all the data for the top 500 coins come and another one in which i have my portfolio
On each of the coins that i am interested in in the price cell i have an ="name of the other sheet"AG2 for example and ofc when i click refresh the price change. Here is the problem though. The coin ranking is constantly change so basically except the top 2 coins all the other prices change as well and i have to manually change it each time.
The "best" thing i thought was to take the api for only certain coins but still i would have that problem in a smaller scale and if i wanna add new coins then here we go again.
Do you have a suggestion? btw sorry for the trouble
Hi Angel, no need to apologize, I'm happy to help : )
Instead of referencing cells directly, you can use VLOOKUP, that way it doesn't matter if the position of the coins change. Here's one article about how VLOOKUP works: https://exceljet.net/excel-functions/excel-vlookup-function
If that isn't clear, feel free to share your sheet with edit access, I can then help you in your sheet directly.
Hello, I'm trying to retrieve a quote for The Graph which has symbol GRT from Coinmarketcap. However, when I request that token, I get a quote for Global Rental Token which apparently has the same symbol. Is there a way to specify exactly which token I desire? Thank you.
I see, in that case I think you need to use the slug instead of the symbol. So instead of
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=GRT
you can usehttps://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?slug=the-graph
.How often do the prices update?
You can check CoinMarketCap's docs for specifics, but their faq says "Most endpoints update every 1 minute".
Hi All, how do I get this to change prices in real time. Thank you. https://docs.google.com/spreadsheets/d/e/2PACX-1vSOvzpKY_Zbq5vpzFgAXZAnktchaBTJj6_1LWiLbos7jQnJuEmvWfDvYkvafiLTb4OXhjvpn5D_xYIL/pubhtml#
By default you need to click Run for these requests to refresh, but API Connector provides two methods to refresh automatically: scheduling and the IMPORTAPI function. Neither method is exactly realtime though (scheduling is limited to 1x an hour, and importapi to once every couple minutes).
Hello, first of all, congratulations on your website.
to get the quotations of cryptocurrencies in euros? would the following formula be correct?
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?start=1&limit=1000&EUR=CONVERT&CMC_PRO_API_KEY= (MI API)
Please enter it like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=1000&convert=EUR&CMC_PRO_API_KEY=yourapikey
Alternately you can run it like shown in the article, with the API key entered into the Headers section.
MUCHAS GRACIAS
Hi There!
just wanted to ask if there's a way to get all the coins listed which have a market cap of $5M - $10M
Sure, you can check their documentation about it here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyListingsLatest
Basically you'll need to enter a URL like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?market_cap_min=5000000&market_cap_max=10000000
I'm getting the following error when I try this:
{ "status": { "timestamp": "2021-03-03T14:06:52.064Z", "error_code": 1002, "error_message": "API key missing.", "elapsed": 0, "credit_count": 0 } }
Please make sure you've included your API key in the headers section, with Key = X-CMC_PRO_API_KEY, Value = your API key
Hi,
I'm also getting an error 'Attribute provided with invalid value:'
In the header box I have this:
Key = X-CMC_PRO_API_KEY, Value = my API key
Just putting in my key returns the error Brad mentioned above.
In the value box next to it I have:
application/json
Do you think it's spacing? Sorry, I'm completely new to this!
That sounds like it's correct but I can't really say without seeing it. Please check the screenshots in the article to see exactly what it should look like, or feel free to send a screenshot via support if you'd like me to take a look.
Amazing post Ana, thank you so much!
Managed to get it work with the coingecko's, thanks Ana!
Hi,
Great work! Can you please tell me why it does not recognize and fetch data for all these coins? I have put all these into Inputs tab. I "refresh all now" but on Summary tab all those below are empty from columns C to N. Thanks
LTO,DATA,AMKR,NULS,CELR,CTXC,ONE,MLT,WIN,DUSK,BLZ,AION,CTSI,COTI,WRX,GXC,NKN,ARPA,DOCK,STPT,COS,KEY,MITH,PERL,MDT,GTO,MBL,CHR,TCT,DREP,TROY,OGN,AKRO,BEAM,FIO,IRIS,JST,NBS,OXT,REEF,RIF,SKL
I tried your set of symbols and saw the following error returned in the status bar: "Invalid value for "symbol": "MLT").
So it looks like MLT isn't a valid symbol. Please change it to AMLT and your request should work.
celer network. dusk network , enjin coin, energy web token for these coins telling resource at url not found.
Not totally sure what you're referring to, which endpoint are you using? I see info for, say, Enjin Coin with a URL
like https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ENJ
Hey Amanda, I would just pull the data into a 'raw data' sheet, then use Google Sheets' VLOOKUP or MATCH functions for this, that way it doesn't matter if the source rows get rearranged (the source rows will always get rearranged since sort order changes between pulls). Or if you meant you want fewer columns returned, you can use the "aux" parameter described in their documentation to cut out response properties you don't need. For example,
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1&aux=max_supply
would send a response without data tags.Update: You can now use API Connector's visual field editor to do this more simply.
Hello,
is it possible te replace the list of symbols
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=CHSB,XRP,BTC
with a reference cell that would contain all the symbols ?
What is the good syntax ?
thank you !
Hey Nicolas, sure, you can put all your symbols into one cell (e.g. Sheet1, cell A1) and then reference that cell like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=+++Sheet1!A1+++
You can check this article for more info on referencing cells in your requests: https://mixedanalytics.com/knowledge-base/api-connector-create-api-request-based-on-cell/
Hi, I tried you URL as above and the result is displayed horizontally. (only 1 row, all tickers & their values displayed horizontally in many many columns)
How do I fix this? Thank you.
Hi I think this is the issue mentioned in the article under the header "latest quotes for BTC and ETH, converted to euros". Please check that section for how to resolve it with JMESPath, or use a different endpoint.
How do I pull all the information for coins in DeFi because using
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?tag=defi
Only pulls the first hundred.
I paid but I'm still a little confused.
Thank you.
Hi there, by default CoinMarketCap limits data to 100 rows. Just add
&limit=1000
to the end of your URL to get more.If you still need more records after that, you can apply pagination handling. Check the section above called "Handle Pagination" for information on how to set that up, or feel free to respond if you'd like further clarification.
Suggest adding it to the guide. Clearly, everyone needs this.
It is in the guide, here. It's also in the preset application menu as one of the parameters. Have I misunderstood what you're suggesting I change?
Hi Ana,
How i can build the P2P binance API?
Thanks for your support.
Hey Guillermo, sorry, I'm not really sure what your question means, but it looks like people might be discussing it here? So maybe you can check or post in there for more info.
Hello! Is it possible to just pull a list of specific coins? I'm trying to track the prices against my purchases but the way the API organizes by CMC rank messes with how my table links to different cells... I.e, if a coin changes rank, my price tracker will link to the wrong coin. Does that make sense?
Hey there, yeah, there's an example in the article of pulling in specific coins. But it sounds like your table is linking to specific cells, right? When you make a spreadsheet you usually don’t want to reference cells directly, since it’s pretty normal for source data to move around. You can use a Sheets function like VLOOKUP so the data stays in place regardless of its exact location in the list. With VLOOKUP you set the name of the coin as your lookup value, then set your sheets to read from that, rather than having them look for a fixed cell location. Hope that helps point you in the right direction!
Yes! This is super helpful. Thank you so much for the follow up
Hello Ana,
Congratulations on your work.
I am working on excel, everything is working fine when I send the following requests
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=600&convert_id=2790
and
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=600&convert_id=2781
but I would like a return with 2 currencies usd "id 2781" and eur "id 2790"
I have tried the following queries which do not work.
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=600&convert_id=2781,2790
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1,2,3&convert_id=2781,2790
Thank you
Sergio from France
Hey Sergio, what does the error message say? I tried it in my own account and it says "Your plan is limited to 1 convert options" so it looks like you need to be on a paid plan for this request to work.
Thanks Ana for your quick response.
I have an HTTP 400 Bad Request in my dashboard.
and in excel i have the same.
DataSource.Error: Web.Contents failed to get content from "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=9180&convert_id=2781,2790" (400): Bad Request
Details:
DataSourceKind = Web
DataSourcePath = https: //pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest
Url = https: //pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest? Id = 9180 & convert_id = 2781,2790
Yep, the "Your plan is limited" error is an HTTP 400 error. I'm not familiar with using Excel to connect to APIs so I can't comment on that, but I suspect it's the same issue.
Hello Ana,
I don't know why, but for some reason in the Summary Tab the columns percent_change_7d (0 value), percent_change_24h (0 value), last_updated and market_cap remain empty while all the other columns get regular values. It should do the vlookup, but somehow it is not successful. I tried it with the template sheet out of the box.
Anyone else got this problem?
Thank you for your great work!
Phil
Hey Phil, thanks for the message! I see the problem, if coins have a lot of tags, it's possible they may produce too many columns and go outside the bounds of the VLOOKUP. To fix, go to the Summary tab cell B9. You'll see a formula like this:
=arrayformula(if($B$9:$B<>"",iferror(vlookup($B9:$B,Data!$D:$AZ,match(E$7,Data!$1:$1,0)-3,false),""),""))
Change the AZ to ZZ, like this:
=arrayformula(if($B$9:$B<>"",iferror(vlookup($B9:$B,Data!$D:$ZZ,match(E$7,Data!$1:$1,0)-3,false),""),""))
Then copy it all the way across the row and the data should populate correctly. I've updated the template as well.
Hey Ana,
yep, this was exactly what caused the problem!
Thank you so much for your quick and helpful response!
All the best for you and your assets! 🙂
Cheers!
Phil
I just want to show only the price of the currency in USDT without any other information in one sell lets choose ETH for example
By default API Connector will return whatever the API sends back. You can check their documentation to confirm, but I don't think CoinMarketCap has any endpoint that returns only the price of the currency without any other information, but you can edit the fields displayed in your sheet.
Hey! Awesome tutorial. Can you please tell me how should I use this tutorial to see all the recently listed coins on CMC? (https://coinmarketcap.com/new/)
Thank you!
Thanks! Try this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?sort=date_added&sort_dir=asc
Hello Ana,
First of all, thank you very much for the tutorial as it has helped me to understand the CoinmarketCap API.
I was writing to you in case you can guide me to capture the different markets for one or more currencies. For example, for BTC with USDT
# 1 - BTC - Binance - BTCUSDT - 39,000
# 2 - BTC - Huobi - BTCUSDT - 38950
Is it possible with the API?
Do you mean you want to get the price of Bitcoin on different exchanges? I'm not really sure about that, but maybe this link is what you're looking for: https://coinmarketcap.com/api/documentation/v1/#operation/getV1ExchangeQuotesLatest
Yes, i would like to try with arbitrage. I almost get using Excel but the perfomance is horrible when i try to get the top 100 coins with their respective pairs.
I'll try this night with your solution in google sheets. Thanks !!!!!
Cool, good luck! If you can't get it from the API maybe you can try a site like this: https://bitcoincharts.com/markets/
Hi Ana. I’ve created a spreadsheet to do the auto updates for me from CMC and it has been working great. It has stopped working however about 2 weeks or so ago. I haven’t changed anything on the spreadsheet. Do you know if the endpoints has been changed? I get a “(400) Bad Request” when running the update. Here’s the script I have.
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ADA,ALGO
Hey Van! I don't think anything has changed, I just tested that URL and it worked fine for me. Also, their API documentation says 400 (Bad Request) = "The server could not process the request, likely due to an invalid argument" which implies a misconfigured URL, but I don't see any problem here. Can you please confirm that this is the URL producing the error? Are you running any other requests in your sheet?
I just tried it again in a new worksheet and I get the same errors.
Details: "Web.Contents failed to get contents from 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ADA,AGI,ALGO,ANKR,ATOM,BAND,BAT,BNB,' (400): Bad Request
Seems odd to me it was working fine and now it doesn't.
That doesn't look like an API Connector error message... I guess you're running this in Power BI? Anyway I tried your URL in API Connector and got the following error message:
"error_code":400,"error_message":"Invalid value for "symbol": "AGI""
So it looks like AGI is no longer valid. Just remove that symbol from your list and it should work, please try and let me know how it goes.
Thank you so much for checking and your reply. I tried it again this morning and still failing with that error. I don't have any other script running. It was working before but not sure why it does not work now. Here's a copy of the whole string.
DataSource.Error: Web.Contents failed to get contents from 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ADA,AGI,ALGO,ANKR,ATOM,BAND,BAT,BNB,BRD,BTC,CEL,CSC,DAI,DOT,DSLA,ENJ,ETH,FLR,Gzil,HBAR,HOT,ICX,LINK,MATIC,NPXS,OCE,ONE,PAC,POLX,QNT,RSR,SNX,STMX,TFUEL,THETA,UBX,VET,VTHO,WAN,XDC,XLM,XRP,XTZ,ZIL' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest
Url=https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ADA,AGI,ALGO,ANKR,ATOM,BAND,BAT,BNB,BRD,BTC,CEL,CSC,DAI,DOT,DSLA,ENJ,ETH,FLR,Gzil,HBAR,HOT,ICX,LINK,MATIC,NPXS,OCE,ONE,PAC,POLX,QNT,RSR,SNX,STMX,TFUEL,THETA,UBX,VET,VTHO,WAN,XDC,XLM,XRP,XTZ,ZIL
Thank you so much Ana, that worked. There was an update on AGI to AGIX. You ROCK!!! I appreciate your time and expertise in this matter. Have an awesome week.
Awesome, glad that worked 🙂
It doesn't work. Sorry I wasted an hour on it. Starting with "step 3 - there IS NO API key value provided ANYWHERE.
Sorry you're frustrated, but part 1 shows how to get your API key, and is clearly titled "Get your CoinMarketCap API Key".
Anyway, you need to click the "Get Your API Key Now" button at https://coinmarketcap.com/api/
Hey Ana,
Is there a way to get this data only for BTC and ETH, and historic? This means, daily prices for the last months/year, etc?
Sure, CoinMarketCap provides some endpoints for historical data here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesHistorical
Yes i've been reading, but i believe its only possible to do that with a paid subscription to Coin Market, right?
Yeah, I think so. If you're looking for a free solution you can check some other crypto APIs, e.g. CoinGecko has a
/coins/{id}/history
endpoint.Hi Ana
Is there a way to get only current price and auto update the price? Thank you
Sure, you could use an endpoint like this:
https://pro-api.coinmarketcap.com/v1/tools/price-conversion?amount=1&symbol=BTC&convert=USD
. That will give you the current price of BTC in USD (you can substitute those symbols for other currencies if you like).CoinMarketCap sends back some additional metadata fields in the response, so if you ONLY want to see the price, you could do an HLOOKUP on that field from another tab, like
=hlookup("quote.USD.price",'Sheet1'!A1:K2,2,false)
. Or, if you wanted to do it all in one go, you could add a JMESPath filter to your request likedata.{quote:quote}
As for auto-updating the price, you can check out scheduling.
If i am using Symbol to pull lets say prices for 100 coins. All of a sudden 1 coin came up with a wrong / duplicate symbol, hence pulling in the wrong price. Can i update the URL to use symbols for 99 coins and ID for the 1 coin? OR would i have to change the entire URL and update all 100 coins to ID instead of symbol (which is what I am trying to avoid)? When i tried to put symbol and id in the same URL, it gave an error.
Their documentation says "Alternatively pass one or more comma-separated cryptocurrency symbols" so unfortunately I think it's just one or the other, you can't mix and match.
Hi,
I try to get data for symbol DXT (Dexit finance), but it always displays DXT (data wallet) - which is also the correct symbol. What can I do to get dexit finance values imported?
Since the symbol isn't unique, you should use the ID instead. You can get the ID with a request URL like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/map?symbol=DXT
Once you have it, you use it in requests like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=11661&convert=USD
Hi! I'm wondering how to differentiate between coins with the same ticker symbol (e.g. there are 2+ different MIST coins).
Right now I'm running this query:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=MIST&convert=USD
, but it's pulling the wrong MIST.TYSM for your help!
You'll need to pull from the ID instead of the symbol. Please see my comment here, it shows some examples.
I did this and it is working! thank you!
but the time stamp of the data is always old not the actual time I make the request. Is there a solution for this?
Do you mean the timestamp sent back from CoinMarketCap, or the timestamp set by API Connector (info)? The timestamp from API Connector should match the time you made the request, if not please double-check your sheet locale.
Hey Ana,
Have been using the paid version. I need get the market close prices on a particular date for multiple tokens from Coinmarketcap. Is there a specific query for that. Need this urgently.
Hey Eugene, I checked CoinMarketCap's documentation and it looks like you can get this from a URL like
https://pro-api.coinmarketcap.com/v1/cryptocurrency/ohlcv/historical?time_period=daily&symbol=BTC,ETH&time_start=2020-11-29&time_end=2020-11-30
Please check the documentation link as it describes their requirements for access (you'll need a paid CMC plan).
Is there any way to get the YTD%?
I'm not sure about that, I don't see YTD in their documentation.
How can we find out the ID for a particular coin? Is it listed on coinmarketcap.com somewhere? Like if I wanted to find the ID of a random coin like "SHIB" for instance.
And some of the coins I'm trying to find the ID for aren't in the top 5000. So putting
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=5000
as the API url doesn't work.Like this coin for instance: "https://coinmarketcap.com/currencies/metawars/"
You can do a request to
https://pro-api.coinmarketcap.com/v1/cryptocurrency/map?symbol=SHIB,WARS,BTC
. As long as you have the symbols, you can list them out to get the IDs.Thank you that works perfectly.
trying to pull in wonderland TIME and its pulling the other one.. Can you help
Please check this comment for info on using the ID instead of symbol.
Hi! Is there a way that I can use a symbol + id request? I'm currently working with lots of symbols, but there is only one that have multiple coins on the same symbol. The way I could correct that is by using the id search instead but that would make me change all symbols to ids. So every time I need to add a coin, I need to look for it's symbol?...
Example:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH,SOL,FTM,RAY,SRM,SHIB,RUNE,ATOM,BNB,USDT,CRP
In this case, I need CRP for CropperFinance, not Crypton.
You need to use IDs or symbols, you can't mix and match. So you'll need to switch to an ID-based request.
Tried this one, but it's still not working...
Hmm, that's odd, what kind of error do you get?
Feel free to message support if you'd like me to take a look.
Hello, thank you very much for the tutorial, and the (free) template. I changed it to the coins what hold in my portfolio, but I faced 2 problems:
1. The price (data.XYCoinname.quote.USD.price) shows a "very" rounded number (for example: 0.56$ is 1$ according to this, altough if I look it up in the data_NoJMES sheet, it shows the correct price) Is there any way to make it show the full value?
2. Because I mainly invest into new/startup coins, usually they have "self reported" marketcap, circulating supply, etc. Is there any chance to pull these values into this template?
Thank you for your help, have a nice day Ana.
Hey Bo!
1. to see unrounded numbers, just click the "increase decimal places" button in the Google Sheets toolbar at the top of the page.
2. We can pull whatever data CoinMarketCap provides, but I don't know if those self-reported values are different from the "regular" marketcap, circulating supply etc values. I think for this it might be better to check out CoinMarketCap's API documentation, because they'll have the definitive answer on what's available. If you find the metrics you want there, and don't know how to pull them into your sheet, feel free to reach out and I'll help you set up your request.
hi. i wanted to make a dynamic sheet where i will enter the symbol in cells ranges A3:A7 and it gives me respected prices in cell B3:B7
But here I can see that I have to manually enter the crypto symbol. Is the above possible?
Sure, you can use cell values in your request by wrapping the references in +++ on each side. Please see this article for examples: https://mixedanalytics.com/knowledge-base/api-connector-create-api-request-based-on-cell/
Hi there, I have added the API data but I am not sure how/if it will be updated regularly like every 15 mins like google sheets with stock prices, that is what I'm after, so could you confirm if it will work like that?
Hey Andrew, to update your data you need to manually refresh it or set up scheduling. Scheduling is limited to 1x an hour, so unfortunately even that won't update every 15 mins.
Hi everyone,
I am trying to get certain variables from coinmarketcap API, such as price and so on.. I am using the line:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=
It works well for almost all of the projects I am interested in, but I am having troubles with one symbol A.O.T, project Age of Tanks. For some reason I am not getting anything for this project. It looks like API has some problem with the symbol "A.O.T". Can you show me some tricks how to solve it? Thank you.
Hmm I'm not sure either, their error message says "symbol should only include comma-separated alphanumeric cryptocurrency symbols" so it seems like a bug on their side related to the periods in the symbol name. I can only get it to work when I use the ID instead like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=15439
Thank you Ana. I am new to this so I wasnt sure whether it is my mistake or not. Thank you for providing code that works.
Jan.
How do you find the id to a coin?
Please check this comment for info on using the ID instead of symbol.
Hey there, great article, very helpful thanks a lot !
Quick feedback, for some reason I can't run the request since 2 days while it was working perfectly before. The error message received is the following :
ERROR: The request could not be satisfied 403 ERROR 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. Generated by cloudfront (CloudFront) Request ID: xj3pJgljwmuhrszJ0ZsVg2TKhAxpxxWta_QwcaQCAa06i_rfpGEF4A==
Would you have any idea where it's coming from ?
Hi guy's,
I have the same problem, even if I modify the request it doesn't work.
If you have found a solution could you explain it and how I can solve it .
Many thanks
Quite a lot of people are reporting this error right now, you can see some more reports in our user forum here.
The error comes from CoinMarketCap, so unfortunately there's nothing we can do on this side besides report the error to them through their contact form. Actually, strangely enough CoinMarketCap requests are still working fine for me. Can you please share the exact request URL you're using so I can test it?
Hello,
Thanks
I use this one :
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=1000
Thanks. That one is still working for me, too.
Hey Ana,
Thanks for the reply ! I'm using the exact same url than the one shared by Anonymous, so that's really strange. I'll try submitting a contact form to CMC.
Cheers,
After some back and forth, I received this response from CMC: Thanks for your patience. We can confirm that there was indeed a 403 error due to some backend configuration issues on our end. We've since remedied the issue, and we are seeing a dissipation of 403 errors.
So hopefully the issue is now resolved.
Hello!
I have the same problem, but the weird thing is that on the example that you recently posted it works fine, but on my sheet doesn't, do you know what did you update recently ?
Hey Marco, we haven't updated anything recently. I'm not sure what you mean by the example works, but not in your sheet. Where does it work?
If a symbol on CMC is duplicated, how do we pull in the Symbol by ID instead?
Please check this comment for info on using the ID instead of symbol.
Hi,
Where can I get the ID?
The linked comment you're responding to has an example request of how to get the ID for a specific symbol. Or if you want to see the IDs for all symbols at once, you can use this URL:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/map
I would have a list of top 100 market cap coins only on Binance , is it possible ?
I don’t think there’s an endpoint for that specifically but you can request a coin list from Binance and then compare it to a coin list from CoinMarketCap to see the difference.
Hi,
Thanks for such a great post. It's been very helpful and one of the only APIs I could figure it out. I am having trouble with part 5 (to get more than just the top 100). When I put in the parameters it skipped from coin#100 to coin #1000 and then continued from there. Is there something I am doing wrong?
Hey Daniel, I'm glad this works for you. I think the issue here is that pagination settings only apply to loops after the first loop, so you need to add your limit parameter to that first request URL too, like this
https://pro-api.coinmarketcap.com/v1/global-metrics/quotes/latest?limit=1000
.Can you please check if that resolves it?
Hi,
Thanks a lot for the deatailed document
One question i would like to ask is there away to get the price and volume of a coin from CMC to google sheet for smaller time frame like 5,10,15,20,30 min please?
Thanks for you help
If you check their historical endpoints they provide intervals of 5 mins, 10 mins, and so on. That's a paid endpoint, but you can also find similar free data in other crypto APIs, e.g. minute by minute data from Cryptocompare: https://min-api.cryptocompare.com/documentation?key=Historical&cat=dataHistominute
Hi Ana- Thanks very much for a great app here. I've sent your site to a few friends as well. Question for you... is there a way to make a single click button on my Google sheet that will trigger the "Refresh All Now" function in the the API connector instead of having to click on Extensions>API Connector>Refresh All Now
Thank you for your nice comment! We don't have a button, but you can refresh your requests through a checkbox if you run your requests through the IMPORTAPI function: https://mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/
Hello... I have used the api to keep track of my crytos portfolio. I have a problem. If I include SHDW, it reports the Shadow Token, but I want to have the GenesysGo Shadow, which apparently has the same ticker in coinmarket. To do?
Please check this comment for info on using the ID instead of symbol.
Hello - I have been using this for a couple of months but now it is no longer working. Every time I try to refresh the API pull in Google Sheets I get an error saying:
"Request failed: "data" is not defined."
When I look at CoinMarketCap it is showing the API pull happens (evidenced by the time stamp and CMC saying I've used credits). However, Google sheets is not working or updating because of this error.
Hi Kyle, sorry! That was a bug related to (what should have been) a very minor update.
Can you please refresh your sheet and try again? I noticed and fixed the issue about 10 minutes ago so it should work now, though it might take a few minutes for the update to propagate through to everyone.
Great, thanks. It is now working!
Hi,
Thank you Ana. Wonderful job!
I know a lot of people have already asked about this particular issue of wrong symbol returning by api but I'm using your template and we can only enter symbols in "Inputs" tab.
Not possible to use id instead? Any workaround?..
Thank you for your help!
Hi Alex! Thank you for the suggestion, I just updated the template to accept either IDs or symbols. Please copy it again and you should see the updates (you'll need to import the new JSON file too).
Awesome Ana! Thank you very much.
Hi Ana,
One more issue. How do you update the coins list? I want to add new token id's that are not in your tab.
Can you add a script to do that?
Thank you for your help
Hey Alex, you can run
https://pro-api.coinmarketcap.com/v1/cryptocurrency/map
to see all coins and their IDs.Hi Ana
Json File error, Please update new one..Report status error and i alloking for All time high and all time low data
Hey Bjohanna, I just tested the file and it worked fine on my end. Can you please try downloading the import file again?
Hi! Ana,
First of all, i would like to thank for your very informative articles.
How can i get the historical data of the coins such as Bitcoin, Etherium and Eth withinin 90 days?
Thanks, Khaled!
CoinMarketCap only provides historical data on their paid plans, so if you're looking for free data, I suggest using a different crypto API like CryptoCompare. With CryptoCompare you can get 90 days of historical data for bitcoin with a URL like
https://min-api.cryptocompare.com/data/v2/histoday?fsym=BTC&tsym=USD&limit=90
(please check the linked article for more info)Hello,
You need to find a solution because we can't use anymore this google sheet.
Appsheet changed something, and they don't support anymore the free plan
Sorry, what do I need to find a solution for? Not sure what this has to do with Appsheet...
Sorry, there were some days that API connector has issues and didn't appear anymore. Now it's ok
How can we add names of multiple coins and get data of the coins?
Hey John, you can use the
/v2/cryptocurrency/quotes/latest
endpoint.Hi Ana,
Thanks for this great article.
I have couple of problems, first my Pagination doesnt change, It is on none and I have no option to change it.
Second how can I get the entire historical data (starting from 2013) till today for all coins?
P.S. I have educational subscription which allows me to get whole data
For getting historical data, you should be able to manually change parameters or use pagination handling to automatically cycle through a complete data set, but the specifics depend on the endpoint. Which endpoint are you using?
Thanks Ana.
Yes, I just purchased the pagination feature, and now I have access to entire coins but just recent information (mostly last 90 days). what I want is to have the historical daily information for all coins (their daily prices and volume for example).
I have used many thing but is just not working.
the endpoint that I am using is cryptocurrency.
I don't think CoinMarketCap provides historical daily info for all coins at once. As far as I can see, these are your options:
1) The Listings Historical endpoint returns all cryptocurrencies on a specific date, like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/historical?Date=2021-01-01&limit=5000&aux=date_added
2) The OHLCV Historical endpoint returns historical open, high, low, close, and volume data for a list of currencies over a date range, like this:
https://pro-api.coinmarketcap.com/v2/cryptocurrency/ohlcv/historical?slug=bitcoin,ethereum&time_period=daily&time_start=2018-09-19&time_end=2022-05-30
So whichever method you choose, looks like you'll need to run multiple requests to retrieve all the data. Our multi-query request function can help automate that by letting you cycle through a list of currencies or dates.
Thanks Ana. unfortunately none of these URLs works. first goes with error :
1) AT2: Completed with errors
- We received an error from coinmarketcap.com (400) show response
{"status":{"timestamp":"2022-05-30T12:16:01.801Z","error_code":400,"error_message":"\"date\" is required","elapsed":0,"credit_count":0}}
and second one ends with no results or no error!
Sorry, I didn't test these first as I don't have a paid CMC account. But the first error message looks like the "date" parameter is case-sensitive, so you just need to change "Date" to "date". I'm not sure why the second one didn't work. If you'd like, please feel free to message support and I can run some tests with you directly in your sheet.
Thanks Ana, I just submit a form to support.
I am in hurry as my subscription to coinmarket will end in 7 days. So I would appreciate if you could help me ASAP.
Hi,
First, I would like to thank you for you job, it's amazing ! I never heard about API...just discovered a wonderful tool!
I tried some tests with the recommanded endpoint (specific crypto), but I can't find all the crypto I want on the list.
When I try another recommanded endpoint with all crypto, I can find these cryptos but I don't know how to put them on the previous endpoint ?
I would like to put KNC, utk and dozen of others. So, do I have to go to edit the and inser my crypto on the code himself or can I had these cryptos on the recommanded formular ?
Thank you for your nice comment. You can simply type in the cryptocurrency slugs or symbols and hit enter, you aren't limited to those that you see on the dropdown list.
Hi Ana,
Thank you for your brilliant work! I am currently trying to use your template and getting the following error, when trying to refresh the data:
" 1) Data: Completed with errors
- We received an error from coinmarketcap.com (401) show response
{ "status": { "timestamp": "2022-07-23T10:48:22.816Z", "error_code": 1002, "error_message": "API key missing.", "elapsed": 0, "credit_count": 0 } } "
Could you please tell what can I do with it? Thx!
Hi there, please make sure you've entered your API key in the Inputs tab (cell B1), that should resolve the issue.
getting this error today, anyone else?
1) active cryptocurrencies: Completed with errors
- We received an error from coinmarketcap.com (504) show response
504 Gateway Time-out 504 Gateway Time-out Sorry for the inconvenience. Please report this message and include the following information to us. Thank you very much! URL:
http://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=1000&aux=num_market_pairs%2Cdate_added
Server: nginx_dispatcher_rl_0ec5dc4fac2d464d6 Date: 2022/08/13 18:30:45 Powered by TenginetengineAre you still having an issue? I just tried that URL and it worked for me.
Hi, is it possible to pull other data for the coins like tg group, website, community etc?
Yeah, check out their endpoint for metadata, it returns social links and other info: https://coinmarketcap.com/api/documentation/v1/#operation/getV2CryptocurrencyInfo
Hi Ana,
I read almost your comments and feedbacks. I hope that you can help me about API.
I want to sort API with filter: Marketcap $10M - $100M and 24h Volume ($10M - $100M)
Thank you, Ana.
You can add filters to the /listings/latest endpoint. I think this custom request URL should work, please check:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?market_cap_min=10000000&market_cap_max=100000000&volume_24h_min=10000000&volume_24h_max=100000000&aux=num_market_pairs,cmc_rank,date_added
hi ana,
thanks for the great work; having a problem looking up certain coins like: RVN (2577), HNT (5665), etc.
when i put these in the inputs sheet, it returns N/A in the summary?
I just tested and those IDs seemed to work fine on this side. I wonder if it's gone over some limit, how many coins are you pulling data for? Feel free to contact support if you'd like me to take a look there.
Hi, glad I could come across this, I am using the free api in CoinMarketCap
is there a way to get only one token at a time, with 1h, 1d and 30d and other data to show and does it refresh when i start google sheets
You can use the
/cryptocurrency/quotes/latest
endpoint and enter a single value for the slug parameter to get data for one token at a time. However, to get 1h, 1d, and 30d data you'll need to use CMC's historical data endpoint. That one is only available on CMC's Hobbyist and up paid plans.Hi, could you let me how can I get daily volume of the two exchanges : Binance and Coinbase?
I believe you'd use the (paid) endpoint shown here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1ExchangeQuotesLatest. So an example request URL would be
https://pro-api.coinmarketcap.com/v1/exchange/quotes/latest?slug=binance,coinbase
.If you're looking for free data, you can try one of the data sources listed here, e.g.
https://api.coinlore.net/api/exchanges/
will get you a list of exchanges with their volume (btw, this API returns data in a somewhat unusual format, you can enter a single*
into the JMESPath field to make it print out neatly).hi there, im having some issues with token symbols randomly changing what token they get data for. for instance symbol OCEAN which used to pull data from Ocean Protocol, now pulls data from something called Burst Ocean which is weird because its not even a token listen on coinmarketcap. I can see that potentially happening for tokens that share a ticker, but not sure whats happening here. Is there a way to solve it? thanks!
You can use the ID instead of the symbol to avoid this issue. The /map endpoint will map symbols to IDs, e.g.
https://pro-api.coinmarketcap.com/v1/cryptocurrency/map?symbol=OCEAN
returns a data ID of 3911 for Ocean Protocol, and this ID will never change.thanks for the reply Ana. How would i do that? I'm currently using the template sheet you provided, where i input the ticker symbols on the one page and then I get a dashboard with the data on the other page
I noticed that just in the last couple of days a whole bunch of tokens that used to work fine are now pulling the wrong data, i wonder if some IDs have changed on coinmarket cap
thank you for your help!
If you check the Inputs tab, there's a small dropdown selector in cell B3 that lets you choose whether to identify coins by their ID or their symbol. If you toggle it to ID, you can then enter all the coin IDs. It does sound like CoinMarketCap must have pushed through some update that changed a lot of symbols at once, so using IDs will be more reliable going forward. Just let me know if you run into any issues.
ok great thanks for letting me know. My sheet was not showing that option but I just realised its because it was an old version. I downloaded the new one now and I see it. Thanks again for taking the time to help!
Hi Ana,
I am receiving a,
REPORT STATUS
1) Data: Completed with errors
- We received an error from coinmarketcap.com (401) show response
when trying to Run the request (Extensions >> API Connector >> Refresh All Now)
to refresh to current prices
thx
What does the full error message say when you click "show response"? Generally 401 errors from CoinMarketCap are due to an invalid or missing API key, so please make sure you've entered your key correctly.
Heya Ana!
Thanks for the template, first off! Quick coupla setup questions:
Where do I set the destination to import the data from my API connector? (What tab/cell, precisely?).
Also...if I set the coins I have on the Inputs tab, will only those get populated? (I don't really need to watch the top 100).
You don't need to change the data destination if you're using the template, since it's all already set up in the API request file linked in the sheet (please check the "Information" tab if you missed it). The request file sets the data destination to cell A1 of sheet Data. And that's correct, if you set your coins in the Inputs tab, only those coins will be populated.
Hi Ana,
I have tried over ten times since yesterday to use my Coinmarketcap API - I am on the free plan - to download latest quotes without succcess. Each time I get an error as follows:-
"Completed with errors
- We received an error from coinmarketcap.com (400) show response
{"status":{"timestamp":"2023-12-01T13:33:51.192Z","error_code":400,"error_message":"\"value\" must contain at least one of [id, symbol, slug]","elapsed":0,"credit_count":0}} "
Please help me to sort out where I am going wrong. Thanks,
Hey Efam, is this through our preset integration or your own custom reqeust URL? The error message says you need to include the id, symbol, or slug parameter, so I'd make sure one of those is included, e.g.
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1,2
. If that doesn't resolve the issue, please share how you've configured your request and I can give you more specific feedback.hey Ana, I get the following error across 6 reports. As far as i can tell. nothing has changed on the sheet! Thank you
4) Data_2_2: Completed with errors
- We received an error from coinmarketcap.com (400) show response
{"status":{"timestamp":"2023-12-07T10:30:12.444Z","error_code":400,"error_message":"\"id\" should only include comma-separated numeric CoinMarketCap cryptocurrency ids","elapsed":0,"credit_count":0}}
Hey Richard, are these reports being run through our preset integration, your own custom request, or the template sheet on this page? If it's the template, can you please check the value in Inputs!D8? It should be a comma separated list of IDs, let me know if that's not what you're seeing.
simple! yes that was it, hadn't seen a couple of blank cells way down the list
thanks so much!
Hi Ana - the list of coins seems to be missing some on my sheet - is there a reason for that?
For example, Beam (current CMC rank 63), Optimism (39), Arbitrum,
Is there a limit im not aware of? thanks
There's a limit of 100 coins, but if you have fewer than that, I'd double check that you've entered the correct coin IDs or symbols.
thanks - I have only added 50. But those coins arent in the coin list tab ?
They may have been added more recently than the coin list was updated. To get the IDs of any coin, you can navigate to that page on CoinMarketCap, e.g. if you go to https://coinmarketcap.com/currencies/ethereum/, the URL shows that the coin symbol is
ethereum
, and the UCID (shown in the left-hand sidebar) shows that the ID is1027
.You have a talent for explaining complex concepts simply.
Thanks 🙂
I have created a request and one of the assets I’m trying to track is Bonk on CoinMarketCap: https://coinmarketcap.com/currencies/bonk1/. I know CMC tracks it because i have it on my watchlist, but when I create the request, the USD price cell is empty. How can i fix that?
Please enter the id as
bonk1
instead ofbonk
.Hi, Ana
Please tell me how to tie up the update of the coin exchange rate by now, so that you don't have to click "refresh all now" all the time
To refresh data automatically, you can set up scheduling, or use the IMPORTAPI function so that the request refreshes when you tick a checkbox.
could you please help and send a ready-made IMPORTAPI solution to your template. I will be grateful
Hey Maxym, just create an empty tab and type
=IMPORTAPI("Data")
to run the request through IMPORTAPI.Hi, with this URL https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=999 and all settings for CONFIGURE API REQUEST Custom-Headers: X-CMC_PRO_API_KEY and so on, I receive a list of 999 Coins correctly.
But some of them are not included, and I need some guidelines on how can i receive the exact data like URL added but only for selected coins like BTC,ETH,BNB,SOL,LINK ?
Hi Adrian,
Mixed Analytics has built-in support for CoinMarketCap (select Application > CoinMarketCap when creating a new request). Go ahead and select the `/v2/cryptocurrency/quotes/latest` endpoint, and add the selected coins under request parameters in the `symbol` field.
HI, thank you. I think for LABS Group and for zkSwap Finance (ZF) doesn't exist now for API, for the rest works. Wenn i enter one of this for example zkSwap Finance i habe this error.) API-Data: Completed with errors
- We received an error from coinmarketcap.com (400) show response
Make sure to only include the coin symbol (e.g. ZF or LABS), and not the full name of the coin.