Next post Previous post

Security

April 24, 2019 at 9:01 PM by Dr. Drang

Today I spent some time with Keyboard Maestro, the Numbers app, a shell pipeline, and—you weren’t expecting this—the Social Security Administration. I managed to avoid some tedious data entry.

It started with my going to the My Social Security site to make sure my earnings records were correct. The SSA sends reminders to do this every once in a while, and like most people, I have usually ignored them. But last year I learned that my earnings and contributions for 2016 had not been recorded, and I had to jump through some hoops to get it corrected, so I’m now more diligent about checking.

After confirming that my earnings history was correct and complete through 2018, I started thinking about benefits. I’m 58, and retirement is not that far in the future. I wanted to check out a few retirement scenarios, varying my retirement age and projected earnings between now and then. There used to be an online benefits calculator that would incorporate your earnings history into its forecast, but I couldn’t find it today. Instead, I came across this page, which requires you to enter all your earnings history into a series of fields in a long HTML form.

SSA benefits calculator

If you have earnings going back to 1951, I salute you.

I have almost 40 years of earnings, and there was no way I was going to tab-type-tab-type my way through all of those numbers. So I wrote a short Keyboard Maestro macro to do it for me:

KM macro for entering SSA earnings

Ignore the definition of the earnings variable in the first step; we’ll get to that in a bit. The important thing is that earnings is a multiline string with each line representing a year’s earnings. For me, the list starts in 1980 and continues through 2018.

The macro loops through the lines in earnings. For each line, it

The pause is in there to make sure the focus has moved to the next field before typing the amount.

These are exactly the steps I would have gone through had I filled in the fields by hand. The key to running the macro successfully is to make sure the cursor is blinking in the 1979 earnings field before invoking the macro. Ten seconds later, all the necessary fields are filled with no typos.

Let’s go back now and see how I filled the earnings variable. The numbers came from the earnings history page that I started on, which looked like this (apart from the values, of course):

SSA earnings history

This is an HTML table with years. The values I wanted were in the second column, which I couldn’t select by itself. So I selected the whole table and copied it into a Numbers spreadsheet to do some editing.

You’ll note that we need to enter the numbers in the benefits calculator in chronological order, but the earnings table is in reverse chronological order. So the first order of business was to sort the spreadsheet by the first column. After that I was free to delete the first and third columns, leaving me with a single column of the numbers I needed. Except…

The values in the remaining column have dollar signs and commas, and I was afraid that Numbers had interpreted them as strings when I pasted them in. But, mirabile dictu, Numbers did the right thing and interpreted those values as numbers. So all I had to do was set the style for those cells as a generic number type, with no commas and no currency symbols. Then I copied the column and pasted it into the top step of the macro.

After I was done, I was a little ashamed at using Numbers. As someone who fancies himself a Unix adept, I should have used the command line to extract the second column, eliminate the dollar signs and commas, and reverse the order. It wouldn’t have been that hard:

pbpaste | cut -f 2 | tr -d $, | gtac | pbcopy

By default, the cut command uses tabs as the field delimiters, so it was perfect for extracting a singe field from each line. The -d option tells the tr command to delete the given characters rather than translate them, so that left us with digits only in each line. And the cleverly named tac command does the reverse of cat, returning each line from bottom to top instead of top to bottom. Because tac doesn’t come standard on the Mac, the version I used came from Homebrew; it’s in the coreutils set, which installs GNU versions of many standard commands. These commands are prefixed with a g to avoid potential conflicts with the BSD versions already on the Mac. This three-command pipeline is wrapped in a pbpaste/pbcopy sandwich to do its work on the clipboard. No need to save anything to a file.

The earnings extraction part of today’s work will never have to be done again. In the future, I can just add lines for earnings in 2019 and beyond.

This is obviously not a macro I’m going to be using more than once a year, but now that I have it, I can fill in the benefits calculator fields easily and accurately. It’s not a great savings in time, but it is a great savings in frustration.

Next post Previous post