Photo credit to Rebecca Matthews
I have written extensively about the importance of personal financial accounting, how accounting systems should be designed, and what they need to show visually. Here we are going to take a look at the system I designed in Python. It is a trip down memory lane for me because it was the first "serious" coding project of my life (over 100 lines of code), the first time I used Python, and the first time I wrote a recursive function.
The software is an implementation of an important concept called double-entry accounting where the database is a collection of transactions and each transaction affects the balance on two accounts. To implement this we need a text file which names the accounts, their relations to one another, and the transactions which have occurred between them. Computer friends often complain about this step. "Why not SQL?" "Make a database." SQL and other databases are powerful tools; it is important that we have them and know how to use them. However, having a hammer does not make every problem a nail. It is the hallmark of poor engineering to imagine otherwise. Database technology is important when dealing with large datasets (genomic information, user data on a social media platform, etc) or rapidly changing datasets (the inventory in a retail store, etc). Personal financial data is neither large nor rapidly changing. Using any kind of database comes with a special problem called technical debt. The databases of yesteryear have all depreciated. No one can open them, no one can use them, and the data in them which was not updated is now lost permanently unless it is valuable enough to warrant re-engineering that database software to read the contents. Text files do not depreciate. Every working computer outside a museum can read my accounting file because it is written in ASCII text. Every computer built until I die will be able to read my accounting file.
Personal financial data does not change over time. Whatever financial transactions I engaged in on June 10, 2010 are fixed; they do not change as time marches on. Financial data grows as I participate in financial activity but it does not change retrospectively. Financial data is also small. I have 8 years of financial data in a text file: a record for every time money entered or left my posession or moved from one account to another. That file is 1.3 MB, less data than a single photograph, and it holds 1/8 of a lifetime's worth of financial data (assuming I experience 65 years of adulthood). I am not going to run out of storage space.
accounting_blog.txt
# account code; Account_name; account codes of child accounts
oo___;Outside # account used to k'
mALL_;Total_Wealth;mcc__;mcr__;metr_
mcc__;Checking
mcr__;Credit_card
metr_;Etrade_brokerage
mMMMM;Month_Balance;mtot_;minc_;
mtot_;Total_Expenses;mrh__;mheal;maa__;mgf__;mfit_;msk__;mmi__;mtick
mrh__;Rent_Housing_Utilities
mheal;Health_Care
maa__;Automotive/Transportation_Expenses
mgf__;Groceries
mfit_;Fitness/Training
mmi__;Miscellaneous;mfr__;mclos
mfr__;Restaurant_Food
msk__;Education/Professional_Expenses
mclos;Clothing
mtick;Taxes_Citations_and_Tickets
minc_;Income;mjob_;mrent
mjob_;Payroll_from_job
mrent;Rent_from_roommate
## Transactions
2010-01-01; Starting balance in checking account ; 6205.82;oo___mcc__
2010-01-01; Starting balance in brokerage account ; 30600.12;oo___metr_
2010-01-01; Starting balance on credit card ; 1654.18;mcr__oo___
# These transactions aren't real, they just account for everything which occurred before record keeping began
2010-02-11; MY1CAR - Gas - Chevron - 9.339 - 735.7 - 37142 ; 20.54;mcr__maa__
2010-02-11; Shooting range with friends after work ; 10.00;mcr__mmi__
2010-02-13; Batteries for emergency kit in car ; 24.68;mcr__mmi__
2010-02-19; Phone charger ; 12.43;mcr__mmi__
2010-02-16; MY1CAR - Gas - Chevron - 10.018 - 377.6 - 37520 ; 25.03;mcr__maa__
# this list would continue at a rate of 50-200 transactions per month for the vast majority of people
To run the code we need to modify four lines.
report_blog.py
< 6> file_p = 'accounting_blog.txt' # file path of the accounting data file
< 7> backup_p = '< backup filepath>' # file path where we want to save backup data files
<288> fils,header = monthrep(2010,2) # year, month of the report we would like to generate
<307> #backup(file_p,backup_p) # uncomment this line if running the backup script
Then open a Python interpreter and run the report script.
>>> exec(open('report_blog.py').read())
Total Wealth ................................................... 37601.27
Checking ....................................................... 7458.76
Credit card .................................................... -527.61
Etrade brokerage ............................................... 30600.12
My wallet ...................................................... 70.00
February 2010
Month Balance - 15, 0
Total Expenses - 13, 0
Rent Housing Utilities
2010-02-28 Rent on apt .................................................................... 850.00
Total ................................................................................. 850.00
Health Care
2010-02-21 Doctor visit for cough - copay ................................................. 50.00
Total ................................................................................. 50.00
Automotive/Transportation Expenses - 4
2010-02-11 MY1CAR - Gas - Chevron - 9.339 - 735.7 - 37142 .............................. 20.54
2010-02-16 MY1CAR - Gas - Chevron - 10.018 - 377.6 - 37520 .............................. 25.03
2010-02-21 MY1CAR - Gas - Texaco - 9.038 - 353.1 - 37873 .............................. 20.42
2010-02-27 MY1CAR - Gas - Raceway - 9.795 - 364.7 - 38237 .............................. 19.97
Total ................................................................................. 85.96
Groceries
2010-02-16 HEB ............................................................................ 169.97
Total ................................................................................. 169.97
Fitness/Training
2010-02-28 Rogue fitness lifting belt ..................................................... 98.35
Total ................................................................................. 98.35
Miscellaneous - 5, 3
2010-02-11 Shooting range with friends after work ......................................... 10.00
2010-02-13 Batteries for emergency kit in car ............................................. 24.68
2010-02-19 Phone charger .................................................................. 12.43
Subtotal .......................................................................... 47.11
Restaurant Food
2010-02-16 Reimbursed Phil for work lunch ................................................. 10.00
Total ................................................................................. 10.00
Clothing
2010-02-28 Walmart jeans .................................................................. 23.05
Total ................................................................................. 23.05
Total ................................................................................. 80.16
Total ................................................................................. 1334.44
Income
Payroll from job
2010-02-14 Payroll from work .............................................................. -1279.04
2010-02-24 Payroll from work .............................................................. -1279.04
Total ................................................................................. -2558.08
Total ................................................................................. -2558.08
Total ................................................................................. -1223.64
By running this in the interpreter we have access to an interactive set of functions which make it easier to balance accounts with financial institutions. repf takes three mandatory variables — an account code, a boolean (whether the report is short or long), and a boolean (whether the account total is a sum of every transaction or just every shown transaction). For example, when looking at a checking account, we want the total to include all the unshown transactions and we want a long report.
>>> repf('mcc__',True,False,start=20100215)
Checking - 5
2010-02-28 Rent on apt .................................................................... -850.00
2010-02-16 ATM withdrawal ................................................................. -80.00
2010-02-24 Payroll from work .............................................................. 1279.04
2010-02-28 Paid credit card ............................................................... -1654.18
2010-03-14 Payroll from work .............................................................. 1279.04
Total ................................................................................. 7458.76
27 total transactions, 17 screened transactions
When looking at a spending category, we only want the total to reflect the displayed transactions.
repf('mgf__',True,True,start=20100215)
Groceries
2010-02-16 HEB ............................................................................ 169.97
Total ................................................................................. 169.97
27 total transactions, 17 screened transactions
If the second boolean is false, the sum reflects not only the groceries from 2/16 but also the three transactions at HEB in January.
>>> repf('mgf__',True,False,start=20100215)
Groceries
2010-02-16 HEB ............................................................................ 169.97
Total ................................................................................. 425.01
27 total transactions, 17 screened transactions
If the first boolean is false, no transactions are displayed.
>>> repf('mcr__',False,False)
Credit card .................................................... -782.65
27 total transactions, 0 screened transactions