Getting your EE Ranking statistics in Excel

The code described here does no longer work. Please see replacement Article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3887-Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html .
 

1. Target

For all Experts out there who keep a record of the points they get, and do that manually, here is a way how to obtain the data automatically ...

... as far as possible. Zone points are retrievable at any time, but overall points and associated data is not. Experts Exchange did abolish the retrieval of overall statistics of past days, weeks, months and years, but only recent data is available for each category. Since the method shown here reads the Web pages, you can't get more data than if you look yourself.

The framework I provide here as an Excel Worksheet will allow for filling in as much data as possible. To keep track of overall points, which are not the total of all zone points as you certainly know, you have still to query and enter it manually for past periods. There is no magic wand used, and hence nothing you can't do on your own. And you need only basic Excel skills to use this spreadsheet (though understanding will need some more skills, but it is not really difficult).

Important:
You need to have the "Remember Me" setting enabled in your web login to Experts Exchange via Internet Explorer. Otherwise, you will get no data at all (only members are allowed to see the ranking tables).
Because of the JavaScript method used in the New Skin, Web Queries cannot be applied. Hence it is important that you have switched to Classic Skin. Whether you use Expert or Premium Skin does not matter.

2. The Worksheets

The Workbook as provided here contains no data (of course). It consists of the worksheets:

  RankDef contains the definition of ranks with points to gain for this rank and the next one. It's where you enter your username, and change some settings (if you like). And here are the buttons located which you can use for convenience to start different actions.

  Overall will be filled with the data of all-time points reached in each zone  Month is the template worksheet for individual months  Ranking collects data of each month's worksheet

3. Ranking needs some further explanation...

Besides the data that is filled out semi-automatically based on the formulas referring to months' worksheets (shown for a example matrix of 3 zones), it allows for manual record keeping of overall statistics not retrievable automatically. That is, only data for the current month and year can be queried using the Web interface, and if you haven't been quick enough and waiting too long, last month's data is unreachable. I will describe in detail what is done automatically, what you have to do first, and what to do for the unknown values of the past. In short, columns B to P of the current month, year and total are kept up-to-date by code.

The % columns stand for "Success rate". 95% would mean you are a top expert, with 95% of all contributing (!) members ranked worse than you. In other words, you are one of the 5% of top experts.

The Points column in Overall area allows for Overall milestones. As an example I have added conditional formatting, which colours the cell green on each 500 000 points boundary. The conditional formatting here should show just the idea how to use formulas to colour up your spreadsheet.

The coloured example columns for zones you can see have to be adapted to your needs. The first three are referring to the corresponding month's worksheet, looking up for the zone name noted in row 2 (exact match needed). Row 3 is a short name you can choose arbitrary; I usually hide row 2 and display only the short name.

You will also see some numbers in row 1 of each individual zone. That is the One-Year-Average of that zone, used in last row to get the approximated days for getting the next rank in that zone. Just a teaser ;-) Adapt the area OneYear yourself each month to get recent statistics.

The last 3 columns are for zone point aggregation. That helps for calculating the point in time you got a new rank, a million, or similar, in a zone. They are referring to the values in the corresponding columns of this worksheet by simple cell value cumulation.

You usually will put your own zones of interest in both the month and total area, which should be easy as you can just copy and insert the example columns, and change the zone names in rows 2 and 3.


The "prev" row (for "previous") is the subtotal of all values which are not retrieved. The complete row has to be filled by you, for totals to show correctly. This is most important for the last three example columns. Other values in this row can be filled or not, they are just for convenience.

The Zones column is just to keep track of how broad your pointing is spread over different zones. It counts values in the corresponding monthly worksheet - very simple, if you know how.

4. How to Auto-Fill

First, you will have to enter the Expert name. Open the RankDef worksheet, and type the name in the empty green cell. If you forget this step - never mind, the VBA code checks for it, and will make it really obvious that you should enter something.

If you like, you can adjust the other two green cell values, which limit the number of Overall Zone Ranking and Monthly Zone Ranking zones retrieved, e.g. if you are only interested in the top 20 zones each month, but all in Overall.

Well, the time has come to let the "magic" work. Press the topmost button ("All in One"), and after a short time you should see the worksheets coming into live. Each worksheet is shown while the code works on it as visual feedback.

The "All in One" button retrieves all numbers of the current month and year, including the Member Ranking statistics available. This will be the button you use most often. A generated worksheet will look like this:The other buttons are for starting individual actions, e.g. if you want to update only the Overall worksheet or the member ranking. The buttons located on the right show how to obtain data of the past, as far as possible.
Instead of using the buttons, you are free to use the keyboard shortcuts noted with the button description, or use the VBA Immediate Pane to start actions:

      open VBA Editor ([Alt F11]),
      open Immediate Pane ([Ctrl G]),
      and type

