<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Getting your EE Ranking statistics in Excel

Published on
18,709 Points
7,409 Views
18 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
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 zoneOverall  Month is the template worksheet for individual monthsMonth Template  Ranking collects data of each month's worksheetRanking (updated 30 Oct 2009) Ranking Example Zones (updated 30 Oct 2009)

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:Month ExampleThe 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)

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

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

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
18
Comment
Author:Qlemo
90 Comments
 

Administrative Comment

by:tigermatt
Qlemo,

Congratulations! Your article has been awarded EE Approved status.

Thank you for this contribution to Experts Exchange. Retrieving the data from the site via Excel Web Queries is rather impressive, as is the data crunching the workbook then does with that data.

This gets my "Yes" vote above.

tigermatt
EE Page Editor
0
LVL 61

Expert Comment

by:Kevin Cross
Nice trick, Qlemo.
Voted yes above.
0
LVL 50

Expert Comment

by:Dave
Nice work Qlemo :)

So why don't we see more of you in the Excel TA?
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

LVL 74

Author Comment

by:Qlemo
If you want to see something you can use for your Overall milestones (something I forgot to implement obviously), you need to change some things, but that should be no challenge for anyone:

- create a new "Overall" area in M to P:
-- copy columns I to L, paste them at M (giving M to P);
-- change title in row 1 of M:P to "Overall"
-- remove the contents of M:O, retaining the column titles
-- remove the lines above of each year's last month in this area

- change the VBA code of getMemberRanking to the following code snippet (shown starting with the Cleanup comment). The WITH following the shown WITH in the original code is not needed, but you can leave it there if you like, to have the Overall ranking stats underneath the yearly stats, too.

Having done that, you will have cumulative overall stats in the new columns. With conditional formatting, you can implement a 1 Million colour mark, for example; or you apply some VBA code to make more complicated stuff.
' 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

Open in new window

0
LVL 74

Author Comment

by:Qlemo
This is the third revision now. If you have read the article before, it's about time to do again :-)
The article contains all the code and comments up to now.

I want to thank Mark Wills, who has gone thru all revisions with me (and has been very patient and helpful).
Really outstanding, and great PE work.
0
LVL 58

Expert Comment

by:tigermatt
I've been watching this article. Very nice changes, thanks Qlemo.
0
LVL 74

Author Comment

by:Qlemo
I just noticed that Article points are not included in the Member Ranking. They need to get obtained separately, with a second go.
0
LVL 61

Expert Comment

by:Kevin Cross
That is my understanding as well, Qlemo.  Just as a tidbit, although the current zone ranking includes the Article points you will note that certificates are based solely on the question points.  Therefore, you may see you have 50k points when you run your tool but not yet be granted the Master title until your zone total  less Article points for that zone is 50k and so on.

Thanks for the reminder and again for the tool.

Great work!
0
LVL 74

Author Comment

by:Qlemo
Yes, that is exactly what happened some days ago to me - over 50k, but no Rank. Now we know why.
0
LVL 74

Author Comment

by:Qlemo
Bug: Please correct the code in getMemberRank

    .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)"

