Using the SEOmoz Free API with Excel

by Jason Green on April 19, 2011 · 86 comments

Powered by Linkscape

At one time or another, we’ve all come across a “juicy” list of directories, blogs, or other websites that we might be able to use for some link building. I recently came across such a list and was quickly annoyed at how low the quality of some of the sites was. I wanted a quick and dirty way to rate all of the sites so I could start my linkbuilding process with the most promising websites. That’s when I found the SEOmoz Free API. With my mad Excel skills and lots of help from Leith Ross with some Excel code, I was able to create what I think is the fastest and easiest implementation of the SEOmoz API out there. It’s also easy to customize if you want to hack it into something that suits your needs.

This is a self-service spreadsheet, so you’ll need to have your own API credentials. (Get credentials here) If there’s enough demand, I’d be happy to publish a more polished version for mass consumption. (leave a comment)

I wrote a custom function in Excel that queries the SEOmoz API URL with the various parameters pulled from the spreadsheet. You just need to download the spreadsheet and insert your API credentials.
Press Alt+F8 and run the GetURLMetrics macro to begin processing. Be patient with large lists of URL’s. Currently, it takes about a half-second per URL to process. LinkScape has been renamed Mozscape API and new throttling limits have been added. For the free version, we’re limited to 1 request every 10 seconds. Using that version, please don’t try to run huge lists of URLs. 1000 URLs would take about 3 hours.

