Make Your Own Investments Spreadsheet
After using Quicken for the last 15 years to track my portfolio, I knew there had to be a better way to track my investments each day.
Then I discovered Google Finance! I've used spreadsheets for years but didn't consider myself any kind of an expert. I wrote my first spreadsheets on a Commodore 64. But today, with online access to data, the possibilities are endless.
My idea was to make a spreadsheet where I can get the daily prices on my mutual fund and etf portfolio. Stocks are available too. As long as I had the basic data, I wanted to keep better records by recording weekly, monthly, and quarterly returns and compare them with the previous period. It all ballooned from there.
I'm proud to say that I've been Quicken-Free for a number of months.
First download the sheet from the above link. If you have a Google Docs account, you can save it to your cloud. If not, you can use Google Chrome to show your sheet and edit it.
The Spreadsheet Template
First, Save the sheet to your drive. This one can't be edited until you save it.
The main sheet has several sections:
The List of Investments are divided in to sections, according to tax treatment or investment type. I use Taxable, Taxable ETF, Traditional IRA, and Roth IRA. Each section has totals of
Daily Price change,
Daily change of value,
Date of pricing,
Unrealized Gains (in taxable),
Distributions (in taxable),
Percentage of portfolio in each section,
Capital Gains percentage,
First bought date,
Last bought or sold date,
The way Google Sheets returns mutual fund pricing vs. stocks or ETF's is handled somewhat different. Here are examples of cell entries for a mutual fund and an ETF. Stocks are entered the same as ETF's.
You need only to enter the information in columns:
A, D, I, J, K, L, O, P, Q, R in the taxable section.
In the IRA sections, A, D, L
There are several Totals sections. There is the present value, last week, last month, last quarter and beginning of the year amounts. At the end of each week, month or quarter, I manually enter these amounts.
Then there is a week total section. Each week the number is placed on the left next to the week. There are BOY (beginning of year) and EOY (end of year) amounts too.
The number of Fridays per month varies, so the the green coloring of the cells can be adjusted, as well as the first Friday of each month.
At the end of each month, I enter the portfolio value manually in the 12 months section.
At the end of each quarter, I enter the portfolio value manually there too.
You can add or delete rows according to the number of investments you wish to list.
I also have places to enter dates for the first purchase and the latest purchase or sale. In the taxable section, if the last date is less than one year, the date shows in red, indicating that a sale would be taxed short term capital gains rate.
Besides the main spreadsheet, there are sub-sheets. In my case, I added one sub sheet per taxable investment. This is where I enter each purchase and keep track of the cost basis. I didn't use sub-sheets on the IRA or Roth sections as it isn't necessary to know the cost basis.
There is a master sub-sheet, called Cost.
When you add a new investment, make a copy of the Cost sheet and then rename it to the ticker symbol of your new investment.
If you don't care about purchase data, realized gains, unrealized gains, you can skip these sub-sheets. To be entirely correct, you have to enter every share purchase (or through distributions) manually. Or you can just take the present number of shares and cost of those shares and enter it all on one line.
I didn't design the subsection sheet. I downloaded it from somewhere years ago. I don't remember where, unfortunately, so I am unable to credit the person that made it.
I don't buy and sell a lot, so my sheet doesn't need a lot of frequent changes. If you buy and sell often, changing the sheet manually might not be that pleasant. But it is mostly adding and deleting a row, and adding a sub-sheet.
Favorite Financial Links
Money Life Show with Chuck Jaffe. A daily downloadable podcast on various aspects of money and investing.
The Disiplined Investor podcasts with Andrew Horowitz. How great is that?
Masters In Business with Barry Ridholtz. Podcasts with fascinating people.
Sound Investing with Paul Merriman.