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 alertssample 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 checkLossesis 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 codebelow:

 // 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