Guest post: Paul Long brings us an updated list of The Most Frequent Award Winners

Good Day guys,

If you are developer, DBA, or IT who works with multiple languages, then you probably familiar with the problematic phrase "First day of the week". Regardless the question if you open the week on Sunday or still sleeping during the weekend on Sunday, this is the time for the Surprise with Sunday Surprise post :-)

Today's Surprise is a Guest post, written by Paul Long, who is one of the most active members in the TechNet Wiki community. You can meet Paul in the TNWiki Facebook group or read Paul's interview or check his User Page, but first let's go read Paul's post:

Good day Gurus,

Some of you already know me, but for those who don't, I'm Paul. I've been fairly active with programming since about 2007 and I've been entering articles in the Guru Competition since 2013.

Currently I'm studying a University module on Web Technologies. Alongside regular programming in VB.Net, C#, and Java, I'm personally interested in web technologies, particularly Html, CSS, Javascript, and PHP. I have a personal website, which has been evolving over several years, as I learn more about the web. On my site, I have some VB.Net and C# examples (some of which need some rewriting), some compiled games, but more recently I have become more interested in online games. I'm hoping to rewrite three of my most popular games (HangMan, Mastermind, and Sudoku) in mobile format, as they currently work well in desktop format, but not so well with mobile devices...

What's new?

As some of you will have noticed, I recently undertook the task of updating the TechNet Wiki administrative article: TechNet Guru: The Most Frequent Award Winners.

This is an article I've seen before several times, which for a long time I've wanted to see updated, but I could see from the data it contained that it would take a complete recount of all of the winning articles in all of the previous competitions. Luckily, this is one of the areas where there is some order in the Wiki. The Wiki article: TechNet Guru Contributions contains a table providing links to all of the previous TechNet Awards articles (56 in total at the present time). Originally I wanted to count the articles awarded by extracting the data directly from the Html of the articles, but Html scraping is an exact science, and over the course of more than four and a half years, the Html used to create the Awards articles has changed. This left me with the only viable choice of manually counting the winners.

How?

I started out with a macro enabled Excel 2007 workbook. On Sheet 1 in column A I put the members names (taken from the old Award Winners page). Next, for each month of the competition I created a column.

For each column containing months in my sheet, I added  formulas to count Gold, Silver, Bronze, and Total medals, denoted by G,S,B. For each row containing members in my sheet, I added a formula to count Gold, Silver, Bronze, and Total medals.

At this point, I could see it would require inputting a series of G,S,B characters into the sheet, so I wrote a tool in VB with three buttons, each of which copied one of the G,S,B characters to the clipboard and I could easily paste it to the spreadsheet. This input took some time...

As the original Wiki page I was updating contained an indicator of whether the member had a Ninja Interview or not, I decided an additional column for each member could not only indicate this, but also provide a link to the interview. The full list of Ninja interviews was my data source for the links I used in my page update. The links I copied and pasted into a new sheet in my Excel Workbook, then I wrote a VBA macro to match up the interviews with the members on Sheet 1.

To double check my first medal counts from the 56 months, I created another tool in VB. After saving my Workbook Sheet 1 as CSV, I was able to produce a list for each month, as depicted below:

Using these lists and the Awards articles for the 56 months, I was able to quickly verify the counts for each month.

As I already by this stage, had a Worksheet containing all of the data for the 56 months of the Guru Competition, it was easy to create a line chart showing trends:

* You can download the excel file from the TechNet Gallery.

And here we are

It was a personal achievement completing this work, as I had previously seen the Wiki page I updated, and thought it could be a very useful resource if it was kept up to date. I was also surprised to see I'm at joint 5th on the Gold medal winning leader board :-)

I'll be updating the Most Frequent Award Winners monthly , so be sure to check it!

TechNet Wiki Guru, Paul Long