Categories
Investing

How to create a stock portfolio spreadsheet (Google Sheets)

Learn how to create your own Google Sheets stock portfolio tracker.

You’ve probably seen some of those amazing spreadsheets experienced investors use to keep tabs on how their portfolios are performing. Interestingly, even if you’re not familiar with Google Sheets, it’s really easy to create your own. And if you don’t quite have the spare time, you can always use my dividend tracker.

Using Google Sheets (or Microsoft Excel), it’s possible to create an offline-friendly tool that will automatically keep track of how all your stocks are performing. Time is the most valuable asset we have as human beings and it’s important we learn to use it efficiently.

Create your own stock portfolio tracker

The primary reason that makes spreadsheets (and Google Sheets) so good to work with for tracking your stock and dividend-focused portfolio is the automation available. Using GOOGLEFINANCE we can pull data from the stock market and effectively track everything with minimal input.

Create your very own stock investment portfolio spreadsheet tracker.

How advanced your spreadsheet tracker becomes largely boils down to how much time you have free to learn the formula and what exactly do you need the spreadsheet to do. While websites and other platforms can be great tools for checking on your portfolio, it’s good to have it saved locally.

Firstly, we’re going to need a Google account at the ready to gain access to Google Sheets. (See this handy guide if you’ve yet to create one.) Now, follow these steps:

  1. Go to Google Drive.
  2. Click “New” in the top-left corner.
  3. Select the arrow next to “Google Sheets.”
  4. Choose “Blank spreadsheet.”

This will open up a blank Google Sheet for you to work with. I’d recommend starting with the basics first.

Learn how to invest safely for a brighter future

New to investing? Don’t get caught up in all the meme stocks and people trying to sell your courses. Take a look around my free resources to help you get started on the path to financial independance.

Building the basics

Add some column headers that we will be tracking. You can use the following to continue with this guide:

  • Name
  • Ticker
  • Shares
  • Total cost
  • Purchase cost
  • Current price
  • Market value
  • Market gain
  • Market gain (%)
  • Dividends
  • Total gain
  • Total gain (%)

For the name, you can enter the company (or listing) title. In our example, we’re going with Apple. The ticker is the symbol used by the stock market. For Apple, it’s AAPL. You can find the ticker symbol using Google, Yahoo!, and other financial websites. It’s important we use the correct ticker symbols.

The number of shares can be entered here. We’ve got 50 shares of AAPL (Apple). Next up we enter the total amount paid. The purchase cost is the average cost paid for the shares. If you purchase 25 shares of a company at £100, followed by a further 25 shares priced at £50, your average cost price comes down to £75 per share. This is what we call Dollar-cost averaging.

Working with formulas

In order to automatically work out our average cost price, enter the following formula into the corresponding cell:

=(D2/C2)

Enter the following into the current price cell for your first stock entry:

=GOOGLEFINANCE(B2, "price")

This will ask Google Sheets to fetch the latest price for the ticker symbol in cell B2. It’s incredibly important the correct ticker is used, else we’ll be fetching incorrect data. Market value is what the shares are worth at the current market price (if you were to sell):

=(F2*C2)

The next column is for market gain, which is what will work out our unrealized performance. When I talk unrealized, that means any shares we have outstanding. Shares sold would produce realized gains or losses. Enter the following formula for the corresponding cell:

=(F2-E2)*C2

This formula will subtract the average purchase cost from the current market price and multiply that value by the number of shares we have. If the market price is higher than our purchase cost, we’ll be in the green. This will tell us how much by.

Getting more advanced

If, like me, you also enjoy seeing percentages, add another column with the following formula:

=IF(C2=0,0,E2/C2)

This is a slightly more complicated formula. The IF part asks Google Sheets to check if our total shares column is higher than zero. If not, it’ll print a zero. Should the value entered is higher than zero it’ll divide the market gains by the total cost. Make sure to set this cell format as a percentage (Format > Number > Percent). We’re up 134% in our example, which is certainly not the norm.

Dividends are a brilliant way of making your money work for you (see my what is dividend investing guide for more details). As well as appreciating in value over time, dividends paid out by companies are excellent for compounding wealth. Enter the total amount of dividends collected into this cell.

The total gain will then calculate our dividends alongside unrealized gains.

=(H2+J2)

In order to turn this into a handy percentage, simply add another column with the following formula:

=IF(D2=0,0,K2/D2)

Again, the IF statement will check if we actually paid for our shares since this makes it possible to add companies you’ve yet to purchase any shares in. The next part simply divides the market gains by the total amount paid for that stock.

This also shows the power of compounding. Our dividends increased our total gains by another percent, which is substantial.

Total everything up

To make it easier to glance at how our portfolio is performing overall, we need some total values to look at. We’re going to add this above our table. First, we’ll need to drag our table down by simply selecting it and moving it a few cells below. Fear not as Google Sheets will amend all the formula accordingly so nothing will break.

We’re going to add a few portfolio numbers, including:

  • Portfolio value
  • Portfolio cost
  • Market gain (and %)
  • Dividends
  • Total gain (and %)

The formula for these calculations is incredibly simple. We’re either adding multiple cells together or working out a percentage, which we achieved already elsewhere on the Google Sheets. Firstly, portfolio value, which is calculated by:

=SUM(G9:G)

Why have we omitted the number in the second part of our formula calculation? Because this allows us to add additional rows of stocks to our spreadsheet without having to amend the formula. This will simply add up all the values of B7, B8, B9, B10, etc. A similar formula is used for portfolio cost:

=SUM(D9:D)

The market and total gains, as well as dividends, are a similar affair. You can work these out as follows:

=SUM(H9:H)

For the market gain as a percentage (remember to format the cell if it shows as a full number):

=IFERROR(B3/B2,0)

Dividends:

=SUM(J9:J)

Total gain:

=SUM(K9:K)

And finally, total gain as a percentage:

=IFERROR(B6/B2,0)

You should now have a rather basic, but functional investment portfolio tracker. Add all your stock holdings to this spreadsheet and it’ll display current performance. Compared to the free dividend tracker I published, this won’t do much of the tracking to view history etc., but it’s great to get you started.

My free investment portfolio tracker

If you’d like to have a try at something more advance but don’t have the necessary time or skill to create your own tracker, you could always give my free spreadsheet a try.

By Rich Edmonds

Rich creates content for the top Windows-focused publication, but by night he tries to make his money work for him and rambles far too much here.

Leave a Reply

Your email address will not be published. Required fields are marked *