Pulling CoinMarketCap Data onto a Google Sheet: A Step by Step Guide

Celý článek

As a staunch believer of data-driven investing, I always try my best to gain investible insights backed by data before committing to a decision, it is no different with cryptocurrency trading. And luckily for a not-so-techy guy like me, all I need are Google sheet and CoinMarketCap API to carry out my own analysis. Read on to see how you can fetch and visualize cryptocurrency data using these 2 free tools!

What is CoinMarketCap?

If you aren’t already familiar with CoinMarketCap, here’s a brief intro. It’s a website that provides all the information that you might need related to cryptocurrencies. This includes their prices, market capitalizations, supply volumes, etc.  

What we really need here is its API that gives us access to real time and historical data which will allow us to do some numbers-crunching and chart-building.

What data can I get from the CoinMarketCap API?

Using the CoinMarketCap API, you can view:

• The Price

• The Market Cap

• Volume (in 24 hours)

• Circulating Supply (over the last 24 hours)

• Trends in price (over a day, a week, or a year)

for each cryptocurrency enlisted on the CoinMarketCap website.

How to get data from the CoinMarketCap API into Google Sheets?

Now let’s get down to business. To get access to the API, you need to go to the URL:

Once there, click “Get Your API Key Now”.

You can either sign up for a free Developer Portal account or take advantage of one of their paid plans. With a free account, you get the basic endpoints for latest listings and quotes, and a limited number of calls. If, however, you are interested in extracting historical data or accessing more data endpoints, then you can consider going for a paid membership. Either way, once you have registered for a plan, you will get an API key that you will use for authentication whenever you want to make a call to the API.

Copy this key by simply hovering over the asterix and clicking on the ‘Copy Key’ button. You can then use this key in your code to access your required data.

The CoinMarketCap API provides all the data in the form of a JSON document. Each coin has a designated ID and symbol in the document. For example, the 0th item is Bitcoin, the 1st item is Ethereum, 2nd item is Ripple, and so on. So when you want to know something about Ethereum, you have to access it using the index 1, or the symbol ETH. When you want to access Bitcoin information, you need to use the index 0 or the symbol BTC. 

You can take a peek into the document by going to this link:

One important point to note is that CoinMarketCap recommends using the CoinMarketCap ID to refer to cryptocurrencies instead of symbols. This is because a lot of cryptocurrencies have the same symbol, and symbols are subject to change according to rebranding of the coins. To ensure that you always get your target coin reliably, it is better to use CoinMarketCap’s permanent IDs for the coin. However, since we are just starting out, we are going to be using symbols as it is more intuitive and easily recognizable. Once you get a better hang of this, it is recommended that you start using IDs instead of symbols.

Now pull up your sleeves, it’s time to get coding! Google Sheets allows you to code using Google Script, which is based on Javascript. But even if you don’t know Javascript, this guideline should be pretty simple to understand and use.

Now pull up your sleeves, it’s time to get coding! Google Sheets allows you to code using Google Script, which is based on Javascript. But even if you don’t know Javascript, this guide will be very easy to understand.

Getting the Latest Crypto Prices

You can obtain the latest crypto prices using the free version of the API. To begin, go to Google Sheets and open a blank sheet. Next, you can start coding by navigating to ‘Script Editor’ from the Tools menu. 

1. When the Script Editor opens, you will see a template for a function already present.  You can start coding on top of that. Let’s rename this function as coin_price.

2. Next let’s create a variable named sh1 to hold the handle for the active spreadsheet. This is the sheet we want to print the results to. The name of our active spreadsheet is just Sheet1, so we type:

3. Next , copy the following code to your script editor as it is:

This code contains all the information you would need to get access to a particular endpoint of the API. I obtained it from the CoinMarketCap API Documentation available on:

Since we want to get the latest crypto prices, we will access the endpoint:

v1/cryptocurrency/quotes/latest’

Also, notice the word ‘apiKey’ ? That is where you paste the API Key that you had copied from your CoinMarketCap API account. Make sure you put the key within single quotes.

4. Now, let’s create a variable to hold the URL for the API call. Type:

In our example, we want to know the latest price for Bitcoin. So we will specify the ‘symbol’ as ‘BTC’. As we saw before, the symbol for Bitcoin is BTC. If you want to know the latest price of any other coin, you can go ahead and put the symbol for that instead.

5. Now, we use the Google Script UrlFetchApp.fetch function to get the contents of the JSON file that is present in the ‘url’ we requested. Let’s put these contents into a variable called result.

6. But getting the contents in JSON format will not be of much help. To properly use the data, we need to get the contents in the form of a string. We do this by using the getContentText function. Let’s store the resultant string in variable called txt.

7. Now we need to parse the data in the string into separate Javascript objects, so we can easily extract whatever information we need. For this we use the JSON.parse function and pass the string as a parameter to it. Let’s put the result of this parsing into a variable called d.

var d=JSON.parse(txt); 

8. It’s now time to extract what we want. We want to know the latest price of Bitcoin in USD. The data is arranged in a certain hierarchy in the JSON file, so you will have to know the correct path to access what you need. If you take a look at the JSON code available on the API documentation, you will see:

