*****Important information - Coinmarketcap has changed how you can connect to their data and an API is required. Historic data is only available now on a paid API. You can however still get live and current data with the free API. Therefore the sections below on connecting to coinmarketcap are no longer valid. To learn how to connect Excel to the API, I would suggest you follow the instructions in this article and video***
How to connect Excel to Coinmarketcap API (new article)
Considering The Excel Club is the first Excel blog in the world to reward you with cryptocurrency when you take part in our learn and earn activities, I thought it was time I did an Excel post that touches on the very subject of Cryptocurrency.
Excel has been used to track and chart data before the birth of bitcoin and since then Excel has come a long way. It is now very easy to connect to live, external data sources, model it and chart it and then refresh it with ease.
In this post, we are going to look at the following
- Learn How to connect Excel to Coinmarketcap Ticker for current cryptocurrency pricing and statistics
- Step by Step – How to get Dynamic historic cryptocurrency data from Coinmarketcap to Excel using power query and query parameters
- Create a dashboard to visualize and interact with the data.
As Excel has advanced so much in the last few years, we are going to use Excel Power Tools, Power Pivot and Power Query. The videos are recorded using Excel 365; however, you can do the same with earlier and standalone versions of Excel. You will find all the links and URLs you need in the details below
At the end of this post you will find the Learn and Earn activity. This is your chance to put into practice what you learn in this post and earn some cryptocurrency (steem) rewards for doing so.
How do I Connect Excel to Coinmarketcap Ticker?
Connecting to Coinmarketcap ticker will return a table containing statistics for the top 100 coins ranked by market cap. You can view the API endpoint online and by using power query we can connect using the From Web option on the Data ribbon in Excel. The data is in JSON format, but don’t let that scare you. In a few easy steps, we can connect to the data and load it into Excel or our data model. If you are interested in learning more about working with JSON in Excel you can check out this article.
Step 1: Connect to https://api.coinmarketcap.com/v1/ticker/ from the Web Option in the Data ribbon in Excel.
Step 2: The Power query editor will open with the data contained within a List. Convert this list to a Table.
Step 3: The table will contain one column, each cell containing a Record. Expand the column extracting the Records into new columns.
Step 4: Confirm all data types and update where necessary.
Step 5: Rename the Query to something suitable such as ticker.
Step 6: Load the data as a connection only.
Enjoy a FREE course on us and become an Excel Ninja in no time
How do I connect Excel to Coinmarketcap Historic Data?
Connecting to historic data from Coinmarketcap is a little more complex than connecting to the ticker. The data we will be connecting to is stored as a table on a webpage and is not an API endpoint like the Ticker data.
One of the obstacles is the historical data URL requires an end date. This date is static in the URL however we need to be able to change it and make it dynamic. To overcome this, we are going to set up a Power query parameter which will make the date in the URL dynamic and can be reused in other queries.
Step by step – connecting Coinmarketcap to Excel for historic data
Step 1: Navigate to the Historical Data page of your selected coin on Coinmarketcap. From the calendar option, select a date range. Copy the URL. This will be the base point for the function query and will look something like this;
https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20180731&end=20190731
Step 2: Connect to the base point URL from the Web option in the Data ribbon in Excel and power query will open with the table of data loaded as a query. Rename the query to the coin name and ensure all data types are correct.
Step 3: Create a parameter for the end date. Keep in mind the format of the date in the URL is text YYYYMMDD. By changing this date, all of the query’s that are linked to the parameter will also update.
Step 4: Return to the first query where we connected to the base point. Edit the source setting and using the advanced editor ass the newly created parameter as a part.
Step 5: Add a custom column to identify the coin.
Step 6: Duplicate the query
Step 7: Rename the duplicate query to the new coin name
Step 8: Update the custom column to the new coin name
Step 9: Change the source. The only part of the source that needs changing is the coin name within the URL.
Step 10 – Repeat steps 6-9 for each cryptocurrency you wish to pull historic data from Coinmarketcap.
Step 11 – Append query as new and append all the historic data queries into one.
Step 12 – Load data as a connection only
Create A Data Model
Now that we have established connections to both historic cryptocurrency pricing and stats, and the ticker information, we can now use Power Pivot to model that data adding any calculations we need such as Moving Averages. We will look at the moving averages in a different post. Now we are going to set up a model so we can easily chart our data and uses slicers and timelines to filter the coins and time period.
Step 1: Load the Historic data connection and the Ticker connection to Power Pivot. We do not need to load the other tables that contain individual coin information as we have merged all this data into the one table.
Step 2: Create a table in Excel that contains a unique list of all your coins. Load this table into power pivot. This table is a dimensions table and will make up one side of the one to many relationships.
Step 3: In PowerPivot create a relationship between the fact tables (the historic data and the ticker data) and the newly created dimensions table with the unique list of coins.
Step 4: Select Insert Vertical Pivot Table and Chart from PowerPivot. This will bring you back to Excel where you can now set up and format the chart and table to create your dashboard.
Build a CryptoCurrency Dashboard with live Coinmarketcap data.
The video was rather long, and for that reason we rushed over creating the dashboard, leaving it rather unfinished. Please feel free to use the comments section below on any questions or clarifications you need on presentation and formatting of the dashboard.
The steps taken to replicate the dashboard are as follows
- Make a copy of the pivot chart. Ctrl + C will copy the chart. Paste the chart below the original chart.
- Populate the Pivot table with fields from the Ticker table. In the rows place the coin name and, in the values, add any field from the ticker you wish to display in the table of your cryptocurrency dashboard.
- Populate the first chart with data from the historic table. Add the date field to the axis and both the high and low price to the value. You could instead select the open and close prices to display in the chart.
- Populate the second chart also with data from the historic table. Add the date field to the axis and the volume to the values. Format chart area to No fill, delete the axis labels and overlay the chart on the first chart.
- Insert a slicer using the name field from the ticker data
- Insert a timeline to easily filter the data by different time frames.
- Ensure that the slicer and the timeline are connected to the charts and the table.
- Tidy up on formatting and presentation.
Learn and Earn Activity
Follow the instructions in the activity and post them in the comments section below. If you have a hive account and you log in with your posting key, you can earn rewards for taking part in this learn and earn activity. Once you have completed the steps below, you can take a screenshot of the dashboard and post it in the comments below:
- Connect to Coinmarketcap ticker and load it as a connection
- Connect to historic data for Bitcoin. Add a new column to identify the coin.
- Create a parameter so you can quickly change the end date and update the Bitcoin query
- Copy the bitcoin query for 2 additional coins. Merge the query’s into one table
- Load the relevant data in Power Pivot
- Create an add a dimensions table for the coins and set up the correct relationships
- Set up your pivot charts and tables and create your dashboard
If you have any questions or feedback or tips that could improve the steps above, post them in the comments section below and you too could earn cryptocurrency rewards.
Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox with Earn and Learn activities.
Now there is value in Learning with The Excel Club and our Learn and Earn activities.
We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn - Powered with Blockchain
To Find out more now and start earning while you are learning Excel and Power BI
Cross posted from my blog with SteemPress : https://theexcelclub.com/excel-and-coinmarketcap-live-cryptocurrency-dashboard/