One of my favorite Google Spreadsheet formulae is =IMPORTHTML().

Essentially, what this formula does is grab data from a URL you pass it. Whenever you open your spreadsheet, the data is reloaded, so you always get an up-to-date version of the website you are interested in.

Here’s how the formula is structured:

IMPORTHTML(url, query, index)

• URL: You need to specify the exact URL you want to monitor/extract data from
• Query: enter either “list” or “table”, depending on the structure you are interested in
• Index: a page can contain many lists or tables, you need to let the formula know which table or list you want (not all will be displayed)

So why is this formula so special? You can just as well manually visit the URL you want to track, with the added benefit of getting a better visual experience. Also you don’t have to format the text, which is the case for using the formula in a Google Spreadsheet.

However, there are certain use-cases which make the formula very useful. If you can’t think of any, I have one case here that demonstrates the power of IMPORTHTML().

Example: Keeping on top of your investments

Let’s say I want to get financial data on listed companies which I own. But I am too lazy to search through all 20 stocks on Yahoo finance. Wouldn’t it be nice to have that information in a central location, updated automatically?

With a bit of effort to set it up initially, that can be done with the formula.

Here is how the page of the Starbucks stock looks on Yahoo Finance:

That summary section sure looks like it’s a table. If we take a look with the Chrome Developer Tools, we get confirmation.

Tables work well for the formula because they have structured data. That makes it easier for the formula to separate data into cells in the spreadsheet.

So what happens when we fill out the formula in Google Sheets? We get the exact same data as we saw on the website, but filled in automatically.

You can choose which table you want to import. With the INDEX() function, you can even specify a specific value instead of importing the whole table if you want.

So if I were only interested in the Open price, I could use this formula:

=index(IMPORTHTML(“https://finance.yahoo.com/quote/SBUX”,”table”,1),2,2)

In the cell I would then only get 112.28 instead of the full table.

If you have multiple stocks, you can repeat this process. Note that the URL structure for Yahoo finance is always “https://finance.yahoo.com/quote/{{TICKER_SYMBOL}}”, so all you need are the ticker symbols of any new stock you purchased and with a few clicks you can track that one as well.

The great thing: you now have all of this information in your spreadsheet. That means you can run calculations and track your investment a lot better (and with much less effort!).