September 15, 2021

•

12 Minutes

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.

Project ResourcesGoogle Sheets

•Google Finance

•

Table Of Contents

1. Google Sheet Setup

2. Querying Google Finance

3. IF Statements

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

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

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:

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

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

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

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

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

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

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

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

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

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

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")

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")

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 over a thousand readers and get my short and insightful biweekly email. I share seven notable highlights from my latest book summary in the email.