Import coinbase pro transactions to hledger with capital gains using average cost basis method

in2csv.py

import sys
import csv
import pprint
import datetime as dt
import json
import pathlib
pp = pprint.PrettyPrinter(indent=4)

CBP_PATH = pathlib.Path(__file__).parent.resolve()

def sort_subtxs(subtx):
    operation = subtx['Operation']
    if operation == "Buy" or operation == "Sell":
        return 0
    elif operation == "Transaction Related":
        return 1
    elif operation == "Fee":
        return 2

    return 0

def calculate_amount(subtxs):
    amount = abs(float(subtxs[0]['amount']))
    unit = subtxs[0]['amount/balance unit']
    return amount, unit

def calculate_to_amount(subtxs):
    is_conversion = len(subtxs) > 1
    if not is_conversion: return '',''
    to_amount = abs(float(subtxs[1]['amount']))
    to_unit = subtxs[1]['amount/balance unit']
    return to_amount, to_unit

def calculate_cost_basis(inventory, pair):
    unit, to_unit = pair

    if not inventory:
        return 0, to_unit

    return inventory[unit] / inventory[to_unit], unit

def calculate_fee(subtxs):
    has_fee = len(subtxs) > 2
    if not has_fee: return '0','0'
    fee_amount = str(abs(float(subtxs[2]['amount'])))
    fee_unit = subtxs[2]['amount/balance unit']
    return fee_amount, fee_unit

def load_inventory_from_file(year):
    inventory = dict()

    try: 
        with open(f'{CBP_PATH}/{year}-inventory.json') as jsonfile:
            inventory = json.load(jsonfile)
    except FileNotFoundError:
        pass

    return inventory

def save_inventory_to_file(inventory, year):
    with open(f'{CBP_PATH}/{year}-inventory.json', 'w') as jsonfile:
        json.dump(inventory, jsonfile, indent=4)

def get_type(type, unit):
    if type == 'match': 
        if unit == 'USDC':
            return 'buy'
        else:
            return 'sell'

    return type

def get_pair(type, unit, to_unit):
    pair_set = ['USDC','']
    pair = ''

    if type == 'buy': 
        pair_set = [unit, to_unit]
        pair = f'{unit}-{to_unit}'
    elif type == 'sell':
        pair_set = [to_unit, unit]
        pair = f'{to_unit}-{unit}'
    elif type == 'withdrawal':
        pair_set = ['USDC', unit]
        pair = f'USDC-{unit}'

    return pair_set, pair

def update_inventory(inventory, type, amount, unit, to_amount, to_unit):
    # Eg. ["USDC", "ETH"], "USDC-ETH"
    pair_set, pair = get_pair(type, unit, to_unit)
    cost_basis, cost_basis_unit = calculate_cost_basis(inventory.get(pair), pair_set)

    if not type in ['buy', 'sell', 'withdrawal']: return pair_set

    if to_amount:
        if type == 'buy':
            if inventory.get(pair):
                inventory[pair][unit] += amount
                inventory[pair][to_unit] += to_amount
            else:
                inventory[pair] = { unit: amount, to_unit: to_amount }

            inventory[pair]['prev_cost_basis'] = inventory[pair].get('latest_cost_basis')
            inventory[pair]['latest_cost_basis'] = calculate_cost_basis(inventory.get(pair), pair_set)
        elif type == 'sell':
            # https://en.wikipedia.org/wiki/Average_cost_method
            # commodities available for sale is deducted by the amount
            # of commodities sold, and the cost of current inventory is
            # deducted by the amount of commodities sold times the
            # latest (before this sale) current cost per unit on goods.
            inventory[pair][to_unit] -= amount * cost_basis
            inventory[pair][unit] -= amount

    if type == 'withdrawal' and inventory.get(pair):
        # restoring previous cost basis
        # print(inventory[pair][unit], amount, cost_basis)
        cost_basis, _ = inventory[pair]['prev_cost_basis']
        inventory[pair]['latest_cost_basis'] = inventory[pair]['prev_cost_basis']
        inventory[pair]['USDC'] = (inventory[pair][unit] - amount) * cost_basis
        inventory[pair][unit] -= amount

    # if 'ETH' in pair and (inv := inventory.get(pair)):
    #     print(type, inv)

    return inventory.get(pair)['latest_cost_basis'] or [0, '']

