Yelp

Scrape Restaurant Reviews from Yelp

Goal:

Use Excel VBA to scrape restaurant reviews on Yelp.com, (1) searching for a food type (ex. burgers) within (2) a certain zip code and (3) retrieving each restaurant review for multiple pages into an Excel worksheet

For each returned search result, scrape the following:
Restaurant Name
Link
# of Reviews
# of Stars
Category
Neighborhood
Address
Phone Number

Example:

In this example, we will search for “burgers” within zip code 10005 and get all the search results from page 1 to page 5

Worksheet Prerequisites:

Cell A1: Input your food search term (seafood, steaks, burgers, etc)

Cell A2: Zip Code

Cell A3: Number of pages to gather data from (5 = scrape all data from Page 1 to Page 5)

Code Breakdown:

Open browser window

Browse to Yelp.com

Input food search term from Excel (cell A1) to Yelp’s category search bar

Input zip code from Excel (cell A2)¬†to Yelp’s location search bar

Click “Search” button

Loop Step 1: For each search result, retrieve the Restaurant Name, Link*, # of Reviews, # of Stars, Category, Neighborhood, Address, Phone Number

Loop Step 2: Click “Next Page”

*Note: When VBA retrieve the URL, it is stored as text, to change it to a clickable hyperlink, add the following:

Set Hlinks = Application.Selection
For Link In Hlinks
Application.ActiveSheet.Hyperlinks.Add Link, Link.Value
Next

Repeat Loop until the result page is one page pass the inputted value in cell A3

Close browser (ie.quit; Optional)

Input appropriate headers for each column in worksheet row 1

Result: