Files

190 lines
6.3 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
"""CSV export of docs/spec.md section 8.
Semicolon separated with a UTF-8 BOM and Norwegian number formatting, so the
file opens directly in a Norwegian spreadsheet. `report_csv()` starts the file
and carries the BOM; `transactions_csv()` is appended after it. Kroner are
written as whole kroner and BTC quantities as whole sats, as in the UI.
"""
import database
import rates
import tax
BOM = "\ufeff"
SEPARATOR = ";"
TYPE_LABELS = {
"trade": "Handel",
"income": "Inntekt",
"spend": "Forbruk",
"loss": "Tap",
"transfer_out": "Overføring ut",
"transfer_in": "Overføring inn",
"gift_received": "Gåve (motteke)",
"gift_given": "Gåve (gjeven)",
"admin_cost": "Forvaltningskostnad",
}
TRANSACTION_HEADINGS = (
"Dato",
"Type",
"Konto",
"Til konto",
"Bitcoin pris (kr)",
"Antall inn (sats)",
"Antall ut (sats)",
"Beløp inn (kr)",
"Beløp ut (kr)",
"Verdi (kr)",
"Avgift (sats)",
"Inngangsverdi (kr)",
"Skattepliktig",
"Notat",
)
def _cell(value):
"""Escape one text cell. Numbers are passed through unquoted."""
text = "" if value is None else str(value)
text = text.replace("\r\n", " ").replace("\n", " ").replace("\r", " ")
if SEPARATOR in text or '"' in text:
return '"' + text.replace('"', '""') + '"'
return text
def _amount(ore):
"""Whole kroner, empty when there is no amount."""
return "" if ore is None else rates.kroner_whole(ore)
def _sats(sats):
"""A whole number of sats, empty when there is none."""
return "" if not sats else rates.group_int(sats)
def _percent(value):
"""A percentage with one decimal and a decimal comma."""
return ("%.1f" % value).replace(".", ",")
def report_csv(year):
"""The report block, with the UTF-8 BOM. Append transactions_csv() to it."""
year = int(year)
data = tax.report(year)
totals = data["totals"]
price = data["year_end_price_ore"]
lines = [_cell("Skuld – skattegrunnlag %d" % year), ""]
lines.append("Nøkkel" + SEPARATOR + "Verdi")
lines.append("Metode" + SEPARATOR + _cell(totals["lot_method"]))
lines.append(
"Kurs %s (NOK per BTC)" % rates.year_end_date(year)
+ SEPARATOR
+ _amount(price)
)
lines.append("Formue (kr)" + SEPARATOR + _amount(totals["formue_ore"]))
lines.append("Gevinst (kr)" + SEPARATOR + _amount(totals["gevinst_ore"]))
lines.append("Tap (kr)" + SEPARATOR + _amount(totals["tap_ore"]))
lines.append("Brukt inngangsverdi (kr)" + SEPARATOR + _amount(totals["brukt_ore"]))
lines.append("Netto (kr)" + SEPARATOR + _amount(totals["netto_ore"]))
lines.append("Inntekt (kr)" + SEPARATOR + _amount(totals["inntekt_ore"]))
lines.append("Skatt (kr)" + SEPARATOR + _amount(totals["skatt_ore"]))
lines.append("Formuesskatt (kr)" + SEPARATOR + _amount(totals["formuesskatt_ore"]))
lines.append(
"Forvaltningskostnad (kr)" + SEPARATOR + _amount(totals["admin_cost_ore"])
)
lines.append("")
lines.append(SEPARATOR.join((
"Konto",
"Behaldning %",
"Antall (sats)",
"Formue (kr)",
"Gevinst (kr)",
"Tap (kr)",
"Brukt (kr)",
"Gevinst %",
)))
for account in data["accounts"]:
lines.append(SEPARATOR.join((
_cell(account["name"]),
_percent(account["share_percent"]),
_sats(account["sats"]),
_amount(account["formue_ore"]),
_amount(account["gevinst_ore"]),
_amount(account["tap_ore"]),
_amount(account["brukt_ore"]),
_percent(account["gevinst_percent"]),
)))
lines.append("")
if data["non_taxable"]:
lines.append(SEPARATOR.join((
"Ikkje skattepliktig", "Antall (sats)", "Gevinst (kr)", "Tap (kr)"
)))
for row in data["non_taxable"]:
lines.append(SEPARATOR.join((
_cell(row["name"]),
_sats(row["sats"]),
_amount(row["gevinst_ore"]),
_amount(row["tap_ore"]),
)))
lines.append("")
if data["custody_totals"]:
lines.append("TOT (fysisk behaldning)" + SEPARATOR + "Antall (sats)")
for row in data["custody_totals"]:
lines.append(_cell(row["name"]) + SEPARATOR + _sats(row["sats"]))
lines.append("")
lines.append(_cell(data["skattemelding"]["note"]))
return BOM + "\r\n".join(lines) + "\r\n"
def transactions_csv(year):
"""The year's transactions with the consumed cost and the bitcoin price."""
year = int(year)
conn = database.connect()
try:
rows = conn.execute(
"SELECT t.date, t.type, t.taxable, t.note, t.leg_in_sats, t.leg_in_ore,"
" t.leg_out_sats, t.leg_out_ore, t.value_ore, t.fee_sats,"
" m.name AS market_name, d.name AS to_market_name,"
" COALESCE(SUM(c.cost_ore), 0) AS cost_ore,"
" COALESCE(SUM(c.sats), 0) AS consumed_sats,"
" COALESCE((SELECT SUM(l.cost_ore_original) FROM lots l"
" WHERE l.origin_tx_id = t.id), 0) AS lot_cost_ore,"
" COALESCE((SELECT SUM(l.sats_original) FROM lots l"
" WHERE l.origin_tx_id = t.id), 0) AS lot_sats"
" FROM transactions t"
" LEFT JOIN markets m ON m.id = t.market_id"
" LEFT JOIN markets d ON d.id = t.to_market_id"
" LEFT JOIN lot_consumptions c ON c.tx_id = t.id"
" WHERE t.date >= ? AND t.date <= ?"
" GROUP BY t.id ORDER BY t.date, t.id",
("%04d-01-01" % year, "%04d-12-31" % year),
).fetchall()
finally:
conn.close()
lines = [SEPARATOR.join(("Transaksjonar %d" % year,))]
lines.append(SEPARATOR.join(TRANSACTION_HEADINGS))
for row in rows:
lines.append(SEPARATOR.join((
row["date"],
_cell(TYPE_LABELS.get(row["type"], row["type"])),
_cell(row["market_name"]),
_cell(row["to_market_name"]),
_amount(tax.btc_price_ore(row)),
_sats(row["leg_in_sats"]),
_sats(row["leg_out_sats"]),
_amount(row["leg_in_ore"]),
_amount(row["leg_out_ore"]),
_amount(row["value_ore"]),
_sats(row["fee_sats"]),
_amount(row["cost_ore"]),
"Ja" if row["taxable"] else "Nei",
_cell(row["note"]),
)))
return "\r\n".join(lines) + "\r\n"