Turn Excel into Your 24/7 Free Financial Advisor

If you're a working professional investing in Taiwan or U.S. stocks, your daily routine might look like this: At 9 AM when the market opens, you secretly switch browser tabs to check stock apps; during lunch, you open Yahoo Finance to check today's closing price; at night, you open your U.S. stock app to see if there's a major drop at opening. Then you manually input these numbers into your Google Sheets ledger to calculate whether you're making a profit or loss.

This process is not only time-consuming but also highly error-prone. Now that we've mastered GAS (Google Apps Script) and Vibe Coding, we're going to bring Google Sheets to life, transforming it into a 24/7 "financial advisor" that works for you. It will automatically fetch the latest stock prices online, calculate your profits/losses, and even send you an email alert when stocks plummet!

---

Built-in Superpower: The GOOGLEFINANCE Function

Before we use AI to write code, you must first meet Google Sheets' most severely underestimated hidden gem: the GOOGLEFINANCE function. This is a completely free, real-time financial database provided by Google.

In your Google Sheets cell, simply enter this formula: =GOOGLEFINANCE("TPE:2330", "price") Hit Enter, and the cell will instantly display the latest price of "TSMC (2330)"! (Note: TPE stands for Taipei Stock Exchange. For U.S. stocks, just enter the ticker directly, e.g., AAPL for Apple.)

You can use this function to create a basic investment portfolio table:

• Column A: Stock ticker (e.g., TPE:2330)
• Column B: Purchase price (your buy-in price, e.g., 800)
• Column C: Shares held (e.g., 1000)
• Column D: Current price -> Use formula =GOOGLEFINANCE(A2, "price")
• Column E: Current P&L -> Use formula =(D2 - B2) * C2

It's that simple—you now have a self-updating ledger!

---

Vibe Coding in Action: Building an Automated Alert System

With a self-updating stock price tracker, here comes the most exciting part. We don't want to manually check the spreadsheet every day. We want: "When TSMC's loss exceeds $10,000, Google will automatically email me an alert."

Open the menu in Google Sheets: Extensions -> Apps Script. Then, chant this divine business spell to your cursor:

【Stock Auto-Alert System Vibe Prompt】 I'm writing a Google Apps Script (GAS). I have a sheet named "Portfolio." The structure is as follows:

• Column A: Stock ticker
• Column B: Current P&L (could be positive or negative)

Please write a function named checkStockAlerts with this logic:

1. Read data starting from row 2 (row 1 is headers).
2. Check Column B's "Current P&L." If the loss exceeds -10000 (i.e., over $10,000 loss).
3. Use MailApp.sendEmail to send an email to my address (myemail@example.com).
4. Email subject: "🚨 Stock Loss Alert: [Stock Ticker]". The body should show the current loss amount and remind me to consider cutting losses.
5. Ensure the code is clean and includes detailed Chinese comments.

AI-Generated Auto-Alert Script:

function checkStockAlerts() {
  // Get the active spreadsheet and sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Portfolio');
  
  // Fetch all data (returns a 2D array)
  const data = sheet.getDataRange().getValues();
  
  // Your email address
  const myEmail = 'myemail@example.com';
  
  // Loop starting from row 2 (index 1)
  for (let i = 1; i < data.length; i++) {
    const stockCode = data[i][0]; // Column A: Stock ticker
    const profitLoss = data[i][1]; // Column B: Current P&L
    
    // If loss exceeds $10,000
    if (profitLoss < -10000) {
      const subject = `🚨 Stock Loss Alert：${stockCode}`;
      const body = `Warning! Your stock ${stockCode} has incurred a loss of ${profitLoss} dollars!\n\nPlease log in to check if you need to execute a stop-loss.`;
      
      // Send email
      MailApp.sendEmail(myEmail, subject, body);
      Logger.log(`Alert email sent: ${stockCode}`);
    }
  }
}

Paste this code, then click "Run." If your sheet contains stocks with losses exceeding $10,000, your phone will buzz with an email notification in about 3 seconds!

---

Setting Up Automated Triggers

With this code, the final step is to make it "run automatically daily" without manual clicks.

1. In Apps Script's left sidebar, click the clock icon (Triggers).
2. Click "Add Trigger" at the bottom right.
3. Select the function: checkStockAlerts.
4. Select event source: Time-driven.
5. Select trigger type: Day timer.
6. Select time: e.g., 2 PM - 3 PM (right after Taiwan market closes).
7. Click Save.

Mission accomplished! Starting today, you have a 24/7, unpaid financial secretary. Every day after the market closes, it will calculate your P&L and email you if losses are severe. This automation would cost thousands in setup and monthly maintenance fees if outsourced. But you just built it in 10 minutes—for free!

In the next chapter, we'll tackle another powerful workplace application: Automated mass salary slips and personalized emails for hundreds of employees!