Excel & Hockey – A Tale of a Nerd

I love watching hockey. I’m a little sad that I’ve never actually played, but not by much. And being a self-professed nerd and geek, I’m naturally attracted to the statistics of hockey.

An aside: I am not to the point of being a statistician and analytics expert; I read the analytics articles on various hockey sites and I get lost.

For 20 years, I’ve been tracking various bits and pieces of hockey data in Excel. For the majority of the time, it was all manual entry with the performance line (W, L, OTL, etc.). Naturally, I could calculate points, max points possible, goals per game, points per game and so on.

But I needed more automation because manual entry is a thing of the past. After falling into Excel’s Get & Transform (twin to Power BI’s Power Query), automation began to take hold.

This is the base worksheet of my Excel workbook. Looks familiar to anyone who’s looked at league standings, regardless of sport.

Most of the cells are formulas. The highlighted columns would be the obvious choices for calculations, and those were ones I had for a long time already. Teams rarely change, and when they do, it’s monumental enough to where manual updates are going to be necessary.

Atlanta folding and moving to Winnipeg, changing names, conference, and division. The birth of Vegas. The division realignments of 2014.

So columns A, B, and C are okay being manual (though I automated that as well; more below). But I was sick of manually updating the wins, losses, and OTLs. There HAD to be a better way. And yes, there is.

Enter Get & Transform. Details are in this link, so I won’t waste space explaining it here.

I created a reference worksheet to house the link to an online source that updates current year standings. Then, using G&T, I manipulated the source to output what I wanted.

The “Divisions” query in the left column is what I was mentioning. At the end of the workbook list, I have the teams, conference, and divisions laid out because the source I was using had those listed in a way that I couldn’t use. Using a merge, I pulled together the team names, conference, and division.

All the steps on the right are how I manipulated the original Web source to look how I wanted it. This resulted in the “live feed” worksheet.

Now that we’re out of Narnia (what I call the Get & Transform environment; if you’re curious why Narnia, come to a session I’m teaching on this topic), I can use it on the detailed worksheet.

In each of the result columns, I’m using a VLOOKUP:

I should be using an INDEX/MATCH to get away from so much dependence on VLOOKUP. I’ll probably get there next season.

What I haven’t figured out yet, though, is how to label playoff position. Since the NHL rules are a little more complex than just “top 8 teams in each conference get in,” I haven’t figured out how to capture those sorting rules in a formulaic manner. Maybe next season for that, too!

I was reading the recap of the Red Wings – Blackhawks game of last night (3/6/20). Wings won 2-1, and this line in the article struck me as notable.

Chicago is last in the Central Division despite having a winning record and its point total doubled Detroit’s entering the game.

That piqued my curiosity. I looked in my standings sheet, and sure enough, the Blackhawks are 31-29-8 but rank last in the division and out of the playoff picture.

That indicates that the Central is the strongest division in the league. Visually, I’m guessing that Atlantic is the weakest division, then (based on the performance of Detroit and Ottawa). But how do I verify that?

Let’s look at averages for the divisions.

The formula here uses AVERAGEIF. AVERAGEIF will grab the cells that you need based on certain criteria. In this case, I wanted to calculate the average of all cells in the source if the reference matched. The reference I’m using is the division abbreviation in the first column, which matches the division values in Column C of my standings table.

For each of the W-L-OL-ROW cells, if the division matches, average those values. For the wins cell, the formula is =AVERAGEIF($C$2:$H$32,$U17,$E$2:$E$32)

$C$2:$H$32 is the absolute reference covering the columns from division through ROW, and of course, 2 through 32 refer to the individual team rows.

$U17 is the absolute column within this averages table that contains the reference division. 17 is a relative notation because when I copy/paste this formula to the other cells, I want the rows to change accordingly but not the column so I can continue to reference the division.

E$2:E$32 is the range of the values I want averaged, based on the reference division. The column is relative while the rows are absolute. Since the W-L-OL-ROW is in the same order in the standings as well as in my averages table, I can accept Excel adjusting the columns in the formula automatically.

Now I can confirm that, based on average points earned by teams per division, Central is the strongest division with 79.29 points and Atlantic is weakest with 71.75 points.

There are some cube functions also in Excel where I could probably pull the teams by division, combine the new table with the average then plot them visually, but I’ll probably do that next season as well.

Why Does Any of This Matter?

Seems like a total waste of time since I’m not up to the caliber of hockey analytics people like TSN’s Travis Yost or the analysts being hired by NHL teams directly now. But it does feed my curiosity by putting the data that I want to see in one spot without having to click everywhere on the Internet.

It also serves as practice, learning new functions and how they behave. Then I can put that to better use on the job. So what starts as hockey stats can become analytical methods for student data.

Almost Done

