Prepare Interactive Brokers report for entry into edavki.furs.si

ib_to_edavki.py

### THIS IS DEPRECATED ###
# Use https://github.com/jamsix/ib-edavki instead

from collections import OrderedDict
from dataclasses import dataclass
from dataclasses import field
from decimal import Decimal
import xml.etree.ElementTree as ET
from datetime import date
from datetime import datetime
from enum import Enum

import csv
import re
import textwrap

# Download from https://www.bsi.si/_data/tecajnice/dtecbs-l.xml
rates = ET.parse("dtecbs-l.xml")

# Download from IB -> Reports -> Annual -> CSV
report = open("ib.csv").readlines()


def convert(usd: Decimal, day: date) -> Decimal:
    """Convert USD to EUR based on ECB rate on given day."""
    rate = Decimal(rates.find(f"*[@datum='{day.isoformat()}']/*[@oznaka='USD']").text)
    return round(usd / rate, 2)


@dataclass
class Fund:
    isin: str
    title: str = field(repr=False)
    address: str = field(repr=False)
    country: str


FUNDS = {
    "IE00B3RBWM25": Fund(
        "IE00B3RBWM25",
        "Vanguard FTSE All-World UCITS ETF",
        "70 Sir John Rogerson's Quay Dublin 2 Ireland",
        "IE",
    ),
    "US9219097683": Fund(
        "US9219097683",
        "Vanguard Total International Stock ETF",
        "P.O. Box 1110, Valley Forge, 19482-1110",
        "US",
    ),
    "US9219378356": Fund(
        "US9219378356",
        "Vanguard Total Bond Market ETF",
        "P.O. Box 1110, Valley Forge, 19482-1110",
        "US",
    ),
    "US92203J4076": Fund(
        "US92203J4076",
        "Vanguard Total International Bond ETF",
        "P.O. Box 1110, Valley Forge, 19482-1110",
        "US",
    ),
    "US9220427754": Fund(
        "US9220427754",
        "Vanguard FTSE All-World ex-US ETF",
        "P.O. Box 1110, Valley Forge, 19482-1110",
        "US",
    ),
    "US9229087690": Fund(
        "US9229087690",
        "Vanguard Total Stock Market ETF",
        "P.O. Box 1110, Valley Forge, 19482-1110",
        "US",
    ),
}


@dataclass
class Dividend:
    """A data representation of a single dividend payout."""

    day: date
    fund: Fund
    amount: Decimal
    withhold_tax: Decimal = None


dividends = OrderedDict()
for line in report:
    if line.startswith("Dividends,Data,USD,2019"):
        columns = line.split(",")

        day = datetime.strptime(columns[3], "%Y-%m-%d").date()
        amount = Decimal(columns[5])
        fund_name = re.search("\((.+?)\)", columns[4]).group(1)
        try:
            per_share = re.search("Dividend (.+?) USD per Share", columns[4]).group(1)
        except AttributeError:
            # sometimes description has USD before rate ¯\_(ツ)_/¯
            per_share = re.search("Dividend USD (.+?) per Share", columns[4]).group(1)

        # Sometimes Vanguard Total Bond Market ETF is reported as "conid"
        # intead of ISIN, no idea why.
        if fund_name == "43645828":
            fund_name = "US9219378356"

        fund = FUNDS[fund_name]

        dividends[Decimal(per_share)] = Dividend(day=day, fund=fund, amount=amount)

    if line.startswith("Withholding Tax,Data,USD,2019"):
        columns = line.split(",")
        day = datetime.strptime(columns[3], "%Y-%m-%d").date()
        withhold_tax = Decimal(columns[5])

        # Very rarely the withhold_tax is positive and does not match any dividend,
        # no idea why  ¯\_(ツ)_/¯
        if withhold_tax > 0:
            continue

        fund_name = re.search("\((.+?)\)", columns[4]).group(1)

        # Sometimes Vanguard Total Bond Market ETF is reported as "conid"
        # intead of ISIN, no idea why.
        if fund_name == "43645828":
            fund_name = "US9219378356"

        fund = FUNDS[fund_name]

        try:
            per_share = re.search("Dividend (.+?) USD per Share", columns[4]).group(1)
        except AttributeError:
            # sometimes description has USD before rate ¯\_(ツ)_/¯
            per_share = re.search("Dividend USD (.+?) - US Tax", columns[4]).group(1)
            # and then sometimes it says "per Share" too
            per_share = per_share.replace(" per Share", "")

        # Sometimes Vanguard Total Bond Market ETF is reported as "conid"
        # intead of ISIN, no idea why.
        if fund_name == "43645828":
            fund_name = "US9219378356"

        try:
            dividends[Decimal(per_share)].withhold_tax = withhold_tax
        except:
            print(f"skipping line: {line}")

print(f"Processed {len(dividends.items())} dividends:")

for dividend in dividends.values():

    print(f"Datum:                     {dividend.day.strftime('%d.%m.%Y')}")
    print(f"Identifikacijska številka: {dividend.fund.isin}")
    print(f"Naziv izplačevalca:        {dividend.fund.title}")
    print(f"Naslov izplačevalca:       {dividend.fund.address}")
    print(f"Država:                    {dividend.fund.country}")

    if dividend.fund.country == "IE":
        print(f"Znesek dividend:           € {str(dividend.amount).replace('.',',')}")
        print(f"Tuji davek:                € 0")
    else:
        amount = convert(dividend.amount, dividend.day)
        tax = convert(dividend.withhold_tax, dividend.day)
        print(
            f"Znesek dividend:           ($ {dividend.amount}) € {str(amount).replace('.',',')}"
        )
        print(
            f"Tuji davek:                ($ {dividend.withhold_tax}) € {str(tax).replace('.',',').replace('-','')}"
        )

    print(f"\n\n")

Deprecated in favor of https://github.com/jamsix/ib-edavki.