with

    .Range("A13:A15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 4, FALSE)), 0, VLOOKUP(WhoAmI, Lookup, 4, FALSE))"
    .Range("B13:B15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 1, FALSE)),"""", VLOOKUP(WhoAmI, Lookup, 1, FALSE))"
    .Range("C13:C15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 2, FALSE)),"""", VLOOKUP(WhoAmI, Lookup, 2, FALSE))"
    .Range("D13:D15").Formula = "= IF(ISNA(VLOOKUP(WhoAmI, Lookup, 3, FALSE)), 0, VLOOKUP(WhoAmI, Lookup, 3, FALSE))"

and

  With [Ranking!A5].Offset(DateDiff("m", [Ranking!A5], Now ))

with

  With [Ranking!A5].Offset(DateDiff("m", [Ranking!A5], Now - 1))

Both changes are for correct detection of month change, and for fallback if no Member Ranking is available yet.
0
LVL 41

Expert Comment

by:Sharath
Don't know about others. But I am getting run times when clicking the buttons in the excel.
0
LVL 74

Author Comment

by:Qlemo
I'm getting them somethimes, too, especially in the monthly zone rankings, but it seems to be a timeout issue. If I go into Debug, then continue (F5), all is fine.
0
LVL 54

Expert Comment

by:b0lsc0tt
Wow!  Very impressive.  Finally had a chance to look at this for a few minutes and open the XLS file.  I must make time to see more because I really like it.  Thanks!  Reminded me of the reports I got from Ameba's site and one others.
0
LVL 15

Expert Comment

by:yuk99
Just found about this article in the newsletter. Sounds interesting. But I could not get any data with run-time error 1004: This Web query returned no data.
I've disabled all macro securities, tried different members, but no lack. May be something was changes since the last comment?
0
LVL 74

Author Comment

by:Qlemo
Yuk,
Do you use Internet Explorer for EE? And checked the "Remember Me"?
Both is required for the Web Query feature to work.

For testing, try what is said under point 5, in the first paragraph after the code snippet. The URL you need is     http://www.experts-exchange.com/experts.jsp?etIndex=4
That should show the Member Ranking page Overall for Questions for your account. If you see that page, the Excel Macros should work as intended.
0
LVL 76

Expert Comment

by:Alan Hardisty
Firstly, many thanks for a brilliant spreadsheet.  It is extremely useful and I am sure others will make good use of it.

I'm running Office 2010 beta and the Ranking page is empty apart from the current month.  Should it be displaying info for other months?

Alan
Copy-of-EE-Stats.xls
0
LVL 76

Expert Comment

by:Alan Hardisty
Ignore the above - just read the entire page and discovered how to do it.  Sorry.

Alan
0
LVL 15

Expert Comment

by:yuk99
Yes, Explorer was the key. Sorry I missed it from the text. Great tool!
0
LVL 74

Author Comment

by:Qlemo
I'm happy that it was that easy. Have fun!
0
LVL 42

Expert Comment

by:Meir Rivkin
awsome tool Qlemo, all the best !!!
0
LVL 22

Expert Comment

by:theGhost_k8
Great tool but some how I'm not able to make use of it!!

I tried it with IE + Remember me.
I'm on XP + office 2003.
I never got my excel sheet filled and also check attached image which appears sometimes.
err.JPG
0
LVL 74

Author Comment

by:Qlemo
The Ghost,
Did you check

"
For testing, try what is said under point 5, in the first paragraph after the code snippet. The URL you need is     http://www.experts-exchange.com/experts.jsp?etIndex=4
That should show the Member Ranking page Overall for Questions for your account. If you see that page, the Excel Macros should work as intended.
"

yet? The error you get sometimes might come from the empty sheets, but I'm not certain.
0
LVL 22

Expert Comment

by:theGhost_k8
Hi, things do work with URL but my excel get stuck when I press ANY button.
[ mean while Section 5 is for advanced users :) ]
Well, even if I don't get error, I don't get output either.
I'm liking this thing but not able to use it like every one else  :(

It seems not as simple as put your EE Profile name and Press a button. Or am I missing something?
0
LVL 74

Author Comment

by:Qlemo
There is something completely wrong, and I can't even guess about what.

Seems as we have to do some deep-dive debugging then.
* In RankDef worksheet, set "Max Zones in Overall" to 10
* Open VBA editor (Ctrl-F11),
* choose modul RankEE,
* search for Sub GetWebData (should be around line 70),
* set a breakpoint (F9).
* After that, go back to the buttons, and press "Overall Rank".
You should immediately be positioned in GetWebData. Step thru with F8. The first FOR should only be executed once, the next one 10 times. Go on, and on and on and on ....
The last command you should see is End Sub of btnOverall_Click().

You can also try to press the Shortcut keys (ctrl-z for Overall), that will omit the btnOverall_Click() call and go directly to getZoneRanks.
0
LVL 22

Expert Comment

by:theGhost_k8
Good debugging steps.

The first for loop for Zones exists with this condition:
If rng.Cells((i + 10 - 1) * 1.1, 1).Value = "" Then Exit For
Rest executes fine without producing output!
0
LVL 74

Author Comment

by:Qlemo
That would mean you do not get any data, or the table index is wrong. I crosschecked, and did not see any change you could have made to cause this.

If you use this in Web Query: http://www.experts-exchange.com/experts.jsp?etIndex=4&expertName=Qlemo
do you get this layout?
EE-Rank-WebQuery.gif
0
LVL 22

Expert Comment

by:theGhost_k8
Yes I do.
layout.JPG
0
LVL 58

Expert Comment

by:tigermatt
@Alan,

http:#c6942

I'm also running the Office 2010 Beta (64-bit) and seem to be having problems. Am I missing something silly with this? Can you let me know how you resolved your problem?

Thanks,

Matt
0
LVL 74

Author Comment

by:Qlemo
Ghost, what is that for a browser? It isn't the one used for Web Query, at least I am missing the yellow boxes with arrows (see my image).
0
LVL 22

Expert Comment

by:theGhost_k8
Okay sorry I misread...
First I did it with web query it gave me sign in page.
Later I signed in and again tried and it gave what you shown here.
After web query run success I tried again that EE-Stats.xls but :(
webquery.JPG
0
LVL 74

Author Comment

by:Qlemo
Open the VBA editor, search for     qt.delete    , put a ' in front, and run "Current Month".
Now, in "2009 12", Cell A2, Context menu, "Edit query" (or similar). Should bring up the correct page, filled, and green check on Member Ranking table.
(Note for later: Don't forget to remove the ' after we are finished debugging.)
0
LVL 22

Expert Comment

by:theGhost_k8
Right... It is so...
thismonth-debug-1.JPG
0
LVL 74

Author Comment

by:Qlemo
The zone ranking table does not have green check square (only the yellow one for "table able to be marked").
Please click on that yellow square (only the ranking table should be surrounded with a big rectangle resp. the table gets an visible overlay),
press "Import",
then go into VBA editor,
press Ctrl-G
and enter   ? Worksheets("2009 12").QueryTables(1).WebTables

The result should be 11. If not, your web page has more or less tables then mine. The number displayed should then be entered whereever the line
.WebTables = "11"
is shown (2 occurances).
0
LVL 76

Expert Comment

by:Alan Hardisty
@Tigermatt,

I followed the comment of Qlemo 02/11/09 04:54 PM, ID: 5055 to change the underlying code and then saved it.  I am on Vista Business with Office 2010 Beta 32-bit.

To fill in the data for the year I did the following:
open VBA Editor ([Alt F11]),
      open Immediate Pane ([Ctrl G]),
      and type
call getYearZoneRanks(2009)

(at the end of section 4 in the original post).
0
LVL 22

Expert Comment

by:theGhost_k8
@Qlemo
Yepp and now I got things working :) happy...
It got resolved by setting   .WebTables = "9"  at two places as said.
Nice tool.
"Yes"ed.
0
LVL 58

Expert Comment

by:tigermatt
Thanks Alan.

I managed to work the issue out with Qlemo's help in the end - it turned out that the web query interface was not honouring the Experts Exchange cookie I had already set previously (with 'remember me') via Internet Explorer. I had to manually edit the query on one of the sheets and log in via that Excel interface. To edit the query you must right-click cell A2 in one of the sheets, and then choose Edit Query.

-Matt
0
LVL 23

Expert Comment

by:Kamaraj Subramanian
I am getting error in this line.

qt.Refresh

Do i need to enable reference before i run the code ?

please suggest
0
LVL 74

Expert Comment

by:Glen Knight
Can someone share a workng copy on 2010 beta?
I have run the command alanhardisty has posted above but I cannot get the summary page to refresh.

I am using Windows 7 x64 and Office 2010
0
LVL 76

Expert Comment

by:Alan Hardisty
Having just rebuilt my laptop with Windows 7 64-bit and Installing Office 2010 Beta, I cannot get the spreadsheet to work at all.  It fails on the Member Rank page with:

Run-time error '1004':
This web query returned no data. To change the query, click OK, click the arrow on the name box in the formula bar, click the name of the external data range for the Web query, right-click the selection, and then click Edit Query.

It seems to be failing to connect to the website and suck any data down at all.

Any ideas?
0
LVL 76

Expert Comment

by:Alan Hardisty
Think I have sorted it.  Went into Excel, clicked on the Data Menu and then clicked on From Web, pasted http://www.experts-exchange.com/experts.jsp?etIndex=4&expertName=expertname and this returned a web page for EE that did not auto-login.

So, I logged in on this page and made it remember the username / password.

Closed out and re-ran the All In One button and hey-presto.

Why the heck does the get web data window not auto-login when I have it saved in IE?

Alan
0
LVL 58

Expert Comment

by:tigermatt
Alan,

It worked properly (auto-login) when I was using the spreadsheet under XP/Office 2007. Since upgrading myself to Win 7 and Office 2010 Beta, I've also had issues and have been forced to manually log in via the Excel window.

-Matt
0
LVL 61

Expert Comment

by:Kevin Cross
Nice, Alan.
That will save me time if I ever get to load Windows 7 on my machine as I will probably try out the Office beta also.  Not any kind of expert in Office 2010, but seems they are tauting the improved Office Web Apps which may mean they made a bigger separation in the internal browser for Office like its own web cache/cookies storage.

Either way, as I said, glad you found it...

HNY!
--isa
0
LVL 76

Expert Comment

by:Alan Hardisty
Thanks Matt - nice to know I am not alone!

Glen - I would imagine that this will fix your problem too.

Happy New Year to all of you.

Alan
0
LVL 58

Expert Comment

by:tigermatt
Happy New Year and best wishes for 2010 :)
0
LVL 76

