UPDATE on Google API Project

I fired off an email to S_ _ _ _, trying to get some clarification, and with an update. S_ _ _ _ shot back an answer, showing that he/she's already got quite a bit of insight on this project. Further proof that my readers are, in fact, some of the brightest excel users already...they're just looking to me to help them tweak the masterpieces they've already built! (Hope I can live up to that.) Anyway, I thought all of you might find the subject matter interesting, so here're the emails:

Excel_Geek to S_ _ _ _ :

I have a question and an update:

Question: When you say, "# of competing websites for the search term" do you mean the total number of search results for a given search term, or do you mean the number of Google Adwords client advertisers who have purhcased that particular search term? Obviously the two are vastly different, and getting the latter, if even possible, would require me to be an Adwords client (which I'm not) so I could get access to the Adwords API. What I don't know is if I did have access to the Adwords API if it gives out that sort of information.

Update: Backlinks? Totally doable with the Google Search API. URL? Totally doable with the Google Search API (even without it). PageRank? Hmmmm.....This is what seemingly everyone is trying to achieve (so I've found). Google doesn't make this data readily available via API, and they stopped placing PageRank in their search results pages long ago, but there is the Google Toolbar, which displays the PageRank. I found one application in DOS that returns the PageRank, but it isn't useful to me. I have some ideas on how we might get this data via the toolbar....I'll keep you posted.

Excel_Geek

S_ _ _ _ to Excel_Geek :

Hi,

Answer to the question : I mean the total number of websites for the search term, which google displays in the SERP top left corner. (such as Results 1 - 10 of about 1,720,000,000 for web sites. (0.23 seconds) )

Side Info : I already have a program called keyword analyzer (http://www.keywordsanalyzer.com) which can fetch the number of google campaigns and top bids (exact, broad and phrase match options seperately). I guess this also handled with SOAP, similar to what you have done.

PageRank : There are a few sites which can fetch the pagerank (in text or in graphics) for a given keyword, most of them use ASP or PHP. Here are a few references. It might help.

http://www.thinkbling.com/serps/serps.php
http://www.mcdar.net/KeywordTool/keywordtool.asp
http://www.webuildpages.com/cool-seo-tool/
http://www.splutweb.com/Tools/Tools.asp

Interesting New Google API Related $50 Project

Just before Christmas (Merry late Christmas, by the way...) I received a request for an interesting new Google API-related $50 project.

S_ _ _ _ requested the following data be returned into an excel spreadsheet for each of a list of up to 300 or so keyword phrases:

• The number of websites competing for the search term keywords, as well as intitle:keywords and allinachor:keywords;


• The number of backlinks of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;


• The pageranks of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;


• The URLs of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;

And some bonus optional data S_ _ _ _ would like are the number of searches in Overture Database and the number of searches in Wordtracker Database (using the WordTracker API, with which I'm not terribly familiar).

I'll get right on it, S_ _ _ _, and I'll see what I can do.

Excel_Geek

Google Search Results Page Parsing Code BIG in Russia?

Here's an interesting little tidbit: That Google search page parsing code I posted about way back when...it apparently was a hot topic in Russia! Wow.

Anatoliy Alizar wrote an article on www.webplanet.ru in which he said this about me...

"Generally, the amateurs of program Excel already wrote considerable code for processing of results of Google. Recently one of them published the special the parser, which is processed HTML- page with the results of search Google imports list URL into the cells of sheet Excel. This parser is completely suitable as the basic element for the set of useful macros."

"...amateurs of program Excel..." Indeed, I've been revealed as a fraud! Ha! Seriously, though, I'll humbly confess that "amateur" is likely closer than "expert."

Basically, Anatoliy is holding me out as an example of stuff programmers can do with Google even without subscribing to and using their available APIs. Fair enough... His article has been picked up by many other Russian blogs and websites, so he must have some credibility on this sort of subject matter.

After realizing (through Google Analytics) that considerable traffic was flowing from Russia (man, I love their geo targeting fatures!), I found a nice little website/text translator that handles Russian: www.worldlingo.com. Babel Fish, which I've used loyally for years, claims to do the same, but it errored out on me every time I tried to translate Russian websites to English.

So, to my new-found Russian readers, "Добро пожаловать к Excel_Geek."

взгляд на русском языке.

Limitation on MSN's Stock Quote toolbar add-in

Well, it's happened: I have received a project that I will not be able to produce as specified.

Tom wanted a way to update a system of Excel worksheets using MSN's Stock Quote toolbar add-in to refresh current stock quote data. He uses this system to track and produce billing information for his investment management clients' accounts. The problem with the add-in is that it only allows users to get updates every five minutes. I cannot find any way around this limitation on refreshing stock quote data. I've tried everything I can think of: taking over refreshing with VBA commands, using different "instances" of Excel (as opposed to opening multiple files in the same instance of Excel), etc.

In addition, none of the other Excel gurus out there have published anything (that I can find) on the topic about getting around this limitation. Most of them actually balk at the idea of using the toolbar at all, "...when getting data via web querying Yahoo is available..."

In the end, I've had to suggest to Tom two alternatives, neither of which, I'm sure, are all that attractive, given the amount of time, effort, and familiarity he's got with his system as designed now:

1. Redesign the system so that there's one "master" Excel file that contains all current quote data for all client-account-holdings, and then have each of the account worksheets reference data from that file. (This creates only one place that has to be refreshed, so doing one master refresh will ripple down to each of the other worksheets when you click "update links" upon opening them.)


2. Redesign each of the client-account worksheets incorporating automated web queries (using VBA) to enable automatic and ad hoc/on the fly "refreshes" as often as needed.

We'll see what he wants to do. I, of course, won't be asking for $50 until he's satisfied that he's gotten a solution he can live with.

Aany readers out there that have experience with the MSN Stock Quotes toolbar add-in, and have some suggestions for getting around this 5-minute limitation, please send them to me or post a comment on this blog. Thanks in advance!

Excel_Geek

Interesting New $50 Project - Excel as Tool for Investment Management

Tom from New Jersey recently requested a project involving the system of Excel spreadsheets that he uses to track and manage his investor clients' portfolios, as well as provide billing for his services.

He's using Smart Tags to retrieve current stock quote information (from MSN Money Central), but is having some trouble. MSN Money Central only lets him refresh the quotes every five minutes (per spreadsheet). This is somewhat problematic because if a single spreadsheet has more than one worksheet (for example, if a client has more than one IRA account, he might have them each in a different worksheet within the same spreadsheet) he has to wait five minutes between refreshing each client account.

Tom would like to know if there's some "master switch" he can throw to get all stocks in every worksheet updated, even if it's once per day, say at 6 pm or 9 am, or anytime. Further, he's looking for some kind of macro that would automatically open all of these files and refresh all stock quote info at a particular time. Further, he's spent a great deal of time developing his system and has around 200 of these worksheets, so any wholesale change -- like switching the source of his data from MSN Money, etc. -- would be very problematic.

This is going to be a fun one. I love investment-related projects. Makes me want to get my old day trading automator back out and dust it off...memories...

I'll keep you all posted.

BTW, my Excel_Geek Blog_Mail subscriber list keeps growing! I want to, again, pass along my humblest appreciation to all of you who make me feel as though what I'm doing really helps people. Thank you.

Write Your Own Excel E-Book?

Charley Kyd recently posted a new article on ExcelUser.com about writing your own Excel-related e-book. Interesting...

Maybe I should write my own e-book on Google "hacks" using Excel and VBA. Maybe an e-book on building your own direct email marketing system using Excel, Outlook, Outlook Remption, and VBA...

Charley's article lays out a nice example of how writing an e-book might stack up against writing a traditional book, financially, and she talks about her most recent e-book on Excel Dashboard Reporting. She doesn't say exactly how much she's made with that book, but I can tell you this much: I've been an affiliate marketer of her book on dashboard reporting for a few months, and I've helped her quite a few already...on my tiny little blog!

I'm going to mull around this idea of writing an e-book for awhile, and I'll keep you all posted.

P.S. I want to pass along my sincerest thanks to all of my loyal readers, including the growing list of Excel_Geek Blog_Mail service subscribers. It's you all that make thoughts of writing an e-book on Excel even within the realm of reality.

Thank you,

Excel_Geek

Micropayments Review #1

We're two weeks into the PublicWishingWell.com experiment, so I thought I'd pass along some of our findings thus far with respect to PayPal's micropayment services:

First, let me just say that frustration would be understating our initial feelings. Understand that PayPal released its first press release regarding micropayments on August 31st of this year, so we figured the service was up and running in full, complete with documentation, well-trained support (as we've experienced with PayPal's traditional merchant services). We were, however, dead wrong.

When seeking information on the topic of signing up for a micropayments account, the only means publicly available was the email alias micropayments@paypal.com, which was very slow to respond. In fact, I used other means to actually sign up for the account before ever hearing back from that email alias. Because I had personally been in contact with a business development agent (Jennifer, out of Omaha) for other merchant services needs, I simply called her back and she sent me a promotional email with a link to sign up for a micropayments, enabled account.

The URL was a redirect that landed me back on what seemed to be the standard "create an account" first page, but I was assured it would create a mircopayments enabled account. Even after creating and using the account, however, which did charge the appropriate $0.05 + 5% fees, without actually looking at the fees charged on actual receipts, you'd never know it was a micropayments account. It's not stated anywhere, there's no special micropayments-specific instructions or documentation, and, in fact, when you click on the "transaction fees" link while logged in, you'll actually be told that the account is enable for "standard fees". Confusing.

Oh, forgot to mention, instead of simply having micropayments added to your existing business or premier account you have to get another one for micropayments, which, many of you may know, flies in the face of PayPal's own stated policy of "one personal, one business/premier account per person." When I called customer service about both the issues of not being sure whether or not my new account was, in fact, a micropayments account, and if I was violating their own terms by having more than one business/premier account, despite needing a new one to have micropayments, half of the support people I talked to tried to convince me that PayPal did not, in fact, have any such thing as micropayments, and the other half had heard of it, but had no idea.

Long story short, Jennifer, my angel, was sick for a few days, so I couldn't get in touch with her, and I grew increasingly frustrated, eventually blasting off a steaming email to Peter Ashley, the stated manager of digital content and micropayments for PayPal. Jennifer got back, returned my messages, reassured me that I was set up right, and I was on my way, albeit with no documentation. I'm complaining a bit about documentation, but in the vast majority of aspects, micropayment deployment is identical to standard PayPal services. Don't be afraid.

A great bright spot (to me), is that Mr. Peter Ashley called me back after receiving my email. I happened to be on the phone at the time, so he left a voicemail for me. We're still trying to get hooked up on a call to hash through some of this, but the basics of his message were this: He understands and agrees with the issues I've brought up (lack of documenation, trained support personnel, and apparent need to violate PayPal's own terms to use micropayments if you already have a business/premier account), and he'd like to have the chance to explain why this is. He breifly stated in his message that the micropayments "initiative" was a "soft launch" (like alpha or beta in my estimation) whereby PayPal would be set up to handle inquiries from companies like ours who have a need. They'd learn from early users what exactly is needed, how micropayments will be used, etc., then do some additional development on the services and "get ready for a prime time launch." I'll post my findings, if interesting, I guess, after actually speaking with Mr. Ashley.

Review #1 Conclusion: I was intensely frustrated by PayPal's "soft launch" approach, but now understand it. PayPal's micropayments (when you're assured you've actually got it) is every bit as easy as their standard services, and at $.05 + 5%, you can feasibly sell digital goods/services as cheap as $0.10 and still not lose money (you'd actually keep 40% or $0.04).