I built this script to populate trades made in any trading pair available on Coinbase Pro or Gemini into a Google Sheet so it would be easier to track trades, profitability, and cost basis. This could be extended to other exchanges. See [video](https://www.youtube.com/watch?v=hutDJ-FVatw) for full explanation.

crypto-gsheet-populator.py

import json
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import gemini
import cbpro
import time
import sys
import json
import base64
import hashlib
import hmac
import urllib.request
import requests
import datetime
import urllib.parse
import ftx

#Link to Base Spreadsheet - https://docs.google.com/spreadsheets/d/1VYuy5cSnZiQqF4yp6_sVFpLXXXszCGEiMh-Z37mKims/edit?usp=sharing
#Enter Gemini Keys to Link Gemini Account

gemini_public_key = ''  
gemini_private_key = ''
gemini_symbols = ["BTCUSD","ETHUSD"]
gemini_creds = [gemini_public_key,gemini_private_key]

#Enter Coinbase Pro Keys to Link Coinbase Pro Account
cbpro_passphrase = ''
cbpro_secret = ''
cbpro_key = ''
cbpro_symbols = ["XLM-USD","XTZ-USD","ETH-USD", "BTC-USD", "LINK-USD", "ADA-USD"]
cbpro_creds = [cbpro_key, cbpro_secret, cbpro_passphrase]

# Configure API key (copy/paste from account management)
kraken_api_url = "https://api.kraken.com"
kraken_key_public = ''
kraken_key_private = ''
kraken_symbols = ["XXBTZUSD", "ETHUSD", "ADAUSD", "LINKUSD", "RENUSD", ]

# Configure FTX US API key (copy/paste from account management)
ftx_us_public_key = ''
ftx_us_private_key = ''
ftx_us_symbols = ["BTC/USD", "ETH/USD"]
ftx_us_creds = [ftx_us_public_key,ftx_us_private_key]

#The name of your google sheet file
google_sheet_file_name = "The New Definitive Crypto Sheet"
#The name of the audit file sheet that you want to import transactions into
audit_file_sheet_name = "Audit File"
#Google Sheets Credentials File Name
sheets_creds_file_name ='sheets_creds.json'

#TODO:
#Implement Binance, OKCoin, etc

def _authenticateSpreadsheet():
    #Set up access to the spreadsheet
    scope = ["https://spreadsheets.google.com/feeds",
            'https://www.googleapis.com/auth/spreadsheets',
            "https://www.googleapis.com/auth/drive.file",
            "https://www.googleapis.com/auth/drive"]
    creds = ServiceAccountCredentials.from_json_keyfile_name(sheets_creds_file_name, scope)
    client = gspread.authorize(creds)

    return client.open(google_sheet_file_name).worksheet(audit_file_sheet_name)

def _addGeminiTransaction(transaction):
    #populate transaction details
    transaction_date = str(datetime.datetime.fromtimestamp(transaction['timestamp']).date())
    transaction_id = float(transaction['tid'])
    exchange = "Gemini"
    symbol = transaction['symbol']
    side = transaction['type'].capitalize()
    amount = float(transaction['amount'])
    price = float(transaction['price'])
    fee = float(transaction['fee_amount'])
    sell_side_amount = amount * price + fee
    #populate with new row
    return [transaction_date, exchange, transaction_id, side, symbol, amount, price, sell_side_amount, fee]

def populateGemini(audit_file, symbol):
    #Establish connection to Gemini
    trader = gemini.PrivateClient(gemini_creds[0], gemini_creds[1])
    #Need to run these filters to make sure that you're only bringing over Gemini transactions that have a Transaction ID that is later than the last Transaction ID for a given symbol
    last_gemini_transaction = (list(filter(lambda filterExchange: filterExchange['Exchange'] == 'Gemini', audit_file.get_all_records())))
    last_symbol_transaction = (list(filter(lambda filterSymbol: filterSymbol['Symbol'] == symbol, last_gemini_transaction)))
    if(last_symbol_transaction):
        #pull transactions from Gemini
        transactions = trader.get_past_trades(symbol)[::-1]
        for transaction in transactions:
            #If the transactions from Gemini are after your most recent Transaction for a given symbol in the sheet - add the transaction to the sheet 
            if(transaction['tid'] > last_symbol_transaction[-1]['Transaction ID']):
                audit_file.append_row(_addGeminiTransaction(transaction), value_input_option="USER_ENTERED")
                print('added row')

def _addCBProTransaction(transaction):
    #populate transaction details
    transaction_date = str(transaction['created_at'][0:10])
    exchange = "Coinbase Pro"
    transaction_id = transaction['trade_id']
    symbol = transaction['product_id'].replace('-','')
    side = transaction['side'].capitalize()
    amount = float(transaction['size'])
    price = float(transaction['price'])
    fee = float(transaction['fee'])
    sell_side_amount = float(transaction['usd_volume']) + float(fee)
    #populate with new row
    return [transaction_date, exchange, transaction_id, side, symbol, amount, price, sell_side_amount, fee]

def populateCBPro(audit_file, symbol):
    auth_client = cbpro.AuthenticatedClient(cbpro_creds[0], cbpro_creds[1], cbpro_creds[2])
    #Need to run these filters to make sure that you're only bringing over Coinbase Pro transactions that have a Transaction ID that is later than the last Transaction ID for a given symbol
    last_cbpro_transaction = (list(filter(lambda filterExchange: filterExchange['Exchange'] == 'Coinbase Pro', audit_file.get_all_records())))
    last_symbol_transaction = (list(filter(lambda filterSymbol: filterSymbol['Symbol'] == symbol.replace('-',''), last_cbpro_transaction)))
    #print(f"Last Symbol Transaction: {last_symbol_transaction}")
    print(f"Symbol: {symbol}")
    #pull transactions from Coinbase Pro
    transactions = list(auth_client.get_fills(product_id=symbol))
    for transaction in transactions[::-1]:
        print(f"Transaction Trade ID: {transaction['trade_id']}")
        print(f"Last Symbol Transaction: {last_symbol_transaction[-1]['Transaction ID']}")
        #If the transactions from Coinbase Pro are after your most recent Transaction for a given symbol in the sheet - add the transaction to the sheet 
        if transaction['trade_id'] > last_symbol_transaction[-1]['Transaction ID']:
            audit_file.append_row(_addCBProTransaction(transaction), value_input_option="USER_ENTERED")

def _addKrakenTransaction(transaction):
    #populate transaction details
    transaction_date = str(datetime.datetime.fromtimestamp(transaction['time']).date())
    exchange = 'Kraken'
    transaction_id = transaction['ordertxid']
    side = transaction['type'].capitalize()
    pair = transaction['pair']
    amount = transaction['vol']
    price = transaction['price']
    fee = transaction['fee']
    sell_side_amount = transaction['cost']
    kraken_time = transaction['time']
    #Kraken trades BTCUSD as XXBTZUSD - fixing for spreadsheet display purposes
    if pair == "XXBTZUSD":
        pair = "BTCUSD" 
    return [transaction_date, exchange, transaction_id, side, pair, amount, price, sell_side_amount, fee, kraken_time]

def getKrakenTradeHistory():
    try:
        api_path = '/0/private/TradesHistory'
        api_nonce = str(int(time.time()*1000))
        api_trades = True
        api_post = f'nonce={api_nonce}&trades={api_trades}' % {'api_nonce':api_nonce, 'trades':api_trades}
        api_sha256 = hashlib.sha256(api_nonce.encode('utf8') + api_post.encode('utf8'))
        api_hmac = hmac.new(base64.b64decode(kraken_key_private), api_path.encode('utf8') + api_sha256.digest(), hashlib.sha512)
        api_signature = base64.b64encode(api_hmac.digest())
        
        api_request = urllib.request.Request('https://api.kraken.com/'+api_path, api_post.encode('utf8'))
        api_request.add_header('API-Key', kraken_key_public)
        api_request.add_header('API-Sign', api_signature)
        api_request.add_header('User-Agent', 'Kraken trading bot example')
        api_response = urllib.request.urlopen(api_request).read().decode()
        api_data = json.loads(api_response)
    except Exception as error:
        print('Failed (%s)' % error)
    else:
        #print('Done (%s)' % api_response if len(api_data['error']) == 0 else 'Error (%s)' % api_data['error'])
        #print(api_data)
        return api_data['result']['trades']

def populateKraken(audit_file, symbol):
    transactions = (list(filter(lambda filterPair: filterPair['pair'] == symbol, getKrakenTradeHistory().values())))
    #Kraken trades BTCUSD as XXBTZUSD - fixing for logical comparisons in spreadsheet
    if symbol == "XXBTZUSD":
        symbol = "BTCUSD" 
    #Need to run these filters to make sure that you're only bringing over Coinbase Pro transactions that have a Transaction ID that is later than the last Transaction ID for a given symbol
    last_kraken_transaction = (list(filter(lambda filterExchange: filterExchange['Exchange'] == 'Kraken', audit_file.get_all_records())))
    last_symbol_transaction = (list(filter(lambda filterSymbol: filterSymbol['Symbol'] == symbol, last_kraken_transaction)))

    for transaction in transactions[::-1]:
        if(int(transaction['time']) > int(last_symbol_transaction[-1]['Kraken_Time'])):
            audit_file.append_row(_addKrakenTransaction(transaction), value_input_option="USER_ENTERED")

def _addFTXUSTransaction(transaction):
    #populate transaction details
    transaction_date = transaction['time'].split('T')[0]
    exchange = "FTX US"
    transaction_id = transaction['id']
    side = transaction['side'].capitalize()
    symbol = transaction['market'].replace('/','')
    amount = float(transaction['size'])
    price = float(transaction['price'])
    fee = float(transaction['fee']) * float(transaction['price'])
    sell_side_amount = amount * price + fee
    #populate with new row
    return [transaction_date, exchange, transaction_id, side, symbol, amount, price, sell_side_amount, fee]

def populateFTXUS(audit_file, symbol):
    #Establish connection to FTX US
    trader = ftx.FtxClient(api_key=ftx_us_creds[0], api_secret=ftx_us_creds[1])
    #Need to run these filters to make sure that you're only bringing over FTX US transactions that have a Transaction ID that is later than the last Transaction ID for a given symbol
    last_ftx_us_transaction = (list(filter(lambda filterExchange: filterExchange['Exchange'] == 'FTX US', audit_file.get_all_records())))
    last_symbol_transaction = (list(filter(lambda filterSymbol: filterSymbol['Symbol'] == symbol.replace('/',''), last_ftx_us_transaction)))
    if(last_symbol_transaction):
        #pull transactions from FTX US
        transactions = (list(filter(lambda filterSymbol: filterSymbol['market'] == symbol, trader.get_fills())))[::-1]
        print(f"Transactions: {transactions}")
        for transaction in transactions:
            #If the transactions from FTX US are after your most recent Transaction for a given symbol in the sheet - add the transaction to the sheet 
            if(transaction['id'] > last_symbol_transaction[-1]['Transaction ID']):
                audit_file.append_row(_addFTXUSTransaction(transaction), value_input_option="USER_ENTERED")
                print('added row')


def lambda_handler(event, context):
    for symbol in cbpro_symbols:
        populateCBPro(_authenticateSpreadsheet(), symbol)

    for symbol in gemini_symbols:
        populateGemini(_authenticateSpreadsheet(), symbol)    
        
    for symbol in kraken_symbols:
        populateKraken(_authenticateSpreadsheet(), symbol)
        
    for symbol in ftx_us_symbols:
        populateFTXUS(_authenticateSpreadsheet(), symbol)    
    return {
        'statusCode': 200,
        'body': json.dumps('End of script')
    }