Get stock price drop alerts

Coding level: Beginner
Duration: 5 minutes
Project type: Automation with a time-driven trigger

Objectives

• Understand what the solution does.
• Understand what the Apps Script services do within the solution.
• Set up the script.
• Run the script.

About this solution

If you purchase a stock and the value drops, you can sell that stock, purchase another, and claim a tax deduction. Doing so is known as a tax loss harvest. List your stocks in a Google Sheets spreadsheet and get email alerts if a stock price drops below its purchase price.

How it works

The spreadsheet uses the Google Finance built-in function in Sheets to get the current prices of stocks. The script compares the purchase price of each listed stock with its current price. Then, it emails you a list of stocks that have fallen below their purchase price. You can set the script to run as often as you want.

Apps Script services

This solution uses the following services:

• Spreadsheet service–Loops through each listed stock and compares the stock price to the purchase price.
• Gmail service–Creates and sends an email of the stocks that have fallen below their purchase price.

Prerequisites

To use this sample, you need the following prerequisites:

• A Google Account (Google Workspace accounts might require administrator approval).
• A web browser with access to the internet.

Set up the script

1. Click the following button to make a copy of the Tax loss harvest alerts sample spreadsheet. The Apps Script project for this solution is attached to the spreadsheet.
   Make a copy
2. In your copied spreadsheet, update the sheet with your own stock information, or use the provided test data.

Run the script

1. In your copied spreadsheet, click Extensions > Apps Script.
2. In the function dropdown, select checkLosses.
3. Click Run.

4. When prompted, authorize the script. If the OAuth consent screen displays the warning, This app isn't verified, continue by selecting Advanced > Go to {Project Name} (unsafe).

5. Check your email for a list of stocks that fell below their purchase price. If you didn't receive an email, check to see if any of the stock prices in your list are lower than their purchase price.

Create a time-driven trigger

1. Return to the script project.
2. At the left, click Triggers alarm.
3. At the bottom-right, click Add trigger.
4. For Choose which function to run, make sure checkLosses is selected.
5. For Select event source, select Time-driven.
6. Configure how often you want the script to run and click Save.

Review the code

To review the Apps Script code for this solution, click View source code below:

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/tax-loss-harvest-alerts

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/** 
* Checks for losses in the sheet.
*/
function checkLosses() {
  // Pulls data from the spreadsheet
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    "Calculations"
  );
  let source = sheet.getRange("A:G");
  let data = source.getValues();

  //Prepares the email alert content
  let message = "Stocks: <br><br>";

  let send_message = false;

  console.log("starting loop");

  //Loops through the cells in the spreadsheet to find cells where the stock fell below purchase price
  let n = 0;
  for (let i in data) {
    //Skips the first row
    if (n++ == 0) continue;

    //Loads the current row
    let row = data[i];

    console.log(row[1]);
    console.log(row[6]);

    //Once at the end of the list, exits the loop
    if (row[1] == "") break;

    //If value is below purchase price, adds stock ticker and difference to list of tax loss opportunities
    if (row[6] < 0) {
      message +=
        row[1] +
        ": " +
        (parseFloat(row[6].toString()) * 100).toFixed(2).toString() +
        "%<br>";
      send_message = true;
    }
  }
  if (!send_message) return;

  MailApp.sendEmail({
    to: SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail(),
    subject: "Tax-loss harvest",
    htmlBody: message,

  });
}

Contributors

This sample was created by Jeremy Glassenberg, Product Management and Platform Strategy Consultant. Find Jeremy on Twitter @jglassenberg.

This sample is maintained by Google with the help of Google Developer Experts.

Next steps

• Time-driven triggers
• Extend Google Sheets