Excel Import CoinMarketCap API Data with Query – Syntax Byte

The CoinMarketCap Pro API is a great source for cryptocurrency data on stream and historical prices, exchange information and more. If it is your regard to use Excel to import CoinMarketCap API data, and have it update bouncy, look no far. In this article, I will show you how to use a question 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 solve with Excel for Mac. If you are using Excel 2013 or prior, you will need to install Power Query before attempting these steps .

Getting Current Latest Price Quotes

With the CoinMarketCap API, current quotes are handled by the https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest end point. In orderliness for proper data to be returned, a proper API winder ( sign up on their web site ) must be passed as a header item and cryptocurrencies to return selected by the id/slug/symbol parameters. For this case, I will use the symbol parameter.

Step 1: Setup the CoinMarketCap Web Request in Excel

To pull the data into Excel, we use a vane question. Under the Data tab in the decoration, blue-ribbon Get Data > From other Sources > From Web .
From Web dropdown in Excel
following, 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 web site, choose Anonymous. By putting our API key as a header detail, we are already setup to authenticate with CoinMarketCap, so no far shape is necessary.

Excel Coinmarketcap API request options showing API key passed as header

Step 2: Create the Tables in the Excel Query Editor

next, create the right tables using the question editor program from the returned data. Because this takes many clicks, please refer to the video below to guide you. The flow is basically 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 tilt of quotes. then, expand the value criminal record by clicking the two arrows on the header and check off anything you are concerned in. In this case, I good want the quote indeed I only expand that and keep expanding records deoxyadenosine monophosphate 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 question editor program, you should see the data in Excel as a board.


At this point, you may wish to setup auto-refresh. To do this, go into Connection Properties under Refresh All. Make certain your cursor is on a cell in the table if the Connection Properties release is greyed out .
Connection Properties in Excel
then, you can configure auto-refresh in the properties by checking the “ Refresh every ” box and setting the frequency. You can besides choose to have the data updated every time the file is opened. Keep in mind that on the Basic plan, CoinMarketCap entirely allows 300 requests per day so refreshing more much international relations and security network ’ thyroxine necessarily better if you don ’ t require frequent refreshes for your purposes .
Auto-refresh settings for excel coinmarketcap data
That ’ s it !

generator : https://ontopwiki.com
Category : Finance

Post navigation

Leave a Comment

Trả lời

Email của bạn sẽ không được hiển thị công khai.