Retired and Loving It
The introduction to this series told you a little about the #OldManInTheWoods’ working career and passion for learning how to program computers. I guess in retrospect, my passion was really about finding ways to make my job (and others’) more efficient/streamlined/effective – you pick a term.
When I retired, I had some unfinished business with that snake. I had begun learning how to use python to load data directly into Excel. My goal was to take a large dataset, clean it up and organize it using python, then load it directly into an Excel table so an analyst could work with it to do their job. I had just learned how to use one of the python modules, called pandas, that allows you to work with really large datasets.
The problem was that I hadn’t found a real-world problem to use this on before I retired. I started with good intentions; study the documentation, learn how to work with pandas, maybe even re-write some of my old cost accounting code to use this new tool. My problem was that without a real-world problem to solve, I was just doing a copy/paste of someone else’s code. Don’t get me wrong, you can learn a lot by doing this, but I’ve found that I don’t learn enough detail that way. I need to start with a problem, try to design a solution, and then build the code to solve that problem. I always learn more that way and, for some reason, it sticks with me better.
Finding my problem
I was elected to serve on the board of our local church, in the Episcopal Church which is called the Vestry, and was asked by our Rector to serve as Senior Warden. This meant that I was on the finance committee – now you see how the bean-counter got involved. The first month or so on the finance committee, I played along without trying to stir up anything. We have an outside bookkeeper that produces financial statements every month and sends them via email in a PDF report – usually about 20 pages. The statements consist of a Balance Sheet (pages 1&2), monthly income statement (pages 3-11), year-to-date income statement (pages 12-18), and a bank statement reconciliation (pages 19&20). There’s also a separate report that’s a detailed ledger showing all of the transactions by account and date. This is all fine until you start trying to analyze these statements.
The Balance Sheet was pretty straightforward, I won’t go into details, don’t worry. The real problem was the income statement. Say we’re looking at the month of December and Property Insurance is $300 with a budget of $100. Wow, 200% over budget (doing this math in my head because the report only shows the dollar amount) is a significant variance. My first response is to look at year-to-date to see if we’re over by the same amount. But to do that, I need to go from page 6 to page 9, remember the account number, look it up and see if the variance is the same YTD. Wait…what was the monthly variance – back to page 6 then back to 9 only to find out we paid a quarterly premium and YTD is right on budget. Just to be certain, dig through the detailed ledger to confirm – yep quarterly premium. Get the picture(?) – here’s my problem. And there’s an easy solution, put the month and YTD reports on the same page, just like every hospital income statement I ever saw. At least then, we’ll be able to get a better idea if the variance is significant or maybe just a timing issue.
One…Try the easy way
Having identified the problem, and with a solution in mind, I set about trying to implement the solution. Contact the Parish Administrator to ask the bookkeeper to put the month and YTD income statements on one page. Huh? “Can you give me a mock-up of what you want to see?” Sure no problem – it looks like this
After about a week the Parish Administrator forwards me the answer – “this is not part of our standard report package. We can do it but there will be a set-up fee and an extra monthly charge.” !!?! Now remember, I’m doing this for my church – don’t say what you’re thinking Wiley, and really you shouldn’t even be thinking it.
2… deep breath and try again.
Back through the Parish Admin pipeline – How about if you send us the financial statements in Excel? I can mash-up (that’s a technical term) the month and YTD into one statement and get it done that way. Another week later… (Warning – this is not really a quote – more a paraphrase or the way I heard it ) “No. Our policy is not to send out financial statements in Excel format. The client is likely to mess them up and we want you to have the correct statements.” !!!??!! Lord, please help me not say what I’m thinking. #ShoppingBeauty used to tell me to breathe in through one nostril and out the other at times like this.
3… Third time is the charm
After some time, and the deep breathing exercise, I was able to respond. “I understand your concern. Would you at least give us the detailed ledger in Excel format?” The response this time was more positive and a few days later we got a sample. It looked just like the printed version – and I mean just like it (you’ll understand later). So we asked to include the Excel ledger in the monthly transmission with the preliminary financial statements – no extra charge!
Enter the rogue bean-counter
Now we have a detailed ledger that has an account number, date description, and transaction amount. This is all I really need to make my own income statement – sneaky rogue. The first problem I noted is that we need to sum up all transactions by month, not by date. Easy solution in Excel – EOMONTH() formula for each line.
The next problem was a little more difficult to solve. Remember I said the ledger looked just like the printed report? Well, that means the account number may be in column A, B, or C depending on the type of account – the more detailed account is in column C. This, too can be solved with an IF formula, but the account number is only shown for the entry for each account. This is now a two-dimensional problem. We have to fill in blanks left-to-right and top-to-bottom. It can be done, but this creates a lot of formulas to copy & paste each month and you also have to account for cells that should be blank, e.g. lines skipped after a total.
Enter the snake
Now I didn’t sign on for this to be a full-time job and remember the passion was to make things more efficient. Python has a way to solve this and it gives the #OldManInTheWoods a reason to learn more about the Excel and Pandas tools. This is getting fun! If you want to go deep into these woods with me, you can read the details in the next post, or you can just read on to skip to the good stuff.
With a prototype built in Excel, I knew the steps that needed to be done to clean the data, so I began coding the solution in python. I built a program to:
- Load the data into a pandas table
- Fill in the blanks where appropriate in the account number
- Calculate and store the month-end date value so Excel wouldn’t have to calculate that with every refresh (I could go down a rabbit hole here but I’ll spare you)
- There are a few other pre-processing tasks but I’ll spare you these details
- Write the final version out to a CSV (stands for Comma Separated Values) that I can easily load back into Excel.
More Excel fun
With the cleaned data, I built a table in Excel that I can use for my report. The final version of the report allows me to select a month for my report. When the month is selected, the report populates with the monthly values and YTD on the same line so it’s easy to do the first step of the analysis that I described earlier. Oh, and I added check figures so the #OldManInTheWoods’ report isn’t “messed up”. But that’s not good enough, is it?
To see if a variance is reasonable, sometimes you have to look at the details to see what transaction caused the variance. Remember, we now have the detailed ledger in Excel and it’s been cleaned up so we can use it. So… back to the drawing board for version 2.0. In this version, I added another tab to the Excel report that allows me to enter an account number from the Income Statement and choose month or YTD, “et Voila!!” as they say in France, we have the details we need. Even more fun!! (Right?)
But there’s a wrinkle (or two)
So things rocked along for a couple of months and the #OldManInTheWoods was feeling pretty good about himself when one day I got an error. The tab named “YTD” in the input file provided by the bookkeeper was now called “Sheet 1”. Tricky! Oh well, just a simple change to the code that reads the file and we’re back in business. That is until “Sheet 1” became “YTD” again – stinker!! Then three extra lines got added to the beginning of the file with some information about how it was created.
So it’s not completely automated – can’t just set it and forget it as the commercial says. But we’ve used this report for about six months with minimal babysitting by the #OldManInTheWoods. Then it was time to do the budget for the new year. Oh man, that was easy – just like in the old days when I was building hospital budgets but with fewer accounts to deal with and way less pressure.
This was a fun project and gave me a chance to dig into the details of the pandas module in python. But… don’t ask me to start keeping office hours – I’ve got to get back to the woods!!