Coding level: Beginner
Duration: 5 minutes
Project type: Automation with a time-driven trigger
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.
Before you begin
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
Step 1: Set up the spreadsheet
- Click the button below to make a copy of the Tax loss harvest alerts
Make a copy
- Update the sheet with your own stock information, or use the provided test data.
Step 2: Check for losses
- Click Extensions > Apps Script.
- In the function dropdown, select checkLosses.
- Click Run.
- When prompted, click Review permissions and authorize the script.
- 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.
Step 3: Create a time-driven trigger
- Return to the script project.
- At the left, click Triggers .
- At the bottom-right, click Add trigger.
- For Choose which function to run, make sure checkLosses is selected.
- For Select event source, select Time-driven.
- Configure how often you want the script to run and click Save.
Open the Apps Script project
The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.
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.