To access CI’s Portfolio Tracker With Google Sheets II, click here.

You will need to make a copy of the spreadsheet for your own use. Click File and choose “Make a copy,” then determine what to name the file and where to save it on your Google Drive. Do not send a “request for edit access” using the View button.

This is an update to the spreadsheet created in the original article “Using Google Sheets to Create a Portfolio Tracker” that was published on CI in October 2014. If you are unfamiliar with Google Sheets, and using Google Finance within Google Sheets, please read that article. It explains not only what Google Sheets is, but a majority of the formulas used in CI’s Portfolio Tracker spreadsheet.

Google Sheets is part of Google Docs, an online-based software that harnesses cloud computing. The overall “service” is accessible via Google Drive, which is a file storage and synchronization service created by Google. It allows users to store files in the cloud, share files and edit documents, spreadsheets and presentations with collaborators. Additionally, applications are available that allow you to access your spreadsheet through your tablet and some smartphones. Google Drive, Docs, Sheets and Slides work with the two most recent versions of Chrome, Firefox, Safari and Internet Explorer web browsers. For more information about system requirements, click the link here.

Creating a Google Drive Account

Figure 1. Google App
Launcher Grid

 

One thing that is worth reiterating is how to create a Google Drive account, if you don’t already have one.

If you have an email address that ends with “gmail” – you already have a Google Drive account (and just don’t know it!). To access your Google Drive account, log into your Gmail account. From the “home page” where you usually view your mail, in the upper right-hand corner you will see a grid-like symbol, as displayed in Figure 1.

Figure 2. Google Drive Icon

 

After clicking the grid symbol, you will see several options appear, one of which says “Drive” and is accompanied by the symbol in Figure 2.

And there you have it! Access to your Google Drive account.

If you do not have a Gmail account, you will need to create a Google Drive account by clicking the link below:

https://accounts.google.com/SignUpWithoutGmail

Once you are logged into Google Drive, you can create a new spreadsheet by clicking the blue button that says New on the left-hand side of the page, and then choosing Google Sheets.

Google Sheets allows users to work offline, which is something Microsoft Office Online lacks (this feature must be enabled to work in Sheets).

Functions and Formulas

Google Sheets shines compared to other cloud-computing (and even desktop) spreadsheet applications when it comes to finance data and functions. Aside from basic mathematical functions, Google Sheets has the ability to use look-up functions for stock and mutual fund data provided by Google Finance. For a list of these functions, click here.

To use the GoogleFinance function, enter this formula into a spreadsheet cell:

=GoogleFinance(“symbol”; “attribute”)

Where:

• “symbol” represents the ticker symbol of the stock or mutual fund you’re looking for; and
• “attribute” represents the type of market data you want (if you leave attribute blank, price is automatically returned)

Although many remark that Excel’s VBA is the authority for macros, Google Sheets has a few tricks of its own. Google Apps Script is a scripting language based on JavaScript that lets you perform functions similar to those using macros.

Changes to the Spreadsheet

While the changes to the CI’s Portfolio Tracker are minor, they are worth mentioning.

The new spreadsheet includes a section that allows users to compare the performance of their holdings since purchase to an appropriate benchmark.

On the Watchlist tab of the spreadsheet, more benchmarks were added at the top to give users some ideas of which benchmarks may be useful when tracking their holdings’ performance. When choosing a benchmark, it’s important to consider its holdings, style and weighting methodology (price-weighted versus market-cap-weighted, etc.), among other things.

Below are some popular benchmarks and their respective tickers that will work in Google Sheets.

• S&P 500 Index (SPX)
• Wilshire 5000 Total Market Index (W5000)
• Russell 2000 (RUT)
• Vanguard Small-Cap Index Fund Admiral Shares (VSMAX)
• iShares Dow Jones U.S. Index Fund (IYY)
• Guggenheim S&P 500 Equal Weight ETF (RSP)
• Nasdaq Composite (IXIC)

Unfortunately, I have not been able to find a place where Google mentions the available ETF or mutual fund database. Alternatively, a good way to see if they have data on a particular fund is to type in the ticker and see if it works. On the Portfolio tab of the spreadsheet, the cell J2 allows users to input the ticker symbol of a benchmark.

Figure 3. Entering a Benchmark Ticker

 

Once you have input your benchmark, the spreadsheet will calculate the benchmark’s gain or loss since purchase. The column “J” involves manual input: You will need to input the closing price of the benchmark on the day that you purchased the particular holding in that row. For example, if I am inputting the benchmark price at purchase for Gilead Sciences (displayed in row 5 in the demo spreadsheet), I would look up the Dow Jones U.S. Index’s (IYY) closing price on May 4, 2012, and enter it into cell J5.

Closing prices of securities can be easily found on Yahoo Finance, by typing the ticker symbol into the Quote Lookup box on the right side and clicking search and then selecting the Historical Data tab.

Another alternative is Google Finance, where you can find historical prices by typing in the ticker symbol at the top and clicking the search icon, then selecting “historical prices” along the left-hand side of the page.

Conclusion

While adding the ability to benchmark your holdings’ performance to the spreadsheet is a small change, it is an important one.

Benchmarking allows you to compare your holdings’ performance to the market, or a specialized benchmark. If you are consistently underperforming the benchmark when selecting your holdings, you may have a flaw in your selection process.

It’s important to remember that periods of under- or overperformance compared to a benchmark are to be expected. Longer-term investors may underperform the benchmark for several years before their holding outperforms.

Link to spreadsheet: CI’s Portfolio Tracker With Google Sheets II

You will need to make a copy of the spreadsheet for your own use. Click File and choose “Make a copy,” then determine what to name the file and where to save it on your Google Drive. Do not send a “request for edit access” using the View button.