I'm Talking Bout Crypto Bro!

Over the past year I have been getting really big into Cryptocurrencies.

Being a Software Engineer, I have tried to automate wherever possible my cryptocurrency analysis and so I have decided to create a series of blog posts to share some of those tools and also just general knowledge I have learned about the crypto space.

Why Google Sheets instead of Excel?

To put it quite simple, Google sheets is amazing!

I actively try to get people away from Excel. As a former financial analyst and reformed Excel Guru, I can say without a doubt but if you’re an analyst who is using Excel for anything more than displaying data, or basic math then you are behind the times.

Learn to code, move those models out of Excel, and automate your data munging/computation so you can free your mind up for actual analysis.

Enough Ranting though and back to why Google Sheets is great!

There are so many cool use cases for Google Sheets because it has a robust front-end and back-end API. You can have backend scripts running on a Linux server that connect to your sheet and dump the data you want into your sheet. Or because Google sheets allows you to extend it using a modern programming language, JavaScript, you can add really cool functionality that runs entirely within your Browser.

Today I’m going to show you something I actually use daily. We are going to write a custom google sheets JavaScript function that will get us the latest prices for cryptocurrencies by using the Coin Market Cap API.

Even if you have no experience with programming this tutorial may be realitvely easy for you to follow. And who knows maybe it’ll spark an interest in programming!

Let's get started

The CoinMarketCap API is actually really easy to use and they have a public API that doesn’t require authentication. It will get you the exact same price you see on their website and should represent an average of the price across the exchanges they monitor.

First, lets see how the API functions by getting ALL the latest price info.

Type the following URL into your browser:

https://api.coinmarketcap.com/v1/ticker/

This returns some JSON text where you should be able to search and retrieve the id of the crypto you are after. Note that this retreieves all of the cryptocurrencies on CoinMarketCap.com.

Now Let’s say we are interested in the price of Bitcoin specfically. We can retreieve only the price for it from their API with:

https://api.coinmarketcap.com/v1/ticker/bitcoin/

That should give you a basic idea of how their API works. You hit up a url and depending on the option you pass into it you will get some data back from CoinMarketCap.com.

Time for the Code

Now let’s use JavaScript inside Google Sheets in order to create a sheet function that will use this API to pull the latest prices and help us value our Portfolio!

  • Go to Google Sheets and open a blank sheet
  • Then go to tools->Script editor
  • You should see a blank IDE (Integrated Development Environment or "place you write code")

Copy and paste the following function into the editor

function get_curr_coinmarketcap_price(crypto_id, unused_param) {

  // note that the unused_param is to to force the function to rerun
  //   and recalcuate the functions results when the function is called with 
  //   new input
  // https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet

  // create a string that is the first part of our API calls url
  var url1 = 'https://api.coinmarketcap.com/v1/ticker/';

  // append crypto_id to our url so we can tell the API that we want prices 
  //   only for that id
  var full_url = url1 + crypto_id;

  var response = UrlFetchApp.fetch(full_url);

  //return response.getContentText();
  // parse the JSON string and build it into JavaScript objects
  var data = JSON.parse(response.getContentText());

  // return the first (should be only) price from data
  // extract the value in the dictionary with a key of 'price_usd'
  return data[0]['price_usd'];
}

Be sure to walk through the comments in the code above to get a better understanding of what the code does. If you have any questions, just leave them in the comments section below and I will answer them.

  • Save the project/function in the Script Editor.

Now we need to ensure that the new Script you have written can access your Google Sheets data.

  • Click Run in the menu bar in the Script Editor and Run the function you just pasted.

  • Give the script permission to access this Sheet you have created once you see the pop up box.

Now you will probably get a warning like this but you can ignore it for now:

error

Add the new User Defined Function to the sheet

Let’s use the function to pull a Crypto Price!

  • So go back to the first tab in your Google Sheet and structure your cells to look something like this:

Imgur

We are now going to use our new function to get the current price for Bitcoin and then it will calculate the total value of our holdings.

  • To do that go into cell D3 and utilize the new function we created to populate the price by typing into that cell =get_curr_coinmarketcap_price(B3,1)

Note that we are passing the value in cell B3 as the first parameter of the function and we are just passing a 1 (for now) as the value of the second parameter to the function.

Imgur

Now we could just use that as it is but one important thing to note is that our data will only be refreshed whenever we open the google sheet bc of the google sheets execution model. Google sheets only recalculates formulas whenever the sheet loads or when new values are passed in as parameters of the functions.

This is why our function has an unused_param that we pass into it. We can utilize this behavior to make our functions refresh when we want by changing the value of unused_param!

note: For everyone wondering why I just dont pass in the current time to the function, Google Sheets actually doesn't allow this.

Add the ability to refresh

So, the first step to getting this setup it for you to make sure cell A1 (or some cell that you designate) is storing our random number.

Then you will need to add an image that we can use to make our page refresh. When we click on this image (Sheets does not support buttons), it will run a script that we "attach" to the button.

You can search Google images for a free image that you can use. I just wound up using this one: https://upload.wikimedia.org/wikipedia/commons/thumb/7/7d/Refresh_icon.svg/200px-Refresh_icon.svg.png

  • Copy the image into your Sheet:

Using the Insert image box:

  • Adjust the image to whatever size and position on the sheet that you want.

Adding the refresh_data function

Now let’s add our function that is going to execute whenever we click our button.

  • Go back to your script editor. Below the function we have already added, add the following code:
function refresh_data() {
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}

This function is fairly simple. Whenever this function is executed, range A1 of the active sheet gets set to a random value with the Math.random() function.

  • Back in your spreadsheet, now click on the three dots that show up in the upper right hand corner of the image whenever you mouse over it:

Imgur

  • Then select the option to attach a script.

Imgur

Now paste the name of our new function:

You may need to re-authorize your code to access your Google Sheet.

Adjust the User Defined Function

  • Finally, lets adjust the function in our worksheet so that its second parameter is not a 1 not but a reference to cell A1 of our spreadsheet:

Imgur

Done

And there you go!

Anytime you click on the refresh button, it changes the parameter being passed into our custom worksheet function. This new parameter value causes Google Sheets to recalculate the functions and the latest crypto prices are pulled into your spreadsheet from the CoinMarketCap API!


Affiliate Disclosure

Privacy Policy


Comments

comments powered by Disqus