How to Import CoinMarketCap API Data into Excel Using Power Query

·

The CoinMarketCap Pro API provides a rich dataset of real-time and historical cryptocurrency prices, market data, and exchange information. For analysts, traders, or enthusiasts, integrating this data directly into Microsoft Excel allows for dynamic tracking and custom analysis. This guide walks you through the process of setting up a live data connection using Excel’s Power Query feature.

🚨 Prerequisites: This method requires Excel 2016 or a later version (including Microsoft 365). It is not compatible with Excel for Mac. Users on Excel 2013 must first install the Power Query add-in.

Step 1: Obtain a CoinMarketCap API Key

Before you can retrieve any data, you need an API key from CoinMarketCap.

  1. Visit the CoinMarketCap website and sign up for an account.
  2. Navigate to the API section and subscribe to a plan. A free Basic plan is available, which offers a limited number of daily calls.
  3. Generate and copy your unique API key. You will use this to authenticate your requests.

Step 2: Construct the API Request URL

The API endpoint for fetching the latest market quotes is:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest

To specify which cryptocurrencies you want data for, use the symbol parameter. For example, to get Bitcoin (BTC) and Litecoin (LTC), append ?symbol=BTC,LTC to the URL.

Your final API request URL will look like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,LTC

You can add more cryptocurrency symbols separated by commas.

Step 3: Configure the Web Query in Excel

This is where you tell Excel how to fetch the data.

  1. In Excel, go to the Data tab on the ribbon.
  2. Select Get Data > From Other Sources > From Web.
  3. In the dialog box, select the Advanced option.
  4. In the URL Parts field, paste your complete API URL.

  5. You must now add your API key as a header for authentication:

    • In the first HTTP request header parameter dropdown, select Enter custom and type: X-CMC_PRO_API_KEY
    • In the value field beside it, paste your CoinMarketCap API key.
  6. Click OK.
  7. If prompted for authentication credentials, select Anonymous. Your API key in the header is all that's needed for access.

Step 4: Transform the JSON Data into a Table

Excel will receive the data in JSON format. Power Query helps you parse and flatten it into a structured table.

  1. The Power Query Editor will open, showing the raw JSON response.
  2. The data is nested. Focus on the data object—you can typically ignore the status object.
  3. Click the Into Table button to convert the record into a table. Confirm the prompt if it appears.
  4. You will now see a list of the cryptocurrencies you requested. Click the expand icon (the double-arrow button) in the column header.
  5. A list of all available fields (e.g., name, symbol, quote) will appear. Select the fields you wish to include in your final table. For price data, you will want to expand the quote field and then further expand the nested USD object to access fields like price, volume_24h, and percent_change_24h.
  6. Continue expanding and selecting columns until you have all the desired data.
  7. Once satisfied, click Close & Load. The transformed data will be loaded as a table in your Excel worksheet.

Step 5: Set Up Automatic Data Refresh

To keep your data current, you can configure Excel to refresh it automatically.

  1. Ensure your cursor is selected within the table you just created.
  2. Go to the Data tab and click Refresh All > Connection Properties.

  3. In the Properties dialog box:

    • To refresh on file open: Check the Refresh data when opening the file box.
    • To refresh periodically: Check the Refresh every box and specify the number of minutes.
  4. Click OK.

⚠️ Important Consideration: The CoinMarketCap Basic plan has a rate limit of 300 calls per day. Setting a very short refresh interval (e.g., every minute) will quickly exhaust your daily quota. Adjust the refresh frequency based on your actual needs.

Frequently Asked Questions

Q: Can I use this method with the free CoinMarketCap API plan?
A: Yes, the free Basic plan can be used. However, be mindful of its 300 calls per day limit and 10,000 credits per month usage cap when setting your refresh rates.

Q: Why is my ‘From Web’ option greyed out in Excel?
A: This typically means you are using an incompatible version of Excel, such as Excel for Mac or a pre-2016 Windows version. Ensure you are on Excel 2016 or newer for Windows.

Q: How do I add more cryptocurrencies to my query later?
A: You need to edit the query source. Right-click your loaded table, select Table > Edit Query. In the Power Query Editor, go to Advanced Editor and modify the symbol parameter in the URL to include the new symbols. 👉 Explore more strategies for advanced data management

Q: The data loads but some columns are errors. What happened?
A: This is often caused by rate limiting. You have exceeded your API plan's call limit. Wait for your limit to reset or upgrade your plan for higher throughput.

Q: Can I get historical price data instead of just the latest quotes?
A: Yes, but it requires a different API endpoint (/quotes/historical). The process in Excel is similar, but the URL and the required parameters will change.

Q: Is there a way to practice this without using my own API key?
A: For practice, you can use generic JSON placeholder services. However, to work with real cryptocurrency data, you must use a valid CoinMarketCap API key. 👉 Get advanced methods for API integration