Understanding the gold commodity market requires constant vigilance. This is often something market watchers want to track in Excel, but it’s not obvious this can be done in Excel without a manual process. Excel does in fact offer methods to track commodity prices such as gold. This helps make tracking gold prices much more accessible and enables investors to assess market trends and make informed decisions. This post is going to show you how to get the current and historical price per ounce of gold in your Excel spreadsheet.
Get Current Gold Prices with Stock Data Types
Excel has a stock data type that allows you to convert stock ticker names into rich data types that contain multiple points of information about the stock such as the price, volume, market cap, etc. These stock data types do more than just stocks though. They can also give you financial information on currencies, cryptocurrencies, and commodities such as gold. Unfortunately, these won’t be the spot price but rather the futures contract price. A gold futures contract is an agreement for the sale of gold at a predetermined price at a specified date in the future. The gold price that is determined through this process can provide investors with risk protection against any losses incurred from the fluctuating gold market. This instrument can be used by gold investors and gold producers to hedge against price volatility and minimize the risk associated with changes in gold prices. Here’s how to use the stock data type to get gold prices. Excel will attempt to convert this to a stock data type but will be unsuccessful and show you the Data Selector menu where you can search for the correct data type. 💡 Tip: If you don’t see the Data Selector menu, you can always open it by right-clicking on a data type and then selecting the Data Type, and then choosing Change from the submenu. This will convert the cell to the data type and you should see a small icon in the cell next to the word Gold. When you click on the icon, this will show a data card with various data items that include the current price. These can be extracted into the grid. This will add the above formula into an adjacent empty cell that uses the dot notation to reference the Price field from the data type cell. This data is connected to the financial market data provided by Refinitiv and will update with the latest data when you refresh the data type. 💡 Tip: You can refresh a data type to get the latest gold prices by right-clicking on the cell ➜ choosing the Data Type option ➜ then selecting Refresh. You can also adjust the automatic refresh settings by selecting Refresh Settings.
Get Historical Gold Prices with the STOCKHISTORY Function
You can also get the historical gold futures prices using the STOCKHISTORY function in combination with the Stocks data type. The STOCKHISTORY function will return historical stock prices and other financial metrics based on a stock ticker. The above formula will get the historical gold prices for the last 7 days based on the cell in B2 containing the gold futures contract data type. In the most basic form, the STOCKHISTORY function requires a stock ticker or cell reference to a data type, start date, and end date to return historical prices. The TODAY function is used to get today’s date and 7 days are subtracted to get the date seven days ago as the start date. The end date is also entered with the TODAY function. This results in the previous seven days of gold prices being returned by the STOCKHISTORY function.
Get Gold Prices with the WEBSERVICE Function
If your version of Excel doesn’t have data types or the STOCKHISTORY function, don’t worry, there is still an easy way to get the gold prices. You can use an external API such as MetalPriceAPI along with the WEBSERVICE function to get the gold prices. You will need to sign up to get your API key and use their free plan. Once you have an API key, you can use the following formulas to return the gold prices. There are two API endpoints depending on if you want the current gold price or a historical gold price.
Current Gold Prices
The above formula uses the latest endpoint and will result in the last available gold price in USD for 1 ounce of gold. You will need to replace the xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx value with your own API key. The WEBSERVICE function will return text similar to the above. This is JSON data and will include the price at the end. This will need to be parsed so it only returns the value. You can parse this text with the TEXTSPLIT function based on the semicolon : and right curly brace } characters since the value of interest is between these two. The TEXTSPLIT allows you to split text based on multiple characters, so passing an array of two delimiters {":","}"} will split the value into its own cell. This will return an array of split values and you can use the INDEX function to get only the price which will be in the 1st row and 6th column of the split results. The VALUE function is used to convert the resulting text into a number so it can be used in further numerical calculations.
Historical Gold Prices
A similar formula can be used to return the historical price of gold from any given date. The formula uses the date endpoint of the API where the date is entered in yyyy-mm-dd format at the end of the API URL. This results in a similar JSON text being returned, and it can be parsed in the same way previously shown. This formula can be adjusted to reference a cell with a date value as above. This will make it easier to get the historical price for any date! This uses the TEXT function to ensure the date referenced in cell B2 is in the proper yyyy-mm-dd format for the API. This will get you the historical gold price for any date!
Conclusions
The value of gold prices and other financial instruments are often tracked in Excel spreadsheets by investors. There are a few easy ways to get these current and historical prices into your Excel workbook. Stocks data types, the STOCKHISTORY function, or even the WEBSERVICE function with an external API can be used. How do you import gold prices to your Excel files? Let me know in the comments!
