quarterly-report.py

# quarterly-report.py
#
# Based on an input file of labeled mint transaction data
# run a report for a time range that provides detail on 
# the income and expenses per unit as well as details on
# any shared expenses incurred by the property during that period

import pandas as pd
import numpy as np
import html_config # local variables for generic html start and end
import sys
import webbrowser
import os

# Set the year and quarter to run a report for
# TODO allow this to be set on the command line
# TODO allow this to be overridden with specific start and end dates
REPORT_YEAR = 2022
REPORT_QUARTER = 4
# Comment these lines out to do a quarterly report
# start_date = '2021-01-01'
# end_date = '2021-12-31'

# If not set, output set to stdout
OUTPUT_DIRECTORY = 'reports'

# Ensure that a file transactions.csv is in this project directory
# this will happen automatically as an output from update-transactions.py
# Or set this to the name of the csv file where your property transactions are
PATH_TO_YOUR_TRANSACTIONS = "transactions.csv"

# By default the report will analyze actual utilty expenses against a "limit"
# for each unit.  This is useful in cases where the rent includes a certain
# amount of included utilities.
PATH_TO_UTIL_LIMITS = "util-limits.csv" # Used to calculate tenant charges for utilities

# Set this to True to skip the utilities analysis if your tenants pay for
# their own utilities, or if you don't limit the amount included in the rent
SKIP_UTIL_ANALYSIS = False

# Set this to True to generate only a tenant facing list of utility transactions
GEN_TENANT_UTIL_REPORTS = False
TENANT_REPORTS = []
if GEN_TENANT_UTIL_REPORTS and SKIP_UTIL_ANALYSIS:
    print('SKIP_UTIL_ANALYSIS must be set to False if GEN_TENANT_UTIL_REPORTS is set to True')
    quit()

# Set the name of the column where the transactions are categorized
# In Mint this is usually "Category".  I use Mint's category to link 
# transactions to a property and the add a seperate "Label" column
# after exporting the data for a property from Min
CATEGORY = "Label"


def load_transaction_data(data_file):
    """Read in a csv file of categorized
       mint transaction data and return
       a data frame sorted by date
    """
    # Load the transaction data from the csv into a dataframe
    parse_dates = ['Date']
    try:
        df = pd.read_csv(data_file, parse_dates=parse_dates)
    except IOError as e:
        print('Failed to open', data_file,':', e)
        print('Run "python update-transation-date.py" to create one, or edit')
        print('"PATH_TO_YOUR_TRANSACTIONS" in quarterly-report.py if your data is in a different file')
        quit()
    df.set_index(['Date'], inplace=True)
    df['Amount'] = df['Amount'].astype(float)

    # Get rid of Mint columns that we don't care about
    df.drop(['Category', 'Original Description','Notes'], axis=1, inplace=True)
    df.sort_index(inplace=True)
    return df

def sanity_check_data(df):
    """ Check that all transactions have the required
        CATEGORY fields and that appropriate categories
        also have a Unit field
    """
    # There should be no entries missing a CATEGORY
    if df[CATEGORY].isnull().values.any():
        print('Found transactions without a Label.  Clean up data before proceeding')
        quit()
    # Sanity check that all entries that should have a unit do
    unit_entries = df[(df[CATEGORY] == 'Utilities') | (df[CATEGORY] == 'Rent')]

    if not unit_entries[unit_entries['Unit'].isnull()].empty:
        print('Found Utilities and Rent transactions with no Unit:')
        unit_entries[unit_entries['Unit'].isnull()]
        print('Clean up data before proceeding')
        quit()
        
def load_util_limits(util_file):
    """Read a CSV that has a column for units and
       a column that includes the amount of untilities
       included in the rent for that unti
    """
    # Read in the monthly utilities max before reimbursement.
    try:
        limit_df = pd.read_csv(util_file, parse_dates=False)
    except IOError as e:
        print('Failed to open', util_file,':', e)
        print('Create one as described in the README')
        print('or set "SKIP_UTIL_ANALYSIS" to True in quarterly-reports.py')
        quit()

    limit_df.set_index(['Unit'], inplace=True)
    limit_df['Amount'] = limit_df['Amount'].astype(float)
    return limit_df