Here, under data, we have the first object, Bitcoin, having the symbol BTC. Under this, we have the quote object, which contains the USD object. And under this USD object, we have the price.

In other words, we can access the price using:

d.data.BTC.quote.USD.price

To ensure that we have this recorded into the second column of the first row (1,2) of Sheet 1, we put the above path in a setValue function:

And there you have it!

When you put it all together, the code looks like this:

9. Save this function with a name

10. Click Run.

11. After a few seconds, the script is going to ask for your permission to access your Google Sheet. Grant the access.

12. Go to your Google Sheet, you should see the latest Bitcoin price on the second column of the first row on Sheet 1.

You can then write the word ‘Bitcoin’ next to it , for future reference. You can also choose to add names of other coins you might be interested in and get their latest prices in the same way.

13. Now, let’s add a button to refresh our data, insert an image/drawing into a corner of your sheet:

Click on it and select ‘Assign Script’:

Then insert the name of your function:

Once this is done, you can simply click this image whenever you want to re-run your script and refresh your data to see the latest prices for your coins. But remember not to refresh too many times as there is a limit to the number of API calls you can make.

Getting Historical OHLC Crypto Prices

To get historical data you need to have a paid membership. You can get 3 months’ data on the Standard subscription. 

To get 3 month’s OHLC prices, we follow more or less the same system.

1. Create new Google Sheet, open the script editor and change the name of the default function to ohlc_price. Create a handle for the spreadsheet you want to print the results to. Lets again name it sh1. 

2. Copy and add the following code as it is:

Since we want to get historical OHLC crypto prices, we will access the endpoint:

v1/cryptocurrency/ohlcv/historical’

3. Create a variable to hold the URL for the API call. Type:

This will give us access to the OHLC historical prices for crypto with id 1, which is Bitcoin. If you want to know historic OHLC prices for any other coin, you can go ahead and put the id for that instead.

Say we want weekly data for the past 3 months. We can specify this by adding the following to the URL:

&time_start=2019-06-01&interval=weekly

This set of parameters is going to give us weekly data for every week starting from June 1, 2019 but excluding the last interval. 

For more information regarding this you can refer to the CoinMarketCap API documentation.

4. The rest 3 steps are the same as before. 

5. Now to get the individual values of open, high, low and close prices for BTC, you need to know the correct paths for each. If you take a look at the JSON code available on the API documentation, you will see:

Here, under data, we have the first object, which is Bitcoin, having the symbol BTC. Under this, we have the quotes object, which contains 2 quotes for two timestamps (Jan 2, 2019 and Jan 3, 2019). For the first quote, we can access the open price under the USD object as follows:

d.data.quotes[0].quote.USD.open

For the second quote, we use:

d.data.quotes[1].quote.USD.open

..and so on.

So, to put the two quotes into the second and third rows of the first column( (2,2) and (2,3)) of our Sheet 1, we type:

But we want 10 of these quotes spread out over 3 months. And we want all four values: Open, High, Low, Close. We also want the timestamp. So let’s get them one by one.

First, let’s set up our sheet 1:

Notice that I put the Column Headers in the format Low-Open-Close-High, instead of Open-High-Low-Close. This will make it easier to visualize the data later on. 

In the script editor, we need to start a ‘for’ loop with a variable i. This variable will loop from 0 to 9, incrementing by 1 each time and giving us a total of 10 repetitions of the loop.

At each repetition, we go through rows 2 to 11 of each column (hence the values i+2 in the first parameter of the getRange function). We display the 10 timestamp values for the 3 months in the first column, quotes for ‘low’ values in column 2,  quotes for ‘Open’ values in column 3, quotes for ‘Close’ values in column 4 and quotes for ‘High’ values in column 5. This is repeated for every value of i, starting from 0 to 9. 

That’s it! Your entire code should look like this:

And your spreadsheet would now have all your OHLC values of the past 3 months:

Finally, let’s just do a bit of formatting. We just want to see the date part of the time stamp. For that we create a new column between A and B and introduce a formula :

=left(A2,len(A2)-14)

This will remove the last 14 characters of the timestamp in A2 and leave behind just what we want. Finally, drag the handle at the bottom right to copy the format to all the subsequent rows

Visualizing the Historic OHLC data

This part is straightforward. The best way to visualize OHLC data is by using a candlestick chart, which sums up all the details in your data at one glance. 

1. Simply highlight the area between B2 and F11.

2. Click on Insert-> Chart from the Menu

3. You will find a chart editor on the right side of the window. Click on the drop-down for ‘Chart Type’ and scroll down to find ‘Candlestick chart’ and select it.

That’s it, you can now visualize weekly OHLC values for the past 3 months!

You can do this for daily, monthly or even hourly intervals. Moreover, you can do a similar visualization for other coins in the same way, just remember to change the id or symbol when making the URL call. 

The post Pulling CoinMarketCap Data onto a Google Sheet: A Step by Step Guide appeared first on CoinMarketCap.