asked on

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

zalazar

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.:

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

David Johnson, CD

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.

David Johnson, CD

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=*

Dale Logan

ASKER

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

zalazar

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

Dale Logan

ASKER

Looks like it got worse. See updated file.
Handicaps.log

ASKER CERTIFIED SOLUTION

zalazar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Logan

ASKER