How to Make a Free Investment Tracking Spreadsheet
It’s never been easier to invest your money.
The reality is that today anyone with a little money can be an investor. Apps, online brokerages, automated services, broker-dealer advisors, robo-investors, employer-backed retirement accounts, and good old-fashioned banks are all here to help.
But with so many places and ways to invest, how do you stay on top of your portfolio?
After all, you should conduct an investment portfolio check-up at regular intervals – either monthly, quarterly, or yearly, depending on your investment strategy and ability to control your emotional response to your portfolio performance.
Easily Track Your Portfolio with an Investment Spreadsheet
An investment spreadsheet is one of the simplest, most versatile ways to track and understand the performance of your financial portfolio over time.
Here are 3 reasons why an investment spreadsheet is such a useful way to track your portfolio.
And at the bottom of this article, we’ve included a fully functional, free example investment spreadsheet template to get you started.
1. An investment spreadsheet puts all your investment information in one place.
If you have investments with several different companies, such as online brokerage firms, an investment manager, 401(k)s from a different job, and college savings funds, it becomes very time-consuming to track each investment individually.
But using a spreadsheet allows you to look at one document to get an overall picture of the health of your entire investment portfolio.
The power of Google Finance
Using data from Google Finance, Google Sheets can quickly and automatically pull in historic and current securities data so you can view performance in almost real-time. (Google Finance data may be delayed by up to 20 minutes.) And it does all of this for free.
In a Google spreadsheet, you can easily generate a wide range of data regarding the performance of a US stock or mutual fund by using the formula:
=GOOGLEFINANCE(“symbol”,“attribute”)
where the “symbol” is the stock ticker and the “attribute” is the specific data you wish to know.
The possibilities are incredible.
This goes well beyond just stock prices. With =GOOGLEFINANCE, you can also track the opening price, the closing price for any date, the daily or 52 weeks high and low, the average and current daily volume, market cap, EPS, PE, currency, and more.
Say, for example, I want a report that shows Alphabet’s closing price for a date range. The formula to return a table from February 1 through February 10, 2017 would be:
=GOOGLEFINANCE(“GOOG”, “price”, DATE(2017,2,1), DATE(2017,2,10), “DAILY”)
Note that this formula only works for securities, so you cannot use it to display bond data. If you do invest in bonds, you can use this calculator to figure out the bond’s value at maturity.
For more specific information, you can input additional attributes. For example, inputting
=GOOGLEFINANCE(“GOOGL” , ”price” , DATE(2018,7,1) , DATE(2018,7,17) , “daily”)
will return a chart displaying the daily Google stock prices between the two dates. Note that the correct syntax for the date attribute is DATE(YEAR,MONTH,DAY).
You can easily turn this data into a chart for a visual representation of price change over time.
- Select the data you want to see in the chart, such as the date and closing price.
- From the menu, choose “Insert” and “Chart.” A sidebar will appear on the right side of your screen so you can customize the type of chart and the layout of the information displayed.
Using these concepts, you can create a spreadsheet of your entire portfolio and its performance over the desired timeframe.
For a list of attributes and information on proper syntax, check out Google’s help document.
2. With an investment spreadsheet, you can easily gauge your portfolio’s performance against benchmarks.
Using a benchmark is a great way to evaluate the performance of your portfolio. If the benchmark is increasing for several months or years, but your portfolio is flat or decreasing, you may wish to reconsider your portfolio mix.
Depending on the types of investments in your portfolio, there are several popular benchmarks you can use.
The benchmarks create an index based on various information regarding the stock mix within the benchmark itself, so they are well-rounded representations of the industry upon which they focus.
- The Dow Jones Industrial Average indexes the performance of 30 of the largest, most well-known, publicly traded companies in the US. The Dow is especially helpful if you invest in one of the 30 companies listed.
- The S&P 500 Index indexes the performance of 500 large US companies.
- The Russell 2000 indexes the performance of 2000 small US companies.
- The Dow Jones Wilshire 5000 indexes the performance of over 5000 companies of various sizes and in various industries.
- Lipper Fund Indices are a group of indices tracking the performance of different types of mutual funds.
It’s helpful to know how your portfolio is faring against an index with a similar make-up. If you primarily invest in large US companies, the Dow and the S&P 500 are helpful; if however, you prefer to invest in small-company stocks, the Russell 2000 is a better benchmark.
The Dow Jones Wilshire 5000 will give you a good mix of different types of companies.
When using a graph to compare your portfolio’s performance to that of a benchmark, it’s important to note that index values are typically much higher than individual stock values.
The result is a graph that has relatively straight lines, which isn’t particularly helpful. By playing with the benchmarks you use as well as the gridline values and the values of the y-axis, you will be able to see more detail in the trend lines.
Regardless of the performance of your portfolio, it’s important to keep a healthy perspective. The market will go up and down. Constantly monitoring your portfolio can quickly build fear in times of recession and a false sense of security during times of growth. You should decide ahead of time if you’re a short-term or long-term investor.
Long-term investors shouldn’t obsess over small changes; in fact, for the sake of your psychology, it might be best to check in with your portfolio only quarterly or yearly.
3. Using an investment spreadsheet allows you to easily evaluate your asset allocation against your investment strategy.
Asset allocation is the unique makeup of your portfolio. The asset allocation you choose depends largely on the purpose of your investing. Are you looking to make money to fund a vacation within the next year?
If so, you might want to invest in a more liquid asset, such as a CD or a savings account. Are you saving for your child’s college education in 15 years, or are you preparing for retirement in 30 years? In that case, you’re probably more interested in a long-term investment with more growth potential.
Whatever the case, the purpose of your investment will inform the aggressiveness of your target asset allocation. If you need help deciding which allocation is right for you, read this helpful guide from Vanguard.
Once you know your target asset allocation, you can use your investment spreadsheet to compare your actual allocation against your desired allocation to make more informed investing decisions.
To figure out your asset allocation:
1. Create a master sheet of your various investments. You’ll need to know the ticker, price, and number of shares owned to figure out the value of each holding.
2. Use the formula =GOOGLEFINANCE(“ticker”) to display the current price of each security.
3. Multiply the price of the security by the number of shares you own to figure out the value of your holdings.
- Create categories to classify your investments. The example uses stock and cash.
4. Figure out what percentage of your portfolio is wrapped up in each holding by using the formula =(value of a holding) / (total value of all holdings) x 100
5. Figure out your actual asset allocation using the formula
=(total value of category) / (total value of all holdings) x 100
6. Compare your actual allocation to your target allocation.
7. If you wish, you can create a pie chart for a visual representation of your unique allocation using the steps outlined earlier in this article.
Your asset allocation is a measure of the risk inherent in your portfolio. If your actual allocation is different from your target allocation, you may choose to sell off some of your assets to invest in a different investment category, or you may decide to invest as-of-yet uninvested assets to adjust your allocation.
Overseeing your portfolio with an investment spreadsheet reduces the time needed to check your investments through brokerage portals, statements, apps, and websites, allowing you to spend more time actually evaluating your portfolio’s performance.
With the use of graphs and charts, you can gain insight into your performance against common benchmarks as well as your portfolio’s allocation as compared to your target asset allocation.
Free Portfolio Tracker Spreadsheet Template Example
Check out this example portfolio tracker spreadsheet we’ve created to track indexes, stocks, and mutual funds.
This template is designed to work best with spreadsheets automated by Tiller Money. You can try Tiller Money completely free for 30 days.
How to get the tracker
First, copy this example sheet into your Tiller Money-powered Google Sheet:
- Open your Tiller spreadsheet.
- Copy the URL of your sheet.
- Open the example portfolio tracker.
- Right-click the “Portfolio” tab at the bottom and choose “Copy to.”
- Paste the URL for your Tiller spreadsheet in the bottom of the Copy To window that pops up.
- Do the same for the Detail tab.
Using the Portfolio Tracker Example sheet
Customizing your Portfolio Tracker:
- On the Portfolio sheet update the Symbols in Column A to use your own.
- Update the Shares (column H) and the Date (Column K) for any stocks and funds you have.
- 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.
- Navigate to the Detail sheet
- Edit the GoogleFinance formula in cell A1
- Update the Stock symbol from “GOOG” to a symbol of your choice.
- Change the date range using YYYY-M-D format per the example.
- Duplicate the sheet and update for as many stocks as you’d like to track the daily closing price.
- Update the chart data range to make sure it’s including all days you’ve pulled in.
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.
Start Your Free Trial
Keep a clear, confident view of all your money in one place, with flexible templates, powerful privacy, and top-rated support
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
Thanks Stuart! How did you get the xpath query part (“//table/tbody[1]/tr[1]/td[5]”)/100) of the formula?
Thanks!
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.
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.
Just a note that we posted a basic overview of tracking your investment portfolio with =GOOGLEFINANCE here.