Using the Coinmarketcap API, you can quickly get live ticker information into your Excel spreadsheet for your favorite cryptocurrency.  With the free API that is available, in this article we will look at how you can connect to this API with Excel and keep your data up to date.
<h3>Obtaining a Coinmarketcap API Key</h3>
You can register and sign up for an API key here

<a href="https://pro.coinmarketcap.com/">https://pro.coinmarketcap.com/</a>

The free key will allow you
<ul>
 <li>9 market data endpoints</li>
 <li>10K call credit/mo</li>
</ul>
However, you will not get access to historical data.  If you are using Excel 365 Beta channel and you have the <a href="https://youtu.be/Q7JZThH_kXs">STOCKHISTORY function</a> you can use this to get historical data for the larger cap coins.  However, it is limited in the coins and the function is not yet available to everyone.

Once you have registered, you will be able to access your developer’s dashboard.  This contains the API key you will need.  You will also find a quick link to the API documentation (<a href="https://coinmarketcap.com/api/documentation/v1/">https://coinmarketcap.com/api/documentation/v1/</a>)

<img src="https://theexcelclub.com/wp-content/uploads/2020/09/1_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="602" height="444" /><br/>
<h2>Creating a URL endpoint to connect to with Excel</h2>
The base of the API URL endpoint is

<a href="https://pro-api.coinmarketcap.com/v1/">https://pro-api.coinmarketcap.com/v1/</a>

We will add more to this URL so we can call call certain data.  The API documentation lists the endpoints, but not all of them are available with the free API.

<img src="https://theexcelclub.com/wp-content/uploads/2020/09/2_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="602" height="421" /><br/>

For our examples we will use the end point /cryptocurrency and for our endpoint paths we will look at /quotes/latest and /listings/latest

<a href="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest">https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest</a> will allow you access to current market information for 1 or more coins.  The default price return is USD and you must use CONVERT to add other exchange rates.

You can find full details on the parameters here <a href="https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesLatest">https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesLatest</a>

The parameters allow you to basically filter and define what you want returned.  For example, we want to return information for BTC,LRC,ADA.  As these are coin symbols, we will use the symbol parameter at the end of the API URL.  Each one separated with a comma.

https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,LRC,ADA

If you need to call more than a few listings as you would do with /quotes/latest, you can use /listing/latest.  This will return a full list, by default the top 100 coins by ranking on coinmarketcap.

<a href="https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest">https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest</a>

By adding different paths and parameter we can filter this list as required.  You can find all the details of the parameter in the API documentation.

For Example, this URL will return the to 100 showing in BTC.

<a href="https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?convert=btc">https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?convert=btc</a>

And this URL will return only the coins with the tag for Defi

<a href="https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?tag=defi">https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?tag=defi</a>

Once we have created our API endpoints, we can then use these to connect Excel to Coinmarketcap data.
<h3>Connecting Excel to the Coinmarketcap API</h3>
<ol>
 <li>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.</li>
 <li>Select Advanced</li>
 <li>Enter the API URL endpoint to the first URL parts box<img src="https://theexcelclub.com/wp-content/uploads/2020/09/3_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="602" height="395" /><br/></li>
 <li>In the HTTP request header parameters(optional) first box enter X-CMC_PRO_API_KEY (you will find this on the Authentication section of the API documentation)</li>
 <li>In the HTTP request header parameters(optional) second box enter your API Key and press OK.<img src="https://theexcelclub.com/wp-content/uploads/2020/09/4_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="602" height="377" /><br/></li>
 <li>Excel will connect to the API and open up the Power Query window. The data is returned in JSON format and we must now extract the data.  To learn more about working with JSON in Excel you can <a href="https://theexcelclub.com/how-to-parse-custom-json-data-using-excel/">read this article</a>.</li>
 <li>Select the row for Data List and click on the word list<img src="https://theexcelclub.com/wp-content/uploads/2020/09/5_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="296" height="270" /><br/></li>
 <li>This will expand the lists of data. Select To Table from the ribbon and select ok for the default settings in the To table option box.</li>
</ol>
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/6_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="602" height="366" /><br/>
<ol start="9">
 <li>Next expand the records by clicking on the column header. Select the columns you want to import and deselect Use original column name as prefix and press ok<img src="https://theexcelclub.com/wp-content/uploads/2020/09/7_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="526" height="452" /><br/></li>
 <li>The new columns of data will expand. However, the quote field will still be a record that we must also expand<img src="https://theexcelclub.com/wp-content/uploads/2020/09/8_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="579" height="308" /><br/></li>
 <li>Click on the expander in the column header for quote to expand the record.</li>
</ol>
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/9_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="546" height="315" /><br/>
<ol start="12">
 <li>This opens another record which we much expand again. Click on the expander in the column header and select the columns you want to import<img src="https://theexcelclub.com/wp-content/uploads/2020/09/10_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="580" height="392" /><br/></li>
 <li>This will import all the selected columns</li>
 <li>Carry out any required transformation steps, most notably ensuring all the data types are correct before you load to Excel.</li>
 <li>From the home Ribbon select Close and Load to load the data as a table in Excel.</li>
</ol>
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/11_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="480" height="445" /><br/>
<h2>Refresh the data from Coinmarketcap API in Excel</h2>
Once all the tables have been loaded into Excel, we can keep these up to date with the latest information.  To update the data, from the data ribbon, select Refresh All.

<img src="https://theexcelclub.com/wp-content/uploads/2020/09/12_connect-Excel-to-Coinmarketcap-API.png" alt="connect Excel to Coinmarketcap API" width="566" height="123" /><br/>
<h2>Conclusion</h2>
Connecting to Coinmarketcap API with Excel is a great way to ensure you have the latest pricing information for Bitcoin and your favorite cryptocurrencies.  This article was brief to show you the steps involved in connecting to the API and getting the ticker and current information into Excel.

To look further at the other API points listed in this article and to get some tips on the data transformations required, do check out this video.

<center><strong>SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX</strong></center></h4>
<a role="button" href="https://theexcelclub.newzenler.com/f/email-signup">SIGN UP</a>
<center>Community Invitation</center></h3>
<center>- <a href="https://peakd.com/c/hive-102332/about">Excel For All</a> -</center></h3>
<center>Decentralized and tokenized</center></h3>
<center><a href="https://peakd.com/c/hive-102332/about">Join today</a></center></h3>
https://youtu.be/_5sXO9H6k6I
<h2>Over to you</h2>
What coinmarketcap end point do you like to use and why?   Do you have any feedback on this article and video?  If so, don't be shy and drop a comment below

<section data-id="3312ba9" data-element_type="section">
<p><center><strong>Do you want to start collecting rewards quickly for learning Excel?  Then you should try:</strong></center></p>
<center><a href="https://theexcelclub.com/excel-learn-earn/">10+ Excel Learn and Earn Activities YOU can do Today</a></center></p>

<center><strong>SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX</strong></center></h4>
<a role="button" href="https://theexcelclub.newzenler.com/f/email-signup">SIGN UP</a>

</section> <br /><center><hr/><em>Cross posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : https://theexcelclub.com/how-to-connect-excel-to-coinmarketcap-api/ </em><hr/></center>