In this article, I’ll show you how you can download the latest prices for cryptocurrencies, along with percent and volume changes. The data will be downloaded via an API from coinmarketcap.com. Once you’ve set up the API, it becomes a breeze to pull crypto prices and data into Excel, in just a matter of seconds.
Getting an API Key
One of the first things you’ll want to do is go onto the website https://coinmarketcap.com/api/ where you can request an API key, which you’ll need if you want to query the data. Once you have the key, you can begin pulling in values. You don’t need to worry about saving or remembering your API key because once you’re logged into the site, you’ll see an Overview section that shows you where you can copy your API key by hovering over that section. On this page, you will also see how many credits you have used today and this month versus how many are available on your plan.
Setting up the connection in Power Query
Once you’ve got your API key copied, you can go into Excel and create a Power Query connection. To do this, go under the Data tab and select the From Web button:
Then, you’ll enter the URL for the API connection, which is https://pro-api.coinmarketcap.com. On the documentation page, you’ll also see a list of possible endpoint paths. Under the basic plan, not all endpoints will be available. In this example, I’m just going to retrieve the latest market data. And for that, the URL is as follows:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest
I’ll put that into the Power Query URL. However, because the connection requires authentication, I need to check off the option for Advanced rather than just leave the default to Basic. In the section for HTTP request header parameters, you need to enter X-CMC_PRO_API_KEY (you’ll find this on the documentation page) and your API key. Here’s how that looks:
Then, click on OK and Power Query will go to work on creating your connection.
Formatting the data in Power Query
Once loaded into Power Query, you’ll see this:
If you click on the List button next to data, then you will get a series of records:
On the top-left-hand corner, there is an option to convert this To Table. Click on that button, leave the default options on the next window as they are, and then click on OK. We’re still left with a long list of records. For this step, click on the icon highlighted below, at the top of the column:
When the next screen pops up showing you all the columns that will be expanded, click OK. Now you have something that looks a lot more useable:
But there’s still more information that can be extracted. Scroll over to the last column, which should contain the word ‘quote’ in its name. Here there will be a list of records again. And using that button at the top of the field, this can also be expanded. It only has a USD field and once expanded, it looks like nothing has changed. Click on the header button once more, and now you’ll see fields showing volumes and price changes.
Now, you can load the data into Excel by clicking on the Close & Load button. You should now see it populate in your spreadsheet:
Now you can do a refresh at any point in time and your query will pull in the latest data from coinmarketcap.com.
If you liked this post on How to Pull Crypto Prices and Data Into Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
