Track Your Stock Portfolio Returns Using Google Sheets (or Excel) - Step by Step Tutorial

Download a finished copy of this spreadsheet at InvestSomeMoney.com:
investsomemoney.com/how-to-make-a-stock-portfol…

Track your dividend income, yield, and growth with Excel/Sheets:
investsomemoney.com/create-an-amazing-dividend-…

5 Support Videos for the Excel Stock Portfolio Spreadsheet
investsomemoney.com/support-for-the-spreadsheet…

===================

1:30 Set up Google account
2:15 Headers for portfolio
5:20 Different lots for the same stock
8:00 Purchase cost, current price, & gross current value
13:15 Dividends received
17:10 Net current value, total gain/loss, & annualized gain/loss
25:30 Summary of the total portfolio
31:45 Portfolio weighted-average purchase date
34:20 Formatting
37:45 Charts

===================

How to make a stock portfolio and Excel, Google Sheets, or any other spreadsheet software.

This how-to can be followed along in either Google Sheets or in Excel. Really any spreadsheet software will do fine. The formulas will be the same. The formatting and charting options should be very similar.

I’ll be using Google Sheets myself. I like Excel, and I use it often. But particularly with some of my more intensive models, I like Excel. But, in this case, however, I think that Google Sheets is a better option.

Firstly, because of its GOOGLEFINANCE functionality – which is a formula that automatically updates certain fields. You know, things like price, volume, P/E ratio, earnings per share, etc. A lot of others too.

And Google sheets is cloud-based which allows me to access this portfolio spreadsheet from anywhere. Microsoft does have a cloud-based version of Office and Excel. But I don’t really recommend it.

I’m a Microsoft fan in general and an Excel fan in particular, but that Office 365, or whatever, it is that they call it, fell way short. I just didn’t like it, but, by all means, use whatever you’re most comfortable with.

So, if you’re gonna follow along in Google Sheets you’ll need a Google account. if you don’t have one already then there’s the link in the post that you can follow that will take you to Google and walk you through how to set up a Google account.

Once you have that account set up then you’re gonna go to Google Drive and you’ll select New ► Google Sheets. There’s a picture of that here and once you do that you’ll have a blank spreadsheet ready to go.

So, here’s the finished product. But, let’s walk through the steps to see how we got there.

First things first, you’re gonna enter your headers. This will lay the foundation so we know what information we’re either gonna have to go get or create formulas for.

In this example, I kept things pretty simple. You know, the amount of information that you could compile from an investment’s current or historical data is pretty much limitless. So, I wanted to keep things, in this instance, fairly simplistic.

Obviously, if you wanted to, you could always build upon this. This how-to is meant to be a starting point.

The pieces of information that I’m gonna compile are the company (or in some cases, it might be a mutual fund) name, the ticker symbol, the purchase price, shares, purchase cost, current price, gross current value (without dividends), total dividends received, net current value (which would include dividends), total gains and losses (in terms of dollars and in terms of percentage), and annualized gain and loss.

So, here you’re gonna enter these headers along row 5 and it will stretch from column B to column N.

I like to leave a little space at the top. But also, I want the room to put this in, and this is a place right here where you would enter when the portfolio was updated.

Okay, some of the cells I also bolded, as you can see. We do that too. But with all the headers entered then we can move on to some basic stock information.

This is all going to be information that you will know, or have access to. Simple to get your hands on.

If you don’t have access to it really easily, do what I did in my examples here – I chose stocks at random.

You may have more or fewer that’s fine. You’re not gonna have so many that you’ll run out of space on your spreadsheet.

If you have an online broker, you might need to use it to uncover some of this information. You know, particularly, the purchase date. But if you want an accurate calculation of the annualized gain in loss then that is information that you’re going to want to have.

So, here is where I typed all that information in.

In some cases, you might have different lots of stock. For instance, let’s look at my first stock I’ve listed here. It’s actually a closed-end fund, I think. That symbol is ZF.
Let’s say rather than purchasing all 130 shares on May 4th of 2015, I purchased a hundred shares on May 4th, 2014, and 30 shares on May 4th of 2015. I have a total of 130. But I purchased those in two separate transactions.