To follow along, download the latest Excel macro-enabled spreadsheet below: (Enable macros after opening.)
Excel Spreadsheet (Beta1) 4/19/2011 (Original Version)
Excel Spreadsheet (Beta 2) 9/22/2011
*** 7/23/2012 – Issues with V2 have been resolved. Please download version 3 below. ***
Excel Spreadsheet (Beta 3) 7/23/2012
***[UPDATE: 8/14/2012: I joined the Mozscape API beta testing group today. Expect a new and hugely improved version of this spreadsheet very soon!!!]
***[UPDATE: 9/1/2012: The Mozscape API beta testing is complete and has been released to production. Unfortunately, the release broke our macro. Luckily, during beta testing we were working on an add-in / plugin version. See below…
********* UPDATE 9/5/2012 **********
Version 3.0 is now in beta testing. (very very beta)
Rather than a vba macro, this new version is an installable Excel Add-in.
Check it out here:
This also renders most of the instructions on THIS page, null and void. The new page above will contain the latest information for this project.

The function, named GetURLMetrics, takes a specified URL and whether or not to follow redirects as parameters. [eg. =GetURLmetrics(“”,TRUE) ]

It returns the entire response of the Linkscape url-metrics API call, and the cells to the right parse that into usable fields. If you’re looking for a way to assess many websites quickly, I think this will work for most applications.

This is very much a beta version and I appreciate anyone giving it a try. It worked fine for me, but others have not had the same success. Your questions and suggestions are very much appreciated.

*** Update: If you receive an “invalid signature” error, please refresh the API page on SEOmoz to get new credentials. Make sure to re-copy all of the information needed, and that you don’t accidentally copy line breaks or other hidden characters. It takes a few tries for some, but usually works.

*** Update: Mac users: If anyone was able to get this working on their Mac, or with OpenOffice on any platform, we’d love to hear from you. Several users have tried, but weren’t able to get it working. If you figured it out, please let us know.

Download the spreadsheet here.
(right-click and Save Target As)
The macro/function is saved within and can be viewed by pressing Alt+F11 once in Excel.
Press Alt_F8 to Run.

You can download the text of the macro below. You’ll need all of the included functions for everything to work properly. To run as a stand alone macro, just insert your credentials directly into the code and uncomment the input boxes. (There are notes included in the macro.)

********* UPDATE 12/13/2011 **********
Rolling out December, 16th, 2011, the SEOmoz Free API will be severely limited.
The new rate limit for the Free API is 1 request every 10 seconds.
(Compared to 2 or 3 per second that we’ve been running.)
And batch requests for the URL Metrics Call has been limited to 10 URLs for every POST request.
You can read the official SEOmoz API update here.
I’m currently looking into alternatives, and am entertaining the idea of building my own API.
********* UPDATE 9/5/2012 **********
Version 3.0 is now in beta testing. (very very beta)
Rather than a vba macro, this new version is an installable Excel Add-in.
Check it out here:
This also renders most of the instructions on THIS page, null and void. The new page above will contain the latest information for this project.

Suggestions and support are welcome. Thank you to everyone who has given feedback thus far.

{ 77 comments… read them below or add one }

anne April 22, 2011 at 11:56 am

It’s weird that I have to put in all of my information from the seomoz website. Is there any way to make it so there isn’t so much information needed?


Jason Green April 22, 2011 at 12:03 pm

I could make it a little easier if I can find a way to do the SHA1 hashing within Excel.

However, it would then need for you to provide your secret api key and your access id so I could combine them with the unix timestamp to create the signature hash.

I could also just leave in my api for everyone, but I’m afraid that would end badly. 🙂

Please let me know if you have any more feedback. Thanks!


Sami October 18, 2011 at 11:10 am

I Think SHA1 Don’t Need Built-in Command to be Implemented.


Seth April 26, 2011 at 12:10 am

Im very new to the SeoMoz Api.How do I generate the timestamp and signature?

I either get “invalid signature” or “Request timed out”


Jason Green April 26, 2011 at 12:28 am

Sorry to hear it’s not working for you. A few people I had testing this also had the same error message. For some, it worked if they refreshed the SEOmoz page to get a new signature. Simply refreshing the API page should generate the new credentials for you. The timestamp causes the signature to change each time, so make sure you copy all 3 credentials each time you try.

If you could give it another try and let me know if it works, I’d really appreciate it. I’m trying to get to the bottom of this bug, but it’s proving to be fairly elusive. Thanks for trying it out!


Ruth May 3, 2011 at 5:19 pm

I also got the “invalid signature” error. It looks like when you copy and paste from the API credentials page, you’re copying and pasting in a carriage return at the end of the timestamp and the signature (making the data in the Excel cells two lines tall instead of 1). Delete the carriage return and it should work fine.


Jason Green May 3, 2011 at 5:24 pm

Thanks Ruth! I knew it was probably user error. 😉


Mark Greene May 16, 2011 at 5:29 pm

Thanks for the interesting spreadsheet. It seems to work for me using the URLs you included. For some reason when I enter my own URL it turns into a link. When I hit Alt-Ctrl-F9 nothing happens.
How / where should I be entering my own URLs and how do I make it perform an update.
Thanks for your help.


Jason Green May 16, 2011 at 5:37 pm

It changing to a link shouldn’t hurt anything. I would check to make sure macros are enabled. Also try saving and reopening. Sometimes it just takes a clean start to get things working. Let me know if still not working.


Jeff Oxford May 17, 2011 at 7:08 pm

Thank you so much for this tool. I have modified it a little bit to do backlink analysis for domains. I take the mozRank of a linking domain and compare it side by side with it’s PageRank to see if there is a significant difference. It isn’t as automated as I would like but it gets the job done. Great tool!


Jason Green May 17, 2011 at 11:59 pm

My pleasure. If you have any suggestions for improvement, please let me know.


Jeff Oxford May 19, 2011 at 4:53 pm

Is it possible to add a PageRank api to it? I have been using to return the PageRank for google docs but not sure how to use it in excel. It would be nice to add this metric in along with the SEOmoz metrics.


Jason Green May 20, 2011 at 10:01 am


That’s a nice tool, and should be extremely easy to interface from Excel. I’ll have to see about adding it to v2.0. 🙂


Jeff Oxford May 20, 2011 at 5:12 pm

Sounds great, let me know when you release v2.0. I’ll check back now and then and look for updates.

Alain May 19, 2011 at 5:42 am

Nice! this is awesome! thanks!


Franklin at SEO Frapant May 29, 2011 at 7:20 pm

Thanx a million! This Dutch/French speaking SEO’er will have a great time with it the next couple of days!!!


Lyena Solomon June 9, 2011 at 9:39 pm

I downloaded the spreadsheet, got my API credentials, entered them in the spreadsheet (including API key) and still see “unauthorized” response. Am I doing something wrong? Ctr+Alt+F9 does not change the result.


Jason Green June 9, 2011 at 10:41 pm

Try refreshing the api screen to get a new signature and timestamp. Also be very careful when copying, so you only get the text you want.

It seems that entering the credentials can be tempermental, but if you give it a few tries, it’ll work.

Please let me know if it doesn’t.


Steve Hall June 13, 2011 at 4:03 am

Works great for me. Thanks for sharing this really cool tool.

This will be really useful to get an idea of what kind of competition I am up against, and to be able to track changes in site metrics as a result of link building.


Jason Green June 13, 2011 at 10:00 am

Thanks everyone for your comments and assistance in testing!

I just copied the text of the macro into this post if anyone is interested and doesn’t want to download the Excel file.

If there are any Excel-Using Mac Users or OpenOffice users on any platform, please let us know if this works for you. There have been reports that it doesn’t, and we could use some help troubleshooting.



Addam Hasan June 14, 2011 at 3:30 pm

This is great work. Very impressed!


Eric Siu June 15, 2011 at 2:08 pm

Great work! I think it would help if you could add the # of linking root domains as well as PR(right next to the mozRank column)


Leonardo Armani June 29, 2011 at 6:31 am

Hi Jason,

This is a nice and very well done tool, thanks for sharing!

I’ve added a few extra tabs for some URL manipulation and some extra rows on my version but I’m a little annoyed with the fact that the Spreadsheet tries to update the results all the time. I don’t know much of VB so the only way I’ve found to stop this it is by setting calculating options to manual.

Is there a way we could make it update the results only when clicking a button or hitting CTRL+ALT+F9?


Jason Green June 29, 2011 at 7:11 am

Thanks Leonardo. I’m glad you like it.

I agree that the auto updating is annoying, and I’m working on a new version that you run when you choose.

Until then, there is a setting in excel to tell it to not auto update. I don’t remember the exact location, but I’ll post details here soon.


Jason Green December 6, 2011 at 10:03 am

FYI – Version 2 was released a few months ago which allows you to update on-demand.


Ed July 5, 2011 at 6:01 pm

This great code works amazing!

Do you know on more free SEO API that I could use – i.e. retrieving domain age, H1, H2, and so on?


Kostas August 2, 2011 at 12:42 pm

I am total noob to Seomoz api, but I want to learn how to use it. Thanks for sharing this, it is a good start for me!


JR August 10, 2011 at 4:23 pm

Downloaded and running in Mac Office 2011, doesn’t seem to work. Any advice?


Jason Green August 31, 2011 at 11:20 pm

@JR Sorry. So far, I haven’t heard of anyone having success with this in Mac Office. If there are any success stories out there, please let us know.

– Jason


Ben Neale September 13, 2011 at 1:21 pm

I’ve enabled Macros, but I’m getting an error message saying:

“Excel found unreadable content. Do you want to recover the content of this workbook? If you truxt the source of this workbook, click Yes.”

I click Yes, and it opens, but a dialog pops up saying:

“Excel was able to open this file by repairing or removing the unreadable content: Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))

