>
>
Create A Stock Portfolio Tracker With Google Sheets
Updated:
March 30, 2021

Create A Stock Portfolio Tracker With Google Sheets

Tutorial Time:
12 Minutes
Ravinder Deol

Table Of Contents

1. What Is Google Sheets?

2. What Is Google Finance?

3. What You'll Learn In This (No-Code) Tutorial

4. Google Sheet Setup

5. Querying Google Finance

6. IF Statements

7. Next Steps

What Is Google Sheets?

Google Sheets is a spreadsheet program part of the Google Docs Editors suite. It's a powerful yet often overlooked no-code tool for building without code.‍

What Is Google Finance?

Google Finance is a website by Google, which provides market quotes, international exchanges, the latest finance news, and financial analytics.

What You'll Learn In This (No-Code) Tutorial

By the end of this tutorial, you will understand how to work with Google Sheets to create a stock tracking tool combined with Google Finance.

What You'll Learn In This (No-Code) Tutorial

Google Sheet Setup

Create a Google Sheet.

Name both the sheet and the worksheet 'FAANG Stock Tracker'.

The first seven columns should have the following headers:

  • Name

  • Price (USD)

  • 52 Week High

  • 52 Week Low

  • PE Ratio

  • Buy

  • Sell

Google Sheet Setup

You may want to format the sheet as follows; (1) Delete the remaining columns. (2) Centre the worksheet. (3) Fill the first row in light grey. (4) Make the first row bold. (5) Freeze the first row.

Querying Google Finance

Use Google Finance to identify each stock's ticker by its name. Begin with Facebook, then repeat the process for the remaining FAANG stocks.

Querying Google Finance (Step One)

In the first column, enter the following formula for Facebook's stock name:

  • =GOOGLEFINANCE("NASDAQ:FB", "name")

Querying Google Finance (Step Two)

In the second column, enter the following formula for Facebook's share price:

  • =GOOGLEFINANCE("NASDAQ:FB", "price")

Querying Google Finance (Step Three)

In the third column, enter the following formula for Facebook's 52 week high:

  • =GOOGLEFINANCE("NASDAQ:FB", "high52")

Querying Google Finance (Step Four)

In the fourth column, enter the following formula for Facebook's 52 week low:

  • =GOOGLEFINANCE("NASDAQ:FB", "low52")

Querying Google Finance (Step Five)

In the fifth column, enter the following formula for Facebook's PE ratio:

  • =GOOGLEFINANCE("NASDAQ:FB", "pe")

Querying Google Finance (Step Six)

Apply the same formulas for each of the FAANG stocks. Each time, replacing the ticker symbol for the relevant FAANG stock.

IF Statements

Now, you can populate the buy and sell column using IF statements. You'll want it to say 'yes' if the stock price is less than or equal to your desired purchasing price in the buy column. Else, it should display 'no'.

You'll want it to say 'yes' if the price of a stock is more than or equal to your desired selling price in the sell column. Else, it should display 'no'.

The formula for the buy column is as follows:

  • =IF(D2 <= 100, "Yes", "No")

IF Statements (Step One)

The formula states that if the value of cell D2 (the stock price) is less than or equal to your desired purchasing price (100), it will return 'yes'. Else, 'no'.

The formula for the sell column is as follows:

  • =IF(D2 >= 1000, "Yes", "No")

IF Statements (Step Two)

The formula states that if the value of cell D2 (the stock price) is more than or equal to your desired selling price (1000), it will return 'yes'. Else, 'no'.

Now, the Google Sheet will update dynamically. It will query Google Finance for the latest data. And, if any of your IF statements get met, it will show.

Next Steps

Congratulations on completing this tutorial. Now, why not challenge your capabilities? Try implementing one of the suggestions below. Or try your own.

  • Add more stocks to your Google Sheet.

  • Add more data for each stock to your Google Sheet.

  • Change the conditions for your IF statements.
No-Code Newsletter

Join 2,423 People Building Products Without Code

Hi, I'm Ravinder. Every week, I share a new no-code tutorial. Join 2,423 people building products without code. Oh, and you'll get a list of no-code resources.

You're in! Check your inbox for an email.
Something went wrong. Please try again.
Not convinced? View the tutorials.