Expert Comment

by:Alan Hardisty
mwvisa1 - Happy to help.  Can't leave it all to Qlemo ;-)
0
LVL 74

Author Comment

by:Qlemo
Thanks, Alan, as I'm stilling fighting having NOT to use Office 2007 ;-) I'm not at all keen on trying 2010 beta at the moment. I allowed on my fresh W7 64bit Install for a 2007 copy, but don't intend to walk on the 2010 path, at least as long as it is beta.

I just tried if using both IE 32bit and IE 64bit could be an issue, but they share the same cookies.

Matt, do you have to manually log in each time, or was that an one-time-only action?
0
LVL 76

Expert Comment

by:Alan Hardisty
That was a one-time only login for me.
0
LVL 74

Expert Comment

by:Glen Knight
I have just followed Alan's instructions and it worked perfectly for 2010, just clicking the previous year button now so will let you know.

Can I offer a suggestion for an enhancement for anyone who is interested? It would be good if you could have 2 expert ID's entered and then have comparitive columns/cells for both experts.
0
LVL 24

Expert Comment

by:Tracy
I get errors for any of the buttons I press... am I missing something, is there a reference that needs to be added?
0
LVL 76

Expert Comment

by:Alan Hardisty
What errors are you getting - that bit would be useful to know!

Also - please check out my post above (02/01/10 12:00 AM, ID: 7600) which might sort out your problem.
0
LVL 24

