Hack CoinMarketCap’s historical data in less than 5min
Update : Defunct since July 2020, example on CoinGecko instead
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.
Copy paste the above list of 100 cryptocurrencies and paste it in the “Sheet1” “A1” cell (screenshot 3 ).
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.
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.
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:
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”)
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.
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]”)
In order to get the dates, insert a column on the left and insert in cell “A3”.
=importxml(B2, “//td[1]”)
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]”)
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.
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]”)
Live example: HERE
Please do not hesitate to leave feedback. A telegram chat is also available for support.
If this project adds any value to you or your company, donations are greatly appreciated.Thank you for your kindness.
BTC: 1DGYQ7q6DLo6pzfCrQhv1QmQ9oSKwbWFCn
ETH: 0x66395ca0b1cd2d340fe692ae089106925a1067a1
Thanks for reading.