You'll create an automated monitoring system that tracks semiconductor stock prices in real-time and sends email alerts when your target stocks move beyond specified thresholds. This complete system takes about 45 minutes to set up and runs continuously once configured.
What You Will Learn
- Build a live stock monitoring dashboard using Yahoo Finance API and Google Sheets
- Create automated email alerts for 5% or greater price movements in semiconductor stocks
- Set up conditional formatting that highlights TSMC, NVDA, AMD, and Intel price changes in real-time
What You'll Need
- Google account with access to Google Sheets and Google Apps Script
- Basic understanding of spreadsheet formulas (we'll provide exact formulas to copy)
- 30-45 minutes of setup time
- List of semiconductor stock symbols you want to monitor
Time estimate: 45 minutes | Difficulty: Intermediate
This system leverages Google Sheets' built-in GOOGLEFINANCE function, which is more reliable than many third-party APIs and doesn't require separate API key management. As we explored in our guide to building executive change alerts, Google Sheets provides powerful automation capabilities for financial monitoring.
Step-by-Step Instructions
Step 1: Create Your Stock Monitoring Spreadsheet
Open a new Google Sheet and create your base structure. In cell A1, type "Stock Symbol". In B1, type "Current Price". In C1, type "Previous Close". In D1, type "Change %". In E1, type "Alert Status".
List your target semiconductor stocks in column A starting from row 2. Use these symbols: TSMC, NVDA, AMD, INTC, AVGO, QCOM, MU, TSM. These represent the major players across memory, processors, and specialized chips that drive technology market movements.
Your spreadsheet foundation determines everything that follows — accurate stock symbols are critical because one typo will break your entire monitoring system.
Step 2: Configure GOOGLEFINANCE Functions for Real-Time Prices
In cell B2, enter this exact formula: =GOOGLEFINANCE(A2,"price"). This pulls the current trading price for whatever stock symbol you've entered in A2. Copy this formula down column B for all your stock symbols.
In cell C2, enter: =GOOGLEFINANCE(A2,"closeyest"). This retrieves yesterday's closing price, which serves as your baseline for calculating percentage changes. Copy this formula down column C.
The GOOGLEFINANCE function updates approximately every 20 minutes during market hours, making it suitable for monitoring significant price movements without overwhelming you with tick-by-tick changes.
Step 3: Build Percentage Change Calculations
In cell D2, create the percentage change formula: =(B2-C2)/C2*100. This calculates how much the current price has moved compared to the previous close, expressed as a percentage.
Format column D as percentages by selecting the entire column, right-clicking, choosing "Format cells," and selecting "Percentage" with 2 decimal places. This makes your data immediately readable — you'll see +5.23% instead of 0.0523.
Copy the percentage formula down to all rows containing stock symbols. Your spreadsheet now shows live percentage changes for each semiconductor stock you're monitoring.
Step 4: Create Alert Status Logic
In cell E2, enter this conditional formula: =IF(ABS(D2)>=5,"ALERT","Normal"). This creates a text-based alert system that displays "ALERT" when any stock moves 5% or more in either direction, and "Normal" otherwise.
The ABS function captures both positive and negative movements — a 5% drop is just as significant as a 5% gain in semiconductor markets, where volatility often signals broader tech sector shifts.
Copy this formula down column E for all your monitored stocks. You now have a visual indicator system that immediately highlights unusual price movements.
Step 5: Set Up Conditional Formatting for Visual Alerts
Select the range E2:E9 (or however many stocks you're monitoring). Go to Format → Conditional formatting. Set the condition to "Text is exactly" and enter "ALERT". Choose a bright background color like red or orange.
Create a second conditional formatting rule for the percentage column D. Select D2:D9, set condition to "Greater than" 5, and format with green background. Create another rule for "Less than" -5 with red background.
These visual cues let you scan your entire monitoring dashboard in under 3 seconds and immediately spot which semiconductors are experiencing unusual trading activity.
Step 6: Build Email Notification System with Google Apps Script
Click Extensions → Apps Script to open the Google Apps Script editor. Delete any existing code and paste this automated alert function:
function checkStockAlerts() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A2:E9");
var values = range.getValues();
var alertMessage = "";
for (var i = 0; i < values.length; i++) {
if (values[i][4] == "ALERT") {
alertMessage += values[i][0] + ": " + values[i][3].toFixed(2) + "% change\n";
}
}
if (alertMessage) {
MailApp.sendEmail("your-email@gmail.com", "Semiconductor Stock Alert", alertMessage);
}
}
Replace "your-email@gmail.com" with your actual email address. This script checks your alert column and sends an email containing all stocks currently showing unusual movements.
Google Apps Script requires authorization the first time you run it — click "Review permissions" and allow access to your Gmail and Google Sheets when prompted.
Step 7: Configure Automatic Execution Triggers
In the Apps Script editor, click the trigger icon (clock symbol) in the left sidebar. Click "Add Trigger" and set these parameters: Function: checkStockAlerts, Event source: Time-driven, Type: Minutes timer, Interval: Every 30 minutes.
This automation runs your alert check twice per hour during the day. You can adjust the frequency, but checking more often than every 15 minutes may hit Google's usage limits for the GOOGLEFINANCE function.
Save your trigger settings. Your system now operates independently, monitoring semiconductor stocks and sending email alerts whenever significant price movements occur.
Step 8: Test with TSMC and NVIDIA Price Movements
To verify your system works correctly, temporarily lower your alert threshold to 1% by changing the formula in E2 to: =IF(ABS(D2)>=1,"ALERT","Normal"). Copy this down column E.
Run your Apps Script manually by clicking the "Run" button in the script editor. Check your email within 2-3 minutes — you should receive an alert listing any stocks with 1% or greater movement.
After confirming your system works, change the alert threshold back to 5% for normal operation. During volatile market periods, TSMC and NVIDIA commonly exceed 5% daily movements, making them reliable test cases for your monitoring system.
Troubleshooting
GOOGLEFINANCE returns #N/A errors: Verify your stock symbols are correct and that markets are open. The function only returns live data during trading hours and may show cached prices after hours.
Email alerts not sending: Check that you've authorized Gmail access in Apps Script and confirmed your trigger is active. Gmail has daily sending limits of 100 emails per day for personal accounts.
Percentage calculations showing as decimals: Select column D, right-click, choose Format cells, and set to Percentage format with 2 decimal places. Without proper formatting, 0.05 displays instead of 5.00%.
Expert Tips
- Pro tip: Add a timestamp column using
=NOW()to track exactly when each alert triggered — this helps identify market timing patterns. - Monitor Taiwan Semiconductor (TSM) and TSMC separately if you want both US-listed and Taiwan-listed exposure to the same company.
- Set different alert thresholds for different stocks — use 3% for volatile stocks like AMD and 7% for stable ones like Intel.
- Create a second sheet to log historical alerts by copying alert data automatically — this builds your own semiconductor volatility database.
What to Do Next
Expand your monitoring system by adding sector ETFs like SOXX or SMH to track broader semiconductor market movements. Consider integrating news sentiment analysis using Google Alerts keywords, following the approach detailed in our analysis of automated monitoring systems. Advanced users can connect this data to trading platforms or build predictive models using the historical alert patterns you'll collect over time.
