J-walk posted about a tool that exports the results from Yahoo local search as CSV.
Here’s an alternative that uses Excel to retrieve the results from the Yahoo Local API. The solution requires Excel 2003 because it uses XML and lists.
(aside, you can use all of the Yahoo API’s in Excel 2003, here’s the Image Search In Excel)
Yahoo Local Search In Excel
Enter a query term, zip code, and radius, and the spreadsheet returns the Yahoo local results for the query. Download Excel Local Search Sreadsheet
UPDATE I removed the Radius Field from the spreadsheet. According to Yahoo’s documentation, it doesn’t work with zip code. I also created a zipcode fix to keep excel from dropping the zeros on zipcodes that start with a zero. Many Thanks for testing Jordan.
Update 2 Daily Dose of Excel posted a version that works with versions of Excel prior to 2003
April 5th, 2005 at 1:12 pm
This doesn’t work for me.
Using excel 2003, have macro security set to medium, so it asks when I open it if I want to enable macros, to which I say yes.
Even if I do nothing but put in my zip code and press the button, I get no results.
Could it be that I’m having a problem because my zip starts with a zero?
08736 to be exact. I tried setting the format of the cell to Special|Zip code.
Any ideas?
April 5th, 2005 at 1:25 pm
I tried w/ a different zip, and the problem does seem to be the leading zero.
Also, the radius seems broken as well. I did a search for restuarants in a 30 mile radius of marietta GA (where I used to live), and all of atlanta should show up.
doing the search on yahoo directly yields 125 listings, I was getting 20 on the excel page.
For what it’s worth… I’m not trying to be a pest.
the fact that I could search anything from excel freaking rocks.
April 5th, 2005 at 1:28 pm
Hey jordan, no problem at all….
You are right It is the zip code.. I updated the spreadsheet and uploaded it.
If you downlaod it again you will find it works for your zip now.
April 5th, 2005 at 1:43 pm
Jordan, Correct again, Radius does not work with Zip Code, just city or street. I was using the tool Jwalk posted to as a guide, I guess that’s wrong too, I’ll have to check.
Straight from the Yahoo site:
“The distance as calculated by one of the following methods:
When you enter a street address along with your search term, this is the distance from that particular street address to each result.
When you enter your search term along with a city or a city and state, this is the distance from the city center to the business you’re looking for”
As for resultcount, the max you can return is 20 results.
April 5th, 2005 at 2:00 pm
Also, I’ll leave this as a basic example of how to get some Yahoo api results into Excel, however,,,,
According to the documentation you can get 20 results per query, however using the “start” variable in the string you send you can return various pages of data
example
&start=1&results=20
gives you the first 20 results
&start=21&results=20
gives you the next 20 results
I haven’t tested it, but that’s the what I understand from reading.
April 5th, 2005 at 4:54 pm
awesome… simply awesome.
I use Yahoo quite a bit to gather mailing lists for some marketing I do… and I do it by radius. In the past, it has been a pain in the ass to convert the yahoo results to Excel for mail merge purposes… so this is a gigantic time saver.
Thanks!
August 23rd, 2005 at 12:20 am
Is there any way to include the radius into the search? How about distance?
August 23rd, 2005 at 3:55 pm
Ali:
Both are possible, however the current inputs would need to be changed. Reading the spec shows distance and radius are valid inputs.
October 28th, 2005 at 10:40 am
Hey thanks Mark for the info. I simply LOVE this nifty tool for my marketing lists. I was wondering if there is any possible way to capture fax numbers and email the same way as the address and phone number. Thanks for being such a great help. Rock on AutomateExcel.com!!!
October 28th, 2005 at 11:16 am
Ali: If yahoo provided this data it would be possible, however they don’t currently provide fax and email.
You can look at the Response fields about halfway down the page here to see what else they offer.
November 1st, 2005 at 12:21 pm
Heres another question: Is there any application out there that lets u extract data from websites for free? Specifically, for marketing lists i wanted to know if there was an api that would pick up fax numbers from a website. Thanks for your feedback!
November 13th, 2005 at 10:17 pm
Hey Mark
Im having a real tough time getting the radius variable to work. I want to receive results up to 50 miles away from my query. How would i do that?
Thanks a bunch