How Do I Update Coinmarketcap Api For Excel Spreadsheet

Using the Coinmarketcap API, you lot can quickly get live ticker data into your Excel spreadsheet for your favorite cryptocurrency.  With the free API that is available, in this commodity we volition look at how you can connect to this API with Excel and proceed your data up to date.

Obtaining a Coinmarketcap API Key

You tin register and sign upwards for an API key here

https://pro.coinmarketcap.com/

The complimentary primal will allow you

• 9 market information endpoints
• 10K phone call credit/mo

However, y'all will not go access to historical data.  If y'all are using Excel 365 Beta channel and you have the STOCKHISTORY part you can use this to go historical data for the larger cap coins.  Even so, it is limited in the coins and the part is non yet bachelor to anybody.

Once you have registered, you will exist able to access your developer's dashboard.  This contains the API key you will need.  You volition as well find a quick link to the API documentation (https://coinmarketcap.com/api/documentation/v1/)

Creating a URL endpoint to connect to with Excel

The base of the API URL endpoint is

https://pro-api.coinmarketcap.com/v1/

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

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

https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest will allow yous admission to current market data for one or more than coins.  The default price return is USD and you must apply Catechumen to add other substitution rates.

You can detect full details on the parameters here https://coinmarketcap.com/api/documentation/v1/#functioning/getV1CryptocurrencyQuotesLatest

The parameters permit you to basically filter and ascertain what you want returned.  For example, nosotros desire to return information for BTC,LRC,ADA.  As these are money symbols, nosotros will utilize 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 demand to telephone call more than a few listings as y'all would do with /quotes/latest, y'all can use /listing/latest.  This volition return a full listing, by default the top 100 coins by ranking on coinmarketcap.

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest

By adding dissimilar paths and parameter we can filter this listing equally required.  Y'all tin discover all the details of the parameter in the API documentation.

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

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?catechumen=btc

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

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?tag=defi

Once nosotros accept created our API endpoints, we can then apply these to connect Excel to Coinmarketcap data.

Connecting Excel to the Coinmarketcap API

1. From the information ribbon, select From Spider web which can be found in the Get and Transform grouping of commands. This will open a From Web setup box.
2. Select Advanced
3. Enter the API URL endpoint to the first URL parts box
4. In the HTTP request header parameters(optional) first box enter X-CMC_PRO_API_KEY (yous volition notice this on the Authentication section of the API documentation)
5. In the HTTP asking header parameters(optional) second box enter your API Key and printing OK.
6. Excel volition connect to the API and open up up the Power Query window. The data is returned in JSON format and we must at present extract the data.  To acquire more about working with JSON in Excel you can read this article.
7. Select the row for Data List and click on the word list
8. This volition expand the lists of information. Select To Tabular array from the ribbon and select ok for the default settings in the To table option box.

9. Adjacent aggrandize the records past clicking on the column header. Select the columns you want to import and deselect Utilize original column name as prefix and press ok
10. The new columns of data will expand. However, the quote field volition still exist a record that nosotros must also expand
11. Click on the expander in the column header for quote to expand the record.

12. This opens another record which we much aggrandize again. Click on the expander in the column header and select the columns you want to import
13. This will import all the selected columns
14. Carry out any required transformation steps, most notably ensuring all the information types are correct before you load to Excel.
15. From the home Ribbon select Close and Load to load the data as a tabular array in Excel.

Refresh the data from Coinmarketcap API in Excel

Once all the tables have been loaded into Excel, nosotros can proceed these up to date with the latest information.  To update the data, from the information ribbon, select Refresh All.

Conclusion

Connecting to Coinmarketcap API with Excel is a groovy way to ensure you have the latest pricing data for Bitcoin and your favorite cryptocurrencies.  This article was brief to show yous the steps involved in connecting to the API and getting the ticker and electric current information into Excel.

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

How Do I Update Coinmarketcap Api For Excel Spreadsheet,

Source: https://theexcelclub.com/how-to-connect-excel-to-coinmarketcap-api/#:~:text=From%20the%20data%20ribbon%2C%20select,a%20From%20Web%20setup%20box.&text=In%20the%20HTTP%20request%20header%20parameters(optional)%20second%20box%20enter,up%20the%20Power%20Query%20window.

Posted by: greenehincture.blogspot.com

Share this post