Amazing SEO Tools for Excel: SEOtools By Neils Bosma

by
blue pipes

Our Excel Extension for Google Adwords relies on a framework called ExcelDNA – a .NET Framework runtime that allows developers a great deal of freedom to develop add-ins using either C# or VB.NET. While I was checking out the web site for ExcelDNA I came across an incredible tool called SEOtools created by Neils Bosma.

SEOtools is a must have for Excel enabled SEOs

site crawl example

Here’s a screenshot of Excel extracting the H1 elements found on the URLs listed on the left hand side. That’s an incredibly efficient way to extract your competitors keywords. The platform does a lot – and I’m hopeful it may be extended to provide even more features in the future.

Functions

SEOtools functions are highly self explanatory and incredibly reliable – even with a lot of data. Here’s a run through of the best ones (refer to the guide page for the full list.)

=GooglePageRank([URL]) - Retrieves the Google PageRank by URL

=GoogleIndexCount([URL]) - Returns an approximation of the number of pages indexed by Google by domain (the equivalent to “site:yourdomain.com”)

=WhoIs(string domain) - Retrieves WhoIs for a domain

=IsDomainRegistered([DOMAIN]) - Returns “true” if a domain is registered. WhoIs servers are configured in SeoTools.config.xml

=FacebookLikes([URL]) – Retrieves the the number of total Facebook Likes of an url (same count as in a badge)

=AlexaReach([URL]) – Retrieves Alexa Traffic Reach Rank

=ResolveIp([URL]) – Resolves the IP address of the domain in an URL

=HttpStatus([URL]) – Returns the HTTP status code and its description. Also retrieves the Location header (useful for debugging redirects)

=ResponseTime([URL]) – Returns the number of milliseconds it takes for an url to load (cached)

=LinkCount([URL]) – Returns the number of links on a page (cached)

=HtmlTitle([URL]) – Returns the HTML title on a page (cached)

=HtmlMetaDescription([URL]) – Returns the HTML meta description on a page (cached)

=HtmlH1([URL]) – Returns the first HTML H1 element on a page (cached)

=HtmlH2([URL]) – Returns the first HTML H2 element on a page (cached)

=HtmlH3([URL]) – Returns the first HTML H3 element on a page (cached)

=XPathOnUrl([URL]; [XPATH]) – Fetches the url and returns the result from xpath expression (cached).
EG: =XPathOnUrl("http://google.com";"/html/head/title")

On XPath – a great little tool to help you construct XPaths for search engine results pages is the brilliant XPathBuilder which has been carefully constructed to help Google Docs hacks users form XPath to extract data from search engines results. You can take the XPath generated by that tool and use it in this query too.

Download this tool now

Download the spreadsheet extension here: http://nielsbosma.se/projects/seotools/ - SEO with Excel got even easier today. Thanks Niels, and if you’re listening, we’d love to see a few things added:

- Tweets to URL
- Facebook Shares
- Google +1′s
- Ranking for keyword in Bing and Google

Image credits:
Mikko Miettinen

Amazing SEO Tools for Excel: SEOtools By Neils Bosma, 3.8 out of 5 based on 5 ratings

Related Articles

Rate this Article

VN:F [1.9.22_1171]

  • Rate:

  • Rating:

    3.8

  • Votes:

    5

