This spreadsheet imports historical prices for gold, silver, platinum and palladium from the Internet, and plots several technical trading indicators.
Precious metals form a small but significant part of many portfolios.
But this asset class is volatile – more so than the usual range of equity and bond funds.
So investors need to pay careful attention to where the price has been, and where it might be going.
And that’s the beauty of this free tool.
This web-connected Excel spreadsheet imports historical precious metal prices for gold, silver, platinum and zinc. You get prices in your choice of currencies. The spreadsheet also plots several technical market indicators.
The spreadsheet is free, and the download link is at the bottom of this article. It’s a pretty easy tool to use.
- Select either the gold, silver, platinum or palladium radio buttons
- Enter a start date and end date
- Choose a currency from the drop-down box (you can get precious metal prices in many currencies, including US Dollars, UK Pounds, Canadian Dollars, South African Rand, and 160 others)
- If needed, change the number of trading days in a year and the time window for the historic volatility (daily volatility is annualized with respect to the number of days in a year)
- If needed, change the time window for the relative strength index
One you click the Get Data button, magic happens in the background.
A VBA macro (UDF) connects to a web-service, and downloads historical precious metal prices between the start and end date. The data can be found on the “Data” sheet.
The VBA magic doesn’t stop there – Excel then calculates and plots the
- historical volatility and daily price
- 12- and 26-day exponential moving average
- relative strength index
- and MACD & signal
The calculations are placed on the “Data” sheet, while all four plots can be found on the main Parameters sheet.
Every time you click Get Data, Excel recreates and customizes the plots with VBA. This showcases the flexibility of Excel and VBA – combined, they’re a powerful armory of tools for financial professionals.
Traders use technical indicators to generate buy/sell signals. For example, you can learn how market technicians use MACD here.