Excel Import CoinMarketCap API Data with Query

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 article, 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.

Getting Current Latest Price Quotes

With the CoinMarketCap API, current quotes are handled by the https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest endpoint. In order for proper data to be returned, a proper API key (sign up on their website) must be passed as a header item and cryptocurrencies to return selected by the id/slug/symbol parameters. For this example, I will use the symbol parameter.

Step 1: Setup the CoinMarketCap Web Request in Excel

To pull the data into Excel, we use a web query. Under the Data tab in the ribbon, select Get Data > From Other Sources > From Web.

From Web dropdown in Excel

Next, we need to input the URL we want to use and setup our API key as a header item to authenticate with CoinMarketCap. Make sure the Advanced radio button is selected. Enter the API URL you would like to get data from in the top box of URL parts. In this case, I have used https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,LTC to get the latest prices of BTC and LTC. Then, under HTTP request header parameters type X-CMC_PRO_API_KEY in the dropdown box. Paste your API key from CoinMarketCap in the box beside. Click OK. If you are asked how you would like to authenticate with the website, choose Anonymous. By putting our API key as a header item, we are already setup to authenticate with CoinMarketCap, so no further configuration is necessary.

Excel Coinmarketcap API request options showing API key passed as header

Step 2: Create the Tables in the Excel Query Editor

Next, create the right tables using the query editor from the returned data. Because this takes many clicks, please refer to the video below to guide you. The flow is essentially to click into data, we can disregard the status object. Then, convert to table by clicking Into Table as this is already the point we will see the list of quotes. Then, expand the value record by clicking the two arrows on the header and check off anything you are interested in. In this case, I just want the quote so I only expand that and keep expanding records as long as you want to get the data you need. Then, Close & Load.

Step 3: Finished CoinMarketCap Table in Excel and Setup Auto Refresh

Once you click Close & Load in the query editor, you should see the data in Excel as a table.

At this point, you may wish to setup auto-refresh. To do this, go into Connection Properties under Refresh All. Make sure your cursor is on a cell in the table if the Connection Properties button is greyed out.

Connection Properties in Excel

Then, you can configure auto-refresh in the properties by checking the “Refresh every” box and setting the frequency. You can also choose to have the data updated every time the file is opened. Keep in mind that on the Basic plan, CoinMarketCap only allows 300 requests per day so refreshing more often isn’t necessarily better if you don’t require frequent refreshes for your purposes.

Auto-refresh settings for excel coinmarketcap data

That’s it!

