I got caught up in the cryptocurrency craze back in 2017; the new hype in 2021 has piqued my interest again. There are some great crypto applications out there to track your portfolio, however I wanted to create my own.

‍_

Using Python and AWS Lambda I'm refreshing a Google Data Studio dashboard via Google Sheets with only a few lines of code.

See my other post on scraping my local bike shops eCommerce page for an in-depth look at deployment via AWS Lambda, this article will mainly focus on Google authentication, sheets, data studio and the cryptocompare package.

Create a Google Cloud Project

• If you don't already have a Google Cloud account, login with your Gmail to get started
• Create a New Project or use the default project created for you
• Navigate to API's & Services
• Enable the Google Drive's API service associated with the Gmail you are logged into
• Search for Google Drive and Enable Service

Create a Service Account

• APIs & Services -> Credentials -> Create Credentials -> Service Account
• Name fields however you like, create and SAVE the JSON file
• This JSON file is your authentication file and should be placed in the root folder of your Python file
• Take note of the iam.gserviceaccount.com email - it should also be present in your JSON file ‍

Create a Google Sheet

• Create a blank Google Sheet
• Share the sheet with the service account gmail created in the previous step ‍

Query Crypto Prices - cryptocompare

• pip install cryptocompare
• Plug in a list of coins into a list
• Optional: I have a few obscure coins and not all are available via USDT direct trade, as such I created a second list based on the currency to get historical data only. The get_avg function asks to specify an exchange - this is why you may need a currency specification based on your coins.
• Loop over all coins and call get_price and get_avg
• Append this data to empty lists created before the for loop

coin_list = [
        'BTC', 'XRP', 'NANO', 'OMG', 'ARK', 'SC', 'XRP', 'GNT', 'NEO', 'MIOTA',
        'REQ'
    ]
    currency_list = [
        'USDT', 'USDT', 'USDT', 'USDT', 'BTC', 'BTC', 'BTC', 'BTC', 'BTC',
        'BTC', 'BTC'
    ]
    coin_price_dict = {}
    coin_price = []
    coin_change = []


    for coin, curr in zip(coin_list, currency_list):

        price = '${0:,.2f}'.format(
            cryptocompare.get_price(coin, 'USD')[coin]['USD'])
        hist_price = '{0:,.2f}%'.format(
            cryptocompare.get_avg(coin, currency=curr,
                                  exchange='Binance')['CHANGEPCT24HOUR'])

        coin_price_dict[coin] = [price, hist_price]
        coin_price.append(price)
        coin_change.append(hist_price)

Push Dataframe to Google Sheets

• pip install pygsheets
• Authenticate via secrets.json file (created from service account in previous step) add the columns to the blank dataframe; these columns were built within the for loop in the previous step
• Open sheet via pygsheets, select the first sheet and set the dataframe to begin on the first row and column

    #authorization
    gc = pygsheets.authorize(service_file='secrets.json')

    # Create empty dataframe
    df = pd.DataFrame()

    # Create a column
    df['Coin Name'] = coin_list
    df['Price'] = coin_price
    df['24hr Change'] = coin_change

    #open the google spreadsheet (where 'PY to Gsheet Test' is the name of my sheet)
    sh = gc.open('Crypto Dash')

    #select the first sheet
    wks = sh[0]

    #update the first sheet with df, starting at cell B2.
    wks.set_dataframe(df, (1, 1))

Google Data Studio Dashboard

• Copy this Google Data Studio (GDS) template or create your own!
• Replace the data source with the Google Sheet you created

Deploy Code in AWS Lambda

• Package all dependencies and code into a zip file
• Create a Lambda function and set your desired triggers Important note about pandas and numpy for Lambda - there are a few commons errors that Lambda encounters when using pandas/numpy packages. If you have any errors related to these packages I suggest manually downloading pandas/numpy from pypi.org and plopping them into your zip file. You should download --your python version--manylinux--x86 -- 64, version of the package.
• See my other blog post for a detailed explanation of Lambda functions