Automate Excel

Google Does Spreadsheets

Amit has some Google Spreadsheets commentary.

Create basic spreadsheets from scratch.
You can start from scratch and do all the basics, including changing the number format, sorting by columns, and adding formulas.

Upload your spreadsheet files.
Upload spreadsheets or worksheets from CSV or XLS format - all your formulas and formatting will come across intact.

Familiar desktop feel makes editing a breeze.
Just click the toolbar buttons to bold, underline, change the font, change the cell background color and more…

Awesome! The first remix of one of my spreadsheets: Backlink Checker for Google and Yahoo!

Claus has taken my Google API Keyword Spreadsheet, gave it a facelift, and added Yahoo backlink results side-by-side with the Google results.

For a large screenshot click here, or view the top ten results for the search Automate Excel with their Google and Yahoo backlinks in this screenshot:

The remixed version is the result of the nice folks at ResearchBuzz posting a link which was then picked up by ThreadWatch, who requested to see a Yahoo version also. Cluas then ran with it.

Note 1: A Yahoo developer key is not required. Notice in the large screenshot I entered foobar for the Yahoo API key and got the same results. I believe they are still throttling 5000 queries/day by IP (Thank You Yahoo).

Note 2: The VBA code requires a password, so any curious coders are out of luck, Claus? (You can download the original to see the google query. Not that a vba password has stopped anybody before

Note 3: If any SEO gurus care to do a follow up post on how to use this data in keyword research, that would be great!

Note 4: The spreadsheets I make available for download are free to build upon provided a link and proper credit is given, and they’re not sold. Any questions, simply shoot me an email.

Note 5: Woops, getting carried away with the notes… Nice job Claus.

<Update> Google and Yahoo Results in one spreadsheet. </Update>

I was just checking out this -new to me- excel blog ExcelGeek that has some code on parsing the HTML from a Google result page and returning the URL’s in Excel. This led me to revisit my post Google Code for Ross and create a downloadable sreadsheet for using the Google Api.

The purpose of the spreadsheet is to use the Google API for basic keyword research, or to see how competive it is to rank for a search term.

Some Screenshots: A keyword has been entered and the spreadsheet displays the total results for the keyword and the average backlinks to each of the top 10 results:

And the actual top 10 URL’s for a keyword with their backlink count is displayed:

You can also view the fullscreen shot of a different query by clicking here.

What does this mean?
1. It’s an example of using the Google API in Excel of course

2. The search for Automate Excel is not very competitive to rank for becuase there is a small number of total results for the search and some of the top ten results show 0 backlinks (or votes).

I don’t know much about Keyword ranking so if anyone has further ideas for development leave a comment or hit the contact button up top.

The spreadsheet:
I created the Google API Keyword Spreadsheet with Excel 2003, a Google Developer Key, and the Microsoft Office 2003 Web Services Toolkit 2.01. You may get by without having to download the Web Services Toolkit since the classes are included, however this scenario is untested (hopefully SOAP support will be native in Excel 12).

Google API Keyword Spreadsheet

Note 1: The Google Api is a bit slow!

Note 2: You are limited to 1000 queries/day by google. Each request with this spreadsheet is the equivalent of 11 queries (1 for the total results, 1 each for the backlinks of each result URL). So you’re limited to 90 uses of this tool with the google api/day.

Note 3: I’m determining backlinks by using the “link:domain.com”, not sure if this is the best way.

Note 4: Maybe this will help with the next version?: KEI = (P^2/C)

In case you missed the trackback on my last post Google for Spreadsheets, ResearchBuzz posted some excellent additional tips on Googling for Spreadsheets, Check it out:

ResearchBuzz: Finding Spreadsheets With Google

BTW, ResearchBuzz is written by Tara Calishain, the lead author of Google Hacks. Thanks.

While reading the article googling up passwords I noticed one of the examples given was a google search for budget spreadsheets.

Here’s the syntax used to search google for Excel Spreadsheets:

search query filetype:xls

Simply append filetype:xls to the end of any google query and you’re returned .xls files.

Note: I’d personally inspect any code before “enabling macros”.

A few example queries for spreadsheets:
Budget
Gantt Chart
Solver

Ross,

Here is a barebones example to return the first result from a Google search in Excel using the google API.

In order for the code to work you need to download the appropriate Soap toolkit, and follow the directions for loading the WDSL. You can find the Google WSDL here.

I’ve tested the code with the 2003 toolkit and it works fine. You may need to tweak it some with the 2002, not sure.

Also, keep in mind that the google API returns html formatted results. Maybe Dick has a solution for that?

You’ll need to add your google developer key to the code also:

Sub MyGoogleSearch()

    Dim GoogleSearch As New clsws_GoogleSearchService
    Dim GStruct As struct_GoogleSearchResult
    Dim GoogleKey As String
    Dim GoogleSearchWord As String

    GoogleSearchWord = "Excel"
    GoogleKey = ""

    Set GStruct = GoogleSearch.wsm_doGoogleSearch _
    (GoogleKey, GoogleSearchWord, 0, 1, True, "", _
    True, "", "utf8", "utf8")

Sheet1.Cells(1, 1) = GStruct.resultElements(0).title
Sheet1.Cells(2, 1) = GStruct.resultElements(0).snippet
Sheet1.Cells(3, 1) = GStruct.resultElements(0).URL

End Sub

Note: I’m sure you could finger it out yourself in 10 minutes, but I thought I’d save you (or others) the time