Expert Comment

by:Tracy
I get the following errors for the following buttons:

All In One, Current Month, Current Year, Prior Month, Prior Year Button:
Run-tim error '424' Object Required
Sub: getMonthZoneRanks
Debug line: GetWebData .Cells(2, 1), [maxMonthZones].Value, month, year

Member Rank button
Run-time error '1004' This Web query returned no data.
Sub: getMemberRanking
Debug Line: qt.Refresh (Comment heading:  Now retrieve Overall, Year, Month)

The Overall Rank button runs, but no data is filled in in the Overall worksheet.
0
LVL 76

Expert Comment

by:Alan Hardisty
You will probably fix that by referring to my post above:

Open up Excel, click on the Data Menu and then clicked on From Web, paste http://www.experts-exchange.com/experts.jsp?etIndex=4&expertName=broomee9 and this should return a web page for EE that does not auto-login.

Log in to this web-page and set it to remember the username / password.

Close the window and re-run the All In One button and hey-presto - should work now.
0
LVL 24

Expert Comment

by:Tracy
When I click on the Data Menu, I don't see 'From Web' anywhere, not in the submenus or the sub sub menus.  I'm using Excel 2003.
0
LVL 76

Expert Comment

by:Alan Hardisty
Well - in that case ........

Please try the following:
Choose Data, Import External Data, New Web Query, enter http://www.experts-exchange.com/experts.jsp?etIndex=4&expertName=broomee9 and fingers crossed.

