Holy sheet: How to track your stock portfolio with Google Sheets

Welcome to TNW Basics, a collection of tips, guides, and advice on how to easily get the most out of your gadgets, apps, and other stuff.

From your weight to your money, there’s a tracking app for everything nowadays. Sure, they can make our lives more convenient, and they give us a sense of control, but just the fact that they exist doesn’t mean we should actually use them. In fact, some are simply abusing our laziness to gather personal information for commercial purposes.

Recently, millennials have been bombarded with a slew of new personal finance apps. It seems as if budgeting, saving, and investing is all the rage nowadays, and that everyone’s aiming for FIRE (Financial Independence Retire Early). But again, you don’t really need a fancy app to do all of that.

Tracking your investments with Google Sheets

Google Sheets has a nifty little function called GOOGLEFINANCE, which allows you to monitor a stock’s status and historic movements in real-time. All you need for that is a stock’s so called ticker symbol, a combination of letters and numbers that represent a particular stock. You can find the ticker by googling the company name supplemented by “stock price”, so for example “Apple stock price”. Under the company name, you’ll find the ticker symbol. Make sure to remove any empty spaces.

[Read: Holy sheet: Here’s how to grab a web page’s data with Google Sheets]

Let’s go over some of the basic formulas you need to create a simple stock portfolio tracker in Sheets.

To gather the current stock price of a publicly listed company, simply enter the following:

=GOOGLEFINANCE(“AAPL”)

AAPL is Apple’s ticker symbol. Change that part to the ticker of the company you’re tracking.

To gather today’s price change percentage of a stock, enter the following:

=GOOGLEFINANCE(“AAPL“, “changepct”)/100

Finally, you could display a little line chart, in a cell, visualizing a stock’s historic price movement. You’ll need an additional Google Sheets formula for that called sparkline.

=SPARKLINE(GOOGLEFINANCE(“AAPL”,”price”,TODAY()-365,TODAY(),”daily”),{“charttype”,”line”;”linewidth”,1;”color”,”#5f88cc”})

Some of the parameters you could change to influence what’s displayed here concern the ticker symbol, the start date of the data that needs to be displayed (in this case 365 days ago), the interval (in this case daily), the chart format (in this case a line chart), the thickness of the line, and the color of the line. For all options feel free to check Google’s sparkline support page.

To create a nice little dashboard, all you need on top of this is some basic Google Sheets functions such as cell references, multiplication and subtraction. Furthermore, you could add coloring to certain columns. For instance, you could use conditional formatting to make sure a positive percentage change of the stock price gets a green cell background, and a negative one a red background.

To get you started, I’ve created a simple stock portfolio tracker in Google Sheets with the functions mentioned above. See a preview of it below. Feel free to follow the link, make a copy of the sheet (File > Make a copy), and start playing with it yourself.