Monitor stock changes in Google Sheets

Monitoring stock changes in Google Sheets can be done efficiently using built-in functions, Google Finance integration, and some simple formulas or scripts. Here’s a detailed guide on how to track stock prices and monitor their changes in Google Sheets:

1. Importing Stock Data with GOOGLEFINANCE

Google Sheets has a built-in function called GOOGLEFINANCE that allows you to fetch real-time or delayed stock data.

Example:

plaintext
CopyEdit
=GOOGLEFINANCE("GOOG", "price")

This fetches the current price of Google’s stock.

You can also get historical data or other attributes:

plaintext
CopyEdit
=GOOGLEFINANCE("GOOG", "price", DATE(2025,1,1), DATE(2025,1,10), "DAILY")

2. Setting Up a Stock Tracker Sheet

Create columns:

• A: Stock Ticker (e.g., GOOG, AAPL)

• B: Current Price (using GOOGLEFINANCE)

• C: Previous Price (manually entered or from a previous date)

• D: Price Change

• E: % Change

3. Formula to Track Price Changes

Suppose you have the stock ticker in A2:

• Current Price (B2):

plaintext
CopyEdit
=GOOGLEFINANCE(A2, "price")

• Previous Price (C2): You can enter manually or pull from historical data.

• Price Change (D2):

plaintext
CopyEdit
=B2 - C2

• Percentage Change (E2):

plaintext
CopyEdit
=IF(C2=0, 0, (B2 - C2)/C2)

Format column E as percentage.

4. Automating Previous Price Update

To track changes over time, you can use Google Apps Script to automatically save the daily closing prices in a separate sheet or archive.

5. Using Google Apps Script for Daily Logging

You can write a script to log stock prices every day at a certain time.

Example script snippet:

javascript
CopyEdit
function logStockPrices() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("StockLog");
  var tickers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stocks").getRange("A2:A").getValues();
  var date = new Date();

  for (var i = 0; i < tickers.length; i++) {
    var ticker = tickers[i][0];
    if (ticker) {
      var price = =FinanceApp.getPrice(ticker);  // Note: There's no FinanceApp by default; you would use UrlFetchApp or GOOGLEFINANCE formulas
      // Instead, get price from sheet cell or an API.
      // For example, get price from sheet:
      var price = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stocks").getRange("B"+(i+2)).getValue();

      sheet.appendRow([date, ticker, price]);
    }
  }
}

You can schedule this function to run daily via the script editor’s triggers.

6. Conditional Formatting for Visual Monitoring

To highlight price drops or gains:

• Select the “Price Change” or “% Change” column.

• Go to Format > Conditional Formatting.

• Set rules such as:

  • If > 0, color green.

  • If < 0, color red.

7. Summary

• Use GOOGLEFINANCE to fetch current stock prices.

• Use formulas to calculate changes.

• Optionally automate daily logging with Apps Script.

• Use conditional formatting for easy visualization.

If you want me to generate a step-by-step template or sample sheet formulas with example data, just ask!