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).

Screenshot 4: Split text to columns

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 called ImportXML. 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:

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.