Financial Modeling for Cryptocurrencies: A CoinMarketCap Spreadsheet That Doesn’t Suck

Published in

Cryptosheets

4 min readAug 7, 2018

TL;DR: I created a spreadsheet that automates pulling the entire 1,700+ library of cryptocurrencies from CoinMarketCap. You can find the spreadsheet here:

https://www.dropbox.com/s/19l2bksgojeah4w/Pulling_CoinMarketCap_Data_into_Excel.xlsx?dl=1

CoinMarketCap is the most popular cryptocurrency website in the world. With over 100M page views per month, the website is the go-to resource for cryptocurrency quotes and aggregated spot rates.

While not a perfect solution (we all remember the removal of Korean exchanges event) it still represents the prime place to check cryptocurrency aggregated pricing.

The Problem

While CoinMarketCap does include historical pricing for each coin on it’s website, they do not offer an “Export to CSV” option to easily transfer the data to an outside source (such as Excel, Google Sheets, etc.)

Even then, the “Export” option brings a host of problems, such as:

No auto refresh option
Big potential for mistakes
Requirement to spot check the results

Power users in Excel want the ability to analyze the data for their own trading strategies, but this becomes difficult and cumbersome with the current environment.

A CoinMarketCap dashboard for Excel

We at Cryptosheets developed a simple, beautiful Excel dashboard utilizing the Cryptosheets add-in and CoinMarketCap data.

The workbook features include:

Pulling all 1,700+ cryptocurrencies from CoinMarketCap directly into a worksheet
Name, symbol, rank, Price USD, Price BTC, 24h volume, Market cap, Available supply, Total Supply, Max supply, Percent change (1h, 24h, 7d), Last updated (timestamp, human-readable)
Automatic refresh of data down to every 5-min intervals
Automatic views of Top Gainers, Top Losers, segmented further down by time (last hour, last 24 hours) and market cap

Since we place the data neatly into a worksheet, this gives the user further ability to perform any calculations they want.

Services used:

Microsoft Excel (Excel 2016 for Windows or Mac, Excel Online)
Cryptosheets Add-in*

Note*: The Cryptosheets add-in has a free tier that grants 100 requests per day. Each request equals 100 rows. The CoinMarketCap pull brings in around 1,700 coins, which equals 17 requests.

If you find yourself hitting the request limit but want to continue using the add-in, send me a message and we can work something out. No one should be left in the dark.

If you have Office 365, you are golden.

Summary tab

The summary tab is the re-creation of the CoinMarketCap front page. Here, you will see all of the coins listed, as well as a few selected metrics for a quick snapshot of the market.

Statistics tab

The statistics tab contains a few interesting views that make it easy for the user to see what coins are making the most moves (both positive and negative).

Top Gainers 1 Hour

In the Statistics tab, you will find a ranking of the Top Gainers and Top Losers by the following time periods: 1 hour, 24 hours, and 7 days.

How to use the dashboard

Click here to download the workbook

A note on security — the cryptocurrency industry is rife with corruption, hacks, etc. I know the hesitation in downloading an Excel workbook, so I recommend all users run files through an online virus checking service such as VirusTotal.

Installing Cryptosheets to pull the data

Click here and click the button “Get it now”.

Updating the data

The worksheet uses the “Data” tab to pull in the 1,700+ tickers from CoinMarketCap.

To pull the data, we will log into Cryptosheets, find CoinMarketCap, and export the data

In the Cryptosheets ribbon, click Dashboard
Login to the service through Google, or sign-up option
Click the orange + button
Click CoinMarketCap Tickers
Click the Limit dropdown and choose All
Click the Table Headers toggle
Click into A1 of the “Data” tab, click Highlight Range, and Place Data
Verify that data has updated
You also have the option to choose a timed refresh interval. *Note — CoinMarketCap data refreshes no faster than once every 5 minutes

After implementing, you will now have an auto-updating CoinMarketCap dashboard in Excel that gives ‘at-a-glance’ statistics to follow the big movers in the cryptocurrency space.