def join_subtxs(path_in, path_out):
    with open(path_in) as f_in, open(path_out, 'w') as f_out:
        subtxs_by_timestamp = dict()
        for subtx in csv.DictReader(f_in):
            if (subtxs_by_timestamp.get(subtx['time'])):
                subtxs_by_timestamp[subtx['time']].append(subtx)
            else:
                subtxs_by_timestamp[subtx['time']] = [subtx]

        f_out.write('portfolio,type,time,from_amount,from_unit,to amount,to unit,cost_basis,cost_basis_unit,fee,fee unit,transfer id,trade id,order id\n')
        items = subtxs_by_timestamp.items()
        year = dt.datetime.strptime(list(items)[0][0], '%Y-%m-%dT%H:%M:%S.%fZ').year
        inventory = load_inventory_from_file(year - 1)
        for time, subtxs in items:
            if not year:
                year = dt.datetime.strptime(time, '%Y-%m-%dT%H:%M:%S.%fZ').year

            portfolio = subtxs[0]['portfolio']
            amount, unit = calculate_amount(subtxs)
            to_amount, to_unit = calculate_to_amount(subtxs)
            fee_amount, fee_unit = calculate_fee(subtxs)
            transfer_id = subtxs[0]['transfer id']
            trade_id = subtxs[0]['trade id']
            order_id = subtxs[0]['order id']
            type = get_type(subtxs[0]['type'], unit)
            if ((type == "withdrawal" or type == "deposit") and not transfer_id): continue

            cost_basis, cost_basis_unit = update_inventory(inventory, type, amount, unit, to_amount, to_unit)
            f_out.write(f'{portfolio},{type},{time},{amount},{unit},{to_amount},{to_unit},{cost_basis},{cost_basis_unit},{fee_amount},{fee_unit},{transfer_id},{trade_id},{order_id}\n')

        save_inventory_to_file(inventory, year)

join_subtxs(sys.argv[1], sys.argv[2])

pro.rules

# skip the headings line:
skip 1

fields date,type,coin,amount,,,,,,description
fields portfolio,type,date,from_amount,from_coin,to_amount,to_coin,cost_basis,cost_basis_unit,fee,fee_coin,transfer_id,trade_id,order_id

date-format %Y-%m-%dT%H:%M:%S%QZ

currency
amount %from_amount %from_coin
description %type %transfer_id %trade_id %order_id

account1 assets:crypto:coinbase:pro:%from_coin
account2 expenses:coinbase:unknown

if %type
buy
  amount %to_amount %to_coin @@ %from_amount %from_coin 
  account1 assets:crypto:coinbase:pro:%to_coin
  amount2 %fee %fee_coin
  account2 expenses:finance:fees:coinbase:pro
  account3 assets:crypto:coinbase:pro:%from_coin
  description Buy %to_amount %to_coin with %from_amount %from_coin

if %type
sell
  amount -%from_amount %from_coin @ %cost_basis %cost_basis_unit
  account1 assets:crypto:coinbase:pro:%from_coin
  amount2 %fee %fee_coin
  account2 expenses:finance:fees:coinbase:pro
  amount3 -%fee %fee_coin
  account3 assets:crypto:coinbase:pro:%fee_coin
  amount4 %to_amount %to_coin
  account4 assets:crypto:coinbase:pro:%to_coin
  account5 income:investment:coinbase
  description Sell %from_amount %from_coin as %to_amount %to_coin

if %type
deposit
  skip 1
  account2 equity:transfers

if %type
withdrawal
  amount -%from_amount %from_coin
  account2 equity:transfers
  description Withdrawal %from_amount %from_coin