Hack CoinMarketCap’s historical data in less than 5min

Update : Defunct since July 2020, example on CoinGecko instead

Eloise

Follow

Published in

The CryptoCurious

6 min readJan 27, 2019

--

Live example: HERE

We will use Google sheets, importXML function and coinmarketcap.com. Using this method, you’ll be able to retrieve daily Close, Open, High, Low, Volume and Market Cap. No API needed, no Add-ins, and no coding skills.

For those that don’t want to read, go to the end section -> conclusion in one formula. Try it for yourself for Bitcoin prices, insert the formula in Google sheets or try it out with a

I will be showing how to retrieve close prices. No need to code through CoinMarketCap’s API, all you will need is to use the function ImportXML that is available through Google sheets.

Create a Google sheet file (skip if familiar)

The best part about Google Sheets is that it’s free and it works on any device — which makes it easy to follow prices. All you’ll need is a web browser (or the Google Sheets app on your iOS or Android device), and a free Google account.

On your Mac or PC, once you have created you Google account, head over to https://drive.google.com/, and you’re ready to get started. Click on the New button on the left of your screen, then Google Sheets. Voilà, you have your spreadsheet.

TOP 100 CRYPTOCURRENCIES

Below you have the the list of the current top 100 cryptocurrencies.

BITCOIN,RIPPLE,ETHEREUM,BITCOIN-CASH,EOS,TETHER,LITECOIN,STELLAR,TRON,BITCOIN-SV,CARDANO,BINANCE-COIN,IOTA,MONERO,DASH,NEM,NEO,ETHEREUM-CLASSIC,MAKER,USD-COIN,ZCASH,WAVES,TEZOS,DOGECOIN,VECHAIN,TRUEUSD,ONTOLOGY,BITCOIN-GOLD,QTUM,ZILLIQA,OMISEGO,0X,CHAINLINK,BASIC-ATTENTION-TOKEN,DECRED,AUGUR,PAXOS-STANDARD-TOKEN,LISK,HOLO,NANO,BITCOIN-DIAMOND,BYTECOIN-BCN,REVAIN,STEEM,ICON,DIGIBYTE,BITSHARES,VERGE,SIACOIN,AETERNITY,STRATIS,GEMINI-DOLLAR,PUNDI-X,BYTOM,IOSTOKEN,KOMODO,DAI,STATUS,POPULOUS,GOLEM,ODEM,FACTOM,ELECTRONEUM,LOOPRING,PAYPIE,CRYPTONEX,REPO,MAIDSAFECOIN,ARDOR,HUOBI-TOKEN,INSIGHT-CHAIN,HYPERCASH,AURORA,MIXIN,WALTONCHAIN,ARK,PIVX,PROJECT-PAI,DECENTRALAND,REDDCOIN,THETA,LINKEY,DIGIXDAO,AION,POWER-LEDGER,KUCOIN-SHARES,TENX,BUGGYRA-COIN-ZERO,DENTACOIN,RAVENCOIN,MONACOIN,CRYPTO-COM,POLYMATH-NETWORK,STASIS-EURS,WANCHAIN,BANCOR,WAX,GXCHAIN,ELASTOS,ZCOIN

If you wish to add more cryptocurrencies, you can do so by adding the name as it is displayed on CoinMarketCap’s website link as shown below. Then just add the name at the above list separated by a comma.

Screenshot 2: Use metaverse for Metaverse ETP (source: CoinMarketCap)

Copy paste the above list of 100 cryptocurrencies and paste it in the “Sheet1” “A1” cell (screenshot 3 ).

Screenshot 3: Copy paste the list in cell “A1”

In order to get all coins in separated columns. Go to the Data tab and select Split text to columns (Screenshot 4).

Insert CoinMarketCap’s links for each crypto

CoinMarketCap’s historical data is found in the generic quoted link (below).

=”https://coinmarketcap.com/currencies/"&A1&"/historical-data/?start=20150428&end=20190126"

Enter the above formula in cell “A2” as shown on the above screenshot. This corresponds to a generic link. In “A1”, you reference to the cell with the name of the cryptocurrency. “start=20150428&end=20190126” gets the data from April 28th, 2013 which corresponds to the oldest given data point from CoinMarketCap and end=20190126 corresponds to today (1/26/2018).