call getYearZoneRanks(2007)

Select all Open in new window

This will generate the monthly worksheets for the complete year 2007.

The procedures not requiring an argument are also available via the Macro menu.

If you are certain the points won't change any more for a range of months (2007 is a good guess here), you can fix the values in the Ranking worksheet. Select the range and press [Ctrl Shift X]. This will call the Formula2Value procedure for the selection, removing formulas and replacing them by the calculated value. Doing this will speed up manual changes and reduce file size.

5. The Code behind the scenes

This section is for the more advanced users, and not necessary for utilizing the workbook. However, if you want to understand how all the stuff is done, you will have to read this section.

Option Explicit
                       
                      Sub getZoneRanks()
                        If Not WhoAmIFilled Then Exit Sub
                        
                        Dim ws As Worksheet
                        Set ws = Worksheets("Overall")
                        ws.Activate
                        SetCalc False
                        GetWebData ws.Cells(2, 1), [maxZones].Value
                        SetCalc True
                      End Sub
                       
                      Sub getThisMonthZR()
                        If Not WhoAmIFilled() Then Exit Sub
                       
                        SetCalc False
                        getMonthZoneRanks month(Now()), year(Now())
                        [OneYear].Offset(DateDiff("m", [OneYear].Range("A1").Value, Now()) - 12, 0).Name = "OneYear"
                        SetCalc True
                      End Sub
                       
                      Sub getThisMonth()
                        If Not WhoAmIFilled() Then Exit Sub
                       
                        Dim ws As Worksheet, r As Long
                        Set ws = Worksheets("Month")
                        
                        SetCalc False
                        
                        With ws
                          .Activate
                          GetWebData .Cells(2, 1), [maxMonthZones].Value, month(Now()), year(Now())
                          For r = 3 To [maxMonthZones].Value * 1.1
                            With .Cells(r, 4)
                              If .Value <> "Points" Then .NumberFormatLocal = "# ### ###"
                            End With
                          Next
                          .Cells(1, 2).Value = month(Now()) & " " & year(Now())
                        End With
                        SetCalc True
                      End Sub
                       
                      Sub Formula2Value()
                        Dim c As Range
                        SetCalc False
                        ActiveSheet.EnableCalculation = False
                        For Each c In Selection
                          c.Formula = c.Value
                        Next c
                        SetCalc True
                      End Sub
                       
                      Sub getYearZoneRanks(year As Integer)
                        If Not WhoAmIFilled() Then Exit Sub
                        
                        Dim month As Integer
                        SetCalc False
                        For month = 12 To 1 Step -1
                          Call getMonthZoneRanks(month, year)
                        Next
                        SetCalc True
                      End Sub
                       
                      Sub getMonthZoneRanks(month As Integer, year As Integer)
                        If Not WhoAmIFilled() Then Exit Sub
                        
                        Dim ws As Worksheet, r As Long
                        
                        On Error Resume Next
                        Set ws = Worksheets(year & " " & month)
                        On Error GoTo 0
                        If ws Is Nothing Then
                          Call Worksheets("Month").Copy(After:=Worksheets("Ranking"))
                          Set ws = Worksheets("Month (2)")
                          ws.Name = year & " " & month
                          ws.Tab.Color = RGB(255, 0, 0)
                          ws.Cells(1, 2).Value = year & " " & month
                          ws.Range(Cells(3, 1), Cells(300, 4)).Delete
                        End If
                       
                        With ws
                          .Activate
                          GetWebData .Cells(2, 1), [maxMonthZones].Value, month, year
                          For r = 3 To [maxMonthZones].Value * 1.1
                            With .Cells(r, 4)
                              If .Value <> "Points" Then .NumberFormatLocal = "# ### ###"
                            End With
                          Next
                        End With
                      End Sub
                       
                      Sub GetWebData(rng As Range, zones As Long, Optional month As Integer = 0, Optional year As Integer)
                        
                        Dim URL As String
                        URL = "http://www.experts-exchange.com/experts.jsp?etIndex=3&expertName=" & [WhoAmI].Value
                        If month <> 0 Then URL = URL & "&filterMonth=" & month & "&filterYear=" & year
                        
                        With Application
                          .DecimalSeparator = "."
                          .ThousandsSeparator = "·"
                          .UseSystemSeparators = False
                        End With
                        
                        Dim i As Long
                        For i = 1 To zones Step 10
                          With rng.Parent.QueryTables.Add(Connection:="URL;" & URL & "&zrStart=" & i, Destination:=rng.Cells(1.1 * i, 1))
                            .Name = "experts.jsp?etIndex=3&expertName=" & [WhoAmI].Value
                            .FieldNames = True
                            .RowNumbers = False
                            .FillAdjacentFormulas = False
                            .PreserveFormatting = True
                            .RefreshOnFileOpen = False
                            .BackgroundQuery = False
                            .RefreshStyle = xlOverwriteCells
                            .SavePassword = False
                            .SaveData = True
                            .AdjustColumnWidth = False
                            .RefreshPeriod = 0
                            .WebSelectionType = xlSpecifiedTables
                            .WebFormatting = xlWebFormattingNone
                            .WebTables = "11"
                            .WebPreFormattedTextToColumns = True
                            .WebConsecutiveDelimitersAsOne = True
                            .WebSingleBlockTextImport = False
                            .WebDisableDateRecognition = False
                            .WebDisableRedirections = False
                            .Refresh
                          End With
                          If i > 1 Then
                            If rng.Cells(i * 1.1 + 1, 2).Value = rng.Cells(i * 1.1 - 1, 2).Value Then
                              rng.Range(Cells(i * 1.1 + 1, 1), Cells(i * 1.1 + 1, 4)).Value = ""
                              Exit For
                            End If
                          End If
                          If rng.Cells((i + 10 - 1) * 1.1, 1).Value = "" Then Exit For
                        Next
                        For i = zones * 1.1 To 2 Step -1
                          With rng.Cells(i, 3)
                            If .Value = "No Certificates" Then .Value = " "
                            If .Value = "Level" Then
                              If i > 2 Then
                                rng.Rows(i).Hidden = True
                              End If
                              rng.Cells(i, 4) = ""
                            Else
                              ' This is for all those countries where a comma is not a thousands separator ...
                              rng.Cells(i, 4).Value = Replace(rng.Cells(i, 4).Value, ",", "")
                            End If
                          End With
                        Next
                        
                        Dim qt As QueryTable
                        For Each qt In ActiveSheet.QueryTables
                          qt.Delete
                        Next qt
                        
                        Application.UseSystemSeparators = True
                      End Sub
                       
                      Sub SetCalc(calc As Boolean)
                      Dim ws As Worksheet
                        For Each ws In Worksheets
                          ws.EnableCalculation = calc
                        Next ws
                      End Sub
                       
                      Function WhoAmIFilled() As Boolean
                        Dim ws As Worksheet
                        With [WhoAmI]
                          If .Value = "" Then
                            Worksheets("RankDef").Activate
                            .Activate
                            .Interior.Color = RGB(255, 0, 0)
                            .Offset(, 1).Value = "<--- Please enter your EE Member name!"
                          Else
                            .Interior.Color = .Offset(1, 0).Interior.Color
                            .Offset(, 1).Clear
                          End If
                        End With
                        WhoAmIFilled = [WhoAmI].Value <> ""
                      End Function
                       
                      Sub getMemberRanking()
                        If Not WhoAmIFilled() Then Exit Sub
                       
                        Dim URL As String
                        Dim qt As QueryTable
                        URL = "http://www.experts-exchange.com/experts.jsp?etIndex=4&expertName=" & [WhoAmI].Value
                        
                        On Error Resume Next
                        If [ScratchArea].Name = "" Then [RankDef!L1].Name = "ScratchArea"
                        On Error GoTo 0
                        
                        [ScratchArea].Parent.Activate
                        Set qt = [ScratchArea].Parent.QueryTables.Add(Connection:="URL;" & URL & "&periodID=0", Destination:=[ScratchArea])
                        With qt
                          .WebTables = "11"
                          .FillAdjacentFormulas = False
                          .PreserveFormatting = True
                          .AdjustColumnWidth = False
                          .WebSelectionType = xlSpecifiedTables
                          .WebFormatting = xlWebFormattingNone
                          .WebPreFormattedTextToColumns = True
                          .SaveData = False
                          .BackgroundQuery = False
                          .RefreshStyle = xlOverwriteCells
                          .Refresh
                        End With
                        
                        If [ScratchArea].Count = 1 Then Range([ScratchArea], [ScratchArea].Offset(10, 4)).Name = "ScratchArea"
                        ' Some preparation for further processing
                        With [ScratchArea]
                          .Offset(0, 1).Name = "Lookup"
                          ' Copy rank to the end, else we cannot use VLookup
                          .Range("E1:E11").Formula = "=" & [ScratchArea].Columns(1).Address
                          ' Filter Expert in result rows
                          .Range("A13:A15").Formula = "= VLOOKUP(WhoAmI, Lookup, 4, FALSE)"
                          .Range("B13:B15").Formula = "= VLOOKUP(WhoAmI, Lookup, 1, FALSE)"
                          .Range("C13:C15").Formula = "= VLOOKUP(WhoAmI, Lookup, 2, FALSE)"
                          .Range("D13:D15").Formula = "= VLOOKUP(WhoAmI, Lookup, 3, FALSE)"
                          .Range("E13").Value = "Overall"
                          .Range("E14").Value = "Yearly"
                          .Range("E15").Value = "Monthly"
                        End With
                        
                        ' Now retrieve Overall, Year, Month
                        [ScratchArea].Range("A13:E13").Select
                        qt.Refresh
                        Call Formula2Value
                          
                        [ScratchArea].Range("A14:E14").Select
                        qt.Connection = "URL;" & URL & "&periodID=1"
                        qt.Refresh
                        Call Formula2Value
                        
                        [ScratchArea].Range("A15:E15").Select
                        qt.Connection = "URL;" & URL & "&periodID=2"
                        qt.Refresh
                        Call Formula2Value
                        
                        ' Now tricky bit - get last rank by using very high starting rank
                        URL = "http://www.experts-exchange.com/experts.jsp?etIndex=4"
                        
                        With [ScratchArea].Range("F13")
                          .Select
                          qt.Connection = "URL;" & URL & "&periodID=0&hofStart=1000000"
                          qt.Refresh
                          .Value = [ScratchArea].Range("A2")
                        End With
                        
                        With [ScratchArea].Range("F14")
                          .Select
                          qt.Connection = "URL;" & URL & "&periodID=1&hofStart=1000000"
                          qt.Refresh
                          .Value = [ScratchArea].Range("A2")
                        End With
                        
                        With [ScratchArea].Range("F15")
                          .Select
                          qt.Connection = "URL;" & URL & "&periodID=2&hofStart=1000000"
                          qt.Refresh
                          .Value = [ScratchArea].Range("A2")
                        End With
                       
                        ' Cleanup
                        [Lookup].Name.Delete
                        [ScratchArea].ClearContents
                        qt.Delete
                        
                        ' Now we enter this into the Ranking worksheet
                        With [Ranking!A5].Offset(DateDiff("m", [Ranking!A5], Now))
                          .Range("B1").Value = Replace([ScratchArea].Range("D15").Value, ",", "")
                          .Range("C1").Value = [ScratchArea].Range("A15").Value
                          .Range("D1").Value = Replace([ScratchArea].Range("F15").Value, ",", "")
                          
                          .Range("I1").Value = Replace([ScratchArea].Range("D14").Value, ",", "")
                          .Range("J1").Value = [ScratchArea].Range("A14").Value
                          .Range("K1").Value = Replace([ScratchArea].Range("F14").Value, ",", "")
                          
                          .Range("M1") = Replace([ScratchArea].Range("D13").Value, ",", "")
                          .Range("N1") = [ScratchArea].Range("A13").Value
                          .Range("O1") = Replace([ScratchArea].Range("F13").Value, ",", "")
                        End With
                      End Sub

