Build a Stock Portfolio Tracker in Google Sheets [How To + Free Download]

When I got into share trading, I found myself bouncing around all over the place to keep track of everything. I had a brokerage account that would give me a snapshot of my account obviously, but I had no control over it. I downloaded software solutions and experimented with them but I still found myself wanting to do more. It was after a good while that I decided to start looking into other ways of going about this and that's when I came across the GOOGLEFINANCE() function in Google Sheets.

The GOOGLEFINANCE() function gives us a whole lot of awesome information about shares. I started playing around with the function and found that I could create myself somewhat of a dashboard, making it easy to see where my portfolio stands but with all the control in the world. After some time, I created what I use to this day still and today I want to give you an introduction to this. Grab a coffee, sit back and let's go through this together right now.

Let's start by opening a Google Sheet, you're going to want to play as you read through this post. Trust me, doing so will make this stick far quicker than if you simply read. Right, you've got your sheet open, so let's get started with a simple example of what the function does.

Type the function below into a cell in Google Sheets and click enter.

=GOOGLEFINANCE("NASDAQ:GOOG", "price")

What we have here is the share price for Google. Breaking this down:

• =GOOGLEFINANCE() is the function used.
• NASDAQ is the stock exchange that Google is listed on. If you're working with another exchange, you may get the list here.
• GOOG is the ticker symbol for Google
• price is what we want to know about Google

Therefore, the price of a single Google share is $97.33 at the time of writing this article.

It's honestly that easy to pull the data from Google and populate a sheet. But, to understand this better, we need to look at the full extent of this function and all of its properties and attributes. For starters, here's how this function may be used:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

And, here's a breakdown of the properties:

• ticker: The ticker must include the ticker symbol of the stock as well as the stock exchange it's listed on. For example, NASDAQ:GOOG
• attribute: What data we want to fetch about the stock such as price, priceopen, high, low, volume, etc.
• start_date: This is an optional field that allows us to enter the start date when fetching historical data. Without an end date, only a single day of data is fetched.
• end_date|num_days: Another optional field that allows us to enter the end date for the fetching of data OR the number of days from the start date for which data will be returned.
• interval: A third optional field, which controls the frequency of the data being returned, this could be a 1 or 7 or "DAILY" / "WEEKLY".

For me, what I feel is most important is the attribute, this opens up all the amazing information we can retrieve from Google. To help you out, below is a list of description of each attribute available:

• "price" - Real-time price quote, delayed by up to 20 minutes.
• "priceopen" - The price as of market open.
• "high" - The current day's high price.
• "low" - The current day's low price.
• "volume" - The current day's trading volume.
• "marketcap" - The market capitalization of the stock.
• "tradetime" - The time of the last trade.
• "datadelay" - How far delayed the real-time data is.
• "volumeavg" - The average daily trading volume.
• "pe" - The price/earnings ratio.
• "eps" - The earnings per share.
• "high52" - The 52-week high price.
• "low52" - The 52-week low price."change" - The price change since the previous trading day's close.
• "beta" - The beta value."changepct" - The percentage change in price since the previous trading day's close.
• "closeyest" - The previous day's closing price.
• "shares" - The number of outstanding shares.
• "currency" - The currency in which the security is priced. Currencies don't have trading windows, so open, low, high, and volume won't return for this argument.

Now, those are the attributes for real-time data but we can also pull historical data with these attributes:

• "open" - The opening price for the specified date(s).
• "close" - The closing price for the specified date(s).
• "high" - The high price for the specified date(s).
• "low" - The low price for the specified date(s).
• "volume" - The volume for the specified date(s).
• "all" - All of the above.

There are a number of other attributes available for mutual funds but that's a conversation for another day. Today we're going to look at real-time data for then most part as it's what's most important for creating a share portfolio tracker in Google Sheets.

Very quickly we can do this:

Now, in real time, we can look at some important facts about Telsa shares. Each time we visit the Sheet, the data is updated in real-time. We can expand this to include several stocks and thus build ourselves somewhat of a Dashboard:

With a little bit of styling, conditional formatting, and capturing our own share data, we can come to something like this quite easily:

Let's break down what we've got here:

• Column A: The ticker symbol of the company we've bought shares of.
• Column B: The exchange that the company is listed on.
• Column C: The number of shares we bought.
• Column D: The average cost* we paid per share.
• Column E: The cost we paid for all the shares (Column C x Column D).
• Column F: Current market price of the share multiplied by the number of shares (Column N x Column C). Column N isn't in the screenshot above.
• Column G: Profit or loss we've made (Column F - Column E)
• Column H: The percentage return (Column G / Column E).

*Average Cost: This is the average price you paid per share and is a value you extract from your brokerage account.

So right now, we have a useful dashboard that tracks how our stocks are doing, that updates in real time. You can stop with this basically but there are a few extra bits and bobs that are really interesting so I want to share those with you too.

Let's take things a step further, how about some graphs that look at periods of time to see how a company's price is doing over intervals such as 200, 50, and 20. I chose these intervals because they're linked to something called moving average in technical trading. Take a look at this:

The SPARKLINE() function is outside of the scope of this article but to assist you, we're essentially starting with the following syntax:

=SPARKLINE(GOOGLEFINANCE("NASDAQ:GOOG", "price", TODAY()-200, TODAY()))

This means we want to look at the price of Google from 200 days ago until today, and represent the data as a chart:

That doesn't look like our example but shows the basics. The SPARKLINE() function has a whole lot of its own attributes so with some experimenting, you can get it to look how I did. Below is the exact syntax I used:

=IFERROR(SPARKLINE(GOOGLEFINANCE($A3,"price",TODAY()-200,TODAY(),"weekly"),{"charttype","line";"linewidth",3;"color", if(googlefinance($A3,"price") <index(googlefinance($A3, "price", today()-200),2,2),"red","green")}),"")

These graphs aren't crucial but they're really nice to have as they tell somewhat of a story and add some additional insight into the sheet. Now, we need to add Column N that we mentioned above, which was the share price... easy, it's what we covered at the start of this article, along these lines: =GOOGLEFINANCE("NASDAQ:GOOG", "price") and let's put the name of the company in:

Now we're talking!

The Sector column doesn't get created through GOOGLEFINANCE(), unfortunately, and is instead done by using an IMPORTXML() function that comes with Google Sheets. The IMPORTXML() function is well outside of the scope for this article so if you're interested, you'll have to turn to Google for an explanation.

So, you want to grab a copy of this tracker for yourself instead of doing the work? Okay sure, click here, make a copy and enjoy!