Any ideas would be appreciated…




Jason Green September 14, 2011 at 5:03 pm


I just downloaded the spreadsheet from here and it tested fine. Are you using a version of Excel prior to 2003? Or perhaps on a Mac?

Do other macros work for you? As a test, maybe try recording a macro and making sure it runs. That error message sounds like it could be internal to your installation of Excel.

Let me know if that doesn’t work.


Jason Green


Ben Neale September 15, 2011 at 3:36 am

Thanks for the swift reply, Jason,

I’m using Excel 2007 on Windows XP (running within Parallels Desktop on a Mac).

I’ll have a go at recording a macro, as you suggest and get back to you.




Ben Neale September 20, 2011 at 2:07 am

Hi Jason,

Followed your advice and it’s now working. You were right it was a problem with my installation of Excel. I didn’t have VBA installed. Once that was rectified, everything worked as expected.

Many thanks



Jason Green September 20, 2011 at 8:20 am

Glad to hear it. Yours is the closest we’ve come to getting this to work on a mac.

Ben Neale September 15, 2011 at 3:35 am

Thanks for the swift reply, Jason,

I’m using Excel 2007 on Windows XP (running within Parallels Desktop on a Mac).

I’ll have a go at recording a macro, as you suggest and get back to you.




Tips For Excel September 20, 2011 at 4:57 am

This is very impressive. I love the different ways people find to use Excel.


Ryan September 27, 2011 at 7:58 pm

Using Office 2010 with Win7 64 bit, I had to change the declaration in Module1 to “Private Declare PtrSafe Sub Sleep Lib “kernel32″”

Worked like a champ after that!

Thanks a lot!!



Jason Green September 27, 2011 at 9:23 pm

Thanks for the tip Ryan!

Also, if you remove the Sleep line from the main body of code, you can remove that top line all together. (The text parsing takes long enough that the additional pause isn’t always necessary.)


Gerad Hoyt September 28, 2011 at 2:26 pm

Finally, someone made an excel pull for the SEOmoz API. I seem to be having problem getting the macro to populate the data in the URL Report. Has anyone else had this problem? The query seems to be working but it does not output the result and populate the page with any metrics. Any advice on how i could fix this?

Gerad Hoyt


Jason Green October 3, 2011 at 9:57 am

Gerad, If it seems to run, but doesn’t populate data, it’s probably an issue with the credentials. Verify them and make sure you’ve removed any line breaks.


Hans October 6, 2011 at 3:38 am

Hi Jason,

Great excel sheet. I’m trying to expand it to our own needs. I’m pretty into macro’s myself. But ive got a strange problem. Couple a days ago i first downloaded your sheet and it works fine. Now when i download your sheet (without changing anything myself) it returns the word DA & PA’s. DA = PA which is wrong and it returns a DA of 12 for, which is of course impossible. I’ve tested on multiple computers.

Maybe seomoz changed something in their api?

Thanks in advance


Hans October 6, 2011 at 4:37 am

I’ve looked further into it and it looks like it is not returning the dpa and upa values, so the instr function can not find the values and is returning the value on the 7th and 8th position from the start. The return i got is:



Joe November 23, 2011 at 12:12 pm

Thanks very much for creating this!

When I run the macro I get a message that said it took 2 seconds to process 6 URLs however the table does not populate.

Any ideas?



Jason Green November 27, 2011 at 7:45 pm

Joe, It’s probably an authorization issue. Check that your credentials don’t have any trailing spaces. In these cases I usually refresh my creds on the seomoz site and try again.


Annie Cushing November 27, 2011 at 3:20 am

Thanks so much for this! One problem I ran into (PC, Excel 2007) is I couldn’t paste my Access ID, Timestamp, and Signature into the cells b/c they were merged. So I unmerged them and inserted each into B7, B9, and B11, respectively. But it doesn’t work. Am I doing something wrong?


Jason Green November 27, 2011 at 7:47 pm

Annie, You shouldn’t need to unmerge the cells. To paste your credentials, double-click the cell so it only pastes the text. Let me know if that doesn’t work. 🙂


Matthias November 29, 2011 at 10:25 am

With how many URLs does this work? I have a latop with 2 x 2 GHz, 4 GB, Win7, Excel 2007. Any ideas?


Jason Green November 29, 2011 at 11:36 am

Matthias, The only limitations are those imposed by the seomoz api. (Free accounts can hit 1million url’s per month.) There is also alimit on how fast you can run them, which is about 2 or 3 per second. If you’re willing to wait, the limit is 1million, but that would take about 4 straight days of processing.


Matthias November 30, 2011 at 8:03 am

Hi Jason, thanks for your replay.

Today, I tried to pull data for 50 K urls. It took 2 hours and then the excel sheet stopped with “success” at 7210 urls. I tried it a second time with 25 K urls and again it stopped at 7210 urls with succuss. Memory and cpu usage are low during runtime. Do you have an idea why it stops at 7210 urls even if there are more urls to go?


Robert November 30, 2011 at 10:45 am

