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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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