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

You May Also Like

73 Responses to “How to Automatically Track Your Google Positions in Microsoft Excel”

  1. Screenquip says:

    Very good tool. I was serching for something like this for a long time now. I have about 200 keywords to track so its not so easy for me to do this manually !

    Thank you !
    .-= Screenquip recently posted: Things To Know When Hire A Plasma Screen =-.

  2. Tim Protecto says:

    This is a remarkable and very interesting article: I had no idea all this was possible from excel. I tried it and it works flawlessly. One remark: users need not forget to enable macro's and have at least excel in version 2007 or later to open your spreadsheet example.

  3. reda says:

    Great site….thank from france .
    .-= reda recently posted: creer son blog/wordpress sur un solide NOM DE DOMAINE . =-.

  4. Great! thanks for this

  5. Eugene says:

    I will use this one. This is a helpful way of monitoring my website, shall we say organic monitoring? Thanks for the info. I will just give some update after trying this one.

  6. Sujay says:

    ♥ ♥ ♥!!!!!!!!!!!!!!!AWESOME!!!!!!!!!!!!!!!! ♥ ♥ ♥

    What do I have to do if I want to set page of 100 results over default page of 10 results?

  7. Dude! That i just great! I just downloaded it and am about to set it all up, i have been looking for something like this for the past 2 hrs and only found junk, this is just excellent, thanks A BUNCH!!!!

  8. Yoav Ezer says:

    @sujay: You can do that easily. In BuildSERPURL, type at the end, right after URLEncode(term)
    & "&num=100"

    So the end of the line 29 now reads: URLEncode(term) & "&num=100"

    @Tim Protecto: There's no reason why this wouldn't work pretty much as-is in Pre-2007 versions. I'll publish an XLS file that can do this soon.

    @Everybody: I'm so glad to see that you liked it. Sometimes little tricks like this one make all the difference for an SEO person :)

    • Maarten says:

      great tool it really works awesome

      one question, maybe because i'm not really into macros and stuff, but wherever I look in the macro I cannot find: URLEncode(term)

      Am I looking at the wrong place? ( I go to Tools > macro > and then edit ThisWorkbook.AddCurrentsRankingRow

  9. [...] How to Automatically Track Your Google Positions in Microsoft Excel – Search Engine People [...]

  10. [...] How to Automatically Track Your Google Positions in Microsoft Excel – Search Engine People [...]

  11. Chris says:

    There should be a Noble prize category for this kind of tools. Big bow. Respect.
    .-= Chris recently posted: Een zoekmachine-feuilleton voor meer bezoekers op uw website =-.

  12. [...] rank checkers maar deze week kwam ik op een post terecht van Yoav Ezer die ons in zijn "How to automatically track your Google positions in Microsoft Excel" post een zeer eenvoudige en vooral handige manier geeft om rankings te checken en bij te [...]

  13. [...] How to Automatically Track Your Google Positions in Microsoft Excel – Search Engine People [...]

  14. John says:

    This is really a great tool.
    Is it possible to change the rows in columns? I.g. keywords in the column and the dates in rows?

  15. Wow this is a real money saver. Didnt know u could do that with Excel! I use Ranktracker atm.
    .-= Willem Keller recently posted: Lake Nakuru =-.

  16. Brian says:

    Wow, this is very great thing.. I will download it now and try it.. I knew Excel can be great tool but never thought it can be useful with websites. Thanks for all those explanations.
    .-= Brian recently posted: socotra_island_14 =-.

  17. David says:

    Looks likt it's not working anymore since Google's update. Any suggestions on how to fix it ? Thxs !

  18. David says:

    Thxs Yoav !

  19. InfoTrade says:

    Hi Yoav,
    Great tool! Thanks for sharing.
    Did you try it with other languages than English? it seems that it doesn't work with Hebrew (or should I config something else in the code besides the google server?)

    • Yoav Ezer says:

      Oh my… you are right…

      I've got rid of the URLEncode function, because WinHTTP does its own encoding. It should now work well for Hebrew as well as any other language in Unicode. Please try it and let me know.

      Thanks for the tip!

  20. Stewie says:

    Hi,

    Great file!
    But what about google's personalized search results? I think your file is using the personalized search results..

    • Yoav Ezer says:

      Hi Stewie,

      This is indeed a problem if you have Google's cookie in Internet Explorer, because WinHTTP uses IE's cookies (and there's no way around that).

      I personally use Firefox (and sometimes Chrome), so I didn't notice this problem.

      You can try to log-out of Google in IE, and only then run the macro.

      I'll try to think of another simple way of using HTTP in the macro. Any ideas?

  21. InfoTrade says:

    Thanks for the quick response and fix; I downloaded it again, but now, when it gets to the HEB search kwd, i get a visual basic prompt error : Overflow

    What do you think?

    • J.R. says:

      This is an awesome tool. Is this document not compatible with Open Office? The macro doesn't seem to work. Thanks.

    • If you're hitting the Overflow error (I got it when changing to Google.co.uk), change the following line:

      from:

      Function FindRank(ByVal page As String, ByVal myurl As String, ByRef count As Integer) As Boolean
      Dim pos As Integer, posEnd As Integer

      to

      Function FindRank(ByVal page As String, ByVal myurl As String, ByRef count As Integer) As Boolean
      Dim pos As Long, posEnd As Long

      hope that helps
      .-= James Crowley recently posted: Seagate announces fastest laptop hard drive =-.

  22. Stewie says:

    Thx for the quick reply Yoav!

    I tried your solution I think it works!
    But i normaly don't use IE, only for debugging…But maybe i used it once or twice to search in google…

    anyway, it works, so thank you

  23. Piotr says:

    Awsome!
    but I have two questions:
    1. How to change rows for columns?
    2. Is there some delay between querries? Google don't like robots ;)

  24. Mark says:

    Is there a way in excel using something similiar to this method to also return the top 3 websites of a keyword from the top search engines?

    • Yoav Ezer says:

      What you refer to, is called keyword research. This is an interesting exercise. It'll need some programming to work.

  25. Mark says:

    Thank you for the response Yoav! I am somewhat of a novice excel user with plenty to learn. I've spent many hours researching various topics about excel programming. It's a great program with many possibilities. I will continue researching this but if you could offer any help I would really appreciate it. This may be the topic for another Blog or a continuation of this one. Again thank you for your response.

  26. Bader says:

    I think that I am in love with you :)

    Thanks alot

  27. Sean says:

    Is there any work-around for us Mac users that can't use macros?

    Looks like a great tool and I'd love to be able to use it.

  28. Thanks man, cool tool.

  29. Dries says:

    Great tool & tnx for sharing this, though I get a problem when i check rankings a few times a day. I get the error "Overloop". Not sure what it means. Any thoughts on that? tnx in advance

  30. Dries says:

    Sry to bother you with the Overloop error. The solution was in the comments. One more question though: is it possible to add a language to the url? Belgium has for example Dutch and French. Already tried to use the hl variable but it doesn't work.

  31. [...] was a fantastic post on Search Engine People's blog by Yoav Ezer. It's about a tool he's made, and provided for free download. It's [...]

  32. Dreo says:

    Hi,
    Just want to thank you for this script. It's very useful. :) Is there any way to delay the rank-check of each keyword? I'm thinking that Google might block it if you que for results too fast. I know they don't like auto-scripts. How would I modify the script to delay the lookup by 2-3 seconds between each keyword?

  33. Jasmine says:

    Wow, this is the brilliant-est use of Excel I have ever seen! Great stuff!!
    .-= Jasmine recently posted: InMotion Hosting Review =-.

  34. Paul T says:

    Just looking at this now. I really like it.
    Ok. A couple of questions. I'm building about 1 site a day around 1 or 2 keywords for each site. Without creating a separate spreadsheet for each site, would there be a way to make this spreadsheet work with multiple sites?

    How about getting it to work with Bing (yeah.. I know, I'm not asking for a whole lot)?

  35. Lucas says:

    Hi!!

    Do you know if it is possible to do an excel macro that record the ad copies of adwords in a query, and then printed in the sheet?
    How can I do this?

  36. Rebecca L. says:

    Trying to set this up, but having issues with the macro. Ctrl-Shift-U results in an "Overflow" error. Reading up on the error, it looks like something was too large for the script. Is there a character limit on the keywords? Of the four keywords I used for testing the spreadsheet, the longest one is 19 characters including spaces.
    .-= Rebecca L. recently posted: What does SERP mean What are “Organic” SEO Results SEO Basics VII =-.

  37. Dries says:

    @Rebecca check James Crowley's answer

  38. Mike says:

    Thank you for the free tools, i have used many commercial rank tracking programs and have to say for a novice these tools are great.

  39. [...] Track Your Search Positions [...]

  40. pilouto says:

    Great tool !!!
    I have a question about & "&num=100". When I used it the keyword's position is not the same. Without this, I am in 13 position and with 26. Doubled ?

    Thank you.

    • Yoav Ezer says:

      Pilouto,

      If any of your competitors has more than one result in the 100-result page, then Google moves them to be one near the other. This is called a double-listing.

      However if you only view the 10-result page, then maybe only one of those results are shown, and your site will be higher in the SERP.

  41. Maarten says:

    great tool it really works awesome

    one question, maybe because i'm not really into macros and stuff, but wherever I look in the macro I cannot find: URLEncode(term)

    Am I looking at the wrong place? ( I go to Tools > macro > and then edit ThisWorkbook.AddCurrentsRankingRow

    Read more: http://www.searchenginepeople.com/blog/how-to-automatically-track-your-google-positions-in-microsoft-excel.html#ixzz185P4l0v5

  42. Mike says:

    great ranking tool, perfect for seo. Can you please tell us how to change it dates for the columns?

    THANKS!

  43. Balazs says:

    Hey there,

    Its a very good solution really! My only problem is that the first results come to the first cell and the Office 2010 tells me "Overflow" how can I fix it?

    Thankyou

  44. D J Smart says:

    Yoav, I'm not terribly technical when it comes to Excel so this tool you have created looks like a godsend to me. However, when I download to excel (2010) and fill out the sheet as you explain, when I press Ctrl Shift U, all I get is an error message, 'Overflow'. Any idea how this can be rectified? Thanking you. I know this tool with cut down the amount of time I spend on creating such reports so anything you can suggest would be appreciated. Many thanks.

  45. D J Smart says:

    Yoav, no need to respond … see answer above, and seems to have rectified problem. Thanks to James Crowley. DJS

  46. [...] one to see what position you are in the rankings.  There is a better way, if you have Excel this spreadsheet will help you automatically track your Google [...]

  47. ChrisC says:

    Similar to Paul T's question, i can't seem to get the macro to work with http://www.bing.com, i was able to successfully modify the VBA code to do what i needed for google, but am also trying to compare my google rankings to those of yahoo and bing. is there more to be done other than changing the value for WEBSERVER or am i missing something else?

  48. Greg S says:

    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

  49. Greg S says:

    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

    • ChrisC says:

      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

  50. ChrisC says:

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

  51. dwaxman says:

    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

  52. faca5 says:

    Hello.

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

  53. Tariq says:

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

  54. Ronald says:

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

  55. Alaina says:

    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.

  56. SEO dude says:

    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.

  57. Alex says:

    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

  58. $uperhero says:

    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)

    • Vernon Alderosn says:

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

  59. Faiz says:

    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

  60. Christopher says:

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

    Thank you very much, that would be really useful!

  61. Azamat says:

    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.

  62. Jonathan Lindahl says:

    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 :-)