Ego Charts

Jorge Camoes discussed skyscraper bar charts. He writes:

Listen, unless you work for a statistics office, you should never create a chart like this. I know, it’s irresistible to check how well my state ranks, but identifying each and every data point in a virtually limitless bar chart makes no sense in most cases.

Does anyone really look at the top or bottom five before he finds his own state?

Jon Peltier took the idea a step further and created an accordion chart. Sweet. I’m going to take another step (well, more like a quarter step) and make sure that my state is always showing.

If you download Jon’s chart, you can modify it with these steps:

  1. Add GetIP Function (see below)
  2. Name cell N1 StateName
  3. Change N1 formula to =getip()
  4. Change E2 formula to =IF(A2=StateName,B2,IF(ABS(ROW()-1-StateIndex)<3,B2,0))
  5. Fill down column E

The GetIP function is below. You have to set a reference to Microsoft XML (VBE – Tools – References).

Function GetIP() As String
   
    Dim xml As MSXML2.XMLHTTP
    Dim sHtml As String
    Dim lCityStart As Long
    Dim lCityEnd As Long
    Dim lComma As Long
    Dim lMaxTime As Long
   
    Const sFIND As String = “You appear to be from “
   
    Set xml = New MSXML2.XMLHTTP
   
    xml.Open “GET”, “http://private.dnsstuff.com/info/geolocation.htm”
    xml.send
    lMaxTime = Timer
    Do Until xml.readyState = 4
        DoEvents
        If Timer – lMaxTime > (TimeValue(“00:00:05”) * 60 * 60 * 24) Then Exit Do
    Loop
   
    sHtml = xml.responseText
   
    lCityStart = InStr(1, sHtml, sFIND) + Len(sFIND)
    lCityEnd = InStr(lCityStart, sHtml, “”)
    lComma = InStr(lCityStart, sHtml, “,”)
   
    GetIP = Mid$(sHtml, lComma + 2, lCityEnd – lComma – 3)
   
End Function

Severely not tested. If you don’t live in the US or if you have a comma in your city name, it will blow up. I couldn’t find a decent way to get the state from the ip address, so I resorted to scraping. The site hostip.info looked promising, but it returned “Unknown City” for my IP.

Posted in Uncategorized

8 thoughts on “Ego Charts

  1. “Does anyone really look at the top or bottom five before he finds his own state?”

    Good point. I sometimes forget these are real people using these charts. The GetIP function is a nice, if somewhat friable addition. An alternate approach would be to find the user’s credit card information on the computer, and get the state from the billing address.

  2. Friable or not it going in my code library – by which i mean in a few months I will be looking around DDoE to try and find it again! Excellent little snippet Dick, thanks.

    p.s. I had to look up what friable meant, Stop begin so clever Jon, you have to dumb it down for me!

  3. Jon,
    Using “friable” in a comment ammounts to extraneous othogonality :-) :-). Or is that just the sound of my paradigm shifting?

    Dick, how about we add a function to DDoE to vote on cute words added to comments?

    OK, I had to look it up, too. Nice one Jon. You’d think an engineering degree and an MBA would have prepared me for life.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *