'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Get Live Gold Prices in Excel Using Linked Data Types
Question details
The user wants to import live gold market prices into an Excel worksheet using linked data types to track financial data.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking precious metal investments or financial portfolios that require up-to-date market information without manual data entry.
- Observed behavior
- A method is needed to automatically fetch, display, and refresh real-time gold prices inside a spreadsheet cell.
Ensure you have an active internet connection and are logged into a Microsoft 365 account, as the built-in Stocks data type requires an active subscription.
Fetch Gold Prices via the Excel Stocks Data Type
The quickest and most reliable way to get live financial data is by using Excel's built-in Stocks data type.
Microsoft 365 includes a powerful Linked Data Types feature that pulls rich information directly from financial exchanges. By converting standard text into a 'Stocks' data type, you can extract specific fields like Price, Change, and 52-week Highs.
Type 'Gold' (or a specific ticker symbol like 'XAU/USD' or an ETF like 'GLD') into an empty cell in your worksheet.
Select the cell, navigate to the 'Data' tab on the Excel ribbon, and click on 'Stocks' within the Data Types group.
Wait for the cell to update with a bank/building icon. If a data selector pane appears on the right, choose the exact gold-related instrument or commodity you wish to track.
Click the small 'Insert Data' icon that appears next to the selected cell, then choose 'Price' from the dropdown list. Alternatively, type a formula like =B2.Price in the adjacent cell.
To keep the price current, go to the 'Data' tab and click 'Refresh All'.
Get Gold Prices Using 'Get Data from Web'
If you do not have Microsoft 365, you can import live gold prices directly from a financial website using Power Query.
Import Live Financial Data Using WPS Spreadsheets
WPS Spreadsheets provides robust tools to import and manage live financial data, including precious metal prices, directly from the web. It's fully compatible with Excel formats and offers a smooth, cost-effective user experience for your financial modeling needs.
- 1. Open WPS Spreadsheets: Launch WPS Office and open a new or existing spreadsheet where you want to track gold prices.
- 2. Navigate to Data Import: Click on the 'Data' tab in the top ribbon and select the 'Import Data' option.
- 3. Set Up Web Query: Choose 'Import External Data' or 'New Web Query', and enter the URL of a financial site tracking gold prices.
- 4. Select and Import Data: Highlight the specific data block or table containing the live gold price and click 'Import' to load it into your sheet.
- 5. Refresh as Needed: Use the 'Refresh All' button under the Data tab to update your gold prices dynamically.
Frequently Asked Questions
Why is the Stocks data type missing in my Excel?
The Linked Data Types feature, including Stocks, is exclusively available to Microsoft 365 subscribers. If you are using perpetual licenses like Excel 2019, 2016, or older versions, this feature will not appear on your Data tab.
How often do the gold prices update using the Stocks data type?
Financial data provided by the Stocks data type is typically delayed by about 15 minutes, depending on the specific exchange rules. You can trigger an update manually by clicking 'Refresh All' or set an automatic refresh interval in the Data Type Refresh Settings.
Which ticker symbol should I use for gold prices?
You can simply type 'Gold' and use the data selector to pick the right index. For more precision, you can use specific financial instrument tickers such as 'XAU/USD' for gold spot prices against the US Dollar, or the ticker of a gold ETF (like 'GLD').
Can I calculate the total value of my gold holdings automatically?
Yes. Once you extract the current price (for example, in cell C2 using the formula =B2.Price), you can multiply it by your total ounces held in another cell (e.g., =C2*D2) to track your portfolio's real-time value.