Comments

  2. alan

    Forgive me for being dense, but how do you actually get these queries to run? I’ve downloaded the plugin, installed it as instructed but don’t see any additional options within excel.

    Adding the query string to the forumla bar “=LinkCount(nameofsite.com)” doesn’t appear to do anything. I am on a Windows 7 machine with Office 2010.

    Am I missing a vital step? Or should I just go back to an abacus ;-)

  4. Chris Makara

    i remember this tool being referenced at SEOMoz Mozcon and it definitely sounded like something I wanted to check out! thanks for writing about to remind me to try it out!

    it is installed and works like a champ! i agree with your suggested features to be added…one more nice one would be to pull the number of backlinks to the URL, basically if you ran a site:www.site.com search in google. i am guessing there might be a way to get it to work with xpath somehow in the meantime, but it would be nice if it could be implemented straight into the plugin :)

  5. Vince

    Hi Guys,

    I have downloaded this to my environment, but cannot get it to work, no data, received the security message, but nothing happens. Have I missed anything?

    Thanks

  6. Chris Makara

    @vince – you need to open a blank spreadsheet, then click the SeoTools.xll file you downloaded/extracted and allow it to run in excel. then in cell A1 type in your full url and in cell B1 try a formula such as =GooglePageRank(A1) — this basically is telling excel to find the google pagerank of the url listed in cell A1

  8. Chris Makara

    ok here is the xpath formula if you want to see the total link results of a search (replace A3 with the cell of your URL)…i noticed in my earlier comment i said i wanted a function to search google for site:www.site.com when i really meant to say link:www.site.com


    =XPathOnUrl("http://www.google.com/search?q=link:"&A3&"&num=30&pws=0", "//div[@id='resultStats']")

    in google docs you can actually extract the number only instead of the whole “About 2,030 results (0.09 seconds)” so that only the 2,030 number shows in the cell…there might be a way to do this in excel, but have not looked yet.

  9. euan

    Loving the quick cached/non-cached using the PR check – it comes back with “-1″ if not cached. Wonder how accurate this is and if scaling up will break anything…

  10. Innes

    I am just giving this a go now; selling t-shirts, this could be pretty useful as a means to monitor how effective this kind of marketing could be for us!

  11. JR

    I’ve got the same problem with Office 2010 running on Win 7. Is there a work around?

    Plugin looks more than promising!

  12. JR

    I’ve found a work around, for me the plugin doesn’t work if you just open the file. Go to:
    File > Options > Add-ins > Manage Add-ins > Go

    Then browse and find SeoTools.xll, Excel should add this and one other file. Click ok.

    Now force a recalc on open cells in a sheet and it should work.

  14. LogicalJack

    Had to use the JR workaround method and I must say its a great set of tools. I have tried to not keep too much of the work bundled in spreadsheets but this is definitely making me head that way now.

  15. JR

    I’ve been trying to get the href value of anchors on a page using:

    =XPathOnUrl(“http://www.google.com/search?q=example”, “//a/@href”)

    which doesn’t seem to work. Any ideas?

    This is turning in to unofficial support…

  16. Niels Bosma

    Your XPATH return multiple result.

    Try “(//a/@href)[1]” to get the first result and then “(//a/@href)[2]” and so on.

    Create a column with values 1,2,3,4,5….. and use the following function call to get what you want:

    =XPathOnUrl(“http://www.google.com/search?q=example”, “(//a/@href)[”&A1&"]“)

    I’ll try to find time to write a blog post about it.

  17. JR

    Thanks Niels, I’ve tried it but (//a/@href)[2] only returns the anchor text not the href attribute value?

    //a returns a list of ; separated values so I would expect //a/@href to return a list of ; separated href values?

    I may not know enough about Xpath though.

  28. Niels Bosma

    @Chris Makara:

    Try the RegexpFind function in SeoTools:
    =RegexpFind(“About 2,030 results (0.09 seconds)”;”About ([d,]*) results”;1)

    (I’m adding your link: count as a function in the next release!)

  30. Vince

    Hi Guys,

    I have been trying to get the xpath stuff to work, but nothing is happening wheni copy the formula in, am sure am missing something here?

  31. Vince

    Hi Guy,

    I think would really complement this tool, if it was able to provide a cache date and also just specific information from who is on, owner registration & DNS.

    Do you guys think this would be valuable to add on to the tool set?

    Kind Regards!

  32. JR

    Hi Vince

    If you’re using Excel 2010 use , instead of ;

    Also make sure excel hasn’t reloaded the old plugin, mine does from time to time.

    You could probably scrape the other data you’re looking for – dns etc

  33. Vince

    Hi JR,

    Thanks for your feedback, will try a few scraper tools!

    No am using 07..

    Do I have to change anything to get it to work e.g. the other formulas are written like this above =GooglePageRank([URL] but are entered into excel this way =GooglePageRank(a2)?

    Many Thanks,

    Vince..

  34. JR

    Exactly as you say:

    =GooglePageRank(a2) or (“www.site.com”)

    In 2010 once the plugin is loaded you get type ahead with the functions, so you can check it’s loaded that way.

  35. Vince

    Am using 2007, sorry for my ignorance but lets say I have a url in cell a2 is there anything I need to change to this =XPathOnUrl(“http://www.google.com/search?q=example”;”(//a)[1]“;”href”) – to get it to return the information from cell a2? Hope that makes sense.. Really appreciating your help!

  36. JR

    Hi Vince

    This:=XPathOnUrl(“http://www.google.com/search?q=example”;”(//a)[1]“;”href”)

    Returns the first href attribute values for an anchor on a google search results page.

    If you had a website address in a2 and wanted to get the first href value then the following should work:

    =XPathOnUrl(a2;”(//a)[1]“;”href”)

    To get all href values from the anchors on a page:

    =XPathOnUrl(a2;”(//a)“;”href”)

    Hope that helps

  37. JR

    The only other thing to consider is that in these comments, quotes can sometimes behave oddly when cut and paste in to Excel. Check that they are regular speech marks and not 66/99.

  44. Svetoslav

    I’ve been trying to get the Add-In running for two days. On Office 2010 x64 it works like a charm. I’ve been trying to get it running on Office 2003. It turns out that the tool is actually functioning properly, it is just not available in the menu and you need to enter the functions manually. I guess that is no biggie. Great tool!

  45. eon

    Hey guys, I think this would be promising, but I cant seem to get it running. I can load the add-in, but when I try to execute a formula it crashes excel every time.

    Any suggestions? Has this happened to anyone else?

  48. Kat English

    Hi Guys,
    Love this app, would it be possible to tell you which country the website is based? or is this already a option and i am just missing it.

  52. Banda Larga

    This is one of the most usefull app I ever used. I did the CheckBacklink all the time manually doing a Vlookup at my collected backlinks from seomoz, majestics a href etc etc. With your tool i can do the same in much less time and with more exact results. Thanks a lot for this app!

  53. CelestialChook (@CelestialChook)

    Oh my… this is great! I wish I had had it last week for a job I was doing, but pleased to have found it now. If only there was a Mac version: life would be perfect!

  54. Corey

    I too wish there was a version of this for a Mac. So bummed as I won’t run VMWare on my Mac. I’ve heard so many wonderful things about this. If you ever do a Mac version please let me know!

  57. Canvas Prints

    Greetings! I know this is kinda off topic however , I’d figured I’d ask.
    Would you be interested in trading links or maybe guest authoring a blog article or vice-versa?
    My blog addresses a lot of the same topics as
    yours and I believe we could greatly benefit from each other.
    If you might be interested feel free to shoot me an email.

    I look forward to hearing from you! Superb blog by the way!

  61. free web domains

    Simply want to say your article is as surprising.
    The clarity on your post is just excellent and that i could
    assume you are knowledgeable in this subject. Fine together
    with your permission let me to take hold of your RSS feed to stay up to date with coming near near post.
    Thank you a million and please continue the gratifying work.

  64. ???????? ?

    Excellent post. I was checking continuously this blog and
    I am impressed! Very useful information specifically the last part :) I care for such information much. I was seeking this particular information for a very long time.
    Thank you and good luck.

  65. Mike

    I am also having this problem. Well, not so much a problem as much as it is my lack of Excel knowledge.

    So let’s say I want to pull basic onpage SEO for three sites to compare. I understand how to click in a cell next to the domain name, add a function, paste in the URL, then get the value. But how the heck to I just run a report without pasting URL values into each cell?