How to Automatically Track Your Google Positions in Microsoft Excel

If you have a website then it is a good idea to keep a track of your search engine positions for key phrases that people search for and that bring you traffic. For any SEO professionals this is especially true.

Some people use commercial products for this task, others use online services, but there is a free way to do it. Well, free if you already own a copy of Microsoft Excel, that is!

Before we get into the solution, my apologies to the guys at Webmaster World for singling you out. That particular website ranks nicely in all the test terms, so it became useful to use as an example!

How to Use the Spreadsheet

1. · If you want to use this spreadsheet, first download it from here and open it up in Excel.
2. · Write your website's host name in cell B2.
3. · Enter your search terms in cells B4, C4, D4, and so on (as many as you like).
4. · Select the example rows 5-12 and press Del to clear the contents (If you use the “right-click, delete” approach the chart will become smaller, so resize it back).

· Press Ctrl-Shift-U to refresh the results.

How it Works

If you look at the macro code, there is a fair amount going on under the hood, but it is fairly straight forward.

Essentially what we are doing here is “scraping” the search result then looking within the returned content for specific strings (our links). This approach can be used for a lot of useful purposes so it is worth investigating.

The Macro Code

The main macro subroutine AddCurrentRankingsRow first retrieves the website URL, and locates the data in the sheet. Then it adds a new line for today's date, and works on the term columns:

term = sheet.UsedRange.Cells(4, col).Text

rank = GetCurrentRanking(term, myurl, 3)

sheet.Cells(newRow, col).Formula = rank

For each term column, the subroutine fetches the term itself, then looks at the SERPs (Search Engine Result Pages) to find the rankings of our website, and finally writes it to the respective cell. The subroutine GetCurrentRanking figures out the ranking by iterating the SERPs as long as our website does not appear in the results. When our website appears in the results, it calculates and returns the ranking:

While pagenum <>

pagenum = pagenum + 1

url = BuildSERPURL(term, start)

page = FetchPage(url)

If FindRank(page, myurl, count) Then

GetCurrentRanking = start + count

Exit Function

End If

start = start + count

Wend

GetCurrentRanking uses three handy but simple utility functions:

• BuildSERPURL - This generates the URL of a SERP for a specific term, starting at a certain result number.
• FetchPage - Uses Microsoft's WinHttp library to do a HTTP ‘GET’ request and fetch the SERP's HTML contents.
• FindRank - Finds the position of our website in the organic results in a page.

FindRank is specific to Google results. It disregards the paid advertisements and counts result links. The organic result links are in the form …

...

… so the function just extracts the URL from those links. This function can be easily adapted to other search engines like Bing or Ask.com, but it will require some programming tweaks to work.

How to Run the Macro Automatically

You may want to run the macro automatically, without the need to press Ctrl-Shift-U. In order to do so, add the following subroutine after all the code:

Private Sub Workbook_Open()

AddCurrentRankingsRow

End Sub

This will run the macro every time you open the file, which means you will always see the most updated data.

All above information from : searc hengine people