Python script to parse Fidelity trade history and convert to Beancount ledger format
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
""" | |
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment