Toll Free: 1-877-695-7388

GTA: (647) 699-2838

Search Engine People
  • SEO
  • SEM
  • CRO
  • Display
  • Blog
  • Why Us
  • Contact
  • Join Our Team
  • Get A Quote

Toll Free: 1-877-695-7388

GTA: (647) 699-2838

How to Automatically Track Your Google Positions in Microsoft Excel

Yoav Ezer | April 30th, 2010
Tweet78
Share53
Share
Pin
131 Shares

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

serp-sheet

  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).
  5. Press Ctrl-Shift-U to refresh the results.

All being well, a new line with rankings will be added, and the graph will be updated. Just like magic! (Note: If the macro is run again during the same day, it will not do anything.)

About the Solution

For this solution we used the default-sized result pages (10 results each) and not a 100-result page, because of Google's double-listing mechanism that puts results from the same site together. Your competitors who appear above you in the SERPs might get a double-listing in a larger page, and thus skew the rankings. We want to get the rankings as they are seen by real searchers.

The HTTP User agent is IE7. This is done because the default WinHTTP user agent string is blocked by Google. They really don't want bots to use their service, and rightfully so. This is also a good point to say, use this at your own risk, heh.

We used a configurable parameter, GOOGLE_WEBSERVER so you can choose which Google server to fetch the data from. This is useful if you are tracking rankings from a different region than your potential customers. For example, I am based in the UK but a lot of my customers are in the USA, so I need to see results from outside of my own data center. You can use a localized server like www.google.co.uk, or the Google Caffeine test server or any other specific one by IP.

Finally, to keep things fast and simple, the macro only looks at the first 3 result pages. This is an artificial limit that can be lifted by changing the call to GetCurrentRanking. We placed this limit to shorten the possible time it takes to run the macro. If your website is not found in the results, 0 will be displayed.

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.

serp-macro

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 < maxpages
    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 ...

<h3 class="r"><a href="URL">...

... 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.

Summary

This project serves as a good example for how you can use Excel to scrape search results and monitor your rankings.

I am sure you can think of other ways this could be used, perhaps as part of your link building or reputation management efforts?

Please share your thoughts and ideas in the comments ...

P.S.

  • I've updated the article with slight changes that are required for Google's new search results page.
  • The file is now in the old XLS format instead of XLSX.
  • URL Encoding is done by WinHTTP now.
Tweet78
Share53
Share
Pin
131 Shares
Posted in PRTagged Google, Rank Checking, rankings, SEO

About the Author: Yoav Ezer

Yoav co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software. For more Excel tips, join him on Facebook or Twitter

Codswallop blog

65 thoughts on “How to Automatically Track Your Google Positions in Microsoft Excel”

Comment navigation

