It's enough to write in a column the name of two or three countries from South America and you can obtain the rest of the countries, their capitals, a description from Wikipedia. If you publish the spreadsheet, you can show the locations on a map. From a simple list of country names to a map with automatically generated descriptions, from a list of companies to a stock portfolio, Google Spreadsheets and other Google APIs can be the bridge.
November 16, 2007
Connecting Data Using Google Spreadsheets
A short video tutorial from Google reminds us that Google Spreadsheets has a lot of functions that pull data from the web: facts, financial information, feeds and other files. You obtain interesting results when you use the result from a function as an input for another function. And if you combine this with the magical autofill powered by Google Sets, your spreadsheets is populated with related names and words.
It's enough to write in a column the name of two or three countries from South America and you can obtain the rest of the countries, their capitals, a description from Wikipedia. If you publish the spreadsheet, you can show the locations on a map. From a simple list of country names to a map with automatically generated descriptions, from a list of companies to a stock portfolio, Google Spreadsheets and other Google APIs can be the bridge.
It's enough to write in a column the name of two or three countries from South America and you can obtain the rest of the countries, their capitals, a description from Wikipedia. If you publish the spreadsheet, you can show the locations on a map. From a simple list of country names to a map with automatically generated descriptions, from a list of companies to a stock portfolio, Google Spreadsheets and other Google APIs can be the bridge.
Unfortunately the video only shows an example chosen to work very well.
ReplyDeleteSee what happens when you fill in Google instead of Bayer. Well, the spreadsheet can't find the ticker symbol using the GoogleLookup function. And that ends the computation right at the start :(
Also when you Ctrl+drag_down (Magic Auto Fill) the name Google, you end up with e.g. a list like this:
Google
google
yahoo
msn
altavista
lycos
aol
teoma
gigablast
wisenut
a9
clusty
delicious
technorati
digg
of which only Yahoo (this came up spontaneously, really :) shows a P/E and MarketCap. For All other names GoogleLookup shows no ticker symbol and so no GoogleFinance retrievals.
So please don't believe what these marketing people try to sell you. The GoogleLookup function is totally unreliable and the GoogleFinance function may find data based on a ticker symbol, but you should always use the "name" attribute to verify your data is really from the company you think it is.
(E.g. =GoogleFinance("GOOG", "name") -> Google Inc. but =GoogleFinance("BAY", "name") -> nix, null, nada.)
So this ticker symbol could as well retrieve the data for e.g. the Bayswater Uranium Corp.
You're right, those functions don't return good results all the time. GoogleLookup doesn't show results from a manually compiled database, it uses facts from web pages and these aren't always reliable.
ReplyDeleteThe data from Google Sets is also automatically generated from Google's web index.
From your list of names related to Google, most are services acquired/launched by other companies (Altavista, Gigablast, Teoma, Wisenut, A9, Clusty, delicious, MSN), AOL is operated by Time Warner etc.
So while you shouldn't expect to get perfect answers, the functions are still useful and could save you a lot of time.
"So while you shouldn't expect to get perfect answers, the functions are still useful and could save you a lot of time."
ReplyDeleteI'm afraid I must disagree with that. The output of these functions can not be determined before using them, e.g. by doing a Google search first.
GoogleLookup is especially bad because it eiher gives: no result, an outdated result, an incorrect result, or - when you're lucky - a correct result. So you can't trust te results and always have to cross-check. This means you will have to spend more time using them.
The Magic Auto Fill also works in an undeterminable way. List that are produced are very interesting but more often than not are not waht you expect. This too makes you must double check the results.
These functions are hardly time savers.
The GoogleFinance function isn't perfect either. As there is no way to specify a stock exchange using result simply based on a stock symbol can cost you money...
That's part of the
ReplyDeleterelvence problem that Google tries to pretend doesn't exist
@Lucia: The author of that post is very obviously biased, and if you check the last comment, you should realize how baseless her accusations are.
ReplyDeleteThe crux of her argument is really "Google threatens your privacy!", not whether the ads are relevant (which she just uses as a way to make fun of Gmail).
As said in the video, the functions are not real time. When you save such a spreadsheet and publish, the information is not updated in real time. It always shows the same data, which were calculated at the time of defining the function.
ReplyDeleteKarthik is incorrect. The GoogleFinance functions update every 20 minutes or so. When you have a web page with an embedded Google Docs spreadsheet containing GoogleFinance functions you either have to reload the web page manually to see the changes or to write e.g. a little bit of JavaScript that automates this.
ReplyDeleteThis is illustrated on this web page: http://ahbanen.googlepages.com/portfoliotest
Sorry, I misquoted it. I meant the importHtml function which is not loading the data from the page in real time. I published a spreadsheet to import and show a table from a HTML page. Even if I refresh the publisged document, I always get the same values which were calculated at the time of defining the function.
ReplyDeleteGoogleFinance may be returning the real time data.
Karthik,
ReplyDeleteThe Google Docs spreadsheet import functions have a refesh rate of about once an hour. If you want to discuss workarounds for this I suggest you post a question in the Google Docs spreadsheets forum (http://groups.google.com/group/GoogleDocsSpreadsheets) I'll be glad to help you.