Hi Jason,
Thank you for creating this. I’m new to Seomoz api and was looking for a way to implement it – this might just be it.
One slight problem i’m having – I can enter my credentials and they work ok, then I run the macro and it comes up with the ‘please be patient..’ message. If I don’t press ‘ok’ the message doesn’t disappear and if I press ‘ok’ I get a message saying ‘It took 0 seconds to process 0 urls’ – I have 2 urls in the URL field.
Any ideas? Thanks.


Robert November 30, 2011 at 11:13 am

I probably should have mentioned..
I’m using Office 2010 with Win7 64 bit so I tried changing the declaration in Module1 to “Private Declare PtrSafe Sub Sleep Lib “kernel32″, but I got the following error message;

Compile error:
Expected: end of statement

I then removed the sleep line as you suggested and got to the stage above.


Jason Green December 6, 2011 at 10:06 am

Robert, This sounds like a credentials, or usage limit issue. I’d re-download a fresh spreadsheet and try again. The macro can be a bit touchy, so if anything got changed, it might blow the whole thing up.


Sam January 5, 2012 at 6:17 pm

I used default version did not work for me. I tried making changes to macro by entering the url below format (which worked for me in the browser) . Doesnt work in the macro 🙁

I used this format in the url

http://%5Bmember%5D:%5Bsecret key]


Brad January 30, 2012 at 6:55 pm

Everything seems to be okay when I add my URLs and run the macro, but the data just doesn’t populate in the fields. What am I missing here?

Using Windows 7 / Office 2010

(even tells me “It took 10 seconds to process 13 results”, but results don’t show)



Piotr February 21, 2012 at 1:07 pm

Same problem here.Running Excel 2007.


Will March 9, 2012 at 12:17 pm

I’m getting the same result as well. However, my setup is Windows 7 and Excel 2007..


Kyle February 28, 2012 at 12:17 pm

This tool does not work at all… API info is correct, running Excel 2007, when I run the macro it does not display any data at all. It does, however, say it took 12 seconds to process 10 results. Not sure if coding needs updated or if this is just a super crappy tool…


jant fiyatları February 28, 2012 at 3:39 pm

Thanks for the interesting spreadsheet. It seems to work for me using the URLs you included. For some reason when I enter my own URL it turns into a link. When I hit Alt-Ctrl-F9 nothing happens.
How / where should I be entering my own URLs and how do I make it perform an update.


figurehead March 20, 2012 at 4:45 am

Great job, shame i haven’t managed to make it work yet but i’m working on it. You have all my moral support!


Doug March 21, 2012 at 11:22 pm

Great work Jason! A very handy tool. I am wondering if this could be adopted into a spreadsheet that can pull Google rank providing the URL and keywords, the same way the SEOMoz Rank Tracker tool does. Any thoughts? Thanks!


led billboard April 24, 2012 at 10:09 pm

good article .i learn from you web .I’m new to Seomoz api and was looking for a way to implement it


Alex July 19, 2012 at 4:12 am


Been trying to get this thing to work for 2 days.

I have checked the correct credentials, including checking for any empty carriage spaces. I have refreshed the moz api, but still no luck. I have tried various downloads of the spreadsheet.

Running windows 7 & Excel 2010. Should work!

It hangs on the first message.


Jason Green July 25, 2012 at 9:46 am

Hey everyone! We fixed the issues with Version 2 of the spreadsheet. Please check above for the link to download Version 3.

There’s a 10-second limit per request, so it takes MUCH longer to process a list of URL’s, but we still think it’s a good tool for processing small lists.

Keep the feedback coming. You guys are great!


Bobby August 23, 2012 at 8:21 am

I just ain’t getting this and it’s frustrating as I know it’s not that difficult.

Here is what I am doing:

1. Enabling the macros etc when opening excel.

2. Imputing the required data into the credentials page. I am guessing the timestamp is sample expires on the seomoz api page?

3. Pressing the alt+f8 button and nothing at all is happening.

Sorry for being such a noob, hope you can help.




George Freitag August 30, 2012 at 12:04 pm

Hi Jason,

I was running the tool last night and again this morning and it’s not pulling any data except Links (uid) and External Links (ueid).

Is this a problem I’m just having?

I’ve tried it with both my personal Moz memberID and our company’s.



Robert August 31, 2012 at 7:36 am

Hi George,

We’ve encountered exactly the same problem. The issue coincided with an SEOmoz update –

I’ve emailed their support so when I get an update I will post it here.


Jason Green August 31, 2012 at 9:01 am

Thanks for the update Robert. They rolled out a Beta of the new API, so I wonder if they’re starting to shut down the “old” version?

We are participating in the beta testing of the new mozscape API, and will have a plugin version ready for Excel soon. Hopefully we can get this done quickly and everyone can start using the new version of the API.

Please let us know if you hear anything else.


Robert September 4, 2012 at 6:15 am

Unfortunately they couldn’t help because they don’t provide email support for the Free Mozscape API. They suggested to post the problem on to see if the community can assist…


Jason Green September 4, 2012 at 7:44 am

Thanks Robert. I’m about a day away from releasing the plugin/addin. It works but isn’t polished yet. If anyone wants to test this beta version, I’d be happy to email you a link.

Robert September 4, 2012 at 10:09 am

I’d be happy to test it in beta Jason. Thanks for your commitment to improving and adapting this tool.


Jason Green September 4, 2012 at 5:30 pm

Robert (and anyone else interested in testing),

The first/beta/draft version of the Excel Add-In for the Mozscape API can be downloaded below. Just unzip the file and run Setup.exe. It will install a new right-click menu item.

Select any number of URL’s in a spreadsheet.
Right-click the selection.
Choose “Run Mozscape on Selection”
Enter your access id and private/secret key when prompted. ***You’ll only need to do this once! No more returning to moz to download a new signature and timestamp! 🙂
It will take 10 seconds to process each URL, so don’t select a column of 10,000 URL’s. *This is a moz throttling limit.
It will then create a new sheet called “Mozscape” with all of the url-metrics data.
*If you already have a sheet named “Mozscape”, it will be deleted and replaced with the new data.

Please let me know if you have any suggestions. Thank you all so much for your support.

Download the zip file here:

– Jason Green


Jason Green September 4, 2012 at 5:32 pm

FYI – I just tested the download link and got a warning that “It could be dangerous.”
It’s not….
You can decide if you want to trust me or not. 🙂

– Jason


Aron September 4, 2012 at 10:54 pm


Is there something else that has to be installed such as visual studio to get the addin to install? It will not install for me. Also, for the vsto file extension, which program will be opening it ( I set it to excel which is probably not correct )?



Jason Green September 5, 2012 at 9:13 am

It should install any dependencies if it needs them.

Here are a couple ideas for troubleshooting:
1. This version only works with Excel 2010. (and probably only on Windows)
2. Make sure to unzip the entire zip file. *Don’t run Setup.exe while it’s still zipped or it might not be able to find the other files for installation.
3. You only need to run Setup.exe. The other files contain the code used by the installation process.

If you’ve done everything above and it’s still not working, please let me know. I’ll also be testing this on a few different computers today.

Aron September 5, 2012 at 3:19 pm

I managed to install to excel 2010, but I had to install visual studio 2010 tools for office runtime first.

I tried to follow your instructions and was able to get the addin to run but I am now getting the following message : “The remote server returned an error: (401) Unauthortized. Here is a screenshot :


Jason Green September 5, 2012 at 3:35 pm

Aron, Thank you for working through the installation process!

That error means there’s a problem with the credentials. Are you copying your Access ID and Secret Key from the moz api page? Use the 2 fields in the green box, and make sure to delete any extra spaces that might get added in the copying process.
You sound like you know what you’re doing, but I don’t want to take anything for granted.

Please let us know how it goes.
*If you already saved your credentials, I’m afraid you’ll have to remove and reinstall the add-in to clear and add them again. *(Feature to be added.)

Jason Green September 5, 2012 at 4:48 pm

I’m going to close the comments on this page so we can move the conversation to the new page. We’ve released a new version that is entirely different from everything described on this page.

Please visit the page below to get the latest version and continue the discussion:


Leave a Comment

{ 9 trackbacks }

Previous post:

Next post: