Excel Import CoinMarketCap API Data with Query
HTML-код
- Опубликовано: 30 июл 2024
- Import CoinmarketCap API cryptocurrency prices in Excel using a Web Query and have them refresh automatically at an interval of your choosing!
----
The CoinMarketCap Pro API is a great source for cryptocurrency data on current and historical prices, exchange information and more. If it is your wish to use Excel to import CoinMarketCap API data, and have it updated live, look no further. In this video, I will show you how to use a query to import current prices into Excel and have them updated regularly using the methods shown in the Import JSON Data in Excel article. Be aware that this works best with Excel 2016 or greater, and doesn't work with Excel for Mac. If you are using Excel 2013 or prior, you will need to install Power Query before attempting these steps.
----
Written tutorial: syntaxbytetutorials.com/excel... 
Наука
Thanks, this has been the simplest explanation with all the correct steps I have seen.
excellent. thanks. no music no fancy intros. just really good stuff.
Perfect! Just what I was looking for. Thank you
Fine sir, just used this tutorial to make an excellent portfolio viewer in Excel. Thank you for your wonderful work!
Just perfect. Straight to the point. Thank you
Thanks to you I have done my first API import, your explanation was perfect, many thanks :-)
haha ça m'a fat drôle de voir votre commentaire en premier.
Great tutorial. Very well explained. Thank you
Nice to see it is up to date and welldone explained.
This is awesome! thank you for sharing
Thank you !! You are a life saver
Dude, this was awesome. thank you
Obrigaaaaaado! Me ajudou muito esse video!
Continue compartilhando conhecimento! #Brasil
Thx for the tutorial! U rock! S2
Thank you so much, very useful.
This worked really well, thanks!
Thank you for this video. It's perfect
Good job. Thank you!
you are the GOAT! TY!!
Awesome man thank you so much
Great video thanks
Thank you so much!
Good job, thx!
Thank you very much for this.
Thanks great video.
after you confirm, and everyting is workin fine, how do i go back in and add more coins later?
Thank you for this video. I've been using this logic for some time now. I just recently ran into an issue where "PLA" (PLAIR) is now the 2nd listed "PLA" token on coinmarketcap (due to it's Market Cap being lower than "PLA" (PLANET). So the logic above is now pulling price for Planet, instead of Plair. Any ideas how to fix that so it isolates on PLAIR? I tried adding PLAIR to the URL parts section, but that failed.
Thank you!!
Thanks .. was perfect
Helpful!
Thanks for that.
Thank You!
Thankyou so much
Great video, thanks. My problem is I only have Excel 2010 so I don't think Power Query will work.......?
This make me easier than use Google Drive Thank you
excellent. tks
Can I get the RON or USD with the same query? I mean.. I want to keep my evidence with an excel file. I made some math and now I would love to convert the final answear into RON. Can I just put RON, USD near BTC,LTC ?
Another question. I've built this on VMware Win10 on my Mac. So I edit it on VM Win10 and then move to Mac for daily use. Any Mac users out there know how to "Edit" the coin lists in Mac Excel?
A good video though no why i go a web.contents failed to get contents. registered as an attempt in coinmarket cap
Amazing
Beautiful
im getting DataSource.Error: The request was aborted: Could not create SSL/TLS secure channel . how to fix
Thank you for the explanation, please how do I add the rest of the currencies, at least the currencies I chose
can I do it the other way around? I have my transaction history from Binance and I want to import it to CoinMarket Cap. Is that possible?
very useful guide, thanks. After all the setup, how can I add new crypto currency in the table?
thank you so much for your support , i'would like to ask if i want to import all currencies not just BTC and LTC ,how can i do this?
I would like to know too if you’ve found out
Great stuff! How do I add more currency pairs in a easy way? Without having to import the data all over again and copy paste the API URL and key
This was great! Where's your crypto addresses for donation? Some of us may want to throw you a couple of sats for taking the time to make this awesome video and write up.
Is there a URL we can use to import all data related to all the coins listed on Coinmarketcap
Many thanks! How to import data from binance?
brilliant
I'm trying to use their API to get price change from BTC for all other coins, so how much has ETH, LTC etc. changed in 24h or 7d to BTC. Does anyone know how to do this? I'm sure the data is there in the listing/latest data but needs a formula?
Thanks! I just added &convert=EUR after the api link :-)
Hey, how did you do bro ? :-) can you please give more details
@@hugofund3988 sure, I would like to communicate in another messenger, any suggestions
Excellent tutorial. Question: how do you ADD a crypto to the connection? so example, ETH needs to be added to BTC and LTC. Thank you!
So I did everything right, and I got the price of SLP updated every hour, but now I want to make a list with a new cell for every query made with the updated price, how can I achieve this?
Hello how do i add other coins if i successfully made the excel?
It only gives me "Record" for the value what have i done wrong
Hi, can I just ask how exactly do you use the convert function to get for instance GBP? Thank you and I appreciate your video!
George, did you ever find out how to get prices displayed in GBP?
@@lewminati139 Hello, yes, to do so you can type after "/latest" you add "?convert_id=2791" That would be the ID number for GBP, but if anyone needs a different currency just search it on their documentation and place it there. So the finished link should look like this: pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?convert_id=2791
@@slickxbt Thanks for your help. I can't seem to get this to work. The value.quote column just shows (Record) and not the actual value for some reason. I tried the example shown in the video and that worked OK earlier today. However even the example now only shows (Record). Don't understand what's gone wrong.
@@lewminati139 If you use my link it should give you the top 100 records, I wouldn't know how this would affect if you type in individual cryptos as I didn't figure out how to do 2 separate commands in the same line - that being retrieve x,y,z + convert
This is excellent, thank you. If I want the latest prices for ALL the coins, not just BTC and LTC, what do I enter instead the string instead of BTC,LTC?
I have the same question. Did you find out the answer?
You can simply add the tickers of the coins you want after "=". Example: latest?symbol=BTC,LTC,ADA,SHIB,DOGE,SOL
Thanks for the video. I'm having an issue however, I can't seem to extract the data without the dollar sign attached. That's making it difficult for me to make formulas. How can I extract the info without all the dollar sign?
That's odd as I haven't had that issue myself, but what you can do is another transformation step in the Query Editor to remove the first character. There is some info here: stackoverflow.com/questions/50133461/power-bi-remove-first-7-digits-of-string
Hey cheers for the tutorial, quick question - I have the table loaded in but when going to the query properties to try and set a time interval to see the latest values, I don't have all of the options show in the video. Any idea why that may be? tia
Table design > Refresh ↓ Connection Properties
@ may be you can help me: The update occurs 1 time per minute, but does not coincide with the minutes on the clock, for example, it is updated not at 06:01:00, but at 06:01:44, which is why there is no coincidence with the candles on the chart. Is there any way to fix this, please? And another question is, is it possible to output parameters such as maximum and minimum, opening and closing prices to Excel? Thanks.
I have Excel for mac , latest version and I cannot find this data web import, can you assist please? Thx!
Sorry, I believe the feature is only available on Windows for the time being.
TKS for the video, man i have a question, how i introduce more coins to spreadsheet. like ETH, LINK, others coins.
I would like to know how to get all coins from CMC.
Anyone knows how this works for Mac Os?
How would I go about adding new coins, without going through the whole process?
Where the example of BTC and LTC are selected, just add your other coins e.g. BTC, LTC, ETH, ADA. This should add them to the query, you can also do this within the query you have already created which may be easier but you will have to repeat the query steps as before.
Do you know how to do this for a specific exchange that are is listed on coinmarketcap?
Looks like you can use their /v1/exchange/market-pairs/latest endpoint to get a price at a specific exchange only, but it is only available on their standard plan and up.
Says couldn't authenticate with the credentials provided
How do you share this worksheet for others to use and share around with others, but without sharing the API key? thx
You would need to put the key in the sheet and then make a separate query for that that's linked to from the main query. Or I think the query has a parameters option but it's not as flexible. I show something similar to the first option in my forex exchange rate video.
my problem is some coins are 0.000001345 and the price does not come through...it just comes through as 0
thanks, but i follow as you said and i got all data but the price stuck at 2013 and i din't get any update
Are you using a demo of some sort? I think they have a demo offering that has a sample of old data.
Any idea How to Import the Watchlist from CMC to Excel,, I have many watchlist need to import them to excel.. Please help
I took a quick look at the API but I'm don't think there is unfortunately. Doesn't appear to be a way to get CMC user data.
how do i add additional crypto currencies?
Keeps giving me this, Details: "Specified value has invalid HTTP Header characters.
Parameter name: name". I am using the free version, used both, X-CMC_PRO_API_KEY and X-CMC_FREE_API_KEY NO WORKIE...
Thanks a lot, question how do i do it if i want EUR instead if USD?
You need to use the convert option
@@syntaxbyte could you explain that more detailed please? thank you very much!
The update occurs 1 time per minute, but does not coincide with the minutes on the clock, for example, it is updated not at 06:01:00, but at 06:01:44, which is why there is no coincidence with the candles on the chart. Is there any way to fix this, please? And another question is, is it possible to output parameters such as maximum and minimum, opening and closing prices to Excel? Thanks.
I think that's just the way that the update functionality works. If you want it to work a different way, you would probably need to use VBA to schedule the updates instead.
hmmm the tabs under query > properties are not appearing for me...🤔
Ah nevermind...Data tab > Connections > Workbook Connections > Properties
Slightly different
Hi, I'm new to this, can you please help me. Trying to get the fee API from coinmarketcap, but when I click on Basic plan and on "Get free API key" the site returns me this "Your CMC account needs to be registered for API access. Please use the signup link with this same email/password.". The signup link is nowhere to be seen. Where do I register so that I can get the free API key?
Are you already logged into CMC when doing this? If not, trying logging in beforehand. Otherwise, try logging out and trying again. If both those fail, contact CMC as I'm not familiar with the in and outs of their API sign up process.
I had the same problem. I used another web browser (edge instead of opera) and it worked.
Unable to connect
We encountered an error while trying to connect.
Details: "The 'X-CMC_PRO_API_KEY' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Refer"
Can't get past this screen
Is this error from Excel? It's probably because you chose some other authentication type than anonymous on the dialog shown at 2:50. It might be remembering your decision so it may not be showing the box but still using incorrect settings. I would try a new sheet or resetting you Excel settings. Otherwise, just pass the API key in the URL as a GET parameter instead by using CMC's "convenience" method shown in their docs.
@@syntaxbyte thanks! Will try that
@@syntaxbyte I have the same problem. I tried new xls. Doesnt work. What can I do?
Você tem mais vídeo ensinando a usar a api da coinmarketcap?
holy moly, good tut, but you ran so fast trough it, i had to stop and reroll like 50 times
Where do you even get that link? Nobody explains that..
You can get it from the API docs.
The amount of love sent over to you is un-weighted, you saved me from tons of Search and work
Yo. this video is awesome. BTC at 9K? lol. Hope everyone held.
Anyone know to do this with UCID on coinmarketcap instead of symbol? For example want to add Zyncoin but Zyn comes up as zynecoin, which is incorrect
Where is the API key?
You need to sign up for your own, on the coinmarketcap site. Once you have done so the API key is on the dashboard page.
Man your video has so many landmines... once clicked, no return! Go back to start and try again....
In power query you can undo steps by using the step list on the right
9711.. JESUS CHRIST MAN
Thanks, Didn't work.
Virus in written tutorial !!
What software told you there was a virus? There are no viruses on the site that I'm aware of.
Hey I have a question, how do I pull the data for the entire day of the crypto currency
You need the historical quotes option, which is paid unfortunately: coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesHistorical