How to Track Mutual Funds in Google Sheets (2024)

Google Sheets is an online cloud-based service that allows users to create, edit and collaborate on spreadsheets online and offline.

I covered Google Sheets in a previous Spreadsheet Corner article, Alternatives to Excel. I also created a stock portfolio tracker using Google Sheets. In this article, I illustrate how to use the Google Finance capabilities within Google Sheets to access mutual fund data.

Google Sheets is available through Google Drive, 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 or smartphone. Google Drive and its Docs, Sheets and Slides programs work with the two most recent versions of the browsers Chrome, Firefox, Safari and Internet Explorer. You need to make sure cookies and JavaScript are turned on for your browser.

You’ll need to upgrade to a newer browser version to access all your files in Drive. In terms of operating systems, Google Drive currently works with Windows XP and above, Lion 10.7 (for Mac) and above. It doesn’t work with Linux operating systems, but Google suggests that Linux users can use Google Drive on the Web at drive.google.com or through the Google Drive application.For an updated list of accepted browsers and operating systems, go to Google’s help page on System Requirements and Browsers.

Google Finance is a function within Google Sheets that retrieves current or historical security information. Mutual fund prices are provided by Morningstar and mutual fund data is delayed to end-of-day. More information regarding data provided by Google Finance can be found at the Google Finance Data Listing and Disclaimers page.

Creating a Google Drive Account

To use the Google Finance function within Google Sheets, you will need to create a Google Drive account, if you don’t have one. If you currently have an email address that ends with “@gmail.com,” you already have a Google Drive account.

To access Google Drive, you can go to www.google.com/drive and use your Gmail account information to log in. An alternative option is to log into your Gmail account and click the “cube-like” Google apps menu button toward the upper right-hand corner of the page, as shown in Figure 1.

Then select the Google Drive symbol, as shown in Figure 2.

To create a spreadsheet, select “new” on the left-hand side of the page, then select Google Sheets.

Using Google Finance

The syntax for the Google Finance function is:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days/end_date], [interval])

The only two required fields are the ticker and attribute inputs; these are required to be input for the function to work properly. If you don’t specify an attribute, Google Finance will use price as a default. Using only the required inputs, your formula would look similar to this:

=GOOGLEFINANCE(ticker, attribute)

An attribute can be current data such as price, volume or market capitalization. A list of attributes is available at the function help page.

You have to type the attribute name exactly as it appears on the syntax list of the function help page, or the formula will not recognize what you are asking it to retrieve. On the list of attributes, there are several that are specific to mutual funds:

• “closeyest”- The previous day’s closing price
• “date”- The date at which the net asset value was reported
• “returnytd”- The year-to-date return
• “netassets”- The net assets
• "change"- The change in the most recently reported net asset value and the one immediately prior
• "changepct"- The percentage change in the net asset value
• "yieldpct"- The distribution yield, the sum of the prior 12 months’ income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month’s net asset value number
• "returnday"- One-day total return
• "return1"- One-week total return
• "return4"- Four-week total return
• "return13”- 13-week total return
• "return52"- 52-week (annual) total return
• "return156"- 156-week (three-year) total return
• "return260"- 260-week (five-year) total return
• "incomedividend"- The amount of the most recent cash distribution
• "incomedividenddate"- The date of the most recent cash distribution
• "capitalgain"- The amount of the most recent capital gain distribution
• "morningstarrating"- The Morningstar star rating
• "expenseratio"- The fund’s expense ratio

For example, if you wanted to be able to view a particular expense ratio for a mutual fund, you would type the following formula into Google Sheets:

=GOOGLEFINANCE("VFINX", "expenseratio")

Attributes must be enclosed in parenthesis, as well as the ticker symbol.

The Spreadsheet

CI’s Mutual Fund Watchlist With Google Sheets contains only one tab: Watchlist (Figure 3). Cells highlighted in yellow require manual input, while everything else will be calculated accordingly. CI’s Mutual Fund Watchlist with Google Sheets will appear when you click thislink.

In order to have your own copy that you can edit, you have several options. The first one requires you to sign in to Google, then click File, and Make a Copy. You will then officially have your own copy of the Watchlist. To access the spreadsheet at a later date, sign into Google and click the grid-like menu icon in the top right-hand corner of your page (Figure 1). This will show you choices such as: Calendar, Finance, Maps, News, Gmail and Drive. Click “Drive.” This is where you will be able to access your copy of the spreadsheet.

If you do not have a Google account, once you click the Google spreadsheet link above to open the file, click File, Download As. You will see several options including: Microsoft Excel, OpenDocument format, PDF document, comma-separated values (CSV), tab-separated values(TSV), or web page. Based on your preference, click any of these options to download your own copy of the CI Mutual Fund Watchlist and save to a location of your choosing.

This is a basic mutual fund watchlist designed to demonstrate using Google Finance functions within Google Sheets. As you become familiar with using Google Finance functions, you can add data of interest to you and reorganize the sheet to your preference.

The following two sections review some basic spreadsheet conventions that will be helpful to understand as you work with Google Sheets.

Cell References

You can reference specific fund ticker symbols or attributes by referencing specific cells within your spreadsheet. Cell references do not take quotation marks in the formula. For example, let’s say that the ticker symbol VFINX is in cell A4 in your spreadsheet. You would enter the following formula:

=GOOGLEFINANCE(A4, "expenseratio")

Here you are still pointing to the particular stock ticker; however, you are specifying a cell as opposed to directly naming the ticker in the formula. Why is this useful? Using cell references is helpful if you are creating something similar to a watchlist and will often be changing the ticker symbols you are following. By referencing cell A4 in your formulas, you can pull in information on any ticker you put in that cell instead of having to replace the ticker in each formula.

You can also create an “absolute cell reference.” Absolute cell references are used when you wish to copy or move formulas elsewhere on the worksheet, but you want the formula to consistently refer to a particular cell. Relative cell references, like in the example used above, change when a formula is copied to another cell, whereas absolute cell references remain constant. There are advantages to using both types of cell references.

For example, if you input the formula =(C1+D1) into cell B1, then click on cell B1 and “drag” the formula down, the calculation will repeat itself, except that the cells it references will change to the respective row number. So if you drag the (C1+D1) formula from B1 down to B10, the formula in cell B2 would be:

=(C2+D2)

Google Finance (and Excel) assumes that you want to change the cells that are used in the formula when you copy it. This can be very helpful if you need to repeat the same calculation across multiple rows and columns. Figure 4 shows the formulas using relative cell references in column B. Figure 5 shows the results of the calculations adding the figures in column C and column D in column B for each row.

If instead you wanted to use an absolute cell reference in cell B1, it would look like this:

=($C$1+$D$1)

The dollar signs in front of the cell references instructs the spreadsheet that this is an “absolute” reference. Now when you drag cell B1 formula ($C$1+$D$1) down to cell B10, cell B2 has the formula:

=($C$1+$D$1)

The formula and specific cells used in the calculation stay constant. Figure 6 shows the formulas using absolute cell references in column B. Figure 7 shows how the results of the calculations differ from those in Figure 5, since all column B formulas are adding cells C1 and D1.

The dollar sign in front of the column reference as well as the row reference means that the column and row must stay constant even if the formula is moved. If you were to put a dollar sign only in front of the C in the formula above ($C1), the row reference number would still change when you dragged the formula down.

These concepts will help you not only in Google Sheets but also in Excel.

IF Function

The IF function is used throughout the spreadsheet.

The IF function is defined as:

=IF(logical_text, [value_if_true], [value_if_false])

If the logical text is determined to be true, then the “value if true” will result. If the logical text is determined to be false, then the “value if false” will result. In this case, if the referenced cell is blank (“ “), a blank should appear in the destination cell, and if the referenced cell is not blank, the formula after the final comma should be calculated in the destination cell.

I used this function throughout the spreadsheet so I could easily drag down formulas without error warnings appearing (it makes the spreadsheet appear “prettier,” not to mention easier to read). Some common Excel-related errors include: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!.

Conclusion

Google Sheets offers mutual fund data for individual investors who may be interested in tracking their portfolio using a cloud-based spreadsheet. While many of the data points offered are basic, they are still very useful. Best of all, it is free.

If any CI members create their own mutual fund (or stock) tracking portfolio using Google Sheets (or even a regular spreadsheet), please email us if you would be interested in sharing it with other subscribers: CI@aaii.com.

Discussion