If that does not work - I'll leave it to anyone who has Excel 2003 - have not used that for years I am afraid and don't have a machine with it on :-)
0
LVL 74

Author Comment

by:Qlemo
In 2003, there should be a submenu like "Import External Data", and then "New Web Query". I do not know exactly, since I do not have an English Office at hand.
0
LVL 24

Expert Comment

by:Tracy
Thanks for the effort.  Same errors though.
0
LVL 76

Expert Comment

by:Alan Hardisty
If you want - I can work out the numbers for you and email you the file filled in if you email me via my profile!
0
LVL 74

Author Comment

by:Qlemo
When you do the debugging as Alan posted (via "New Web Query"), what do you see?
0
LVL 24

Expert Comment

by:Tracy
OK, just sent you an email.  Thanks for the help!
0
LVL 76

Expert Comment

by:Alan Hardisty
Spreadsheet on it's way.
0
LVL 24

Expert Comment

by:Tracy
Got it.  Very nice, thank you.
0
LVL 76

Expert Comment

by:Alan Hardisty
Can anyone offer Excel 2003 advice on how to get EE Stats to work - I don't have a 2003 machine handy I'm afraid?
0
LVL 74

Author Comment

by:Qlemo
I'm still on 2003 ...

Sorry, I have seen that I accidentally posted hidden twice. Not suprising that noone listened to me.
I have changed those comments to visible now (http:#c8193 and http:#c8185)
0
LVL 18

Expert Comment

by:Ravi Agrawal
Hi Qlemo,

I am trying for hours to make this worksheet work,

Followed debug steps of comment Ids below--

I am getting the same thing as http:#c7206 (same runtime error 13)

followed http:#c7245 (could not figure out what to do, so skipped it without doing anything)

http:#c7280 (got to display same position & screen as theGhost_k8)

followed http:#c7281 

I get the value 9 in place of 11 for ----
? Worksheets("2010 2").QueryTables(1).WebTables

How to sort it? I guess I am very near but blame my near to zero skills of Excel VB.

Ravi.
How to fix it?

Ravi.
0
LVL 76

Expert Comment

by:Alan Hardisty
@grtraders - What OS / Excel version are you using?
0
LVL 18

Expert Comment

by:Ravi Agrawal
Qlemo sorry to bother you,

Got it fixed, by following http:#c7301

Brilliant work, Amazing skills you have man, Now I understand it.

Ravi.
0
LVL 74

Author Comment

by:Qlemo
Meanwhile I'm using both Excel 2003 (Vista 32bit) and 2007 (W7 64bit), and do not have to change .WebTables to 9. No idea why some have to use the 9, while others like me use 11.
0
LVL 18

Expert Comment

by:Ravi Agrawal
I don't know a thing about excel code (my humble apologies here, Excel coding is just not part of my skills), just have a broad idea of what it does and that's all. I am using Windows XP SP3 & Office 2007 (in comptibility mode for your worksheet). If you want I can send you my statistics just in case you wish to look where the difference is setting in or you can put down the steps where I could capture any data for you & report back.

Something offtrack -- Are you done with Editing of my Article http:/A_2428.htm ? It has already received 8 votes before publishing.

Ravi.
0
LVL 74

Author Comment

by:Qlemo
I suppose it is nothing with the Excel sheet or code itself, but with your local environment. I had the code running on XP SP3/Office 2003 without changes.

The only thing you could do for me is to create a New Web Query, type in the URL I use for getting Member Rank (http://www.experts-exchange.com/experts.jsp?etIndex=3&expertName=grtraders), maximize the Window and create a Screenshot of it. Maybe I can then see the difference.
0
LVL 18

Expert Comment

by:Ravi Agrawal
Could be, How do I create a New Web Query? to get a screenshot..

Assume me to be completely dumb, but not dumb enough to follow what you say :-)

Ravi.
0
LVL 74

Author Comment

by:Qlemo
Go into the Data menu, Import External Data, New Web Query. A dialogue will open. Insert the URL above. Done.
0
LVL 18

Expert Comment

by:Ravi Agrawal
Here, but I think I should get the Ranking Page and not the Points Page.

Ravi.
webquery.PNG
0
LVL 18

Expert Comment

by:Ravi Agrawal
0
LVL 74

Author Comment

by:Qlemo
Could you provide both again, but not scrolled down? There might be less objects on the top then when I do.
0
LVL 18

Expert Comment

by:Ravi Agrawal
Here two series

webquery A 1 to A 3 for http://www.experts-exchange.com/experts.jsp?etIndex=4&expertName=grtraders

webquery B 1 to B 3 for http://www.experts-exchange.com/experts.jsp?etIndex=3&expertName=grtraders

All zipped up, too many images would have made it ugly :-)

