378 lines
16 KiB
Python
Executable File
378 lines
16 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
from beancount import loader
|
|
from beanquery import query
|
|
from beancount.parser import printer
|
|
import argparse
|
|
from tabulate import tabulate
|
|
from decimal import Decimal
|
|
from beancount.core.amount import Amount, add, sub
|
|
from datetime import datetime, timedelta
|
|
|
|
|
|
class bcolors:
|
|
HEADER = '\033[95m'
|
|
OKBLUE = '\033[94m'
|
|
OKCYAN = '\033[96m'
|
|
OKGREEN = '\033[92m'
|
|
WARNING = '\033[93m'
|
|
FAIL = '\033[91m'
|
|
ENDC = '\033[0m'
|
|
BOLD = '\033[1m'
|
|
UNDERLINE = '\033[4m'
|
|
|
|
|
|
def draw_line():
|
|
print('─' * 30)
|
|
|
|
|
|
def get_month_to_date_timestamps(date):
|
|
month = int(date.split("-")[1])
|
|
year = int(date.split("-")[0])
|
|
d = datetime(year, month, 1)
|
|
end_date = d - timedelta(days=1)
|
|
start_date = f"{end_date.year}-{end_date.month:02d}-01"
|
|
return start_date, end_date.strftime("%Y-%m-%d")
|
|
|
|
|
|
def get_last_year_timestamps(date):
|
|
date_parts = date.split("-")
|
|
day = int(date_parts[2])
|
|
month = int(date_parts[1])
|
|
year = int(date_parts[0])
|
|
return f"{year-1}-{month:02d}-{day:02d}", date
|
|
|
|
|
|
def get_sum_balances(balances, account_prefix):
|
|
sum = 0
|
|
for account, balance in balances.items():
|
|
if account.startswith(account_prefix):
|
|
sum = balance if sum == 0 else sum + balance
|
|
if sum == 0 or sum.get_only_position() == None:
|
|
return Amount(Decimal(0), "EUR").to_string()
|
|
result = sum.get_only_position().units
|
|
return Amount(Decimal(round(result.number, 2)), result.currency).to_string()
|
|
|
|
|
|
def get_net_worth(balances):
|
|
total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
|
|
total_liabilities = Amount.from_string(
|
|
get_sum_balances(balances, "Liabilities:"))
|
|
return add(total_assets, total_liabilities)
|
|
|
|
|
|
def get_debt_to_assets_ratio(balances, max):
|
|
total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
|
|
total_liabilities = Amount.from_string(
|
|
get_sum_balances(balances, "Liabilities:"))
|
|
result = round(((total_liabilities.number * -1) /
|
|
total_assets.number) * 100, 2)
|
|
return f"{bcolors.FAIL if result >= max else bcolors.OKGREEN}{result} %{bcolors.ENDC}"
|
|
|
|
|
|
def get_emergency_fund_ratio(balances, expenses, low, mid):
|
|
liquid = 0
|
|
living_expenses = expenses[0][0].get_only_position(
|
|
).units.number / 12
|
|
for account, balance in balances.items():
|
|
if account.startswith("Assets:Liquid"):
|
|
liquid = balance if liquid == 0 else liquid + balance
|
|
total_liquid = Amount(Decimal(0), "EUR") if liquid.get_only_position(
|
|
) == None else liquid.get_only_position().units
|
|
result = round(total_liquid.number / living_expenses, 2)
|
|
color = bcolors.FAIL if result < low else bcolors.OKGREEN if result > mid else bcolors.WARNING
|
|
return f"{color}{result} mth{bcolors.ENDC}"
|
|
|
|
|
|
def get_investment_assets_to_net_worth_ratio(balances, min):
|
|
total_investment = Amount.from_string(
|
|
get_sum_balances(balances, "Assets:Invest:"))
|
|
result = round((total_investment.number /
|
|
get_net_worth(balances).number) * 100, 2)
|
|
return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"
|
|
|
|
|
|
def get_liquid_assets_to_net_worth_ratio(balances, pignorat, min):
|
|
liquid = 0
|
|
for account, balance in balances.items():
|
|
if account.startswith("Assets:Liquid") or account.startswith("Assets:Invest"):
|
|
liquid = balance if liquid == 0 else liquid + balance
|
|
total_liquid = Amount(Decimal(0), "EUR") if liquid.get_only_position(
|
|
) == None else liquid.get_only_position().units
|
|
total_liquid = sub(total_liquid, Amount(pignorat, 'EUR'))
|
|
result = round(
|
|
(total_liquid.number / get_net_worth(balances).number) * 100, 2)
|
|
return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"
|
|
|
|
|
|
def get_savings_ratio(balances, net_yearly_income, yearly_savings, min):
|
|
result = round((yearly_savings.number /
|
|
net_yearly_income) * 100, 2)
|
|
return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"
|
|
|
|
|
|
def get_debt_service_ratio(balances, net_yearly_income, debt_payments, max):
|
|
result = round((debt_payments.number / net_yearly_income) * 100, 2)
|
|
return f"{bcolors.FAIL if result >= max else bcolors.OKGREEN}{result} %{bcolors.ENDC}"
|
|
|
|
|
|
def get_non_mortgage_debt_service_ratio(balances, net_yearly_income, mortgage_payments, max):
|
|
result = round((mortgage_payments.number / net_yearly_income) * 100, 2)
|
|
return f"{bcolors.FAIL if result >= max else bcolors.OKGREEN}{result} %{bcolors.ENDC}"
|
|
|
|
|
|
def get_solvency_ratio(balances, min):
|
|
total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
|
|
result = round((get_net_worth(balances).number /
|
|
total_assets.number) * 100, 2)
|
|
return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"
|
|
|
|
|
|
def get_interest_coverage_ratio(net_monthly_income, expenses, debt_payments, mortgage_payments, min):
|
|
living_expenses = expenses[0][0].get_only_position().units.number
|
|
interest = debt_payments.number + mortgage_payments.number
|
|
interest = interest if interest > 0 else 1
|
|
result = round((net_monthly_income - living_expenses) / interest, 2)
|
|
return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result}{bcolors.ENDC}"
|
|
|
|
|
|
def get_max_leveraged_investment(balances):
|
|
total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
|
|
return Amount(round(total_assets.number * Decimal(0.9), 2), total_assets.currency).to_string()
|
|
|
|
|
|
def get_position_as_str(inventory):
|
|
position = inventory.get_only_position()
|
|
if position is None:
|
|
return position
|
|
else:
|
|
return Amount(Decimal(round(position.units.number, 2)), position.units.currency).to_string()
|
|
|
|
|
|
def print_report(date, balances, expenses, net_monthly_income, net_yearly_income, debt_payments, mortgage_payments, savings, pignorat):
|
|
print(f"{bcolors.BOLD}Balance Sheet (date={date}){bcolors.ENDC}")
|
|
draw_line()
|
|
print(f"{bcolors.BOLD}Assets{bcolors.ENDC}")
|
|
print(f"\t{bcolors.BOLD}Comptes bancaris{bcolors.ENDC}")
|
|
print(tabulate([
|
|
["Corrent", get_sum_balances(
|
|
balances, "Assets:Liquid:Caixabank:Corrent")],
|
|
["Estalvi", get_sum_balances(balances, "Assets:Liquid:Estalvi")],
|
|
["Compte d'inversió", get_sum_balances(
|
|
balances, "Assets:Liquid:R4:EUR")],
|
|
["Total", get_sum_balances(balances, "Assets:Liquid:")],
|
|
]))
|
|
print(f"\t{bcolors.BOLD}Inversions{bcolors.ENDC}")
|
|
print(tabulate([
|
|
["Fons d'inversió", get_sum_balances(balances, "Assets:Invest:Fund:")],
|
|
["ETFs", get_sum_balances(balances, "Assets:Invest:ETF:")],
|
|
["Accions", get_sum_balances(balances, "Assets:Invest:Stock:")],
|
|
["Renta fixa", get_sum_balances(balances, "Assets:Invest:Fixed:")],
|
|
["Total inversions", get_sum_balances(balances, "Assets:Invest:")],
|
|
]))
|
|
print(tabulate([
|
|
["Inversions pignorades", Amount(
|
|
round(pignorat, 2), 'EUR').to_string()],
|
|
]))
|
|
print(f"\t{bcolors.BOLD}Propietat personal{bcolors.ENDC}")
|
|
print(tabulate([
|
|
["Vivenda principal", get_sum_balances(
|
|
balances, "Assets:PersonalProperty:VivendaPrincipal")],
|
|
["Cotxes", get_sum_balances(
|
|
balances, "Assets:PersonalProperty:Cotxe")],
|
|
["Joies, Art, Col·leccionables", get_sum_balances(
|
|
balances, "Assets:PersonalProperty:JoiesArtCollecionables")],
|
|
["Metalls preciosos", get_sum_balances(
|
|
balances, "Assets:PersonalProperty:MetallsPreciosos")],
|
|
["Altres propietats", get_sum_balances(
|
|
balances, "Assets:PersonalProperty:AltresPropietats")],
|
|
["Total propietats", get_sum_balances(
|
|
balances, "Assets:PersonalProperty:")],
|
|
]))
|
|
print(f"\t{bcolors.BOLD}Deutes{bcolors.ENDC}")
|
|
print(tabulate([
|
|
["Deutes per cobrar", get_position_as_str(
|
|
balances["Assets:Debt:DeutesPerCobrar"])],
|
|
["Total deutes", get_sum_balances(balances, "Assets:Debt:")],
|
|
]))
|
|
print(f"\t{bcolors.BOLD}Beneficis laborals{bcolors.ENDC}")
|
|
print(tabulate([
|
|
["Tickets Restaurant", get_position_as_str(
|
|
balances["Assets:Benefits:Edenred:TicketsRestaurant"])],
|
|
["Targeta Transport", get_position_as_str(
|
|
balances["Assets:Benefits:Edenred:TargetaTransport"])],
|
|
["Pla Pensions Empleados Zurich", get_position_as_str(
|
|
balances["Assets:Benefits:DZP:PPEZurich"]) if "Assets:Benefits:DZP:PPEZurich" in balances else "-"],
|
|
["Total beneficis", get_sum_balances(balances, "Assets:Benefits:")],
|
|
]))
|
|
print(tabulate([
|
|
[f"\t{bcolors.BOLD}Total Assets",
|
|
get_sum_balances(balances, "Assets:")]
|
|
]))
|
|
|
|
draw_line()
|
|
print(f"{bcolors.BOLD}Liabilites{bcolors.ENDC}")
|
|
print(tabulate([
|
|
["Hipoteques en vivenda principal", get_position_as_str(
|
|
balances["Liabilities:Hipoteca:VivendaPrincipal"] * Decimal(-1))],
|
|
["Hipoteques en vivenda d'inversió",
|
|
Amount(Decimal(0), "EUR").to_string()],
|
|
["Targetes de crèdit", get_position_as_str(
|
|
balances["Liabilities:Credit:Caixabank:TargetaCredit"] * Decimal(-1))],
|
|
["Línies de crèdit per inversió", get_position_as_str(
|
|
balances["Liabilities:Credit:Renta4:PolissaCredit"] * Decimal(-1))],
|
|
["Factures impagades", get_position_as_str(
|
|
balances["Liabilities:Factures:FacturesPendents"] * Decimal(-1))],
|
|
["Préstecs personals", Amount(Decimal(0), "EUR").to_string()],
|
|
["Impostos no pagats", get_position_as_str(
|
|
balances["Liabilities:Taxes:IRPF"] * Decimal(-1))],
|
|
["Altres passius", Amount(Decimal(0), "EUR").to_string()]
|
|
]))
|
|
print(tabulate([
|
|
[f"{bcolors.BOLD}Total passius{bcolors.ENDC}", f"{bcolors.BOLD}{
|
|
get_sum_balances(balances, "Liabilities:")}{bcolors.ENDC}"],
|
|
]))
|
|
|
|
draw_line()
|
|
print(f"{bcolors.BOLD}Net Worth\t{get_net_worth(balances)}{bcolors.ENDC}")
|
|
|
|
draw_line()
|
|
print(f"{bcolors.BOLD}Financial Ratios{bcolors.ENDC}")
|
|
print(tabulate([
|
|
["Debt-to-Assets Ratio",
|
|
get_debt_to_assets_ratio(balances, 50), "50 %"],
|
|
["Emergency Fund", get_emergency_fund_ratio(
|
|
balances, expenses, 3, 6), "3-6 mth"],
|
|
["Investment Assets to Net Worth Ratio",
|
|
get_investment_assets_to_net_worth_ratio(balances, 50), "50 %"],
|
|
["Liquid Assets to Net Worth Ratio",
|
|
get_liquid_assets_to_net_worth_ratio(balances, pignorat, 15), "15 %"],
|
|
["Savings Ratio", get_savings_ratio(
|
|
balances, net_yearly_income, savings, 20), "20 %"],
|
|
["Debt-Service Ratio",
|
|
get_debt_service_ratio(balances, net_yearly_income, debt_payments, 35), "35 %"],
|
|
["Non-Mortgage Debt-Service Ratio",
|
|
get_non_mortgage_debt_service_ratio(balances, net_yearly_income, mortgage_payments, 15), "15 %"],
|
|
["Interest Coverage Ratio", get_interest_coverage_ratio(
|
|
net_yearly_income, expenses, debt_payments, mortgage_payments, 1.5), "1.5"]
|
|
]))
|
|
|
|
|
|
def get_balances(entries, options, date):
|
|
balance_query = f"SELECT account, convert(sum(position), \"EUR\") as position FROM date <= {
|
|
date} WHERE account ~ '^(Liabilities|Assets)'"
|
|
rtypes, rrows = query.run_query(
|
|
entries, options, balance_query)
|
|
balances = {}
|
|
for row in rrows:
|
|
balances[row[0]] = row[1]
|
|
return balances
|
|
|
|
|
|
def get_expenses(entries, options, date):
|
|
start_date, end_date = get_last_year_timestamps(date)
|
|
expenses_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {
|
|
end_date} WHERE account ~ 'Expenses:' AND date >= {start_date}"
|
|
rtypes, rrows = query.run_query(
|
|
entries, options, expenses_query)
|
|
return rrows
|
|
|
|
|
|
def get_income(entries, options, date):
|
|
start_date, end_date = get_month_to_date_timestamps(date)
|
|
income_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {
|
|
end_date} WHERE account ~ '^(Income:Work|Income:Savings|Income:Invest)' AND date >= {start_date}"
|
|
rtypes, rrows = query.run_query(
|
|
entries, options, income_query)
|
|
net_monthly_income = rrows[0][0].get_only_position(
|
|
).units.number * -1
|
|
|
|
start_date, end_date = get_last_year_timestamps(date)
|
|
income_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {
|
|
end_date} WHERE account ~ '^(Income:Work|Income:Savings|Income:Invest)' AND date >= {start_date}"
|
|
rtypes, rrows = query.run_query(
|
|
entries, options, income_query)
|
|
net_yearly_income = rrows[0][0].get_only_position(
|
|
).units.number * -1
|
|
return net_monthly_income, net_yearly_income
|
|
|
|
|
|
def get_debt_payments(entries, options, date):
|
|
# FIX: Agafar nomes els pagaments de deute, enlloc de també les addicions de deute
|
|
start_date, end_date = get_last_year_timestamps(date)
|
|
debt_payments_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {
|
|
end_date} WHERE account ~ 'Expenses:R4:Interessos' AND date >= {start_date}"
|
|
mortgage_payments_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {
|
|
end_date} WHERE account ~ 'Liabilities:Hipoteca:' AND date >= {start_date}"
|
|
rtypes, rrows_debt = query.run_query(
|
|
entries, options, debt_payments_query)
|
|
rtypes, rrows_mortgage = query.run_query(
|
|
entries, options, mortgage_payments_query)
|
|
debt_payments = rrows_debt[0][0].get_only_position().units if len(
|
|
rrows_debt) > 0 else Amount(Decimal(0), "EUR")
|
|
mortgage_payments = rrows_mortgage[0].position.get_only_position(
|
|
).units if len(rrows_mortgage) > 0 else Amount(Decimal(0), "EUR")
|
|
return debt_payments, mortgage_payments
|
|
|
|
|
|
def get_savings(entries, options, date):
|
|
start_date, end_date = get_last_year_timestamps(date)
|
|
investments_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {
|
|
end_date} WHERE account ~ '^Assets:Invest:' AND date >= {start_date}"
|
|
rtypes, rrows = query.run_query(
|
|
entries, options, investments_query)
|
|
result = rrows[0][0].get_only_position().units if len(
|
|
rrows) > 0 else Amount(Decimal(0), "EUR")
|
|
liabilities_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {
|
|
end_date} WHERE account ~ '^Liabilities:Credit:Renta4:' AND date >= {start_date}"
|
|
rtypes, rrows = query.run_query(
|
|
entries, options, liabilities_query)
|
|
liabilities = rrows[0][0].get_only_position().units if len(
|
|
rrows) > 0 else Amount(Decimal(0), "EUR")
|
|
result = add(result, liabilities)
|
|
return result
|
|
|
|
|
|
def get_assets_pignorats(entries, options, date):
|
|
assets_pignorats = {
|
|
'VANSMCAP': Decimal(58),
|
|
'VANGL': Decimal(1025.41)
|
|
}
|
|
result = Decimal(0)
|
|
for _, curr in enumerate(assets_pignorats):
|
|
pignorat_query = f"SELECT DISTINCT GETPRICE('{curr}', 'EUR', {
|
|
date}) as price"
|
|
rtypes, rrows = query.run_query(
|
|
entries, options, pignorat_query)
|
|
result = result + rrows[0][0] * assets_pignorats[curr]
|
|
return result
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(
|
|
description='Generate balance sheet report')
|
|
parser.add_argument('date', metavar='date', type=str, nargs=1,
|
|
help='Report date in ISO format (e.g. 1970-01-01)')
|
|
|
|
args = parser.parse_args()
|
|
date = args.date[0]
|
|
|
|
filename = "ledger/main.beancount"
|
|
entries, errors, options = loader.load_file(filename)
|
|
|
|
if errors:
|
|
printer.print_errors(errors)
|
|
|
|
balances = get_balances(entries, options, date)
|
|
expenses = get_expenses(entries, options, date)
|
|
net_monthly_income, net_yearly_income = get_income(
|
|
entries, options, date)
|
|
debt_payments, mortgage_payments = get_debt_payments(
|
|
entries, options, date)
|
|
savings = get_savings(entries, options, date)
|
|
pignorat = get_assets_pignorats(entries, options, date)
|
|
print_report(date, balances, expenses, net_monthly_income, net_yearly_income,
|
|
debt_payments, mortgage_payments, savings, pignorat)
|
|
|
|
|
|
main()
|