Python script to parse Fidelity trade history and convert to Beancount ledger format

fid_trade_hist_to_bean.py

"""
Convert Fidelity trade history HTML tables to beancount transactions

There are two formats: 1) open positions, revealed when you click on
a ticker in the Positions view; and 2) closed positions, which are
shown when you click on the "Closed Positions" link from the same
view.

The open positions only contain buys for the current holding so if you
had partial sells, those won't be shown (but should be in the closed
positions view). The closed positions show each buy lot along with
the attributed sell for that lot. Since often there are multiple sells
for the same date, we accumulate them by date and output just one
sell transaction in those cases.

This script is called by the "Fid Trade Hist to Bean" Shortcut which
pases the selected text from the table into this script as stdin. The
ticker, account owner and account type are not visible in the trade
table and won't be part of the selection so I prompt for those in the
shortcut and pass them in as arguments.

The resulting Beancount transactions are written to stdout and the
shortcut then sends that to the clipboard so I can paste it into
a ledger file.
"""
import re
import os
import sys
import datetime
import argparse
from collections import defaultdict
from typing import List

TRACE_FILE = os.path.join(os.path.split(__file__)[0], '_fid_trace_.txt')

BUY_TEMPLATE = """\
{date} * "Buy {ticker}"
  Assets:Fidelity:{owner}-{type}:Cash             -{cost:.2f} USD
  Assets:Fidelity:{owner}-{type}:{ticker}:Shares      {quantity:.4f} {ticker} {{{price:.4f} USD}}
"""
SELL_TEMPLATE = """\
{date} * "Sell {ticker}"
  Assets:Fidelity:{owner}-{type}:Cash             {proceeds:.2f} USD
  Assets:Fidelity:{owner}-{type}:{ticker}:Shares      -{quantity:.4f} {ticker} {{}}
  Income:CapitalGain
"""

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("ticker", help="Trade ticker")
    parser.add_argument("owner", help="Account owner")
    parser.add_argument("type", help="Account type")
    parser.add_argument('-s', '--save', help="Save input to _fid_trace.txt", action='store_true')
    parser.add_argument('-r', '--replay', help="Replay last trace results and print ouput", action='store_true')

    args = parser.parse_args()

    fin = open(TRACE_FILE) if args.replay else sys.stdin
    lines = fin.readlines()
    if args.save:
        with open(TRACE_FILE, 'w') as f:
            f.write(''.join(lines))

    trades = create_trades(lines)
    print_trades(trades, args.ticker, args.owner, args.type)

def create_trades(lines: List[str]):
    trades = defaultdict(list)
    for line in reversed(lines):
        # this is the pattern for the open trade history
        # capture the date, quantity and total cost only. we'll derive the cost per share to get more precision
        m = re.match(r'^(\d{4}-\d{2}-\d{2})\s+(?:Long|Short)\s+.*?([,\d\.]+)\s+[$,\d\.]+\s+([$,\d\.]+)$', line)
        if m:
            date = to_date(m.group(1), '%Y-%m-%d')
            quantity = to_float(m.group(2))
            cost = to_float(m.group(3))
            trades[date].append({'quantity': quantity, 'amount': cost, 'side': 'b'})
            continue

        # this is the pattern for closed trade history, which includes buy and sell info for each lot in each row
        # capture the quantity, buy/sell dates, cost and proceeds. accumulate trades by date so we can aggregate sells
        m = re.match(r'^\s*([,\d\.]+)\s+(\d{2}/\d{2}/\d{4})\s+(\d{2}/\d{2}/\d{4})\s+([+$,\d\.]+)\s+([+$,\d\.]+)\s+[-+$,\d\.]+\s+[-+$,\d\.]+$', line)
        if m:
            quantity = to_float(m.group(1))
            buy_date = to_date(m.group(2), '%m/%d/%Y')
            sell_date = to_date(m.group(3), '%m/%d/%Y')
            proceeds = to_float(m.group(4))
            cost = to_float(m.group(5))
            trades[buy_date].append({'quantity': quantity, 'amount': cost, 'side': 'b'})
            trades[sell_date].append({'quantity': quantity, 'amount': proceeds, 'side': 's'})
            continue
    return trades

def print_trades(trades: List[dict], ticker: str, owner: str, type: str):
    if trades:
        print(f"; trade history for {ticker}")

    for date, tradelist in sorted(trades.items()):
        # consolidate sells on the same date
        if all(t['side'] == 's' for t in tradelist):
            quantity = sum(t['quantity'] for t in tradelist)
            amount = sum(t['amount'] for t in tradelist)
            print_sell_trade(ticker, owner, type, date, quantity, amount)
        else:
            for trade in tradelist:
                if trade['side'] == 'b':
                    print_buy_trade(ticker, owner, type, date, trade['quantity'], trade['amount'])
                else:
                    print_sell_trade(ticker, owner, type, date, trade['quantity'], trade['amount'])

def print_buy_trade(ticker: str, owner: str, type: str, date: datetime.date, quantity: float, cost: float):
    price = cost / quantity
    print(BUY_TEMPLATE.format(
        date=date.strftime('%Y-%m-%d'),
        quantity=quantity,
        cost=cost,
        price=price,
        ticker=ticker,
        owner=owner,
        type=type,
    ))

def print_sell_trade(ticker: str, owner: str, type: str, date: datetime.date, quantity: float, proceeds: float):
    print(SELL_TEMPLATE.format(
        date=date.strftime('%Y-%m-%d'),
        quantity=quantity,
        proceeds=proceeds,
        ticker=ticker,
        owner=owner,
        type=type,
    ))

def to_float(s: str) -> float:
    return float(s.replace(',', '').replace('$', '').replace('+', ''))

def to_date(s: str, fmt: str) -> datetime.date:
    return datetime.datetime.strptime(s, fmt).date()

if __name__ == '__main__':
    main()