Docs: Track Your Investment Portfolio

Google Sheets Show & Tell
, ,
1

About the Google Sheets Investment Tracker

The GoogleFinance formula will get you nearly live (20 minute delayed) stock price data fed into your finance spreadsheet. You can go well beyond stock prices.

This is a static sheet, not available via the Tiller Community Solutions add-on at this time.

3104Ă—1970 660 KB

You can track the opening price, the closing price for any date, the daily or 52 week high and low, the average and current daily volume, market cap, EPS, PE, currency, and more.

Check out this example spreadsheet we’ve created to track some indexes, stocks, and mutual funds. The basic formula for pulling Alphabet’s stock price, for example, would be =GoogleFinance(GOOG, “price”).

How to get the tracker

Copy this example sheet into your Tiller-powered Google Sheet:

  1. Open your Tiller spreadsheet.
  2. Copy the URL of your sheet.
  3. Open the example portfolio tracker.
  4. Right click the “Portfolio” tab at the bottom and choose “Copy to.”
  5. Paste the URL for your Tiller spreadsheet in to bottom of the Copy To window that pops up.
  6. Do the same for the Detail tab.

Using the Portfolio Tracker Example sheet

Customizing Your Portfolio Tracker

  1. On the Portfolio sheet update the Symbols in Column A to use your own.
  2. Update the Shares (column H) and the Date (Column K) for any stocks and funds you have.
  3. Add rows as necessary above and below for each type and copy/update the existing formulas into the new rows until you’ve filled in everything you want to track.

Customizing Your Detail Sheet

  1. Navigate to the Detail sheet
  2. Edit the GoogleFinance formula in cell A1
  3. Update the Stock symbol from “GOOG” to a symbol of your choice.
  4. Change the date range using YYYY-M-D format per the example.
  5. Duplicate the sheet and update for as many stocks as you’d like to track the daily closing price.
  6. Update the chart data range to make sure it’s including all days you’ve pulled in.

3104Ă—1970 592 KB

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Get Help > Spreadsheet templates category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

2 Likes
2

FYI: returnytd via @googlefinance may not give the expected result (it returns the value per the last quarter rather than the year til now).
I found somewhere on the internets to use webscraping via googlesheets instead. Here’s an example using a stock symbol in Cell A1 of your sheet.

=IMPORTXML(concatenate(“http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:”,A1), “//table/tbody[1]/tr[1]/td[5]”)/100

2 Likes
3

Thanks Stuart! How did you get the xpath query part (“//table/tbody[1]/tr[1]/td[5]”)/100) of the formula?

Thanks!

4

This may have been where I got it:
https://www.early-retirement.org/forums/f28/retrieving-ytd-return-from-google-sheets-95830.html
don’t ask me how it works.

1 Like
5

I made this Stock Time Machine as an iteration of this sheet if you’re ever looking for specific date ranges for how price has changed. Basically the same “Detail” sheet, but with easier to enter start/end dates.

2 Likes
6

Just a note that we posted a basic overview of tracking your investment portfolio with =GOOGLEFINANCE here.