How to import live Gold / Silver spot prices in Google Sheets

Live ticker prices

I use Google Sheets to keep track of my budgets, liabilities, assets, and a whole host of other stuff.

One of the great features that exist natively in Google Sheets is the =GoogleFinance function, it lets you bring in live (or historical) prices for a ticker directly within the worksheet, so you never have to manually search and update the prices for your chosen stocks or funds.

e.g.
=GOOGLEFINANCE("NASDAQ:TSLA") will show you the live price for Tesla, and
=GOOGLEFINANCE("NYSE:PINS") will do the same for Pinterest.

Live gold and silver prices

For precious metals like gold and silver, however, the GoogleFinance function doesn't work for that.

From searching around the internet, there are a couple of methods floatinga round: (1) web scraping with a formula, or (2) web scraping with a custom script.

A formula is much quicker in my opinion, and is accessible to far more people, so that's the method I'm going to use.

Web scraping via formula

I've picked gold.co.uk as my site of choice for the live gold and silver prices, but you're free to pick something else.

If you're happy with this choice as well, then just copy and paste my formulas below and get on with your day.

Formula examples

• Gold £/oz

=Index(ImportHTML("https://www.gold.co.uk","table",1),2,2)

• Gold £/g

=Index(ImportHTML("https://www.gold.co.uk","table",1),2,3)

• Silver £/oz

=Index(ImportHTML("https://www.gold.co.uk","table",1),3,2)

• Silver £/g

=Index(ImportHTML("https://www.gold.co.uk","table",1),3,3)

Building your own formula

Hopefully, you were able to follow the logic just based on the example formulas provided: =Index(ImportHTML("https://www.gold.co.uk","table",1),2,2)

To give a bit more info on what the formula is doing:

1. From the website https://www.gold.co.uk, import the 1st table (in the order of the HTML code).
2. From this table, only give me the value from row 2 and column 2.

That's it. Now let's use this logic to build your own.

Process

Let's say you want to use BullionByPost.co.uk instead as your source of gold prices (or whatever you choose).

• First, identify where the data is on the website front-end itself. In this example, I want to pull the data from the top right hand table of the website.

• Then, build out the base formula for importing the HTML table: =ImportHTML("https://bullionbypost.co.uk","table",1). Just by chance, it happens to be the first table on this website so the result looks like this as soon as I press enter.

• If the website you're using has multiple tables, simply loop through them until you find the table you're looking for by changing the last value in the formula (e.g. from 1 to 2, to 3, to 4, etc...)

• Now that you have identified the correct table, it's time to select the exact value that you want with the help of =Index. So if you wanted Platinum prices in Euros, then that would be row 4 and column 4 in the first table above.

Downsides of this method

As easy as this method is, there are a couple of constraints:

1. If the website ever changes their layout, then this will break the formula - although, you've seen how easy it is to build your own formula, so it will only take a couple of minutes to fix your formulas when this does happen.
2. This method will only work with HTML tables and lists, so if the data is not formatted as a table or a list, then it won't work and you'll need to find a different method. However, there are enough websites out there that I'm sure you can find one that stores their prices in an actual table!

Hope that method worked for you. Happy stacking!