Google Sheets: Bitcoin Price vs Gold Price

Google Sheets Bitcoin Price vs Gold PriceGoogle Sheets, available as part of both consumer Gmail and organization-wide G Suite, allows users to easily create trend charts of anything with a valid Google Finance ticker symbol. This is done using the GOOGLEFINANCE function.

What’s more, users can create charts that compare the ongoing performance of two or more stocks and/or mutual funds. The GOOGLEFINANCE function supports any date range. It supports both daily and weekly intervals. You can “set it and forget it” — cells will auto-populate or auto-update at regular intervals.

Google Sheets Bitcoin Price vs Gold Price Chart

The Bitcoin price and the price of gold have been compared to one another by many in 2017, as the dollar value of Bitcoin surpassed the spot price for one ounce of gold in USD in early March 2017. During May of 2017, the dollar price of Bitcoin climbed well beyond the price of gold. In June of 2017, Bitcoin continued its ascent toward USD $3,000.

The GOOGLEFINANCE function supports the symbol BTCUSD, which is the Bitcoin price in US dollars. It’s as simple as pasting a formula like this into a cell:

=GOOGLEFINANCE(“BTCUSD”, “price”, DATE(2017,1,1), DATE(2017,12,31), “DAILY”)

The GOOGLEFINANCE function does not support commodities such as gold. Because of this, we used a simple INDEX function to import the current price of gold from Packetizer. The formula was dragged down for all 365 days in 2017. That formula is:

=Index(ImportXml(“http://services.packetizer.com/spotprices/?f=xml”, “/SpotPrices/gold”))

The result of these two columns of data is the following Google Sheets chart, which is the daily Bitcoin price compared to the current gold price. If you hover your cursor across the bars, the daily Bitcoin prices will display. Here is a direct link to the sheet.

Stock Ticker Example: Apple vs salesforce.com

These are two well-known technology companies that are based in the San Francisco Bay area. By creating a chart from two GOOGLEFINANCE functions, the relative performance can be easily seen.

Index Example: DJIA vs Nikkei 225

It’s also easy to compare two stock market indices:

GOOGLEFINANCE Stock Attributes

Using simple formulas, it’s easy to display stock attributes in a Google Sheet. We chose CRM, the ticker for Salesforce.com.

GOOGLEFINANCE Fund Attributes

It’s just as easy to display fund attributes in a Google Sheet. This information is for the Fidelity┬« Contrafund┬« Fund.

Leave a Reply

Your email address will not be published. Required fields are marked *