Golf handicap lookup

I recently joined a golf group of about 40 people. Twice a month, a couple of my buddies have to go a website and look up everyone's new handicap index. They've been doing this for years and constantly complain about the amount of time it takes to do this. I know there's got to be a way to make this very easy. I tried to use "Get External Data from Web", but the site is not formatted to where Excel sees it as a table. However, when I right click on the section I do see some information in there that may allow for the lookup to be automated. I am fairly familiar with using VBA in Access, but not much at all for Excel. If anyone could offer some help to automate this process it would be greatly appreciated.

The attached Excel file is an example of the information needed to lookup handicaps. Each golfer has a GHIN number. The website offers the ability to lookup handicaps one at a time or multiple golfers at a time. The web address is: http://www.ghin.com/lookup.aspx

Thanks for any help.

GroupHandicaps.xlsx
Dale LoganConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zalazarCommented:
I have created a PowerShell script in combination with cURL that can do this.
First download cURL from this location.

http://www.paehl.com/open_source/?download=curl_743_0_ssl.zip
Which is cURL version 7.43 with SSL support
See also: http://www.paehl.com/open_source/?CURL_7.43.0

Open the zip file and unpack it to a directory
e.g. C:\Scripts\GetHandicap

Create a text file called "Users.txt" in the same directory and type in all the GHIN numbers you want to look up.
IMPORTANT: one number per line
E.g.:
0331361
0331362
0331363
0331364

Open in new window


Then create a file called "GetHandicap.ps1" in directory C:\Scripts\GetHandicap
Copy the PowerShell script below in it and save it.

$strUsers = Get-Content "Users.txt"
foreach ($GHIN in $strUsers)
{
  & .\curl.exe "http://widgets.ghin.com/HandicapLookupResults.aspx?entry=1&ghinno=$GHIN&css=default&dynamic=&small=0&mode=&tab=0" -s -o GetHandicap_Tmp.htm | Out-Null
  $strFile = "GetHandicap_Tmp.htm"
  $strHtml = gc $strFile
  $strMatch = $strHtml -match '<span id="ctl00_bodyMP_tcItems_tpHandicapCard_headerGolferHandicap"[^>]+>(.*?)</span>'
  $strMatch = $strMatch.Trim()
  $strHandicap = $strMatch -replace '<.*?>'
  Write-Host "$GHIN;$strHandicap`r"
}

Open in new window


Open a command prompt window (cmd.exe) and type the following to run the script:
cd /d C:\Scripts\GetHandicap
powershell.exe -ExecutionPolicy Unrestricted -File GetHandicap.ps1

Open in new window


The output should be the user followed by the handicap which has been looked up from the Internet.

If you want to output it to a file you can simply redirect the output via:
powershell.exe -ExecutionPolicy Unrestricted -File GetHandicap.ps1 >> Handicaps.log

Open in new window

0
David Johnson, CD, MVPOwnerCommented:
The formula's are available.. for example see http://handicapserver.com/get_started#method
and one only needs to input your scores.. the above site costs $3 /year / golfer. There probably are android/ios apps that will allow you to update your handicap as well.
0
David Johnson, CD, MVPOwnerCommented:
to run that script you will need curl.exe here is a list of win32 sources
http://curl.haxx.se/dlwiz/?type=bin&os=Win32&flav=-&ver=*
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale LoganConsultantAuthor Commented:
Zalazar,

This is looking really cool. There must be something slightly wrong. In addition to the information that you said would be in the output, there are error messages. See the attached file for that.

I'm sure there's a way to automate the command prompts that have to be entered. I will need to research that. I am hoping to turn this over to a couple of retired guys that would never be able to pull that off. I want to figure out a way for all of this to be run by clicking on a button in an Excel file.

David,

We are not looking for a way to calculate handicaps. Our club uses the GHIN system and we are required to enter all of our scores there. Just looking for an easier way to extract the handicaps to be used in our matches.

Thanks, Dale
Handicaps.log
0
zalazarCommented:
Thanks very much.
I have added a check on the $strMatch variable which should hopefully resolve it.
Please find the new PowerShell code below.

$strUsers = Get-Content "Users.txt"
foreach ($GHIN in $strUsers)
{
  & .\curl.exe "http://widgets.ghin.com/HandicapLookupResults.aspx?entry=1&ghinno=$GHIN&css=default&dynamic=&small=0&mode=&tab=0" -s -o GetHandicap_Tmp.htm | Out-Null
  $strFile = "GetHandicap_Tmp.htm"
  $strHtml = gc $strFile
  $strMatch = $strHtml -match '<span id="ctl00_bodyMP_tcItems_tpHandicapCard_headerGolferHandicap"[^>]+>(.*?)</span>'
  If ($strMatch) {
    $strMatch = $strMatch.Trim()
    $strHandicap = $strMatch -replace '<.*?>'
  } else {
    $strHandicap = "No information found"
  }
  Write-Host "$GHIN;$strHandicap`r"
}

Open in new window

0
Dale LoganConsultantAuthor Commented:
Looks like it got worse. See updated file.
Handicaps.log
0
zalazarCommented:
That's a pity. On a computer with Windows 8 and PowerShell 3.0 it actually works fine.
I have tested it on Windows 7 and can reproduce the problem.
Windows 7 uses PowerShell 2.0 by default.
I modified the code (please see below) to avoid this problem.

$strUsers = Get-Content "Users.txt"
foreach ($GHIN in $strUsers)
{
  & .\curl.exe "http://widgets.ghin.com/HandicapLookupResults.aspx?entry=1&ghinno=$GHIN&css=default&dynamic=&small=0&mode=&tab=0" -s -o GetHandicap_Tmp.htm | Out-Null
  $strFile = "GetHandicap_Tmp.htm"
  $strHtml = gc $strFile
  [string]$strMatch = $strHtml -match '<span id="ctl00_bodyMP_tcItems_tpHandicapCard_headerGolferHandicap"[^>]+>(.*?)</span>'
  If ($strMatch) {
    $strMatch = $strMatch.Trim()
    $strHandicap = $strMatch -replace '<.*?>'
  } else {
    $strHandicap = "No information found"
  }
  Write-Host "$GHIN;$strHandicap`r"
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale LoganConsultantAuthor Commented:
Perfect. Thank you very much for the solution. I will now research how to run this from Excel.
0
Dale LoganConsultantAuthor Commented:
zalazar,

Just so you know, I've asked a new question that is related to the solution you provided.

Question
0
zalazarCommented:
You're welcome and very good that it works.
Thanks for posting the link to the new question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.