Select all Open in new window

Almost all actions performed are based on Web Queries, issued by GetWebData. Web Queries call a Web page, then analyse the contents, and extract data structured as a table. You can play with that by going into the Data menu, Import External Data, New Web Query. Provide a valid Web link, and you will see what you can get. Or comment the line with qt.Delete in GetWebData, and start a month or overall read; then open context menu on cell A2, and "Edit Query". You should see the Web page used, with some yellow square symbols and a single green one for the member/zone ranking table. The URL you see determines the Web page, but the table reference is stored as a number in the query object (in .WebTables) and not directly visible to you.

The Web pages shown are not formatted using your country specific delimiters for numbers. Hence I had to change them or Excel won't be able to use the numbers because the comma is not always a valid thousands delimiter.

Since the Web Queries are doing nothing more than calling Web pages and extracting data from them, you can't get more than 10 zones per page. So we have to ask the Web pages repeatedly with a different starting page. Each query will again retrieve the column headers (Zone, Rank, Points), and we have to account for that. Because pretty printing matters, the code empties some of the cells, like removing the "No Certificates" rank, and hides the "superfluous" column titles.

As a last step, all internal linking is removed for the Web queries. Since we will not use the Refresh feature of those queries, the original linkage is useless, wasting storage memory.


GetMonthZoneRanks fills a single worksheet with zone ranking data of one month (month and year are provided as arguments). If the referenced worksheet does not exist, it will be created using the Month worksheet as template. New worksheets are inserted directly after the "Ranking" worksheet, so to get the correct order of worksheets, you should create the newest first (December, November, and so on). GetYearZoneRanks, shown later, takes that into account already.

