<jupyter_start><jupyter_text>Interface to KMyMoney saved files===============You must save your data as SQL (or you can export your current data to an sql file)<jupyter_code>filename = "/Users/briot/Comptes.kmm"
from kmymoney import KMyMoney    # our own file
from jupyter_utils import disp, as_numeric   # our own file

kmm = KMyMoney(filename)


# For convenience, autoreload scripts (kmymoney.py) before executing commands.
%load_ext autoreload
%autoreload 2

# jupyter labextension install \
#    @jupyter-widgets/jupyterlab-manager
import ipywidgets as widgets

# pip install qgrid
# jupyter nbextension enable --py --sys-prefix qgrid
# import qgrid

# Direct look at the database (needs `pip install ipython-sql`)
%load_ext sql
sqlite = kmm.sqlite
%sql $sqlite

# Setup matplotlib
import matplotlib.pyplot as plt
%matplotlib inline
import datetime

selectedAccounts = widgets.SelectMultiple(
    options=sorted(tuple(x) for x in kmm.accounts()[['name', 'accountId']].to_numpy()),
    rows=10,
    value=['A000241', 'A000242'],
    description='Accounts',
    layout=widgets.Layout(width='600px'),
)
display(selectedAccounts)

mindate = widgets.DatePicker(description='Start date', value=datetime.datetime(2019, 1, 1))
display(mindate)

maxdate = widgets.DatePicker(description='End date', value=datetime.datetime.now())
display(maxdate)

context = {'accounts': selectedAccounts, 'mindate': mindate, 'maxdate': maxdate}<jupyter_output><empty_output><jupyter_text>Display the ledger for the selected accounts---------A single SQL query is used to compute the lines of the ledger in a format similar to what KMyMoney outputs. We compute the running balance directly in the sql query, which is useful to find operations that brought the balance over some threshold for instance.We then manipulate the result via Python's Pandas to extract specific information. We cannot, for instance, reduce the range of dates directly in the SQL query, since the running balance is computed on the result rows, so it would be wrong if we remove some rows. (An alternative might be to have a third SELECT statement to filter, but doing that in Pandas is more flexible since it also limits the number of queries we do to sqlite -- keeping everything in memory works fine for a typical kmymoney file)<jupyter_code>def show_ledger(accounts, mindate, maxdate):
    disp(kmm.ledger(
        accounts=accounts,
        mindate=mindate.strftime('%Y-%m-%d'),
        maxdate=maxdate.strftime('%Y-%m-%d'),
    ).fillna('-'))
    
widgets.interactive_output(show_ledger, context)  # call function when context changes<jupyter_output><empty_output><jupyter_text>All transactions in a given category------Display a subset of transactions. Useful to cleanup a file for instance<jupyter_code>cat = (
    'Interne',
    'reconciliation',
    # 'Opening Balances',
)

def subset_of_transactions(accounts, mindate, maxdate):
    p = kmm.ledger(accounts=accounts, mindate=mindate, maxdate=maxdate)
    p = p[ p['category'].isin(cat) ]              # Only keep transactions from specific categories
    p = p.drop(['balance', 'reconcile'], axis=1)  # meaningless columns in this view
    disp(
       p.loc[ 
           p[['deposit', 'paiement']].max(axis=1)  # create a series with max(deposit,paiement)
           .sort_values(ascending=False).index     # sort it, and retrieves the indexes in the original series
       ]  # list the rows of p using the indexes of the sorted series
       .append(p.sum(numeric_only=True), ignore_index=True) # Add a 'Total' row
       .fillna('-'),
       height=200
    )
    
widgets.interactive_output(subset_of_transactions, context)<jupyter_output><empty_output><jupyter_text>Deposits and Paiements for a specific date range------------These plots paiements and deposits, not exactly the same as plotting the Expenses and Income accounts, because it is possible to make either paiements or deposits on either of those (for instance a reimbursement for some paiement you made earlier)<jupyter_code>def show_deposits_and_paiements(accounts, mindate, maxdate):
    kmm.plot_by_category(
        accounts=accounts, mindate=mindate, maxdate=maxdate, values=['paiement', 'deposit'])
    kmm.plot_by_category(
        accounts=accounts, mindate=mindate, maxdate=maxdate, values=['amount'], kind='bar')
    
widgets.interactive_output(show_deposits_and_paiements, context)<jupyter_output><empty_output><jupyter_text>Net worth by month or year---------------Networth is computed by looking at the current positions in all accounts (EUR or number of shares) at the end of some periods (monthly, yearly,...), and applying the price of the stocks as of the end of that period. In a ledger, the prices are computed as of the transaction itself.<jupyter_code>def show_networth(accounts, mindate, maxdate, by_year=True):
    p = kmm.networth(
        accounts=accounts,
        mindate=mindate.strftime('%Y-%m-%d'), 
        maxdate=maxdate.strftime('%Y-%m-31'), 
        by_year=by_year, 
        with_total=True)
    disp(p, height=600)

widgets.interactive_output(show_networth, context)<jupyter_output><empty_output><jupyter_text>Group the transactions into bins-----------------------<jupyter_code>import pandas as pd

def bin_and_plot(accounts, mindate, maxdate):
    """
    Group data into bins of specific ranges:  [0, 10), [10, 20), ...
    """
    bins = [(0, 50), (50, 100), (100, 200), (200, 500), (500, 1000), 
            (1000, 3000), (3000, 5000), (5000, 10000), (10000, 100000000)]

    l = kmm.ledger(accounts=accounts, mindate=mindate, maxdate=maxdate)
    l = l[ ~l['paiement'].isnull() ]   # Only keep transactions with a paiement
    
    binned = pd.cut(  # for each element, contains the name of the bin it belongs to
        l['paiement'],
        pd.IntervalIndex.from_tuples(bins, closed="left"),
        labels=[f"[{l},{r})" for l, r in bins],
        precision=0,
        include_lowest=True
    )

    c = binned.value_counts()
    c.sort_index(inplace=True)
    c.plot(kind='bar', title='Size of paiements')
    
widgets.interactive_output(bin_and_plot, context)<jupyter_output><empty_output><jupyter_text>Investments----------<jupyter_code>def show_history_prices(accounts):
    p = kmm.price_history(accounts=accounts)
    if not p.empty:
        pl = p.interpolate().plot(kind='line', title='Price History', figsize=(10, 5));
        pl.legend(bbox_to_anchor=(1.3, 1))
    
widgets.interactive_output(show_history_prices, {'accounts': selectedAccounts})
    
# Get from Yahoo Finance
import pandas_datareader as data
df = data.DataReader('ETH-EUR', start="2017-06-29", end="2020-05-15", data_source='yahoo')
df['Adj Close'].plot(title='Closing prices from Yahoo Finance', figsize=(10, 5));<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11851851851851852, 'fraction_numerical': 0.019703703703703702, 'mean_word_length': 4.278342455043003, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '376709', 'license': 'MIT', 'n_tokens_mistral': 2241, 'n_tokens_neox': 2070, 'n_words': 631, 'path': 'kmymoney.ipynb', 'repo_name': 'briot/kmymoney_python', 'size': '6750'}