Crypto Coin Tracker

Login
Crypto

CoinGecko USD Coin Tracker Template

Colin Dellow

Colin Dellow

Example Coin Tracker with Coin Gecko

Using the CoinGecko USD Coin Tracker template we're able to :
  • Access long term coin trend data from Coin Gecko - 7 day, 30 day, 60 day, 200 day and 1 year price changes for crypto coins
  • Access real-time pricing data from Coin Gecko
  • The template automatically pulls this data for the top 250 coins by market cap and gives you a slick Google Sheets interface to visuallize the data from the Coin Gecko API
  • The template is setup to pull the long term historical data every day and pull the pricing data every hour.

Resulting Spreadsheet

Here's a live view of a sample spreadsheet created using the template:

Get the Spreadsheet

SyncWith User

  1. Open the add on Sidebar
  1. Search for CoinGecko
  1. Select CoinGecko
  1. Select CoinGecko USD Coin Tracke

Not a SyncWith User

  1. Get the template
  1. Click use template
      2. notion image
  1. Install SyncWith, Click Add to Sheets
      2. notion image
  1. Click Domain or Individual Install
      2. notion image
  1. Accept Permissions
      2. notion image
  1. Click Accept to Google Permissions
  1. Click Use > Manage connections
notion image
 

Using the Spreadsheet

  1. Select a coin from the dropdown (spreadsheet will take up to 15 coins)
  1. That's it, the data auto populates
  1. You can refresh the connections manually if you want to get real-time price information from the CoinGecko API otherwise the pricing will refresh hourly

Spreadsheet Design

If you're interested in changing the functionality of the spreadsheet it's helpful to understand how it's been built, here's a high level snapshort of the spreadsheet:
notion image

Connections Used

Historic Data

  • The only request parameter is per_page = 250 which limits the response to 250 coins
  • This endpoint defaults to an ordering by market cap descending
  • The /api/v3/coins endpoint has a giant response with all the data in all the currencies so most of the columns are removed in the JSON transform - to learn more about SyncWith's ability to do JSON transforms we have a tutorial on JSON transforms in preview mode.
  • This connection is getting more data than we're showing and this could allow you to display more in the future
notion image

Hourly Price Data

  • We have 3 request parameters:
    • vs_currency = usd - so that all the data coming back is in us dollars
    • sparkline = true - so that we can get back a comma separated set of data to display the 7d sparkline graphs
    • per_page = 250 - similar to the historic data connection this limits the response to 250 coins which are ranked by market cap
  • The JSON passed back is fairly straightforward and there is a basic transform that removes a few of the columns as our main goal here is just to get a real time price in USD
notion image