74 thoughts on “Excel Import CoinMarketCap API Data with Query”

  2. How can we import data of all cryptocurrencies since CMC limits the data to 100 for 1 query…thank you.

  3. Great, worked a treat.
    Can you show how to get either BTC into Excel in Euros, or, show how to get the USD/EUR FX rate into excel.
    Thanks.

    1. Would be of interest for me as well-thx
      Converting in Excel by using a fixed exchange rate does not match the figures you see on CMC…

      1. If you convert the rate using a fixed exchange rate it probably won’t match CMC because the EUR market will be run separately than the USD market and therefore converting the BTCUSD rate to BTCEUR won’t necessarily match the exchange rate exactly. Might even be an arbitrage opportunity but usually not.

      1. Thankyou, i was stuck trying to concatenate, …latest?id=1,1027,2010?convertAUD it’s one ? and & after that, thanks for that!

  6. Thank you! Very thorough and explanatory! I have a problem tho, I can’t sign up fro CoinMarketCap free API, it simply results ‘Not Found’ after I click the button for Sign up. Is this feature still available?

    1. try to log out from you current coinmarketcap account, I had the same problem and after that I was able to activate my api account using the same credential (email and name + adding of course the name and selecting the plan)

  7. Thanks for the instructions, they were quite helpful. Is there a way to return the price of a coin in BTC value (sats)? E.g. ETH/BTC instead of ETH/USD

    1. The convert option doesn’t just accept fiat currencies, it also accepts BTC as a value. You can use it to get ETH/BTC.

      1. This is great news. Except, I don’t see anything in the instructions about the “convert option”. Can you advise how to do this please?

        1. You need to add it into the URL. It is documented in CoinMarketCap’s documentation, not this tutorial.

    1. Not that I know of at the moment using this method. I know they have implemented some of the query code but don’t have the interface set up so you may be able to get someone to set it up on Windows and then open the file on a Mac if you have the latest 365 version. Otherwise, have a look at my Google Maps VBA tutorial (https://syntaxbytetutorials.com/excel-function-to-calculate-distance-using-google-maps-api-with-vba/) The code there could be adapted for use with CoinMarketCap and that will work on mac.

  9. How could I connect from excel to an API that needs me to send him first by POST USER and Password and he answers me with a json where they see a token and then consult again by GET placing the token in the head all this from excel.

    1. You may need to use VBA or multiple queries in this case. However, you could try the “Access Web content” dialog box that pops up when you connect. Screenshot of the access web content dialog

  10. I have been working on this for 3 hours, saw your video and i was done in 20 minutes. Thank you so much.

          1. Im using this technique and its working properly with one little problem in my case:
            I need to pull the price of a coin wich the ticker is $CHAMP, but there are 2 $CHAMP coins on coinmarketcap and since the other one (the one i dont need the price to be pulled) has a bigger rank or marketcap thn the one i need, excel is pulling the price of the wrong coin. Is there any workaround on this issue? Please help. I cant find a way to do it. Thanks in advance.

      2. Can I add this to multiple conversions? GBP, USD, BTC… get the same quote in 3 currencies?

  12. Hi,
    All works well until excel is closed. I use version 2016
    When open the file again is deleting the API.
    It says: ,, we foun a problem ……query tables deleted”
    Don’t understand what the problem is ???

    Do you have any idea?
    Thank you.

    1. Rather than using https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest you would use https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/historical. This is a premium endpoint of CoinMarketCap’s API and is only available on the standard plan and up.

  14. Would you need historical access to get all time high prices for a list of coins ?
    Been tryign to add a column for ATH for data im receiving through the API using the above explained method. – Any ideas ?

    1. It’s possible to get historical data using CMC but I believe data more than a certain age is paid. Have a look at their API docs.

  15. Some coins use the same symbol. For instance, CND may refer to Cindicator or Cannadrix. How can I specify that I want “CND” to get the quote for Cindicator in my query?

      1. Where can you find the IDs on Coinmarketcap.com? I can’t find them for the life of me on a given coin/token page and the ID isn’t used in the URL.

  16. I notice on coinmarketcap that they list specific exchanges including ByBit – I would love to get the perpetual contracts BTCUSD price specifically from the ByBit exchange – any idea if this is possible and what the endpoint would be? (and if available to non-subscribers) Thanks.

  17. is there any way to stop the data being presented as a table? Can it just be embedded into a cell and look and appear just like normal text? To be able to change its font colour, background colour, yet still function as a live update query cell? Thanks.

  19. It’s such a great tutorial, but I get stuck in the loading data step. Instead of show table with data, it show the login page instead. Please help me with this error. Thank you very much.

    1. I don’t understand where you are stuck. Does the Query editor open with the login page data from CMC or there is an Excel login page? For the Excel login page, usually you just want to choose anonymous. The CMC auth is done using HTTP headers. If excel isn’t showing an anonymous option, just start again with a fresh workbook. If it’s pulling the login page data from CMC, something is wrong with your URL or HTTP headers.

  22. I keep getting an error message and have my API key copied and have followed the instructions to a “T”. any help?

    1. Without posting the error message, you make it difficult to help you. As it stands, I have no idea what could be going on.

    1. Hey I have the same problem (with eur), have you found a solution yet?

  24. Thanks for the info. Is there a way to send a changing list of coins to the API? I.e. I have a list of coins in Excel that I want pricing on, but the list changes and I don’t want to have to update the API call each time.

    Thanks

  25. HI,
    Just to say guys you are fantastic.
    Beautifully presented, simple yet functional.
    Thank you very much.
    Milan from Bristol UK
    🙂

  26. Guys can you help with this one, please.
    I am not getting price of the coins in query
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=algo,forth,ankr,btc,blz,ada,atom,eth,ftt,mdt,ltc,iota,zrx,matic,xpr,ren,trx,safemoon
    even when i add:
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=algo,forth,ankr,btc,blz,ada,atom,eth,ftt,mdt,ltc,iota,zrx,matic,xpr,ren,trx,safemoon&convert=usd
    Sorry but i have never done it and would like to have this coins in line with price refreshed every 30 minutes.
    Milan

  28. Hi,

    Thankyou for this tutorial!

    I am having a bit of a problem. I am using the following URL – https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ETH,BTC,ADA,XRP,THETA,OCEAN,BAKE,LTC

    When I convert this into a table, I expect the table to be like
    A1 – ETH
    A2 – BTC
    A3 – ADA
    A4 – XRP
    A5 – THETA
    …

    But, excel automatically sorts them alphabetically and gives me :

    A1 – ADA
    A2 – BAKE
    A3 – BTC
    A4 – ETH
    A5 – THETA
    …

    This is problematic because I have other cells, referencing the quotes for calculations. For example, if I add BUSD in the table, instead of putting it at the end of it, Excel puts it at Cell A4 (between BTC and ETH) which ruins my calculations that is referencing the value of A4 | ETH in cell B4

  30. Pingback: Excel 365 Save or Open an ODC File – Syntax Byte

  31. Hi, Do you know how to proceed when there are several tokens with the same name? I am trying to import for example BNC from BIFROST project but there are BIONIC, BIFROST (BNC), BitNowCoin, BNC TOKEN which have the same token name ^^
    Any Idea?
    Thanks

  32. Thank you for the explanation, please how do I add the rest of the currencies, at least the currencies I chose

  33. First, thanks for posting this. I used your tutorial this past summer and found it to work beautifully, thank you! Then just today, 11-30-21, my excel file started to NOT work where I could not pull prices from crypto on CMC and gave me an error “400 Bad Request”

    I even even tried to sign up with CMC w a different email address and followed these instructions again, but continue to get the same error message. So, I am wondering how to fix this.

    I cleared my cookies, but that didn’t help. I am n0t sure what else to do.

    Any help would be greatly appreciated!

    GC

  35. I appreciate, cause I found just what I was looking for. You’ve ended my four day long hunt! God Bless you man. Have a great day. Bye

  36. Is there any way to show the “list” data, for example, to show the platforms a coin is listed on for each coin?

  38. How do you keep the price value static on another sheet in the same workbook. I tried basic ‘=latest_symbol_BTC_ETH[@[Value.quote.USD.price]]’, but whenever I sort the other sheet, the value disappears.

    1. Like, you want to keep a record of prices? Sounds like something you might need a macro for honestly. I don’t believe there’s a built-in mechanism to store historical query results.

Comments are closed.