How To Make A Cryptocurrency Portfolio Dashboard In 20 Minutes

 

Considering all the craze around cryptocurrencies happening all around us this year, our team has also decided to dip their toes into these crypto-waters and invest a little bit of money into the hype. We have discussed and searched around for apps to use for monitoring the performance of our portfolios and found a bunch. However, we thought that it could be a cool exercise and material for our new blog to try to build one ourselves.

In this blog, we will build a simple Google Sheet based portfolio monitoring tool. It is supposed to auto-update with new values on exchange rate changes.

 

Prerequisites

Before we start you will need a couple of things:

1. Setup your Google API account and get the keys necessary. A really cool tutorial on how to do this and also access Google sheets programmatically is available here.

2. Get yourself familiar with the Coinmarketcap website and their API as well.

3. Make a Google Sheet in this format. (Don't forget to share it with your email from API credentials as described in the tutorial above)

4. Fill out the Google Sheet with currency Id-s and Amounts that you (wish to) own.

 

 

Get your dependencies covered

These are the libraries we will need to make our portfolio app working:

import json
import gspread
import requests
from oauth2client.client import SignedJwtAssertionCredentials
from datetime import datetime

 

Connect to Google Sheet

Let's start by connecting to the Google API and by validating your credentials (you can check for details in the tutorial above):

json_key = json.load(open(credentials)) # json credentials you downloaded earlier

scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'],                                               (json_key['private_key']
                                            .encode()), 
                                            scope) # get email and key
                 
fl = gspread.authorize(credentials)

 

After you have connected to Google Sheets, you can retrieve the sheet that you have created:

 

sheet = '<name of your Google sheet>'
worksheet = '<name of worksheet within your sheet>'

#connect to sheet
sheet = (fl
         .open(sheet_name)) # open sheet
worksheet = sheet.worksheet(worksheet_name)

 

Get your values from google

Next up we need to get the ranges from our Google Sheet that we want to manipulate. First we will get the IDs of coins in our portfolio.

# get altcoin ids
ids = worksheet.range('A2:A100')
max_ind = 1
for i in ids:
    if i.value == '':
        break
    else:
        max_ind += 1

 

This right here is a bit hack-y and please don't kill me for it – but gspread does not provide a neat way of figuring out how many filled values you have in a column (or if it does please enlighten me in the comments, anyway I wouldn't fit into 20 minutes figuring it out ;-)). So what we do is pull a bunch of values and check for the first empty one to get the number of rows we are interested in.

After this we will pull the ranges in the columns we are going to be changing shortly:

# get fields to change
price_eur = worksheet.range('D2:D{}'.format(max_ind))
daily_change = worksheet.range('E2:E{}'.format(max_ind))
hour_change = worksheet.range('F2:F{}'.format(max_ind))

 

Pulling data from CoinMarketCap 

Now to the fun part. We will pull some recent stats for every altcoin in our portfolio and update the fields we have just pulled:

# update from Coinmarketcap
for i, values in enumerate(zip(ids, 
                               price_eur, 
                               daily_change, 
                               hour_change)):
    r = (requests
        .get('https://api.coinmarketcap.com/v1/ticker/{}/?convert=EUR'
             .format(values[0]
             .value)))

    if r.status_code == 200:
        js = r.json()[0]
        values[1].value = js.get('price_eur')
        values[2].value = js.get('percent_change_24h')
        values[3].value = js.get('percent_change_1h')
    else:
        values[1].value = 'NULL'
        values[2].value = 'NULL'
        values[3].value = 'NULL'

 

The last thing we have to do is to write (commit) the changes back to the sheet. If this reminds you of database sessions, it's because it is very similar.

 

# Update google sheet in batch
worksheet.update_cells(price_eur)
worksheet.update_cells(daily_change)
worksheet.update_cells(hour_change)
    
# Set timestamp
worksheet.update_acell('I2', 
                       datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S'))

 

We have also added one field with current timestamp, so you know when your portfolio was updated last.

 

Polishing

And we are done. You have a bare bones portfolio app at your disposal. To make it more usable in real life, you can do two more things:

1. Calculate amounts in USD/EUR in your Google Sheet. You can achieve this with a simple formula by multiplying columns C and D (G2=C2*D2).

2. Schedule a cronjob to refresh the portfolio automatically. You can do this on your local machine (UNIX) or on a server if you have one. You can find a cool tutorial on cronjobs for Mac here.

 

Final words

This little project shows is a nice demonstration of how to use proper tools to make things work together quickly and build something that actually works just to try it out.

We have combined the use of Google Sheets (basically 2st century Excel), APIs and Python to make our lives easier. And that's what Data Science is all about :-)