Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.
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')
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment