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.
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:
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
- puts the line into the variable
- types a Tab;
- pauses for a quarter of a second; and
- types the
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):
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.
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.