A Python-Powered Budget Spreadsheet

By Cormac Purtill

This is a guest blog post written by a DataNitro user. If you'd like to post a user story, contact us!

October 23rd, 2013

I had some time between projects and wanted to pick up a new language for doing 'bits and bobs' - bolting things together, moving files around, and general administrative tasks. Shell scripts and batch files are ok, but not portable. I tried Perl a while ago, and while it's very powerful, it's what a colleague describes as 'write-only' - returning to some scripts after six weeks or so, I found it impossible to work out what was going on!

Python ticks all the boxes for me: it’s very clear and powerful, and it's easy to create or pull in modules that do whatever heavy lifting I need. I had a look at Google's introductory course on YouTube, and took to it like a duck to orange sauce.

A baby duck


One of the jobs I wanted help with was the monthly rollover of my finances spreadsheet. This spreadsheet keeps track of transactions my bank knows about, those *I* know about that haven't gone through yet, and those that I know will come up later in the month. I can't trust the bank's opinion of my balance, and this helps me track my 'real' balance, especially towards the end of the month. I also use it to track my spending in various areas (going out, groceries, etc).

Maintaining the spreadsheet – rolling it over, resetting the monthly budget, and a couple of other jobs were mundane and time-consuming, so decided to try automating them with Python. A few solutions come recommended on StackOverflow, but typically have caveats like "it's tricky but it works”. DataNitro was mentioned without any detail, so I thought I'd take a look. Just like Python, it's exactly what I was after – powerful, but very easy to get started with, and intuitive. I don't know why this combination is still so rare!

The first job is to clear the old month's transactions on my 'Cashflow' worksheet. These start off grey, and I turn them white when they show up on the bank's system. So to rollover, I need to wipe all the white ones, and move any leftover unprocessed grey rows up to the top.

Last month's budget data


The code snippet that does this is as follows. DataNitro calls are in red.

import cormac.utils as c # debug
import datetime
import os
import re
import string
import sys

#----Lots of constants snipped, _COL_THIS and _ROW_THAT----

###
# initialise
def initialise():
  debugStr = raw_input('Debug? (Y/N)')
  debug = (debugStr == 'Y') or (debugStr == 'y')
  c.init_debug(debug)

###
# Reset row
def wipe_row_cashflow(rowNum):
  c.debug('Wiping row', rowNum)
  CellRange((rowNum, _COL_CASHFLOW_DATE), (rowNum, _COL_CASHFLOW_CATEGORY)).value = None
  CellRange((rowNum, _COL_CASHFLOW_DATE), (rowNum, _COL_CASHFLOW_CATEGORY)).comment = ''

###
# Set all rows’ background to grey
def set_rows_grey():
  c.debug('Setting rows to grey...')
  for rowNum in range(_ROW_CASHFLOW_START,_ROW_CASHFLOW_END):
    CellRange((rowNum, _COL_CASHFLOW_PAYEE), (rowNum, _COL_CASHFLOW_WHO)).color = _COLOUR_GREY

###
# Move surviving rows up
def move_rows_cashflow(rowsToMove):
  c.debug('Moving rows', rowsToMove)
  destinationRow = _ROW_CASHFLOW_START
  for sourceRow in rowsToMove:
    # I think I can do this with copy_from – need to check
    # that copies any comment too 
    CellRange((destinationRow, _COL_CASHFLOW_PAYEE),
	          (destinationRow, _COL_CASHFLOW_CATEGORY)).value = \
      CellRange((sourceRow, _COL_CASHFLOW_PAYEE), (sourceRow, _COL_CASHFLOW_CATEGORY)).value
    CellRange((destinationRow, _COL_CASHFLOW_PAYEE),
	          (destinationRow, _COL_CASHFLOW_CATEGORY)).comment = \
      CellRange((sourceRow, _COL_CASHFLOW_PAYEE), (sourceRow, _COL_CASHFLOW_CATEGORY)).comment
    destinationRow += 1
    wipe_row_cashflow(sourceRow)

###
# Rollover Cashflow sheet
def rollover_cashflow():
  c.debug('Rolling over Cashflow sheet...')
  active_sheet(_SHEET_CASHFLOW)
  
  c.debug('Wiping old rows...')
  rowsToMove = [] 
  # For any row that could be populated, wipe it if it’s white
  for rowNum in range(_ROW_CASHFLOW_START,_ROW_CASHFLOW_END):
  if Cell(rowNum, _COL_CASHFLOW_AMOUNT).value != None and
     Cell(rowNum, _COL_CASHFLOW_PAYEE).color == 'white':
      wipe_row_cashflow(rowNum)
  elif Cell(rowNum, _COL_CASHFLOW_AMOUNT).value != None:
      rowsToMove.append(rowNum)
      
  c.debug('Moving existing rows up...')
  move_rows_cashflow(rowsToMove)
  
  set_rows_grey()

