|
Google Sheets

Create A Stock Portfolio Tracker With Google Sheets

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.

Updated:
June 9, 2021
|
Length:
12 Minutes
Join 1,000+ readers and get my bi-weekly newsletter. I share work that I've shipped and work that has piqued my curiosity. You'll also get access to all of my resources.
You're in! Check your inbox :)
There's an issue. Try Again :)

Project Resources

Google Sheets

Google Finance

Table Of Contents

1. Google Sheet Setup

2. Querying Google Finance

3. IF Statements

1. 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:

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.

2. 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.

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

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

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

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

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

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

3. 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:

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:

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.

Join 1,000+ readers and get my bi-weekly newsletter. I share work that I've shipped and work that has piqued my curiosity. You'll also get access to all of my resources.
You're in! Check your inbox :)
There's an issue. Try again :)