Once you copied the formula in the cell, please verify that the double quote marks are well pasted, it should show up in the same color as it is on the screenshot.

Screenshot 5: Check double quotes and that the last date is today’s date.

Once done, drag “A2” to the left of the sheet in order to fill “B2”, “C2”, “D2”, …., “CV2” which should correspond to the ZCOIN column, the last Top 100 coin.

Google sheet’s ImportXML function

You can import data from any web page using a little function calledImportXML.ImportXML pulls information from any XML field—that is, any field bracketed by a <tag>and a </tag>. So, you can grab data from any website and any metadata generated by any website, anywhere.

Let’s have a look at the structure of CoinMarketCap’s website. This is the page where we get historical prices for Bitcoin.

Screenshot 6: Bitcoin’s historical prices (source: CoinMarketCap)

You can see it looks like a table with the date in the first column, market cap in the last one and each row corresponding to the date’s data. If we have a look at the HTML code by viewing the page source, this is what we get:

Screenshot 7: view-source:https://coinmarketcap.com/currencies/bitcoin/historical-data/

An HTML table is defined with the <table> tag. Each table row is defined with the <tr> tag, which corresponds to each date. A table header is defined with the <th> tag, Date, Open, High, Low, Close, Volume, Market Cap. A table data/cell is defined with the <td> tag.

The importXML function will help you extract the data from the above table. We are interested by the table’s data/cell which is defined with the <td> tag. It works as follows:

=importxml(A2, “//td”)

Screenshot 8: Bitcoin’s historical Open, Low, …, Market Cap in a single column

A2 cell referenced is for Bitcoin’s data. No need for you to do this, but if you drag “A3” to the left of the sheet, you’ll get the below screenshot.

Screenshot 9: All data points from CoinMarketCap historical data

Since CoinMarketCap’s website is well built, tables for all cryptocurrencies have the same structure with rows aligned (all dates on the same row etc…). In order to only get the close price which corresponds to column 5 (Table 1), it work as follows:

=importxml(A2, “//td[5]”)

Table 1: ImportXML //td structure for https://coinmarketcap.com/currencies/bitcoin/historical-data/

Screenshot 10: Bitcoin’s Historical Close Prices in a single column

In order to get the dates, insert a column on the left and insert in cell “A3”.

=importxml(B2, “//td[1]”)

Screenshot 11: Bitcoin’s Historical Close Prices and dates

Drag “B3” to the left of the sheet in order to get all cryptocurrency close prices. Wait about 2min for all prices to update, time will depend on the traffic on CoinMarketCap’s website. I have noticed that Google sheets puts some limits on the number of calls on the function ImportXML, so do not try to make a couple hundreds of call or they will be blocked. In case it does happen, try to open another sheet using another Google account.

Copy all data as value in order to stop the ImportXML call and freeze the data in the sheets.

You can do the same for the other 5 data points (Open, Low, High, Volume and MarketCap). You just need to switch the //td[5] with Table 1’s corresponding numbers.

You now have the Top 100 historical close prices from CoinMarketCap!

Refresh Data Everyday

If you are interested in getting the data refreshed everyday you will need to create another reference cell in the ImportXML formula. In this example, I’ll specify the cell as L2.

=importxml(“https://coinmarketcap.com/currencies/BITCOIN/historical-data/?start=20150428&end="&L2, “//td[1]”)

Screenshot 13: You can index your ImportXML function in order to only get the first column as shown on the above image.

In reference cell L2, you will need to specify today’s date in the following format YEAR&MONTH&DAY. The following formula will get you that format:

=year(today())&if(len(month(today()))=1,”0"&month(today()),month(today()))&if(len(day(today()))=1,”0"&day(today()),day(today()))

In screenshot 14, you can see that L2 displays the right format.

Screenshot 14: =year(today())&if(len(month(today()))=1,”0"&month(today()),month(today()))&if(len(day(today()))=1,”0"&day(today()),day(today()))

And there you go, your data will be refreshed every day!

Conclusion in one formula

Historical Bitcoin Close Prices in one formula:

=importxml(“https://coinmarketcap.com/currencies/BITCOIN/historical-data/?start=20150428&end=20190126", “//td[5]”)