← Older Comments
  1. Greg S says:
    January 25, 2011 at 11:06 am

    I am trying to use your SERP Rankings spreadsheet to work with my website. I am using a mac computer and Office 2011 for Mac. when I press “Ctrl-Shift-U” Excel returns a pop up screen as follows.

    Compile Error:

    Cant Find Project or Library.

    count = 0

    While pagenum termRow) Then
    If sheet.Cells(newRow – 1, dateCol).Formula = CLng(Date) Then
    Exit Sub
    End If
    End If

    sheet.Cells(newRow, dateCol).Formula = Date
    For col = dateCol + 1 To rightCol
    Dim term As String, rank As Integer
    term = sheet.UsedRange.Cells(4, col).Text
    rank = GetCurrentRanking(term, myurl, 3)
    sheet.Cells(newRow, col).Formula = rank
    Next

    Dim newRange As Range
    Set newRange = sheet.Range(sheet.Cells(termRow, dateCol).Address & “:” & sheet.Cells(newRow, rightCol).Address)
    sheet.ChartObjects(1).Chart.SetSourceData newRange
    End Sub

    Please let me know if this an issue with MAC computers only or if I am supposed to use different keys to run this worksheet on a MAC.

    Thanks,

    Greg

  2. Greg S says:
    January 25, 2011 at 11:07 am

    count = 0

    While pagenum termRow) Then
    If sheet.Cells(newRow – 1, dateCol).Formula = CLng(Date) Then
    Exit Sub
    End If
    End If

    sheet.Cells(newRow, dateCol).Formula = Date
    For col = dateCol + 1 To rightCol
    Dim term As String, rank As Integer
    term = sheet.UsedRange.Cells(4, col).Text
    rank = GetCurrentRanking(term, myurl, 3)
    sheet.Cells(newRow, col).Formula = rank
    Next

    Dim newRange As Range
    Set newRange = sheet.Range(sheet.Cells(termRow, dateCol).Address & “:” & sheet.Cells(newRow, rightCol).Address)
    sheet.ChartObjects(1).Chart.SetSourceData newRange
    End Sub

    1. ChrisC says:
      February 3, 2011 at 7:01 am

      macros dont work on macs with newer versions of office as far as i know. i have to run them on an old dell of mine, had the same problem

  3. ChrisC says:
    February 14, 2011 at 6:08 am

    “This function can be easily adapted to other search engines like Bing or Ask.com, but it will require some programming tweaks to work.”
    If nobody knows specifically how to do this, does anyone know at least where I could look to figure it out on my own? I have spent lots of time looking online through blogs and for literature on how to do it but can not find anything. Thanks in advance for any help that can be given. This program is pretty sweet.

  4. dwaxman says:
    March 30, 2011 at 1:28 am

    The latest version of Excel for Mac 2011 does in fact support macro but I am also getting the error “Compile Error: Can’t find project or library” on Sub AddCurrentRankingsRow()

    Any ideas?

    Thanks

  5. faca5 says:
    May 3, 2011 at 11:55 pm

    Hello.

    Macro stop working with last google updates?! When It start run, error “Overflow”?! Possible to get fix.

  6. Tariq says:
    August 2, 2011 at 12:40 am

    On running the macro it gives following error “System Error &H80072EFD” can you please resolve the same.

  7. Ronald says:
    October 25, 2011 at 8:20 am

    This is the tool I am looking for, very happy that you’ve build it.
    Could you please tell me how to get de macro working again or tell me when you expect to fix the problem. Everytime I run the macro it gives the following error “System Error &H80072EFD”.

  8. Alaina says:
    November 27, 2011 at 10:13 pm

    How to recover rank for the day if I forget to open the sheet. Suppose I forget to open the sheet on Sunday and now I want ranks both for Sunday and Monday. Please suggest me what to do.

  9. SEO dude says:
    January 23, 2012 at 7:31 am

    Hi Yoav,
    Great idea for an excel seo tool.. Not sure if you are still able to respond…I was wondering whether you could check the excel spreadsheet vba code. I’ve been trying to make it to display rankings but it only returns 0s.. Not sure if something had changed since the last time you’ve updated the tool but it doesn’t seem to work anymore, unfortunately. Could you please let us know? Thanks.

  10. Alex says:
    March 12, 2012 at 10:27 am

    Hi, I also can’t get this to work on Excel 2010 it just throws up an error when I refresh it. Is there an update for this?

    Thanks

  11. $uperhero says:
    April 26, 2012 at 8:48 am

    I got it working by editing the VBA code in Excel 2010 and replacing this line:

    FetchPage = req.ResponseText

    with this:
    FetchPage = Mid(req.ResponseText, 32000, 100000)

    It seems that the req.ResponseText was just too much text to be stored in the string variable. So the above modification will only store from character 32000 (where the first results html starts) to 100000 (where roughly the 50th result ends)

    1. Vernon Alderosn says:
      April 26, 2012 at 10:52 am

      $uperhero,
      Would you mind posting the revised excel macro. I am not familiar with VBA code. Thanks

  12. Faiz says:
    August 28, 2012 at 12:43 pm

    When I refresh by pressing cntrl+shift+U it gives me the “Overflow” error. How can I resolve this? or do you have another sheet which is working fine

  13. Christopher says:
    February 4, 2013 at 9:51 am

    Did anyone find how to switch the layout from rows to columns ??

    Thank you very much, that would be really useful!

  14. Azamat says:
    February 7, 2013 at 12:29 pm

    Hi, guys. Can you help me please. I’m using to pull out search from google.com/ig/dell as it searches globally and pull out global results. As just google.com – locolized.
    Can you put google.com/ig/dell search to the code as by default. Tried to input manually, but it seems doesnt work. Thank you.

  15. Jonathan Lindahl says:
    August 5, 2013 at 1:06 pm

    Very cool tool.

    I also have problem with Overflow (maybe because I’m using a Danish website)? James Crowley writes that you should change the line – but where is it exactly I need to change this line?

    Hope someone can help 🙂

Comment navigation

← Older Comments

Comments are closed.

Recent Posts

  • The Manifest Names Search Engine People Among Toronto’s Most Reviewed SEO Companies
  • Movin’ On Up! Why Migrating to Google Analytics 4 (GA4) Should be a Priority
  • A Year in Review: The Digital Marketing Trends That Defined 2021
  • The Basics of Video Marketing
  • Just How Much Do Google Reviews Impact Your SEO Ranking?

Categories

  • Analytics & ROI Analysis
  • Company News
  • Content
  • Conversion Optimization
  • Display Advertising/RTB
  • Email Marketing
  • En Español
  • En Français
  • Inbound Marketing
  • Lead Nurture & Marketing Automation
  • Local Search
  • Marketing
  • Mobile
  • Partnership Marketing
  • PPC
  • PR
  • SEO
  • Social Media Marketing
  • Web Design

Additional Posts

8 Ways To Use Social Media To Reward Customer Loyalty

April 29th, 2010 | by Bob Nunn

Remarketing with AdWords – Google’s Latest Toy

April 28th, 2010 | by Tyler Calder

7 Ways to Measure the Link Opportunities in Your Keyword Space

April 27th, 2010 | by Garrett French

LET'S TALK

Need more information or want to get in touch?

Get in touch!
  • SEO
  • SEM
  • Display
  • Blog
  • Why Us
  • Join Our Team
  • Contact Us
  • Local SEO
  • Small Business SEO
  • Enterprise SEO
  • International SEO

LOCATION

1305 Pickering Parkway,
5th Floor Pickering, L1V 3P2

PHONE

Toll Free: 1-877-695-7388
Greater Toronto Area: (647) 699-2838

Social

© Search Engine People Inc. 2023 – Canada’s Top Digital Agency
© SEP 2023 – A Search Engine People Company | Privacy Policy

Search Engine People