Google 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 a function called GOOGLEFINANCE.
What’s more, users can create charts that compare the ongoing performance of two or more stocks and/or mutual funds. GOOGLEFINANCE 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.
Example 1: Bitcoin vs Gold
Bitcoin and 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.
The GOOGLEFINANCE function supports the symbol BTCUSD, which is the cost of Bitcoin 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 Apmex. The formula was dragged down for all 365 days in 2017. That formula is:
The result of these two columns of data is the following chart. If you hover your cursor across the bars, the daily Bitcoin prices will display.
Example 2: 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.
Example 3: 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.