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.
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.
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:
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,
|New! If you have something important to say to the TNWiki community, if you finished a nice project for the TNWiki community and you want to show it, or if you simply feel the need to publish a Guest post in the TNWiki blog, then contact one of the TechNet Wiki council members in-private.|