Growth5 Blog

Monday, April 12, 2010

Scraping the Web with Google Docs Part II

Awhile back, we posted a tutorial on how you can pull data from external sites like finance.yahoo.com into your very own Google Doc and have it update as the site it's being pulled from updates. We use this functionality to track the fundamentals of comparable public companies to our portfolio companies within a single Google Doc and to pull exchange rates into our own docs so we can write formulas using the "scraped" data.

You can check out the original post here.

Since the original post, I have received a handful of emails from people asking how they can pull data from external web sites without having to "hard-code" the symbol into the formula. The example formula we used in the previous post to get US Dollars in Swiss Francs was:

=Index(ImportHTML("http://finance.yahoo.com/q?s=USDCHF=X","table",1),8,2)

For this example, place "USDCHF" in cell A5. Then adjust the formula above to read as follows:

=Index(ImportHTML("http://finance.yahoo.com/q?s="&A5&"=X","table",1),8,2)

You're basically just replacing USDCHF with "&A5&" in the formula so the symbol portion of the url you're scraping from will foot to A5 to complete the "scraped" url.

Setting the formula up to refer to other cells will give you more flexibility in tracking symbols as you will be able to change them in your Google Doc without having to adjust your formula. You could do a whole list of symbols across 2o cells, and then cut/paste or drag your formula to foot to each of those symbol cells.

Labels:

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home