1

Precious Metals – Historical Prices and Technical Trading Charts

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.

Relative Strength Index for Platinum in Excel

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.

Main screen of the technical trading spreadsheet for precious metals

The spreadsheet is free, and the download link is at the bottom of this article. It’s a pretty easy tool to use.

  1. Select either the gold, silver, platinum or palladium radio buttons
  2. Enter a start date and end date
  3. 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)
  4. 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)
  5. 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.

An Excel spreadsheet that imports precious metal prices from the Internet and plots technical charts. This is the data sheet.

The VBA magic doesn’t stop there – Excel then calculates and plots the

The calculations are placed on the “Data” sheet, while all four plots can be found on the main Parameters sheet.

An Excel spreadsheet that automatically generates technical trading charts for gold, silver, platinum and palladium

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.

Download Excel Spreadsheet to Import Historic Precious Metal Prices from Web & Plot Technical Charts


One Response to "Precious Metals – Historical Prices and Technical Trading Charts"

  1. Nitiin A Khandkar says:

    Hi Samir,

    Great work! How do I go about downloading data for say aluminium or steel (ferrous/non-ferrous) from the internet and convert the same into charts? Thanks!

Leave a Reply

Submit Comment
What is 14 + 12 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)
© 2014 Invest Excel. All rights reserved. XHTML / CSS Valid.

Facebook