Ravi.

webquery.zip
0
LVL 74

Author Comment

by:Qlemo
Thanks for the screenshots. But: No difference found. I'm clueless :(
0
LVL 41

Expert Comment

by:Sharath
Qlemo: Can we have a final version of this excel sheet with all macros embedded? Appreciating your effort here.
0
LVL 74

Author Comment

by:Qlemo
I'm working on an extension which gets the ranking in each zone in addition. I'll need some days more to get that smooth, and incoporate all changes I did meanwhile into the exisiting public workbook. Probably I will open a follow-up article for it, as this thread is getting large and confusing. Anyway I'll keep you informed.
0
LVL 41

Expert Comment

by:Sharath
Qlemo: Its not an order :) Work on it in your leisure time only. Thanks for your selfless effort.
0
LVL 61

Expert Comment

by:HainKurt
this should be a part of EE member site... they may change the website any time, which may ruin all your work...
0
LVL 61

Expert Comment

by:HainKurt
not working for me. Windows 7, Office 2007, IE 8

I am getting error here (Ln 273 - GetMemberRanking)

    .Range("B1").Value = Replace([ScratchArea].Range("D15").Value, ",", "")

Immediate window

?[ScratchArea].Range("D15").Value
Error 2042

No data at all...
0
LVL 74

Author Comment

by:Qlemo
Strange. Could you post the results of
     ? [ScratchArea].Address
     ? [ScratchArea].Count
The latter should show 55 (11 rows, 5 columns).
0
LVL 74

Author Comment

by:Qlemo
I'm sorry having to say the WebQueries used here don't work for about a month, because the site changed the stats pages twice meanwhile.

The stats are improved now, but they cannot be used by WebQueries anymore. I'm working on a "inofficial" and unsupported method to pull the stats - the method is working, but needs several changes in the code and the sheets used to adapt to the changed format.

The major two improvements are that the Zone Rank is shown when calling the unfiltered Zone Ranking stats, and that you can call for Overall or Question or Article points only. Previously the Zone Rank has been Overall only.
The con is that you can only ask for stats for the recent period - a paricular day, month or year is no longer available.
0
LVL 58

Expert Comment

by:tigermatt
Qlemo,

A shame these changes have been made, as having access to those stats made great applications like yours possible. I'm looking forward to the revamped version.

-Matt
0
LVL 74

Author Comment

by:Qlemo
As soon as I have managed to get the automation right, I need to provide code to automatically convert the old into the new workbook. I have done so many changes meanwhile the old and new format are not "compatible". Else you would loose your history data. Another obstacle, and one day more of work. Sigh.
0
LVL 63

Expert Comment

by:SysExpert
How about a version for people that do not have any history files yet, for now.

And then a later version to handle getting the history back.

Thanks !
0
LVL 74

Author Comment

by:Qlemo
You do not have a history? Strange ;-).

Yes, I think I should do so. I will (probably) create another article for that, because this one is already confusing enough. Scheduled for the weekend.
0
LVL 74

Author Comment

by:Qlemo
Sorry, folks, the XLS is ready now, but the article is not ... I have to explain some stuff, and that is taking longer than I thought.
0
LVL 74

Author Comment

by:Qlemo
0
 

Administrative Comment

by:Patrick Matthews
Qlemo,

It is my privilege to let you know that this article has been selected for the Editors Choice designation, EE's highest accolade for articles.  Great work!

matthewspatrick
Page Editor
0

Featured Post

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Join & Write a Comment

Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month