###
# main
def main():
  initialise()
  rollover_cashflow()
  #----snip----

main()

The next task is to reset two similar worksheets, ‘Budget’ (variable costs I know will come up, like phone and credit card bills), and ‘Fixed’ (fixed monthly costs, like insurance). These both have a column with a ‘y’ that gets set when the money goes out. I just need to clear these ‘y’s, and, for the variable costs, copy over any I know from a ‘next month’ column:

More of last month's budget


###
# Rollover Budget sheet
def rollover_budget():
  c.debug('Rolling over Budget sheet...')
  active_sheet(_SHEET_BUDGET)
  
  # Clear 'y' column
  CellRange((_ROW_BUDGET_START, _COL_BUDGET_Y), (_ROW_BUDGET_END, _COL_BUDGET_Y)).value = ''
  
  # Copy 'next month' values over
  for cell in _CELLS_BUDGET:
    cellDestId, cellSourceId = cell
    c.debug('Copying cell', cellSourceId, 'to', cellDestId)
    
    cellSource = Cell(cellSourceId)
    cellDest = Cell(cellDestId)
    cellDest.value = cellSource.value
    cellSource.value = '0.00'

###
# main
def main():
  initialise()
  rollover_cashflow()
  rollover_budget()
  rollover_fixed()
  #----snip----

main()

Next are two credit card worksheets – these work in the same way as the ‘Cashflow’ sheets above. A couple of sheets for things like savings balances don’t need any rollover work, but when I get some tinkering time I might get them to look up share prices and exchange rates.

The final job is to summarise the old month’s categorised spending to a summary sheet. When I got to this stage I realised it needs to be done before clearing all this information from the ‘Cashflow’ sheet. It’s useful to have the most recent month at the top rather than the bottom, so I insert this row first, and then populate it from a summary column on the ‘Cashflow’ sheet. CellRange works really nicely here – converting a column on one worksheet to a row on another.

Then, to sum the values (in Excel rather than in Python) uses .formula instead of .value.

Finally, a call to raw_input() keeps the window open until I’m finished with it.

The new, processed budget


# Update Summary sheet
def update_summary():
  c.debug('Updating Summary sheet...')
  active_sheet(_SHEET_SUMMARY)  
  insert_row(_ROW_SUMMARY_INSERT)
  
  # Set month to last month - this is a guess.
  # tbd use spreadsheet filename to get month
  today = datetime.date.today()
  firstOfMonth = datetime.date(day=1, month=today.month, year=today.year)
  lastMonth = firstOfMonth - datetime.timedelta(days=1)
  month = lastMonth.strftime("%b-%y")
  Cell(_ROW_SUMMARY_INSERT, _COL_SUMMARY_DATE).value = month
  
  # Populate row. Could do it 'by hand', but this is easier.
  # It relies on the destination columns being in the same order as 
  # the source rows.
  destCol = _COL_SUMMARY_START
  srcRow = _ROW_CASHFLOW_SUMMARY_START
  destRange = CellRange((_ROW_SUMMARY_INSERT, _COL_SUMMARY_START),
                        (_ROW_SUMMARY_INSERT, _COL_SUMMARY_END))
  srcRange = CellRange(_SHEET_CASHFLOW, (_ROW_CASHFLOW_SUMMARY_START, _COL_CASHFLOW_SUMMARY),
                       (_ROW_CASHFLOW_SUMMARY_START+_NUM_SUMMARY_FIELDS-1, _COL_CASHFLOW_SUMMARY))
  destRange.value = srcRange.value
  
  # Total
  formula = '=sum(' + str(_COL_SUMMARY_START) + str(_ROW_SUMMARY_INSERT) + ':'+
             str(_COL_SUMMARY_END) + str(_ROW_SUMMARY_INSERT) + ')'
  c.debug('formula is', formula)
  Cell(_ROW_SUMMARY_INSERT, _COL_SUMMARY_TOTAL).formula = formula
  
#----snip----

def main():
  initialise()
  
  update_summary() # Need to do this before editing Cashflow sheet
  rollover_cashflow()
  rollover_budget()
  rollover_fixed()
  rollover_creditcard1()
  rollover_creditcard2()
  
  c.output('Execution finished successfully.')
  c.output('Press any key to exit.')
  # Pause
  raw_input()

main()
  

So that’s it! This is a simple use of Excel, Python and DataNitro, but they all come together to make an irritating job simple.

Now to get Excel to talk to eBay…




Want to use Python in Excel?

Try DataNitro!

 


Are you interested in a custom Excel plugin? We can help.
Contact us at code@datanitro.com.