Web Scraper

posted Nov 7, 2015, 12:18 PM by Craig Hatmaker   [ updated Nov 7, 2015, 4:48 PM ]

Make your own web scraper apps!
A web scraper copies data displayed by web pages into, in this case, Excel.  WWWHelper.xlsm is a free XL workbook that creates other web scraper apps without coding.

Intro
Many people don’t realize getting web tables into XL has been simple enough with XL’s Get External Data option and now with PowerQuery. As long as the web data is in an HTML table accessible from a static URL, and does not need to merge with other data, XL’s native tools work really well. When web data does not meet these criteria, this tool works better and easier.

My customer requests are increasing for web data in XL. The data often resides in multiple web pages and may require populating and submitting web forms to get the needed data. I use this tool to automate web scraping and overcome XL’s native limitations. Example uses include:
  • Stock values
  • Exchange Rates
  • CPU Benchmarks
  • Applicant data from career sites
  • Government carrier safety statistics
  • Phone number telecoms for texting from Outlook
  • CRM data from social media sites.
  • Fantasy football player statistics.
  • And more...
WWWHelper.xlsm Demo
To meet customer demand I created a tool to simplify scraping web elements into XL’s tables, or ranges. Simplifying this task is critical because there are no guarantees that methods used to pull data from a particular website today will work tomorrow. We are completely at the website designers’ whim. The importance of being able to maintain web scraping apps quickly and easily cannot be overstated.

While this tool uses VBA, we don’t need to know VBA to use it. This tool uses normal XL tables to hold paths that map where our web data is and how to get it into XL. Thus, the only skill we need is the ability to enter data into normal XL tables.

For those with VBA skills, this tool provides a wealth of code and examples that make creating web scrapers quick and easy. Included are functions to populate and submit web forms opening up websites that require sign-ons or input parameters for selecting data – such as a Google search.

This workbook is available only by request: Craig_Hatmaker@Yahoo.Com

https://dl.dropboxusercontent.com/u/13737137/Projects/LDAP/LDAP.pdf

This PDF is available only by request: Craig_Hatmaker@Yahoo.Com


Discuss this post or other BXL topics at: facebook.com/BeyondExcel
Comments