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.
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.
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.
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.
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.
From the data ribbon, select From Web which can be found in the Get and Transform group of commands. This will open a From Web setup box. In the HTTP request header parameters(optional) second box enter your API Key and press OK. Excel will connect to the API and open up the Power Query window.
In Excel, use the "DATA" tab and select 'From Web' to import data from the API. Follow the prompts to connect to the API and retrieve live price data for your chosen cryptocurrencies.
The CoinMarketCap API, for example, offers a free plan with 11 market data endpoints and up to 10,000 call credits per month, whereas premium plans have access to historical data, increased call credits, more endpoints, and more.
Use the UrlFetchApp method to make HTTP requests to the API. Include your CoinMarketCap API key in the request header for authentication. Write a script to fetch the latest price of a specific cryptocurrency or other data as needed. Use the =getCryptoData() formula in a cell in your Google Sheets to display the data.
What is the rate limit for CoinGecko API (public plan)? CoinGecko's Public API has a rate limit of 5 to 15 calls per minute, depending on usage conditions worldwide.
One way to call an HTTPS REST API from Excel is to use the Web Service functions available in Excel to send a GET or POST request to the API and parse the response. In Excel, you can use the following functions to interact with a web service: WEBSERVICE() : Returns the contents of a web page as text.
Market data is available to users via the API and includes price quotes, volume, market capitalization as well as other important metrics. Numerous cryptocurrencies can avail these. The users need to sign up for an API key, although the use of the API is free. Accessing its features requires this key.
An API key for CoinMarketCap is a unique alphanumeric code provided by CoinMarketCap to users or developers who want to access and use CoinMarketCap's cryptocurrency market data via their API (Application Programming Interface).
The CoinMarketCap provides a range of data solutions through its API (Application Programming Interface) services for developers to access real-time cryptocurrency market data. Developers can get variety of data from its API, such as, Price and Market Data, Historical Crypto Data, Exchange data, Global Metrics and more.
Under the Data tab in Excel, select From Web and select Advanced. Enter the URL of the NSE website from where you want to parse the data (copy the Request URL from the Headers section in the Diagnostic pane). Paste the values for accept-encoding, accept-language, and user-agent under HTTP request header parameters.
Introduction: My name is Kelle Weber, I am a magnificent, enchanting, fair, joyous, light, determined, joyous person who loves writing and wants to share my knowledge and understanding with you.
We notice you're using an ad blocker
Without advertising income, we can't keep making this site awesome for you.
