Student Contact Address Verification PowerSchool Plugin

PowerSchool’s student contacts permit some detailed information about contacts associated to a student, but lacks any meaningful automated validation with the “Lives With” flag. This is, of course, a natural constraint as a student can live part time with one parent and another, even if their designated home addresses don’t match (to avoid sounding like I’m criticizing PowerSchool here).

This plugin compares a student’s address to the addresses of contacts claimed to be living with the student. If the addresses don’t match within a large percentage, an alert icon appears to signal a need for review of both the student’s address in Addresses or General Demographics pages or the contacts themselves.

It could be a simple misspelling or order of the components of the address (123 Main Street East versus 123 E. Main). Or it could be a custodial arrangement, but not everyone may be aware of it.

Download from PowerSource.

New Guitar Project

Starting another guitar build. This one is going to be total cheese metal: a V-style body. I’m going with a solid black this time, no dyes or stains. It’s also going to be tuned to B-standard (B-E-A-D-F#-B) because I really like the lows of Cabernet.

However, I’m torn with the accent color: black or gold, so I’m soliciting opinions along with a poll. Here’s the mockup of what they could look like.

I like the pop of color on the gold-on-black, but it’s also hard to not see either Batman or Boston Bruins in it, which are not what I’m trying to go for (surprisingly). I like the simplicity and understated feel of the black-on-black but it could be somewhat “blah” although that could be more of a result of the mockup image and not the real-world object.

Comments welcome below.

Ground….Nope

Got the rogue ground wire soldered onto the tone pots, but that wasn’t the problem. It was when I desoldered the jack because I forgot the nut and then resoldered that I think I made a mess of the ground at the jack.

First Pics…No, Not Quite

Was almost about to be able to post photos of the newly completed Ke Kai….until the audio test failed. Could barely hear faint notes, but they were so faint that my tuner couldn’t pick up the signal. I propped up the bass near the rest of the arsenal, started cleaning up my tools….and noticed a wire sitting on the desk that I’d set aside.

Yup, forgot a connection. Hoping that’s it, because I really don’t want to try to de-solder and reconnect everything again.

Tomorrow.

First Test-Fit

First test-fit of pieces for Cabernet.

The pickguard is going to be painted red. That white pearloid just isn’t my style! 🙂

But this little crack was why it $50 was knocked off:

A $5 tube of wood filler will take care of it.

Arrival and Name

The new guitar kit has arrived, and after a test-fitting/inspection, it’s interesting to note that the crack in the body that led to this being considered a B-stock is hardly noticeable. I’ve ordered a tube of wood filler anyway from Home Depot, which I’ll go pick up tomorrow.

Name-wise, I’m going with “Cabernet.” It’ll be red, to fit with the guitars I have at the permanent home and also to take advantage of the vine themed inlays on the fretboard.

The mental picture for the theme:

  • Body & headstock dyed red to highlight the natural wood look
  • Red on neck (heh…)
  • Black grain filler on body
  • Pickguard sprayed to a deep red
  • Black faded burst style with dye, like what I did for Ke Kai

The trem is actually a Floyd-Rose-licensed piece, just like on Madeleine, my Jackson Kelly.

I’d considered a set of Dimarzio pickups in red, but that would’ve cost me almost $300, so I’ll have to save that for another time.

New Project

Solo Guitars had a B-stock item available, one of two models that I’d been looking at. Since it was $50 off and requiring what appears to be a $5 repair, I went for it. An Ibanez JEM-inspired 7-string. No name as of yet, will need to wait until it’s in my hands and I have an idea of how to finish it.

The inspiration here came from wanting a lower-tuned guitar. Either a 7-string or a baritone, neither of which I’ve had in my collection ever. I was considering a Rhoads-style V, then either setting it up and tuning it to B-to-B as a fake baritone on a traditional 6-string scale, or in the wildest of fantasies, installing a custom baritone-scale neck from Warmoth.

Only problem with that plan was that the neck I designed at Warmoth ended up at $365 for the neck alone. So that’s not happening! But an ebony fretboard, with black perloid starburst inlays for fret markers?

Maybe some day…. Anyway….

The rated build challenge is higher than that of Blue-J or Ke Kai, probably because of the Floyd-Rose-inspired tremolo bridge. But lucky for me, Madeleine, my Jackson KE-3, has a FR trem that I’ve learned to adjust.

I’m not sure yet if I’ll go with the red theme to match my other instruments down at the permanent home or a blue/green to fit with the gear up north. I was leaning toward just pure green because of the vine inlay on the fretboard, and calling it Poison Ivy, but that’s kind of cliché already.

I don’t know, maybe something red and calling it Chateau Picard? 🙂

Not quite brutal enough for a 7-string, though.

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.