Tales from the Pivot Table

I’m working on a little project for some of our partners. Basically pulling some data out of Fastpath and having Excel pull it into a pivot table via a macro. In the past, I’ve tried to stay away from macros and VBA when I could. Frankly, the security issues macros can cause sometimes force people to shy away from using them. This time I had no choice.

I started with code to import a report and pull out the header to make pivot table creation easier. Then came code to create a pivot table. I got everything working for one ERP system, but I need this work on multiple ERP’s. My first thought, the sloppy thought, was to just replicate the code and make multiple Excel files. That thought didn’t last long. Too hard to maintain.

After that I decided to copy parts of the code and give users the chance to select their ERP and drive the code selection that way. That’s when things got interesting. In testing I realized that my Excel code simply skips pivot table columns if they aren’t present. For example, if D365 uses “Role” and Oracle EBS uses “Responsibility”, I could put both of those items in the code, but if I’m running this for D365, Responsibility won’t come in because it’s not in the file. Excel just ignores that Responsibility is missing and moves on. This made coding for multiple ERP’s much easier. I no longer needed to make users pick an ERP system. With the right code, it just works for each ERP I’ve setup.

It wasn’t this easy of course. I found a bug in my v1 that blew out an entire worksheet as I moved to v2. Oops. Pulling out the header proved to be more challenging than expected since the header can be different lengths. I still have some testing to do, but all the preliminary stuff looks good.

So what’s the point? Well 1) It’s cool that Excel VBA doesn’t error out when building a pivot table if a field is missing, it just moves on. 2) Projects like this take some deep work. They need large blocks of time and they can’t be rushed. Distractions kill work like this. Twitter, email, slack, all of it. It’s a lot of “no’s” to tackle a project of even limited complexity.

Oh, and for those of you asking, yes, I built the whole thing in Excel for Mac and it runs just fine in Excel for Windows.

Musings – Data is only as good as…

We like to talk about data. Big data, crowd sourced data, etc. Big data likes more data to better interpret or smooth the data statistically. But what happens when the data is wrong?

Simple example, all of the nutrient tracking apps, think MyFitnessPal and similar, are a mix of crowd sourced, vendor provided, and authoritative data. They generally do a good job on macros like protein, carbs and fat. A significant percentage of items are crowd sourced and I’ve found that they can be terrible counting at lesser nutrients. Potassium and Iron are good examples.

Authoritative sources I’ve seen show that a 100g of beef provides about 7% of a person’s daily potassium requirement and between 10 and 24% of a person’s daily iron requirement, depending on the cut. In many apps, both of these are regularly zero in nutrient apps.

So what happens when big data relies on inputs that are mostly wrong? I suspect you get a lot more wrong and the wrong becomes entrenched.

Eero, Hue, and the weirdest solution

Ever have those days when everything you touch breaks? Yeah me too.

A few days back my internet broke. After a lot of frantic troubleshooting (I had a work call in 90 minutes), I determined that my cable modem was fine, but my Google WiFi setup had died. The main node simply wouldn’t see the cable modem. I tried switching nodes and even a complete factory reset. No luck.

I replaced it with an Eero system now that the price has come down. I’d heard good things and Google WiFi had some bugs that Google never bothered to fix.

Parts of the switch went smoothly. Others were very rough. My Hue bridge for my smart lights just refused to connect to the internet. I tried everything including a hard reset with no luck. Finally, I saw a thread suggesting if there are multiple networks, see if the Hue bridge is connected to the wrong network. Eureka!

I turned off my guest network (disrupting the Roku TV my wife was watching, sorry hon) and reconnected. Sure enough, it found the internet and was good to go. It took some work to put back the pieces, but the bridge definitely connected. After that, I reenabled the guest network and everything still seemed to work.

If your Hue can’t see the internet and you have multiple networks, this is worth a try. It’s definitely worth trying before hard resetting the Hue.

Musings – Apple Watch Charger…my precious

Why do people keep coming to visit and forget to bring their Apple Watch charger?

My wife’s grandmother is with us under hospice care. As a result, we’ve had a small parade of friends and relatives visiting, and they consistently show up without their Apple watch charger for an overnight stay. We have one extra that we use for travel, so it’s been fine, but still. Based on the hunger in people’s eyes when they ask if we have a spare Apple Watch charger, I’m declaring it the new ring of power, the new precious, the new crack.

I don’t have some grand point about this. It was just interesting to note that the Apple Watch has reached that level of need. I used to see this same pleading in iPhone users desperately searching for a plug. That’s mostly gone now, but Apple has managed to give us a new addiction.

Musing – Lets play, Gatorade

I was watching regular TV the other day, and saw a Gatorade commercial (repeatedly, in the most annoying way) with professional athletes pleading that they want to play, indeed, they were willing to play any sport. (Here’s a link to an example) It would have been funny except for the contrast with professional athletes who keep not wanting to play, or stopping play, and then deciding to play again a few days later. External circumstances made the ad fall flat. I realize that ad buys are made well in advance. I also know that they can be pulled. I think Gatorade is wasting money on this add now and it may actually be functioning as a negative.

I suspect that no one at Gatorade was willing to pull the ad because ads are expensive. This is a lesson worth repeating as much for me as for anyone. We say “ignore sunk costs” as a shorthand, but sometimes things get shortened enough that they lose their original meaning. I like to think of it as, “Ignore what you’ve already spent and make the best decision you can for the current circumstance.” The fact you spent money doing the wrong thing, whether “wrong” was under your control or not, doesn’t mean you keep throwing money away once you figure out it’s not the right choice for the current environment.