In most cases you will not need to call that procedure on your own, since there is GetThisMonthZR. It will get the data for the current month. You could implement a grace period of a few days, if you like, by changing the call to e.g.
 

Sub getThisMonthZR()
                        getMonthZoneRanks month(Now()-5), year(Now()-5)
                      End Sub

Select all Open in new window

that will allow for 5 days, so if you call it at 4th of November, it still retrieves October statistics.

For initial setup, you certainly want to use getYearZoneRanks. However, since you have to provide a year, you can't do that directly, only via code, as done with the buttons or in Immediate Pane.


GetMemberRanking has gotten quite long. I had to use some tricks to get the corresponding values, you can't retrieve them directly. That is what happens:

The Overall Member Ranking screen is called with your member name. As you know, you get a list of 10 members, and one of them is you. Normally, you are positioned at row 6, but that can change under rare circumstances (wouldn't work for the "toppest artists" like angelIII or Mestha, for example). That is why I decided to use VLookup again to get the correct row. The values found are copied and the formulas removed - we are using the "ScratchArea" as a temporary database soon.

Now, we do the same with Yearly and Monthly Member Ranking.

After that, we can try to estimate the number of ranked experts in each period. Again, we cannot get that directly, as there is no table to read which contains that value. But if we retrieve the very last ranked member, let's say at rank 1 000 000, we will always get a single row, containing the last rank given.
Again, we perform this for Overall, Yearly and Monthly, and fix the values each time.

Now all we have to do is to find the corresponding cells in the Ranking worksheet. It helps we know each month builds a new row, the rest is simply maths, and copying values from one cell to another via VBA code.


Hidden in the Ranking worksheet is another piece of "code", this time as Excel formula. Since the zone ranking columns there refer to worksheets named according to the value in column A (month and year), we have a lot of indirection. That is, the cell addresses we refer to are calculated - combined with the Lookup function, this allows for easy cut & paste without having to change anything in the cells. With the worksheets, you do not have any automation that relocates addresses with new worksheets, as it is done with cells on the same worksheet if you copy a formula in a cell. For example,  = A3+B3  in cell  C3  will be changed to  = A4+B4  if copied to cell  C4. But  '2009 10'!A3  will only change to  '2009 10'!A4.

Hence, the address is built with  = Indirect("'" & year(A3) & " " & month(A3) & "'!A3"), and you can copy the formula to above or below. This so-called indirect addressing is used to build a cell address with other formulas resulting in a string, and refer to this dynamically addressed cell (or range).

However, indirect addressing implies that Excel refreshes and recalculates contents on each change of cells. Speed can decrease drastically if overused (just as a little warning for other projects). And the usage here makes it much more complex, as indirect addressing is combined with the Lookup function.

To avoid unnecessary recalculations on cell changes the VBA code switches calculations off while filling worksheets. That is always a good idea if you fill in much data by VBA code.

6. Final Thoughts

Be aware that there might be a difference between the data retrieved for current or past months if there is some point discussion or Moderator operation that results in removing and reassigning points. Usually this will only apply to the previous or current months, so if you find differences between the sum of your zone points and the total retrieved from the Web, you should retrieve the last month again. Of course this does not work for Member Ranking (no past periods, you remember?). With that you have to recalculate manually, until the result fits.

Also we have to remember that Member Ranking is only calculated once a day, and we have some time shift between our and Experts Exchange's clocks. "Today" is hence relative ;-).

7. Changed since first published

This article was published first on 19th October, and went thru two major revisions since then, to allow for more automation.
Revision 2 introduced record keeping of monthly and yearly Member Ranking.
Revision 3 added the feature for Overall Member Ranking record keeping, and it sets the OneYear range for the one-year statistics now appropriate itself when querying the monthly stats (previously you had to change OneYear yourself).

If you have downloaded the spreadsheet in those "early days", make sure you do again. The monthly worksheets, the Month template and Overall are the same, but everything else has been changed. The last revision is of 30th October.

Please do not forget to press the "Yes" button if this article was helpful.
EE-Stats.xls