Table of Contents
Tags
Have you ever felt the necessity to continuously monitor data from a website and track it in your spreadsheet? Then the IMPORTHTML function is probably the function of your choice. So let’s take a look how the IMPORTHTML function works:
How does the IMPORTHTML function work?
The IMPORTHTML function parses data from any website and writes it into your Google Sheet. It works almost as it would be an API to an external website. However, it works best with websites that don’t frequently change their content. Every time a website would change their content or elements you would need to update the formula. Next, let’s take a look on how to apply the IMPORTHTML function in your Google Spreadsheet.
How to use the IMPORTHTML function - A step by step guide
In the following we assume you are interested in parsing the Gold Price from the Internet & you will get the information form the website https://tradingeconomics.com/commodity/gold
- “URL”: The first element you need to pass in the IMPORTHTML function is the URL of the website where you want to parse the data from. Make sure to pass the URL in parentheses.
=IMPORTHTML(“https://tradingeconomics.com/commodity/gold”)- “QUERY”: The next element you need to pass in the function is the query element. Based on the data on the website you can either query a table or a list. You can typically see if the data you want to parse is presented in the form of a table or a list. In case it is not clear you can simply right click on the Page in Google Chrome & select “View Page Source” to see the HTML code of the page. In our case the data we want to parse is presented in a table form. Make sure you also put this expression in parentheses since it is a string.
=IMPORTHTML(“https://tradingeconomics.com/commodity/gold”,”table”)- “INDEX”: The third element you need to specify is the index. It simply refers to which table or list of the website you want to parse. In our case we want to parse the first table so we pass the value 1 into the function. Since it is a number you do not place any parenthesis this time:
=IMPORTHTML(“https://tradingeconomics.com/commodity/gold”,”table”,”1”)- “LOCALE”: The last element you can optionally pass is the locale. It refers to the country version of the website you want to parse. In the case you just parse numbers you won’t need it and can leave it empty. This is actually the case in our example so we won’t specify it in the function.
=IMPORTHTML(“https://tradingeconomics.com/commodity/gold”,”table”,”1”)That’s it - our IMPORTHTML function is complete. The table we were trying to parse looked like this:
And our output in Google Sheet looked like this:
Combining the IMPORTHTML with the INDEX function in order to display the current price of Gold & Silver in Google Sheets
Most of the time you are not actually interested in parsing a list or a table from a Website. Instead you want to obtain the value that is inside a table or a list. In order to achieve that you can simply combine the IMPORTHTML function with the INDEX function.
Let’s assume that in our case we only want to import the current price of Gold and Silver and display them in our spreadsheet. We can use the INDEX function to reference to the value that we want to display.
The INDEX function allows you to specify which value should be displayed. The syntax works like this:
- You first specify the range that you want to apply the index on
- You specify which row you want to display
- You specific which column you want to display
In our case we can simply wrap the INDEX function around our IMPORTHTML function since we want to work with the very same table.
As you can see in the table above the price of Gold is written in the second row of the second column of the table. Therefore our INDEX function refers to row 2 and column 2:
=INDEX(IMPORTHTML("https://tradingeconomics.com/commodity/gold","table",1),2,2)The price of Silver is written in the third row of the second column of our table. We would obtain the value with the following parameters:
=INDEX(IMPORTHTML("https://tradingeconomics.com/commodity/gold","table",1),3,2)Applying the IMPORTHTML function in combination with the Google Sheets function we were able to display the current spot price of Gold & Silver in our Google Spreadsheet. This is something that Google Finance at the moment unfortunately does not support natively.
Build on top of the data you imported via IMPORTHTML
Now that you have the data you wanted in your Google Spreadsheet you might as well use it & build something on top of it. With Chartmat you can actually use your Google Spreadsheet as a Database for the Web Apps you want to build on top of it.