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 jsonimport gspreadimport requestsfrom oauth2client.client import SignedJwtAssertionCredentialsfrom 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 earlierscope = ['https://spreadsheets.google.com/feeds']credentials = SignedJwtAssertionCredentials(json_key['client_email'], (json_key['private_key'].encode()),scope) # get email and keyfl = 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 sheetsheet = (fl.open(sheet_name)) # open sheetworksheet = 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 idsids = worksheet.range('A2:A100')max_ind = 1for i in ids:if i.value == '':breakelse: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 changeprice_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 Coinmarketcapfor 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 batchworksheet.update_cells(price_eur)worksheet.update_cells(daily_change)worksheet.update_cells(hour_change)# Set timestampworksheet.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 :-)