How To Use Coinmarketcap Api

The CoinMarketCap Pro API is a great source for cryptocurrency information on electric current and historical prices, substitution information and more. If information technology is your wish to utilize Excel to import CoinMarketCap API information, and take it updated live, look no further. In this article, I will testify you lot how to use a query to import electric 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 all-time with Excel 2016 or greater, and doesn't piece of work with Excel for Mac. If you lot are using Excel 2013 or prior, you lot will demand to install Power Query before attempting these steps.

Getting Electric current Latest Toll Quotes

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

Step 1: Setup the CoinMarketCap Web Request in Excel

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

Adjacent, we need to input the URL nosotros want to use and setup our API primal as a header item to authenticate with CoinMarketCap. Make sure the Advanced radio button is selected. Enter the API URL you would like to go information 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 asking header parameters type Ten-CMC_PRO_API_KEY in the dropdown box. Paste your API central from CoinMarketCap in the box beside. Click OK. If y'all are asked how you would like to authenticate with the website, cull Bearding. By putting our API primal as a header detail, we are already setup to authenticate with CoinMarketCap, so no farther configuration is necessary.

Excel Coinmarketcap API request options showing API key passed as header

Step 2: Create the Tables in the Excel Query Editor

Side by side, 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 menses is substantially to click into information, we can disregard the condition object. Then, convert to table by clicking Into Tabular array as this is already the point we volition see the list of quotes. So, expand the value record by clicking the two arrows on the header and cheque off anything you are interested in. In this instance, I just want the quote then I only aggrandize that and go on expanding records as long equally you desire to get the data you need. Then, Close & Load.

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

Once you click Close & Load in the query editor, yous should run into the information in Excel as a table.

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

And so, you can configure car-refresh in the properties by checking the "Refresh every" box and setting the frequency. You can as well choose to have the data updated every time the file is opened. Proceed in mind that on the Basic plan, CoinMarketCap only allows 300 requests per day then refreshing more often isn't necessarily amend if y'all don't require frequent refreshes for your purposes.

Auto-refresh settings for excel coinmarketcap data

That's it!