analyze-property

This project contains a set of utilities that enable the generation of reports based on an export of mint transaction data associated with an income property. Among other things it can generate an Income and Expense detail report for a time period, and also generate reports for tenants with leases that require them to pay any portion of utilities above a certain amount included in the rent.

Checklist to run the utilities

The utilities in this package are written in python. While it is not necessary for users of the package to write or understand python, you should be comfortable working in a command line environment and installing the necessary python environments required to run the scripts.

Prerequisites:

Create a local copy of the repo, using either:
- the git cli by typing git clone https://github.com/jpjpjp/analyze-property
- or by navigating to the repo on github, and clicking the green Code button and choosing "Download Zip"
The python runtime used is conda. If this is not yet installed please, download and install minconda
Once properly installed run the following command in the main project directory:

conda env create -f environment.yml
This will create the environment for the scripts to run in and download all necessary dependencies. After this process completes activate the new conda virtual environment:

conda activate analyze-property

This will "activate" the anaconda environment that will allow python to run. (For those using an autoenv type utitlity, there is also a .env file for automatically activating the environment whenever the directory is entered.)

Preparing the data

This project was built to process transaction data exported from Mint, although any CSV of transaction data could potentially be used. My mechanism for managing the data in Mint is to assign all transactions related to a particular property to a single mint "Category" named after the property.

For any given property related transaction, I then set one or more "labels" on the transaction. By my own convention, I create labels that begin with the prefix "Label-". The scripts in this project expect labels such as:

Label-Rent -- rental income
Label-Utilities -- if transactions are classified as Utilities, some additional analysis is done to compare costs (if the property owner pays utility bills) and income (if tenants reimburse property owner)
Label-Security -- this label is used for security fees collected and returned and is also removed from the P&L analysis
Label-Closing -- costs associated with the original closing of the property. These transactions are excluded from the year-over-year P&L analysis and used as the basis cost to determine the annual ROI.

Other labels (eg: Label-Repairs) can be set as well, but none of these utilities require them.

For transactions that are specific to a particular Unit, I also specify a "Unit-" label with the unit name. This allows the tools here to provide details about income and expense on a per unit level. The Label-Unit label is required for transactions that also have the Label-Rent or Label-Utilities label.

In addition to columns which provides categorization information, this project assumes the following standard Mint fields are also available:

Date -- A string in datetime format, ie: 01/05/2018
Description -- A description of the transaction
Amount - The amount of the transaction. This will be interpreted as a float
Transaction Type -- Either "credit" or "debit" to indicate an expense or income. The project currently expects at least one "credit" and one "debit" in any given year's worth of transaction.

Finally, any category that has a "Transaction Type" of both "debit" and "credit" is assumed to be an expense, where the credits are ultimately subtracted from the debits to determine overall expense. Categories that have only "credit" (ie: "Rent") are categorized as income. If there is a "debit" transaction that has the "Label-Rent" the reports may not work correctly without some data or code massaging.

Preparing the rent and utility limit data

Some analysis will be performed at a per unit level. Our property includes per-unit Utilities meters, but we pay the bills and include a certain amount of utilities in the rent. When the monthly utility bill exceeds this limit, the tenant(s) are charged an overage fee.

In order for this to work the following additional steps need to be taken

All transaction data with a Label-Rent or Label-Utility label, must also include a Unit-XXX label.
This project directory must contain a csv File that enumerates all the units and specifies the amount of utilities cost included in the rent. An example of what this file might look like is:

Unit Amount

FirstFloor 100

SecondFloor 90

Unit	Amount
FirstFloor	100
SecondFloor	90

In this example the maintainer of the data would label rent and utility transactions in mint with a Unit-FirstFloor or Unit-SecondFloor label for any transaction that was also labeled Label-Utility

These tools expect this CSV to be called "util-limits.csv", however this can be overridden my modifying the value of the global PATH_TO_UTIL_LIMITS variable in quarterly-report.py. If your tenants pay for their utilities, or if they are included with no limits set SKIP_UTIL_ANALYSIS to True in quarterly-report.py.

Creating an initial Transaction report in Google Sheets

Since Mint transaction data does not include true "columns" for the transaction categories(labels), and units, we post process an export of the Mint data, where the Label-* and Unit-* labels are added to a new CSV with a Labels and Unit column. This data is maintained as a Google Sheets doc.

To create the initial transactions file:

Create a new blank Google Sheets document
From the File menu select import
Choose upload and select the initial-transactions-template-for-google-sheets.csv in this project
Under "Import Location" select the "Insert New Sheet(s)" option
Rename the new sheet "All Transactions"
Give the new Untitled Spreadsheet a name. (For example Property Transactions).
Examine the URL for the Google sheet and copy the Spreadsheet ID. It will look something like this https://docs.google.com/spreadsheets/d/1YfOleSudsfd6kmQFGRaLStsadfAeKSjBSEM-kfAo/edit#gid=1085075729. The spreadsheet Id is the long string after the "spreadsheets/d/", up until the "/edit#..."
Edit update-transaction-data.py, and change the value of the global variable SPREADHEET_ID to the string taken from the URL