def get_report_range(year, quarter):
    # For Rental income extend the quarter +/- 10 days
    if REPORT_QUARTER == 1:
        start_date_rents = '%d-12-21' % (REPORT_YEAR -1)
        end_date_rents = '%d-4-10' % (REPORT_YEAR)
        start_date = '%d-1-1' % (REPORT_YEAR)
        end_date = '%d-3-31' % (REPORT_YEAR)
    elif REPORT_QUARTER == 2:
        start_date_rents = '%d-3-21' % (REPORT_YEAR)
        end_date_rents = '%d-7-10' % (REPORT_YEAR)
        start_date = '%d-4-1' % (REPORT_YEAR)
        end_date = '%d-6-30' % (REPORT_YEAR)
    elif REPORT_QUARTER == 3:
        start_date_rents = '%d-6-21' % (REPORT_YEAR)
        end_date_rents = '%d-10-10' % (REPORT_YEAR)
        start_date = '%d-7-1' % (REPORT_YEAR)
        end_date = '%d-9-30' % (REPORT_YEAR)
    elif REPORT_QUARTER == 4:
        start_date_rents = '%d-9-21' % (REPORT_YEAR)
        end_date_rents = '%d-1-10' % (REPORT_YEAR + 1)
        start_date = '%d-10-1' % (REPORT_YEAR)
        end_date = '%d-12-31' % (REPORT_YEAR)
    else:
        print('Invalid Quarter specified: ', REPORT_QUARTER)
        quit()
    # Not currently using the extended period
    return start_date, end_date