Last week, we went up north. I took up Ke Kai in kit form to start work on her, and also some finishing pieces and tools for Blue-J.

The finishing pieces included a set of Seymour-Duncan humbuckers in blue that I found on eBay.

After careful review of the wiring instructions, I was still a bit shaky in confidence, so I grabbed a notebook and diagrammed the existing wiring to corroborate the published instructions. The issue was that the published instructions and diagrams just had lines connecting the components but in completely different colors. The pots and wires were already partially assembled so I had trouble tracing where each one went.

But after I rewrote everything, I gain enough confidence to begin soldering.

And I realized that I need that Doc Ock soldering “third hand” thing. But somehow, I managed to get everything soldered in place.

Before tightening everything down, like the pickups, selector switch, and pots, I had to plug it in to test sound. I’d read a passing post from another guitar builder forum that while tape can’t be effectively used to test out solder points, I can check the integrity of my solder job by touching the poles of the pickups with a metal screwdriver.

I plugged into my little 2-watt Marshall portable amp, and with each volume & tone knob set, I cycled through the pickup selector switch to test the pickups. Everything seemed good.

OK, time to tighten down everything, finish final assembly, and string it up!

Then came another problem, due to not inspecting all the parts more closely.

I had one tuning machine completely missing the string hole in the shaft. There’s no way to feed a string! I’ve since been in contact with Solo, and they’re sending me a replacement. In the meantime, Blue-J is a 5-string guitar.

I was too aggressive in final sanding on that bottom edge and didn’t notice it until long after lacquering.

I replaced the stock knobs with abalone-topped dome knobs. I have a set of black ones so I might swap out two of the chrome for the black ones to give it a bit of visual indicator which is tone/vol or which is bridge/neck. Not sure yet, will have to try it out to see how it looks.

Hopefully, when I get back up there in April, I’ll have the last tuning machine in hand to complete Blue-J.

But even with just the five strings, I plugged her in through my distortion and delay pedals. Running through Danzig’s “Twist of Cain,” I was able to make that E squeal with the appropriate harmonic. I’m not able to play my latest song snippet idea as that requires all 6 strings, but that’ll come.

Overall, I’m very happy with how this kit turned out. I learned a lot about coloring and wood finishing that I’ve begun applying to Ke Kai. Now I have to manage my growing addiction to building guitar kits!

Like collecting hockey jerseys, this could get overly expensive if I’m not careful. And guitars take up more room than jerseys.

Prep, Coloring, and Coating

The first thing I noticed was that the nut was awful. Too high, and slots too narrow for the strings. The action was so high that playing the first fret was nearly impossible.

There were also a number of dings and scratches in the body.

Kinda’ tough to see, which is probably a good thing since that means they can be sanded out

To take care of the nut, I had to buy a set of nut files. I was too aggressive on the slot for the low-B, because it ended up too low in the nut and buzzed on the first fret. I found out that a drop of superglue allowed to dry and cure would act as “padding” to raise it up.

So I did that…and inadvertently glued my finger and thumb together with just a single drop. That ended up being several minutes in the bathroom with nail polish remover.

Another problem was the roughness of the frets’ ends. Running my hands up and down the edges of the fretboard, I was catching all the points and ragged ends of the frets. In 30-some-odd years of playing guitars and basses, I’ve never experienced this. I used an old jeweler’s file I had lying around to smooth out the ends of all the frets.

After the test assembly, I found that I need to level the frets, because after adjusting action and relief, I still had buzz on about the 13th or 14th fret on the D string. I was really irritated at the low quality of this kit by this point.

The Journey to the Workshop

Last week, we were up north so I could start working on Ke Kai as well as finishing Blue-J. I sanded down the body of the bass to smooth out the dings and such, and also ran some major sanding on the neck. I attached the body to the rig and began prep and color.

Ke Kai – Intro

Hawaiian for “To the Sea.”

After going back and forth with indecision over whether I want a 4-string or 5-string bass, I decided on another 5-string. I also had new ideas for color and finish, so the Ke Kai project will be a bit more advanced practice for this absolute beginner in guitar finishing.

Before I ordered the kit, I ordered a plank of ash and staining supplies to practice the ideas in my head. A friend suggested using prestain conditioner to more evenly distribute the color.

Throughout the practice phase, I had to work through a lot of impatience as well as my current skill level not quite up to the task of what’s in my head, so I’ve compromised and in that effort, I think I’ve come upon a design idea that could be both achievable and unique.

The kit is a 5-string ash-body bass from TheFretwire. After receiving the kit and examining the parts, I’ve determined that it’s of lower quality than the kit I received from Solo. But part of DIY kit building is to make-do with the included hardware until/unless one saves up the money to upgrade over time.

Work will begin later in February when we’re back at the house up north.



