Building a stock portfolio tracking spreadsheet

Today I want to take a look at building out a spreadsheet that I can use to track all my investments in one place. I have multiple accounts and going from site to site to check my investments sucks, so I want to track everything on a single spreadsheet. Originally I was going to go with Microsoft excel for my spreadsheet of choice, but finally decided to the use Google Sheets mostly due to portability and cost.

Let's just right in and start by going to google sheets and creating a new blank spreadsheet.

Google sheets has some built in functionality that will allow you to pull in financial data, but lets start by setting up columns headers.

This is the basic list of information that I want to track to start off with, but there is still information that related to dividends that will be a little more tricky and will require access to data that can't be easily pulled within Google sheets.

Now I will breakdown each column and talk about how to set up them up if they are not a field that will be manually input.

Column A: Symbol

This is the column that will hold the stock ticker symbol. So for example if we had shares of Microsoft the ticker symbol would be MSFT.

Column B: Company Name

We are going to use a built in function to fill in this field based off the value in column A. To do this we will use the following function:

The GOOGLEFINANCE function takes a symbol in this case the value in A2, and then it can take a number of parameters to that will determine what data is returned from google finance. In this case we are going to use the "name" parameter to return the company name.

NOTE: If you'd like more information on the GOOGLEFINANCE function you can read more about it here.

Column C: Shares

This is the column where I will input the number of shares that are owned.

Column D: Buy Price

This is the column where I will input my average cost. I say average cost because from time to time I will either be buying or selling shares and will need to adjust this number to get my actual cost for the shares I have.

For example: Say I buy 1 share of Microsoft at $100, when I input my cost it would be $100 per share. Now a week later the price of Microsoft drop to $75 and I decide to buy 4 more shares. I could make another entry and track the purchases individually, but I want to only have one entry per ticker symbol, so I take the total cost of my which would be $400 and I divide that by the number of share. 400/5 = 80 so my average cost would be $80. Your average cost could return a number that has more than 2 decimals places and I recommend entering what ever that number is into this field to ensure accuracy for other calculations.

Column E: Market Price

This column will be calculated automatically using the GOOGLEFINANCE function, except this time we will use the "price" parameter to retrieve the price of the stock.

Cell formula: =GOOGLEFINANCE(A2, "price")

NOTE: This price is not real time and is usually delayed by about 20 minutes so it should only be used for informational purposes only.

Column F: Cost Basis

This column will display what your actually current out of pocket cost is for the stock. For me I am calculating this using the number of share in Column C multiplied by the Average cost in Column D.

Cell formula: =C2*D2

Column G: Market Value

This column will display the current market value of the stock based on calculating the number of shares in Column C and multiplying it by the market price in Column E.

Cell formula: =C2*E2

Column H: Gain/Loss

This column will display the gains or losses for the stock based on taking the Market Value in Column G and subtracting Cost Basis in Column F.

Cell formula: =G2-F2

Column I: Growth

This column will display the percentage of gain or loss for the stock. This calculated by taking the Gain/Loss value in Column H and dividing it by the market value in Column G.

Cell formula: =H2/G2

The next thing I want to do is set up a total for the Cost Basis and Market Value columns using the SUM function. I also want to see the overall gains and losses and total growth, these can just use the same calculations that are used to calculate those columns for the individual rows.

Now you can do some basic formatting to make values display properly, so money values with $ or percentages with %. Some of the other things I have done are freeze some columns and rows so that I can always see the column headings and Company name and symbols. If you are not familiar with Google Sheets you can freeze rows and columns by going under the View menu and choosing the Freeze options you want.

Below you can see my somewhat final set up with formatting.

While this will give me the basics of what I want and allow me to add sheets for tracking accounts with different brokerages, the one thing I am missing is information related to dividends. I have looked into a few different options to try and pull this data from a different data source since it is not easily available using the built in GOOGLEFINANCE functionality.

If you want to manually try to maintain dividend information you could use a site like dividend.com to look up dividend information for each stock. Hopefully I will be able to do a follow up blog post if I am able to come up with an automated solution for pulling dividend data.

Thanks for reading and if you found this helpful please like, share, or comment.

Search This Blog

Journey into Investing