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).
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).
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 zoneMonth is the template worksheet for individual monthsRanking 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]),
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 ExplicitSub 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 TrueEnd SubSub 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 TrueEnd SubSub 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 TrueEnd SubSub Formula2Value() Dim c As Range SetCalc False ActiveSheet.EnableCalculation = False For Each c In Selection c.Formula = c.Value Next c SetCalc TrueEnd SubSub 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 TrueEnd SubSub 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 WithEnd SubSub 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 = TrueEnd SubSub SetCalc(calc As Boolean)Dim ws As Worksheet For Each ws In Worksheets ws.EnableCalculation = calc Next wsEnd SubFunction 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 FunctionSub 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 WithEnd Sub
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
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