Final Coats and Departure

Today is the day of departure. We’re heading back home after over a week up north. I’ve decided to leave Blue-J up here to fully cure, since we’re not likely to be back until February. I decided to take advantage of the extra time by spraying more coats onto the body, and also stain the headstock after all.

I was nearly out of the Watco lacquer. The Duplicolor shipment had arrived on Friday, so I decided to give that a try on top of the Watco. After all, I had 4 coats of the Watco on already.

I removed the hardware and sanded down the face. I masked off the sides, nut, and most of the fretboard, and shoved paper towel into the truss rod cavity. I also backed the tuning peg holes with masking tape.

Stained with a higher concentration of the diluted blue tint, and sprayed clear in multiple coats along with the body and got this.

I can see where it was more difficult to sand down by the nut and at the top of the headstock because I didn’t get all of the shipping sealer off. But, another lesson learned.

I reassembled the basement, putting the window panes back into place and cleaned up my work area. I moved the guitar more toward the center of the basement and moved the table underneath it; I had this sudden fear of the suspension wire giving out and the body crashing to the floor, splitting in half.

The Duplicolor sprayed a lot more finely so there’s more pebbling/orange-peel than the Watco. Duplicolor also smells a hell of a lot worse (not to say that the Watco was like smelling a pot of pasta sauce on the stove or that breathing the fumes is a good idea, but still…).

On the first coat, I was too heavy handed because I wasn’t expecting the velocity of spray compared to the volume. I ended up with two drip spots on the body that I didn’t notice until the first coat had dried for almost 2 hours. I gently sanded down with 400-grit, cleaned the areas, and sprayed the 2nd coat (again, 3 passes per coat, with intervening dry time).

I let the work dry overnight and inspected during workstation clean-up. The spot under the horn is barely noticeable, but the one on the back is still there though not nearly as bad as when I first noticed it.

I hope to get a better idea of repairing this in February.

See you in 6 weeks!

Clear Coat


The stain is dry, time to clear coat. I had originally ordered 3 cans of Duplicolor clear coat via Amazon, as it was $2 cheaper per can than if I went to a local store. Unfortunately, I paid for that savings in delayed shipping. The Duplicolor wouldn’t arrive until Friday the 27th, leaving very, very little time to do anything at the house up north here.

So, taking a break from the kids, I drove 1/2 hour into the nearby major city to Home Depot. I perused the woodworking/finishing aisle, and after determining that I should’ve just bought everything I needed here, I picked up a can of Watco gloss acrylic lacquer.

I taped off a spot that would be behind the neck plate to test out how the clear coat would work.

I also set up a box fan blowing out of the basement window with a matching furnace filter attached to the back. This will be my spray booth.

The test spray worked out nicely. The square was very glossy, and dried very quickly.

I taped up the neck pocket to prevent lacquer build-up, then drilled a screw into the joist above me, suspended a long strand of coated wire, then fed and tied it through the neck screw holes.

Then I began spraying.

  • Three very, very light passes all around, with dry time in between passes.
  • I removed the filter to get more air flow going and let this coat dry for two hours.
  • Reattached the filter, then three more light passes along with dry time for each pass.
  • Remove the filter, allow to dry (90 minutes this time).
  • Another three passes
  • Dry time.

So far, this is the result after the second coat.

After these photos, I added the third coat. I’m planning on at least one or two more coats for the rest of the night. Then she rests overnight.

Where I feel like I lost out on time because of the delay in shipping of the Duplicolor and before I found the Watco is that I won’t be able to assemble while we’re at the up-north house. We’re heading back home on Sunday (in two days as of this writing). I’m torn between leaving the guitar here to continue drying and curing until we come back or packing it all up and taking it home to continue work down there.

I could bring it all home and let the finish continue off-gassing in the garage. And when ready, begin the electronics. But I don’t have a decent enough workspace at the main house (without doing some major basement/lower level reorg, which was in the works since I don’t go back to work until Thursday the 2nd).

This is probably my impatience talking.



Blue Stain


The last few days were spent on staining with blue. It took multiple coats using a diluted solution of the Transtint and water. I measured it out into a glass measuring cup with a sealable lid so I could keep the concentration consistent over several days while I worked. The end result wasn’t what I’d pictured in my mind at the start of the project, but did result in some happy accidents.




I started wiping off the dried filler and stumbled into an unexpected “happy accident.” With the way I was removing the dried filler, I found I could control how much I took off and where. The uneven pattern was adding a bit of character and depth that I hadn’t anticipated:

But I was able to see a better effect of the filler in the grain:

Later in the day, I assembled the finishing rig. The parts consist of:

  • 1″ straight PVC pipe
  • 3/4″ straight PVC pipe
  • 1″ 3-way elbow
  • 1″ tee
  • 1/2″ machine screw

