Track Shitcoin Pricing with Google Sheets

Asset management is always important and interesting. My portfolio includes stocks, bonds, and commodities as a classic method. Recently I also added shitcoins as well.

I appreciate Google Sheets, which brings me helpful functions to track real time pricing. GOOGLEFINANCE function is pretty useful to get the price of ETFs and Bitcoins but shitcoins are not available. I’d like to introduce my Google App Script (GAS) function to track shitcoin pricing.

Utilize CoinMarketCap API

Many blog websites introduces IMPORTXML function to get the current price of cryptocurrency. However I noticed IMPORTXML function against particular websites needs periodic refresh, which is not that easy to achieve.

CoinMarketCap is one of the largest website that organize cryptocurrency market data. Fortunately they provide us free API and using the API would be better & smarter solution than IMPORTXML.

Access https://coinmarketcap.com/api/ to get the API key.

Click the blue button at the center and choose Basic plan, which is free. Once create your account, you would see account page like shown below.

Mouse over the area written as “API Key”, which is on the left next to the circle graphs, then you can copy your API key.

GAS GAS GAS

Open a spreadsheet and select the menu item Tools -> Script editor.

function crypto(ticker) {
  
}

Created the function called “crypto” which takes ticker of cryptocurrency as an argument and return current price as USD. Let’s write the request URL and its parameter.

function crypto(ticker) {

  var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + ticker;
  var requestOptions = {
    method: 'GET',
    uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
    qs: {
      start: 1,
      limit: 5000,
      convert: 'USD'
    },
    headers: {
      'X-CMC_PRO_API_KEY': 'Your_API_key_is_here'
    },
    json: true,
    gzip: true
  };
  var result = UrlFetchApp.fetch(url, requestOptions);

}

You might want to refer the official API document https://coinmarketcap.com/api/documentation/v1/ to know detail.

Line 3, the variable “url” has the request URL and concatenate the variable “ticker” at last.

From line 4, the variable “requestOptions” includes methods and options.

Line 13, replace Your_API_key_is_here with your acquired API key.

Line 18, UrlFetchApp function sends the request and the returned value are stored in the variable “result”.

If your requesting is successful, the JSON response from API server is something like this.

{
  "status":{
    "timestamp":"2020-09-11T08:18:53.692Z",
    "error_code":0,
    "error_message":null,
    "elapsed":17,
    "credit_count":1,
    "notice":null
  },
  "data":{
    "BTC":{
      "id":1,
      "name":"Bitcoin",
      "symbol":"BTC",
      "slug":"bitcoin",
      "num_market_pairs":9086,
      "date_added":"2013-04-28T00:00:00.000Z",
      "tags":[
        "mineable",
        "pow",
        "sha-256",
        "store-of-value",
        "state-channels"
      ],
      "max_supply":21000000,
      "circulating_supply":18485787,
      "total_supply":18485787,
      "is_active":1,
      "platform":null,
      "cmc_rank":1,
      "is_fiat":0,
      "last_updated":"2020-09-11T08:17:32.000Z",
      "quote":{
        "USD":{
          "price":10203.3238165,
          "volume_24h":55229657182.8796,
          "percent_change_1h":-0.689671,
          "percent_change_24h":-1.10781,
          "percent_change_7d":-1.84363,
          "market_cap":188616470763.8461,
          "last_updated":"2020-09-11T08:17:32.000Z"
        }
      }
    }
  }
}

Response information against a request is structured in “data” start from line 10. It has various information, but here what we want is “price” at line 35.

Finally, let’s parse the JSON stored in the variable “result” and get the price. Below shows the completed code.

function crypto(ticker) {

  var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + ticker;
  var requestOptions = {
    method: 'GET',
    uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
    qs: {
      start: 1,
      limit: 5000,
      convert: 'USD'
    },
    headers: {
      'X-CMC_PRO_API_KEY': 'Your_API_key_is_here'
    },
    json: true,
    gzip: true
  };
  var result = UrlFetchApp.fetch(url, requestOptions);

  var txt = result.getContentText(); // JSON as a text
  var jsonData = JSON.parse(txt);
  var path = "jsonData.data." + ticker + ".quote.USD.price"; // path to the current price
  var price = eval(path);

  return price;

}

Line 20, the variable “txt” stores the JSON as a text.

Line 21, the variable “jsonData” get the parsed data of the JSON by using JSON.parse().

Line 22, the variable “path” stores a path to the “price” as a text.

Line 23, evaluate the path and return price as USD.

Check if it works

Let me try if crypto function works fine. Here I used “IOTX”, which I actually bought.

Looks good.

crypto is simple function that returns the current price of shitcoins in USD. If you prefer more sophisticated function, changing query and parsing different attribute from JSON is one option.

Summary

In this article, I introduced my GAS function that track shitcoins pricing. The function crypto is simple enough, take a ticker as an input and return the price.

Creating original functions in Google Sheets is often practical and helpful. I hope this article brings you some benefit for your shitcoin management.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です