We have moved to a new blog site!

Please follow us on this site: http://www.excelwebscrapingsolutions.com/

Advertisements

Extract data from web

All of us at some point were faced with a task to get some data from the web. Usually if we are looking for some sort of business information the first place we will be looking into is yellowpages or some other public directory. Imagine that you need to pull let’s say auto repair shops in the US for your marketing campaign. This task will seem endless if you would go and start copy/pasting the information by hand. Vast majority of people are not familiar with the powers that a tool like Excel has.
If you are little bit tech savvy you might want to look into the basics of Visual Basic programing. Since Excel is a Microsoft tool it will enable you to automate a lot of things that you would otherwise do manually.
So coming back to yellow pages. You are all familiar with this screen

Image

You would have to do a boring task of inputting all the cities or states in the designated search box, then view the results, copy all information from the results and do it all over again. Makes sense to automate something that you have to do all over again in the same steps?

You can write a basic search macro that would take a list of cities that you have on a worksheet.

sheet_cities

Here is a snippet of code that is used to loop through the cities and place them in the search bar of Internet explorer when automating this process:

yellowpages_code

 

You are done with part I of automating Yellowpages. Now the next part is adding the part of the code that will extract the information. This is more extensive and requires some research from the developers end to figure out how to get the parts of information that he needs. The task involves going throguh the HTML tag in the source code to find the tags, class names and other elements that hold the key information.
After running the vba code that has all the necessary elements you would get results in a XLS sheet like these:
results

A trivial task that would take for ever with Excel can be completed within few hours and you get well organized results in a XLS sheet that you can sort, filter and do any other data manipulation task.

Welcome to our new blog

In this blog we will write about Excel usage in pratical life, how you can save money with Excel automation and what are all the possibilities with Excel. Also we will post practical advices and help you solve your Excel problems.