Monthly Payment Tracker spreadsheet

Increasingly the various businesses that I pay monthly (rent, garage, credit cards, phone bill, newspaper bill, and utility bill) are only notifying me that I have a payment due by email or worse, not notifying me at all.  Combined with a shrinking window between when the bill is issued and when late charges start to accrue, there is increasing pressure on me to track my bills.

In the past I would accumulate unpaid bills on a designated spot on my desk at home.  The presence of paper bills there told me that I had bills to pay.  When a paycheck rendered me sufficiently liquid to write checks, I would pay them.

As the transition to email notification and non-notification has progressed, many of these bill issuers have offered to help me out with this scheduling problem.  All I had to do was give them the ability to directly take money from my bank account in whatever amount they desired and at whatever time they liked.

You’re kidding, right?

Not all of these institutions are crooked, and perhaps none of them are crooked today, but the documented behavior of some of their peers has convinced me that I should not trust any of them to take only what they are owed from my account.

So, how to keep on top of these due dates?

The first step was to create a spreadsheet.  Each column was a payee.  Each row was a date.  When I paid bills I would insert a row and put that day’s date in the leftmost column and put dollar amounts in each column corresponding to a bill I had paid that day.

It ended up looking something like this:

Date Shady Bank Weird Bank Odd Bank Rent Parking Pa Bell
2017-02-01 XXX YYY
2017-02-13 ZZZ WWW
2017-02-25 QQQ
2017-02-26 VVV

It helped me keep an eye on things, particularly after I set up some color coding so that the background of odd-numbered months was different from that of even-numbered months.

But I was still not happy, since the payment windows established by the banks were becoming smaller and smaller.

I decided to figure out a formula that I could put in the top of each column, right below the column headings, that would tell me how many days had elapsed between the most recent payment and today.

How to do that?

The top level structure of the formula was “=today()-(something)” where something translated to the date in column A of the row with the most recent payment.

How to find the row number of the most recent payment?

With some research, I finally came up with this formula – in this case for Column E:

=today()-index($A:$A,max((E3:E<>””)*row(E3:E)))

This is pretty weird … here’s how it works:

max((E3:E<>””)*row(E3:E)) finds the highest row number in column E on or after row 3 that contains a nonempty value.

index($A:$A, max(…)) finds the value in column A at the row identified in the max(…) formula above.

How did I figure out the idiom index(column, max(…))?  I’d like to claim that I’m a master of Google Sheets and read relevant documentation, but it ain’t so.  I did some searching and found this StackOverflow page that illustrated the idiom.  I had to adapt it significantly for it to work properly, but now I have a row of values at the top of the columns, just below the headings and in the frozen part of the sheet.  Each cell reports the number of days since the last payment in that column.  Here’s the complete formula for column G:

=today()-index($A:$A,max((G3:G<>””)*row(G3:G)))

I have also established conditional formatting for that row so that when more than 20 days has passed since that bill was paid the time is highlighted in red to draw my attention to it.

So now it looks something like this:

Date Shady Bank Weird Bank Odd Bank Rent Parking Pa Bell
16 16 3 29 29 4
2017-02-01 XXX YYY
2017-02-13 ZZZ WWW
2017-02-25 QQQ
2017-02-26 VVV

Now that’s funny

A friend of mine told me that she had logged in to the Social Security Administration’s website recently and was able to review her account and get a forecast of her Social Security retirement income, should she make it to retirement.

Intrigued, I decided to do the same thing.

I went to the website (www.ssa.gov) and tried to create myself an account.  I entered my Social Security number, my name, my address, my date of birth, all sorts of information.

After a while it told me that it could not create an account for my social security number.  It suggested that I call the help desk and gave me a toll-free number.

So I called the number and followed the instructions to get to the help desk.

The help desk person was very nice.  She asked me all of the same information that the website had asked.

She confirmed that the site would not activate access for me.  She asked me if I had bad credit.  I said no, I think I have excellent credit.  She asked me about my mortgage, and I told her that my wife and I had paid off our mortgage a few years ago.

