How to Get Silver Prices in Excel [3 Best Ways]

If you need to populate the current silver prices in the precious metal trading markets, you’ve come to the right place. Keep reading this step-by-step guide until the end to discover three effortless ways to get silver prices in Excel.

Most smart and successful traders like you prefer to maintain their own spreadsheets for stocks, bonds, commodities, precious metals, and so on. This helps you with informed decision-making when investing in the highly volatile market of stocks and commodities. The reason is, you just shouldn’t rely on another person’s tips or suggestions.

Now, creating and managing an Excel workbook for the current silver price, historical silver prices, and so on, shouldn’t be challenging in Excel. Though, there are no direct buttons or formulas in Excel that you can use to automate the task of fetching silver prices from the stock or commodities market.

You need to rely on a few tricks and tips shared in this tutorial below. Once you create the spreadsheet, it becomes easy for you to track the pricing, track its investments, and monitor the profits in one single Excel workbook.

Read on to find out simple and intuitive methods to get silver prices in Excel using these three effortless methods:

Use Stock Data Types to Get Current Silver Price

The Data Types command menu of Excel enables you to convert any company name or ticker symbol into a stock. Subsequently, it also fetches various market data related to the stock, like price, last traded time, ticker symbol, and many more.

At the time of writing, this function is only available on the following Excel editions with a Microsoft 365 subscription:

• Excel for Microsoft 365
• Excel for Microsoft 365 for Mac
• Excel for the web
• Excel for iPad
• Excel Web App
• Excel for iPhone
• Excel for Android tablets
• Excel for Android phones

Here’s how you can confirm if you have this feature or not:

1. Open the Excel desktop or web app.
2. Access the Data tab on the Excel ribbon menu.

3. Now, check if the Excel installation or web app got the Data Types commands menu or not.
4. If it’s there, you should be able to see these Data Types options: Stocks, Currencies, and Geography.

If you’ve got the above feature in your Excel web app or desktop app, follow these steps to convert the text Silver into a commodity market component and fetch the real-time price for silver from the US market:

1. Type in the text Silver in any cell.
2. Select the cell and then switch the ribbon menu to Data.
3. Click the Stocks option in the Data Types command section.

4. Excel will try to convert the text Silver into an available stock value. Since silver isn’t any equity stock, Excel won’t be successful.
5. As a result, it’ll show a Data Selector sidebar on the right side.

6. In the Data Selector box, you need to look for the Silver Futures contract with the ticker symbol @SI0Y.
7. Once you select this item from the Data Selector panel, the Details section will open in the same sidebar showing information or data fields for the silver futures contracts like Price, Currency, Change, Change %, etc.

8. Scroll to the bottom of the sidebar and click Select.

9. Instantly, the text Sliver will convert into a commodity futures stock with an icon for the stock market or institution.
10. The cell should also show the Insert Data icon on the top right corner of the cell.

11. Click the Insert Data icon to open a context menu containing several items and their figures for the Silver futures contract.
12. From the context menu, click data like Price, Ticker symbol, Trade Unit, Last trade time, Country/region, and so on.
13. Excel will insert data in the same row from left to right.

Here, you’ll get silver prices in Excel indirectly using the futures contract of silver that futures traders trade on the commodities market. However, the price data in this futures contract is the current silver price that you’re looking for.

The silver price in the worksheet won’t update automatically. Thus, each day, you need to hit the Refresh button in the Queries & Connections command menu after selecting the stock item, in this case, Silver.

Use STOCKHISTORY Function to Get Historical Silver Prices

Yet another smart way to fetch historical silver prices in your Excel workbook to maintain investments in the commodities market is the STOCKHISTORY function of Excel. At the time of writing, this function is only available to the following users of Excel:

• Microsoft 365 Personal
• Microsoft 365 Family
• Microsoft 365 Business Premium
• Microsoft 365 Business Standard

Along with any of the above subscription packages, you’ll also need the latest Excel desktop app or web app to use this function. Because the STOCKHISTORY function also requires the Data Types command menu as it utilizes the text-to-stock conversion offered by the Stock data Type.

Now, using the converted Stock data type component, Silver, in the current Excel worksheet, here’s how you can create a database of historical prices of silver from the USA commodity markets for the previous seven days:

1. Select the cell where you want to populate silver prices for the previous seven days.
2. Enter an equal (=) operator and copy and paste the following formula:

=STOCKHISTORY(B7,TODAY()-7,TODAY())

3. Hit Enter and Excel will create a table with the Date and Close price column header automatically.

Don’t forget to customize the above formula by changing the stock cell reference address. In the current tutorial, the silver stock was in the cell B7. You must change it according to your own worksheet. So far, you got the historical silver prices from the last seven days.

Use WEBSERVICE Function to Get Silver Prices

When you’re not using a supported Microsoft Excel edition or subscription plan that provides you the Data Types commands section and the STOCKHISTORY function, you can use the WEBSERVICE function. You’ll be able to call the WEBSERVICE function if you’re using the Excel desktop app 2013 or later.

Though your Microsoft Excel app on the MacBook or iMac show the formula, however, the function won’t fetch any results since it depends on Windows systems. Also, the WEBSERVICE function isn’t available on Excel Web App as well as Excel for the web.

The WEBSERVICE function automatically imports data from internet or the intranet resources like URLs, databases, APIs, etc., into an Excel worksheet. To use this function to get silver prices in Excel, you also need to rely on a commodity market pricing API. For instance, you can use the MetalpriceAPI.

Go to the above website and sign up for a free account. You’ll need a free account to get a secure and custom API access key. Also, the free account will allow you to make up to 100 API calls in a month. If you need to make more API calls, you must sign up for a paid subscription.

Once your MetalpriceAPI account is up and running, follow these methods to get silver prices in Excel:

Real-Time Silver Prices

Simply typing the WEBSERVICE formula and the API URL will fetch information in text format. So, you need to customize the WEBSERVICE function by nesting it in other formulas. Try the following steps as mentioned here:

Firstly, open MetalpriceAPI and run an API call to fetch the current silver price by entering XAG as the Base Currency and USD as the Currencies. Then, you’ll get the following information on a new web page:

{"success":true,"base":"XAG","timestamp":1678617080,"rates":{"USD":20.54500001}}

The API call URL will be the following:

https://api.metalpriceapi.com/v1/latest?api_key=xxxxxxxxxx&base=XAG&currencies=USD

The Key xxxxxxxxxx will change when you’ll use your own account for the API call. Open an Excel workbook where you need live silver prices. Then, follow these instructions:

1. Select any cell and type in an equal (=) sign.
2. Copy and paste the following formula and hit Enter:

=WEBSERVICE("https://api.metalpriceapi.com/v1/latest?api_key=e15962d2d3f1b79ffe2967b72d4147b0&base=XAG&currencies=USD")

3. You should now see the price of silver in a text string as shown earlier.
4. Now, you can use the TEXTSPLIT formula in the following way to spilt the texts into different cells:

=TEXTSPLIT(C7,":","}")

5. In the above formula, C7 is the text string that you got from the API call made via the WEBSERVICE function.
6. By now, you should get the silver price in a different cell, cell G10 in this tutorial.
7. In any of your further calculations, refer to this cell to factor in the silver price.

Historical Silver Prices

Using an API service for commodity prices, you can also get historical prices for traded precious metals including silver. The API call URL to be used with the Excel WEBSERVICE function is as below:

=WEBSERVICE("https://api.metalpriceapi.com/v1/2023-10-03?api_key=e15962d2d3f1b79ffe2967b72d4147b0&base=XAG&currencies=USD")

As you can see in the above function, you simply need to replace the text “Latest” with the date. You must use the YYYY-MM-DD date format so that the API can understand your request.

Conclusions

It’s pretty popular among investors and traders to track current and historical precious metal prices, like silver prices, in Excel spreadsheets for affordability and convenience. You don’t need to pay any third-party agencies to get silver prices in Excel. Also, you don’t put your investment decisions at risk by relying on someone else’s market analytics.

You can right away start using the above methods to get silver prices in Excel. These methods involve using various Excel functionalities like Data Types, WEBSERVICE formula, and STOCKHISTORY command.

Give the above methods a try, and let me know your experience in the comment box below. Also, if you know any other intuitive and effortless ways to import silver prices in Excel, do comment in the comment box.

Next up, learn how to go to cell A1 in Excel.