def generate_report(df, start_date, end_date):
    """Examine the transaction data for the time period
       and genrate a summary of the transactions for review
    """
    # Remove any transactions that had to do with collecting or returning security
    security_df = df[(df[CATEGORY] == 'Security') | (df[CATEGORY] == 'Security-Income')]
    df = df[(df[CATEGORY] != 'Security')]

    # Exclude the data for everything except our quarter
    period_data = df.loc[start_date:end_date]  # Note: NOT using extended quarter range
    rental_income = period_data[period_data[CATEGORY] == 'Rent']
    utilities = period_data[(period_data[CATEGORY] == 'Utilities')]
    other_income = period_data[(period_data['Transaction Type'] == 'credit') & (period_data[CATEGORY] != 'Rent')]
    expenses = period_data[(period_data['Transaction Type'] == 'debit')]
    unpaid_util_overages = float(0)

    # print(rental_income)
    # print(other_income)
    # print(expenses)
    
    html_config.initialize()
    print(html_config.HTML_OPEN)

    print('<H1>Income and Expense Report for %s-%s:' % (start_date, end_date), '</H1><p>')

    # List all unit specific rents and expenses for the quarter
    for UNIT in sorted(rental_income['Unit'].unique()):
        # Show rental income info
        temp_df = rental_income[rental_income['Unit'] == UNIT]
        print('<br><H2>Total rent for Unit ', UNIT, ': ${:,.2f}'.format(temp_df['Amount'].sum()), '</H2>')
        print(temp_df[['Description', 'Amount']].to_html())
       
        if not SKIP_UTIL_ANALYSIS:
            # Show utilities payments and calculate any overage due
            temp_df = utilities[(utilities['Unit'] == UNIT) & (utilities['Transaction Type'] == 'debit')]
            print('<br><H2>Utilities Expenses for Unit', UNIT, ': ${:,.2f}'.format(temp_df['Amount'].sum()), '</h2>')
            overage = temp_df.assign(Overage=lambda x: x.Amount - limit_df.loc[UNIT].Amount)
            # Disable warning when setting negative overage values to zero
            pd.set_option('mode.chained_assignment', None)
            overage.Overage[overage.Overage < 0] = 0
            pd.set_option('mode.chained_assignment', 'warn')
            print(overage[['Description', 'Amount', 'Overage']].to_html())
            # Show if utilties costs exceeded allotted amount
            if (overage['Overage'].sum() > 0):
                print('<h3>Utilities overage for unit', UNIT, ': ${:,.2f}'.format(overage['Overage'].sum()), '</h3>')
                unpaid_util_overages += overage['Overage'].sum()
            # Show any untilities that were collected    
            overage_collected = utilities[(utilities['Unit'] == UNIT) & (utilities['Transaction Type'] == 'credit')]
            if not overage_collected.empty:
                print('<br><H2>Util overages collected for Unit ', UNIT, ': ${:,.2f}'.format(overage_collected['Amount'].sum()), '</H2>')
                print(overage_collected[['Description', 'Amount', CATEGORY]].to_html())
                print('<h3>Net Utils overage for unit', UNIT, 'for period: ${:,.2f}'.format(overage['Overage'].sum() - overage_collected['Amount'].sum()), '</h3>')
                

        
            # Generate unit specific Utility usage reports
            if GEN_TENANT_UTIL_REPORTS and OUTPUT_DIRECTORY:
                TENANT_FILE = '%s/122-Spring-St-%s-%s-Unit-%s-utils.html' % (OUTPUT_DIRECTORY, start_date, end_date, UNIT)
                TENANT_REPORTS.append(TENANT_FILE)
                sys.stdout = open(TENANT_FILE, 'w')
                print(html_config.HTML_OPEN)

                print('<H1>Unit', UNIT, '</H1>')
                print('<br><H2>Utilities Expenses for: %s-%s' % (start_date, end_date))
                print('<br>Utilites included in rent: ${:,.2f}'.format(limit_df.loc[UNIT].Amount))
                print('</H2>')
                print(overage[['Description', 'Amount', 'Overage']].to_html())
                # Show if any utilties overage may be due
                if (overage['Overage'].sum() > 0):
                    print('<h3>Utilities overage for unit', UNIT, ': ${:,.2f}'.format(overage['Overage'].sum()), '</h3>')
                if not overage_collected.empty:
                    print('<br><H2>Util overages collected for Unit ', UNIT, ': ${:,.2f}'.format(overage_collected['Amount'].sum()), '</H2>')
                    print(overage_collected[['Description', 'Amount', CATEGORY]].to_html())
                    print('<h3>Net Utils overage for unit', UNIT, 'for period: ${:,.2f}'.format(overage['Overage'].sum() - overage_collected['Amount'].sum()), '</h3>')

                print(html_config.HTML_CLOSE)

                # Restore stdout to the main report file
                sys.stdout = open(REPORT_FILE, 'a')
        
        # Show other unit specific transactions
        if SKIP_UTIL_ANALYSIS:
            unit_exp = expenses[(expenses['Unit'] == UNIT)]
            unit_income = other_income[other_income['Unit'] == UNIT]
        else:
            unit_exp = expenses[(expenses['Unit'] == UNIT) & (expenses[CATEGORY] != 'Utilities')]
            unit_income = other_income[(other_income['Unit'] == UNIT) & (other_income[CATEGORY] != 'Utilities')]
            
        if not unit_exp.empty:
            print('<br><H2>Other Unit specific expenses for: ', UNIT, ': ${:,.2f}'.format(unit_exp['Amount'].sum()), '</h2>')
            print(unit_exp[['Description', 'Amount', 'Unit', CATEGORY]].to_html())
        print('<p>')
        
        # Show any other unit specific credit
        other_income = other_income[other_income['Unit'] == UNIT]
        if not other_income.empty:
            print('<br><H2>Expense offsets for Unit ', UNIT, ': ${:,.2f}'.format(other_income['Amount'].sum()), '</H2>')
            print(other_income[['Description', 'Amount', CATEGORY]].to_html())
            
        # Add a line between units
        print('<hr>')
            
    # List the shared income and expenses for the quarter
    temp_df = other_income[other_income['Unit'].isnull()]
    if not temp_df.empty:
        print ('<br><H2>Non unit specific income: ${:,.2f}'.format(temp_df['Amount'].sum()), '</h2>')
        print(temp_df[['Description', 'Amount', CATEGORY]].to_html())
    gen_expenses = expenses[expenses['Unit'].isnull()]
    if not gen_expenses.empty:
        print ('<br><H1>Non unit specific expenses</h1>')
        # Get the list of expense categories and generate summary for each
        for category in sorted(gen_expenses[CATEGORY].unique()):
            temp_df = gen_expenses[(gen_expenses[CATEGORY] == category)]
            print ('<br><H2>'+ category +': ${:,.2f}'.format(temp_df['Amount'].sum()), '</h2>')
            print(temp_df[['Description', 'Amount', CATEGORY]].to_html())
        
    # If there were any security transactions in the period give a security report
    if not security_df.loc[start_date:end_date].empty:
        temp_df = security_df.loc[start_date:end_date] 
        print('<hr><H2>Security related transactions:</H2>')
        print(temp_df[['Description', 'Amount', 'Transaction Type', 'Unit']].to_html())
        for UNIT in sorted(rental_income['Unit'].unique()):
            unit_df = security_df[security_df['Unit'] == UNIT]
            collected = unit_df[(unit_df['Transaction Type'] == 'credit')]['Amount'].sum()
            returned = unit_df[(unit_df['Transaction Type'] == 'debit')]['Amount'].sum()
            print('<center><H4>Current Liability on Unit '+str(UNIT)+': ${:,.2f}'.format(collected-returned), '</H4></center>')
                    
    # # Summarize the periods income and expenses -- old way to be discarded...
    # print('<br><H3>Total income for period: ${:,.2f}'.format(rental_income['Amount'].sum()), '</H3>')
    # print('<H3>Total expense for period: ${:,.2f}'.format(expenses['Amount'].sum() - other_income['Amount'].sum()), '</H3>')
    # print('<H3>Profit for period: ${:,.2f}'.format(rental_income['Amount'].sum() + other_income['Amount'].sum() -expenses['Amount'].sum()), '</H3>')
    # Summarize the periods income and expenses
    print('<br><H3>Total income  for period: ${:,.2f}'.format(rental_income['Amount'].sum()))
    print('<br><H3>Total expense for period: ${:,.2f}'.format(expenses['Amount'].sum() - other_income['Amount'].sum()), '</H3>')
    print('<H3>Profit for period: ${:,.2f}'.format(rental_income['Amount'].sum() + other_income['Amount'].sum() -expenses['Amount'].sum()), '</H3>')
