Automates the import of Coinbase transactions into a Google Sheet for cost basis tracking.

coinbase_advanced_trade_sheet.py

import json
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime, timezone
from coinbase_advanced_trader import coinbase_client
from coinbase_advanced_trader.config import set_api_credentials

# Link to Base Spreadsheet - https://docs.google.com/spreadsheets/d/1VYuy5cSnZiQqF4yp6_sVFpLXXXszCGEiMh-Z37mKims/edit?usp=sharing
COINBASE_PUBLIC_KEY = ''
COINBASE_PRIVATE_KEY = ''
COINBASE_SYMBOLS = ["BTC-USD", "ETH-USD"]
GOOGLE_SHEET_FILE_NAME = "The New Definitive Crypto Sheet"
AUDIT_FILE_SHEET_NAME = "Audit File"
SHEETS_CREDS_FILE_NAME = 'sheets_creds.json'

set_api_credentials(COINBASE_PUBLIC_KEY, COINBASE_PRIVATE_KEY)


def authenticate_spreadsheet():
    """Authenticate and access the Google 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 add_coinbase_transaction(transaction, id):
    """Format a Coinbase transaction for the audit file."""
    transaction_date = str(transaction['created_time'][0:10])
    exchange = "Coinbase"
    transaction_id = id
    symbol = transaction['product_id'].replace('-', '')
    side = transaction['side'].upper()
    amount = float(transaction['filled_size'])
    price = float(transaction['average_filled_price'])
    fee = float(transaction['total_fees'])
    sell_side_amount = float(transaction['filled_value'])
    return [transaction_date, exchange, transaction_id, side, symbol, amount, price, sell_side_amount, fee]


def populate_coinbase(audit_file, symbol):
    """Populate the audit file with Coinbase transactions for a given symbol."""
    sheet_transactions = audit_file.get_all_records()
    coinbase_transactions = [
        t for t in sheet_transactions if t['Exchange'] == 'Coinbase']
    symbol_transactions = [
        t for t in coinbase_transactions if t['Symbol'] == symbol.replace('-', '')]

    sheet_transaction_ids = [t['Transaction ID'] for t in symbol_transactions]

    all_orders = coinbase_client.listOrders(product_id=symbol)['orders']
    valid_orders = [
        order for order in all_orders if order['status'] != 'CANCELLED']

    for order in reversed(valid_orders):
        timestamp_str = order['created_time']
        if timestamp_str:
            timestamp_str = timestamp_str[:23] + 'Z'
            created_time = int(datetime.strptime(
                timestamp_str, '%Y-%m-%dT%H:%M:%S.%fZ').replace(tzinfo=timezone.utc).timestamp() * 10000)

            if created_time not in sheet_transaction_ids:
                audit_file.append_row(add_coinbase_transaction(
                    order, created_time), value_input_option="USER_ENTERED")


for symbol in COINBASE_SYMBOLS:
    populate_coinbase(authenticate_spreadsheet(), symbol)

#To get rid of the lambda_handler error in AWS
#def lambda_handler(event, context):
#    for symbol in COINBASE_SYMBOLS:
#        populate_coinbase(authenticate_spreadsheet(), symbol)

#    return {
#        'statusCode': 200,
#        'body': json.dumps('End of script')
#    }

This Python script, coinbase_audit.py, is a tool designed to help you track your cost basis by automatically importing your Coinbase Advanced Trade transactions into a Google Sheet.

The script uses the Coinbase Advanced Trader API to fetch transaction data, including details such as transaction date, exchange, transaction ID, side, symbol, amount, price, sell side amount, and fee. It then formats this data and appends it to a specified Google Sheet using the Google Sheets API.

The script is configurable, allowing you to set your Coinbase API keys and specify the Google Sheet file and worksheet names. It also includes error handling to ensure that only valid transactions are imported.

This script is a useful tool for anyone trading on Coinbase who wants to maintain a detailed record of their transactions in a format that is easy to view and manipulate for further analysis. Please note that this script is not financial or tax advice, and should be used as a tool to assist in tracking and maintaining your own records.