In this case, I bought two 24″ pieces of the 1″ pipe and one 24″ piece of the 3/4 because it’s just easier to transport to the house rather than buying the super-long bulk stock. I cut 9″ off of one of the 24″ pieces to be the vertical, leaving the other to take the bulk of the body’s weight once I got the whole rig together (thank you, high school math and science).

I used a 1/2″ machine screw as a set screw in the T-piece to hold the 3/4″ pipe in place once I bolted it to a scrap piece of wood to attach the body.

When I began coating the sides and front with grain filler, I discovered a problem. To account for centrifugal force, I bolted the body to center with the 3/4″ horizontal pipe. Unfortunately, to minimize the center-of-gravity problem with the weight of the guitar and the lack of a counterweight, the 3/4″ pipe is bolted right up against the heel of the neck joint of the body, which meant I couldn’t get the filler into that spot.

But since it was just screwed into place, I could readjust and reposition the wood piece to the other side so I could get to the heel.

The weight distribution makes it more awkward to rotate now, but I could at least get to the heel.

After letting the grain filler dry for a few hours on the front and sides, I wiped it off using some deliberate carelessness to feather the edges.

And thus ends Sunday’s work. Letting the moisture dry off for the night before I begin staining tomorrow.




Now that I’m at our vacation home, I set up the workstation in the basement and did some test coloring and more examination. I decided that the neck should remain as-is, because it seems like there’s a more durable coat of sealant on it. I tried sanding off the top coat on the back of the joint where it would connect to the body, but it was taking more effort than I expected.

In various angles of light, I could see the shimmer of the mahogany, so it felt like it’d be a bit of a shame to lose that contrast with the future blue body.

I assembled the tuning machines just for a sense of some accomplishment:

I sanded down the body with 220 grit sandpaper to prep it for the Goodfilla ebony grain filler.

Then I realized my first mistake. I didn’t sand enough, because when I wiped off the dried grain filler with a damp shop rag, I was taking off all of it. I ended up right where I started again. So I went down to a 120 grit to take the sealer off and then I could tell that I was making a difference with a touch test. I applied the grain filler, thinned with a little bit of water to make it more paste-like than chunky-solid.

It was late when I finished laying it in on the back of the body, so I decided to let it dry and sit overnight until I could tackle it in the morning again.



The Blue-J Project – Intro


A log for a custom guitar build

For decades, I’d been fascinated by the BC Rich Mockingbird guitar. US audiences may associate this with Slash. For me, however, the style was favored by one of my favorite guitar players, the late Hide (‘HEE-deh‘) of X-Japan:

When I was in Japan in the late ’90s, I wandered into a music shop and saw a replica of Hide’s yellow Mock with the hearts. Despite knowing that it was a consumer-grade copy, I wanted to try it out, to get the feel and balance of the unique shape. It stuck with me since then.

I’m certainly not of Hide’s caliber in guitar playing, and my aesthetic style is nowhere in his stratosphere, so I wanted to build a Mock more to my own style. Typically, I lean toward red guitars, but a sudden flash of inspiration and some trial mock-ups later, I settled on a deep blue, as shown in the lead-in image.

The project is named Blue-J (since my legal first name starts with “J”), but I haven’t decided whether that’s the name of the guitar or not.

I ordered the MBK-1 kit from Solo Guitars (no affiliation links, as I’m not going to blog nearly enough to drive traffic to the company to earn a cent), and five days later, the box was home. Did a test fit of the neck and body and to examine the mahogany:

Upon examination of the grain, I decided that a solid color and sunburst fade would be a disservice to the natural qualities of the wood. So it’s going to be stained instead.

Supplies I bought to accomplish this include:

  • TransTint wood stain/dye – blue
  • Goodfilla ebony grain filler
  • A bunch of PVC to build the finishing rig
  • Collection of sandpaper ranging from 120 – 3000 grit
  • More shop rags than I ever thought I’d ever need

To make the finishing job easier, I bought supplies at Home Depot to make a finishing rig of PVC and a piece of project lumber. While I have scrap wood available, they’re in 8′ deck plank sizes, so cutting them down would’ve just been extra work for the $4-5 cost of the finished project board that I have to cut anyway.

I also want to replace the stock tone/volume knobs that came with this kit, but will make that decision after the finish is complete to make sure that the vision in my head actually matches reality.

Because the mahogany is an open-pore wood, I need grain filler to keep the wood from getting all funky once I lay down color substance. After several days of poring over various Internet forums, blogs, and vendor sites, I decided I wanted a dark-to-black grain filler to help accentuate the grain against the blue stain. This was where I settled on the Goodfilla ebony.

Several YouTube videos on refinishing guitars later, I was as ready as I was going to be.