Daily Budget Report

In [2]:

    

# This file should include the parameters

from daily_budget_report_local_constants import spend_limit

spend_limit


    

In [3]:

    

import io
import pandas as pd

from pathlib import Path

import stardog

conn = stardog.Connection('bs',
                          endpoint='https://localhost:443/stardog',
                          username='admin', password='admin')


    

In [4]:

    

query_text = Path('monthly_spend.rq').read_text()
csv_results = conn.select(query_text, content_type='text/csv')
monthly_spend_txns = pd.read_csv(io.BytesIO(csv_results))


    

In [5]:

    

print(f"Imported {len(monthly_spend_txns)} txns")

monthly_spend_txns.head()


    

In [6]:

    

monthly_spend_txns["date"] = pd.to_datetime(monthly_spend_txns["date"], format="%Y-%m-%dZ")
monthly_spend_txns = monthly_spend_txns.sort_values(by="date")
monthly_spend_txns["category"] = monthly_spend_txns["category"].str.replace("https://github.com/jbalint/ledger-ontology#Expenses", "")
monthly_spend_txns["payee"] = monthly_spend_txns["payee"].str.slice(0,20)
monthly_spend_txns.dtypes


    

In [7]:

    

monthly_spend_txns["cumsum"] = monthly_spend_txns["amt"].cumsum()


    

In [8]:

    

monthly_spend_txns.head()


    

In [9]:

    

monthly_spend_txns["category"].unique()


    

In [116]:

    

from bokeh.io import output_notebook, show
output_notebook()

from bokeh.plotting import figure, output_file, show

from bokeh.models import CategoricalColorMapper, HoverTool, Span

from bokeh.palettes import Category20


    

In [128]:

    

# https://docs.bokeh.org/en/latest/docs/user_guide/categorical.html
palette = Category20[20]
color_map = CategoricalColorMapper(factors=monthly_spend_txns["category"].unique(),
                                   palette=palette)


    

In [134]:

    

TOOLTIPS = [
    ("payee", "@payee"),
    ("amt", "@amt"),
    ("category", "@category"),
    ("total", "@cumsum"),
]

# create a new plot
p = figure(
    tools=["pan", "box_zoom","reset","save",HoverTool(tooltips=TOOLTIPS)],
    title="monthly spending",
    x_axis_label='date',
    y_axis_label='spend',
    x_axis_type="datetime",
    plot_width=900,
    # This manually gives space for the legend on the left
    x_range=(monthly_spend_txns["date"].min() - pd.Timedelta(days=9),
             monthly_spend_txns["date"].min() + pd.Timedelta(days=34)),
    y_range=(0, max([spend_limit, monthly_spend_txns["amt"].sum()]) * 1.05)
)

p.circle(x="date", y="cumsum", source=monthly_spend_txns,
         size=8,
         color={'field': 'category', 'transform': color_map},
         legend_field="category")

p.legend.location = "top_left"

p.renderers.extend([Span(location=spend_limit, dimension='width', line_color="red", line_dash='dashed', line_width=3)])

show(p)


    

In [143]:

    

monthly_spend_txns.groupby(["category"])["amt"].sum()


    

In [142]:

    

with pd.option_context('display.max_rows', None, 'display.max_columns', None):  # more options can be specified also
    print(monthly_spend_txns)