The woman told me that that explained the problem.  It seems that the Social Security Administration outsources the identity verification function to one of the big credit bureaus.  The credit bureau can not verify identity for folks without significant debt, it seems.

The only way for me to get an online account with the Social Security website is to take my passport or other identification to a Social Security field office and identify myself to them.

How absurd.

If I have paid off my mortgage, I can’t get access to the Social Security website without a personal visit to a field office?

Well, I work for a living and I can not afford to take time off from work to go visit the Social Security field office in order to get access to the website.  Too bad that the identity verification service doesn’t work for folks without debt.

 

Buying notebooks …

Well, I’m trained as a scientist and engineer, so I keep a notebook. This is something I have done religiously since I was in grad school, much to my wife’s dismay.

Since 1991 I have loved the National brand Chemistry Notebook (number 43-571), but National was bought a few years ago and the new owners cut a stupid corner by reducing the notebook from 128 pages to 120. Worse yet, this notebook has become rather expensive to buy, costing upward of $10 per book. The pages are still numbered for me, but the reduction from 128 to 120 remains an irritant. national-brand-43-571-chemistry-notebook

So, when I recently changed jobs and, at the same time, ran out of notebooks I decided to switch to the Clairefontaine 9542C.  This is a smaller notebook with paper that is slightly more opaque and quadrille ruled 5×5 to the inch.

9542C_3

Oddly, despite the fact that it is made in France and described with metric dimensions (14.8 cm x 21 cm) the ruling is specified as 5×5 to the inch.  I agree that this is a convenient grid size for technical notebooks, but is there no metric ruling that matches?  0.5 cm comes to mind, since that would end up very close to 5×5 to the inch, since 5 x 0.5 cm is 2.5 cm, and 2.54 cm is an inch.  Perhaps it is marketed as 0.5 cm square grid in Europe but as 5×5 to the inch in the US?

Anyway, I needed to buy some more of these notebooks.  Normally I pick them up from a stationery store near my apartment, but that is inconvenient and expensive.

I tried looking for them on Amazon (amazon.com, to be precise).  While I can find them, it’s hard to tell which product is being sold because Amazon’s product information for these Clairefontaine notebooks is dreadful.  And they’re expensive.

After being frustrated by the unusually low quality of Amazon’s offerings I tried searching Google for “clairefontaine 9542c”.  To my surprise, I found an amazon.de page near the top of the organic results.  Even more of a surprise was the fact that it was offering five of these lovely notebooks for about 10 euros, or only a little bit more than I was paying for one in the US.

Not reading German I decided to try amazon.co.uk.  There I found these notebooks, again better described, priced at ten pounds for a package of five.  I ordered two packages.  Even with shipping to the US these notebooks come out at about half the price that I pay for them in the US.

A Fine Tunnel

Some years ago I went skiing with some Italian friends.  I flew to their home in Pisa and we drove north along the west coast of the country to Sestriere, at the triple juncture of Switzerland, France, and Italy, a lovely ski area.

The road we took went through the area of Genoa.  This particular road is a high-speed limited access highway.  Probably the A12 according to modern maps.  In this section the coast is very steep, almost cliffs running down to the compact city of Genoa.  One  section of the road is particularly dramatic, an alternating sequence of bridges and tunnels through the steeply inclined terrain above Genoa.

Anyway, as we drove north we went through one of the longer tunnels.  As we were in the middle of this particular tunnel I saw a sign that gave me a peculiar Alice in Wonderland sensation.  The sign, a professionally executed one with all the hallmarks of the highway system, showed an outline of a cup of coffee, a little wisp of steam proceeding from its top.  With the picture of the cup of coffee was the text, “A fine tunnel.”

Yes, I reflected for a moment, it is indeed a fine tunnel, but why have such a sign?  Shortly thereafter I realized that I had read the sign in the wrong language.  It was not praising the qualities of the tunnel in English but rather alerting tired drivers to the fact that there was a place to get a cup of coffee at the end of the tunnel (read it aloud as ‘ah feen-eh toon-nel’ to hear how it would sound to Italians).

My host, the driver of the car, and I laughed at my initial reaction.  A fine tunnel, indeed!