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.
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.
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:
### # 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.
# 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…
Are you interested in a custom Excel plugin? We can help.
Contact us at firstname.lastname@example.org.