GOOGLEFINANCE Function: Create Your Own Comparative Charts

GOOGLEFINANCE Function ExamplesThe GOOGLEFINANCE function is part of Google Sheets. Google Sheets is available in both consumer Gmail and organization-wide G Suite. The function allows users to create trend charts and comparisons for any assets that have a valid Google Finance ticker symbol.

Here are several examples of how the GOOGLEFINANCE function can be used in Google Sheets to compare the performance of stocks and indexes against one another.

GOOGLEFINANCE Function Examples

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. Here is a link to the Google Sheet. You can save it as a new Google Sheet, or save it as a Microsoft Excel file.

Bitcoin vs Gold

To view the Bitcoin price in Google Sheets is as simple as pasting a formula like this into a cell:

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

The GOOGLEFINANCE function does not support commodities. Because of this, we used an INDEX function to import the current price of gold from a service called Packetizer. That formula is:

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

The resulting combination is the following chart. The chart shows the daily Bitcoin price compared to the current gold price. You can display daily Bitcoin prices by hovering your cursor.

The chart takes 15 to 30 seconds to load from Google Docs. You can view the full chart here.

DJIA Chart

It’s easy to chart a stock market index using the GOOGLEFINANCE function. Additional columns can be added to the spreadsheet to track performance against a target or a maximum value for a time period. Here is a link to the Google Sheet.

GOOGLEFINANCE Stock Attributes

With the use of simple formulas, the values of various stock attributes can be displayed in a Google Sheet. For the example below, we chose CRM, the ticker for salesforce.com. Here is a link to the Google Sheet. You can save it as a new Google Sheet, or download it in a variety of formats, including .XLSX.

GOOGLEFINANCE Fund Attributes

Fund attributes can be displayed in a Google Sheet in a similar manner. This information is for the Fidelity® Contrafund® Fund.

ProsperWorks: The CRM Made for G Suite

Learn More →

Leave a Reply

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