Get gold spot prices in Excel with a live connection to the web. Import the open, bid, ask and previous closes prices, updating regularly.
I’ve written spreadsheets to download historical daily gold prices. But investors need more than that – for day-to-day trading decisions, you need spot prices.
Excel makes this easy with its web query tools. Here’s a step-by-step method to retrieve live (but delayed by 15 minutes) gold spot prices from Yahoo Finance
You can either watch the video, read the tutorial, or download the demonstration spreadsheet at the bottom of this article.
Step 1. Click on the Data Tab on the ribbon, and then click From Web
Step 2. You should now be in the New Web Query browser.
In the URL bar, type http://finance.yahoo.com/q?s=XAUUSD=X and hit Go. You should see see the Yahoo Finance website
The XAUUSD=X in the URL is simply the ticker for gold in US Dollars. You can query the price in other currencies simply by replacing the USD with the appropriate three-letter code for your currency – so JPY for Japanese Yen, EUR for Euros, or GBP for British pounds (you’ll find a complete list of codes here)
Step 3. Scroll down a few lines until you see the table highlighted below. Click the arrow on the top-left so that it turns into a tick.
Step 4. Click Import. You should find yourself at the following windowBear in mind that your data will be in a range of cells that’s four rows by two columns. Enter where you want the top-left hand corner of the data to be placed.
Step 4. Click Properties…. You’ll now be at this window
Step 4. Click OK once, and once again. You should now find yourself magically transported back to the main Excel workspace.
If you drag-select the range, right-click and select Edit Query, you find yourself back in the External Data Range Properties window again. You can edit the properties of your web query.