Authorizing the Google Sheets API

These utilities use the Google Sheets GSpread API to read and update this "source of truth" each time new Mint transaction data is available. Prior to running this program for the first time, the Google Sheets API must be enabled, and a client must be created. This is described in the Authentication section of the GSpread API docs. This blog also describes both how to set up authorization and how our program creates a data frame from the Google sheets data.

Note that our program uses assumes that the authentication credentials are downloaded to a file named sheets-cred.json.

Processing the mint transaction data

The update-transaction-data.py script automates the process of updating the google sheets doc based on an export of mint transaction data. Prior to running this script, the user should log in to mint and filter the transactions to show only those that are categorized as being associated with a particular property. This set of transactions should the be exported to this project directory as a file called "mint-transactions.csv". (This name can be overridden by setting the global variable MINT_DATA in update-transaction-data.py).

The script will use the Google Sheets API to access a the sheet specified by the SPREADSHEET_ID variable. The script will download the existing transaction data in Google sheets and begin comparing it with the mint data. If the script outputs a Google Sheets API 401 error, ensure that it was properly authorized. If a 404 error is output, ensure that:

the SPREADSHEET_ID variable was properly set
the new spreadsheet has a name (is not still "untitled")
the sheet with the transaction row headers is named "All Transactions"

Assuming that the Sheets data is successfully fetched, each transaction in the Mint data, starting with the most recent, is checked against the Sheets data. If it does not already exist it is added to the list of new transactions, with the "Label-XXX" label added to the "Labels" field and the "Unit-XXX" label add to the "Unit" field, if applicable.

Note that if the script finds transactions in the mint data that do not have a "Label-" label, or are missing a "Unit-" label for a Rent or Utility transaction, it will print and error and stop running. When this occurs return to Mint, clean up the data, re-export and try again.

If you prefer not to require that a unit is specified for the Utilities transactions set REQUIRE_UNIT_WITH_UTIL_TRANSACTIONS to False in update-transaction-data.py.

Once a duplicate transaction is found, the script will continue to check for 7 more days worth of data in case a transaction was added to mint since the last time this script was run. Modify the LOOK_BACK_DAYS variable in update-transaction-data.py to look back further. This is useful if you updated or added a property related transaction several weeks or months earlier than you last ran this script.

Once all transactions are found, the new data is uploaded to Google Sheets and a local copy of the categorized data is saved in a "transactions.csv" file. (This can be changed by modifying the LOCAL_TRANSACTIONS_FILE global variable). This file is used as input for generating reports.

I typically try to update and label transaction data in mint at least once a week or so.

About 10 days after the end of each quarter, I find it helpful to run this report as a way of ensuring that all the transaction data was properly recorded in mint, while the data is still relatively fresh in my mind.

Setting up report generation

An HTML summary report for a period can be generated by running the quarterly-report.py script. The data range for the report can be controlled by adjusting the values of the REPORT_YEAR and REPORT_QUARTER variables before running the script.

Optionally, if a different period is desired, setting the start_date and end_date variables in YYYY-MM-DD format will generate a report based on those dates instead.

The following data is provided in the report for each unit

Rent collected for the period with a list of each rent transaction
Utility bills paid for the period with overages calculated for each month. Total overage for the unit for the period is also calculated
Any unit specific expenses and credits are also detailed

If any transactions with the "Security" label occurred during the period these are detailed and a summary of the existing Security liability for the property is provided.

Finally the total income and expense for the period is also provided.

Generating a quarterly report

The shell script run-quarterly-report will run both the update-transaction-data.py and quarterly-report.py scripts on the mint-transactions.csv file in the project directory.

If any mistakes are found I will typically delete all the most recent transaction data in google sheets, make the corrections in mint, export the latest mint-transactions.csv file and rerun the process.

Additional Reports and Visualizations

While the primary goal of this project is to massage property related transaction data from mint for the purposes of generating quarterly P&L reports, the repo includes some jupyter notebook files that provide visualizations and other potentially useful reports.

To run the notebooks enter the following in a command shell with the conda environment active: jupyter notebook

The following notebooks should be available in the list that was opened in your browser:

analyze_property_transactions

This notebook reads the data in a transactions.csv file (generated by the update-transaction-data.py utility), and visualizes the following:

Expenses and Income by year
Annual P&L and ROI
Expenses by category per year
Income and Expense by Unit

Variables that can be set in the first cell of the notebook:

PATH_TO_YOUR_TRANSACTIONS - CSV file with transaction data
PATH_TO_RENTS = CSV File with per unit rents. Format is
- one row per year, with year in first column
- one column per unit, with unit names matching unit labels in transaction data

An example of what the rents.csv file might look like is:

Year	FirstFloor	SecondFloor
2020	1000	900
2021	1100	950

analyze_security_transactions

This notebook finds all the security related transactions in the data and prints out the current outstanding liability per unit.

This notebook has the same variables as analyze_property_transactions

generate_supplemental_income_and_loss_tax_info

Creates a .csv file that provides all the data needed to fill out a Schedule E form at tax time.