#    print('</H3>')
        
    print(html_config.HTML_CLOSE)
    sys.stdout.flush()

    

# Read in the data to run the reports
print('Loading transactions from', PATH_TO_YOUR_TRANSACTIONS)
df = load_transaction_data(PATH_TO_YOUR_TRANSACTIONS)
if not SKIP_UTIL_ANALYSIS:
    limit_df = load_util_limits(PATH_TO_UTIL_LIMITS)

# Validate good data
sanity_check_data(df)

# Determine the data range for the quarterly report if the
# start and end dates were not expliciltly set
try:
    start_date
    end_date
except NameError:
    start_date, end_date = get_report_range(REPORT_YEAR, REPORT_QUARTER)

# Set the output file
if OUTPUT_DIRECTORY:
    REPORT_FILE = '%s/122-Spring-St-%s-%s-report.html' % (OUTPUT_DIRECTORY, start_date, end_date)
    sys.stdout = open(REPORT_FILE, 'w')

# Generate the report
print('Generating reports...', file=sys.__stdout__)
generate_report(df, start_date, end_date)

# Show the tenant reports if they were generated
if GEN_TENANT_UTIL_REPORTS and OUTPUT_DIRECTORY:
    for REPORT in TENANT_REPORTS:
        webbrowser.open('file://' + os.path.realpath(REPORT), new=2) # new=2: open in a new tab, if possible

# Show the report in a webbrowser
webbrowser.open('file://' + os.path.realpath(REPORT_FILE), new=2) # new=2: open in a new tab, if possible