對於現代投資者而言，隨時掌握最新資訊是關鍵。手動檢查多個證券帳戶、追蹤股價並更新試算表，可能是一個繁瑣且耗時的過程。Google Sheets能將您的整個投資組合集中到一個能近乎即時更新的動態儀表板中。 告別靜態、過時的數據。透過利用一個強大的內建函式，您可以將簡單的試算表轉變為一個自動化的投資組合追蹤器，一目了然地提供關鍵洞察。本指南將引導您一步步建立它。

For the modern investor, staying updated is key. Manually checking multiple brokerage accounts, tracking stock prices, and updating spreadsheets can be a tedious, time-consuming process. Google Sheets could centralize your entire portfolio into a single, dynamic dashboard that updates itself in near real-time. you can forget static, outdated data. By leveraging a powerful, built-in function, you can transform a simple spreadsheet into an automated portfolio tracker that provides critical insights at a glance. This guide will walk you through how to build it, step by step.

核心引擎：了解 GOOGLEFINANCE 函式

The Core Engine: Understanding the GOOGLEFINANCE Function

這項自動化功能背後的秘密就是 GOOGLEFINANCE 函式。這個工具可以直接從 Google 財經獲取當前和歷史的證券數據到您的試算表中。

The magic behind this automation is the GOOGLEFINANCE function. This tool can fetch current and historical securities data directly from Google Finance into your spreadsheet.

基本語法很簡單：

The basic syntax is simple:

=GOOGLEFINANCE(ticker, [attribute])

• ticker：股票或 ETF 的代碼。最關鍵的是，對於國際股票，您必須包含交易所前綴。例如：TPE:00878 (台灣)、NASDAQ:AAPL (美國)。ticker: The stock or ETF symbol. Crucially, for international stocks, you must include the exchange prefix. For example: TPE:00878 (Taiwan), NASDAQ:AAPL (USA).
• [attribute]：您想要獲取的特定數據，例如 "price" (價格)、"change" (漲跌) 或 "marketcap" (市值)。[attribute]: The specific piece of data you want, such as "price", "change", or "marketcap".

逐步指南：建立您的自動化儀表板

Step-by-Step Guide: Building Your Automated Dashboard

步驟 1：建立您的試算表結構

Step 1: Structure Your Spreadsheet

開啟一份新的 Google 試算表並設定您的欄位。一個穩固的基礎是關鍵。我們將包含靜態數據（您只需輸入一次）和動態數據（Google 會自動更新）。

Open a new Google Sheet and set up your columns. A strong foundation is key. We’ll include both static data (what you enter once) and dynamic data (what Google will update).

步驟 2：使用正確的代碼列出您的持股

Step 2: List Your Holdings with Correct Tickers

在「股票代碼」欄（例如，儲存格 A2）中，輸入您每項持股的完整代碼，包含交易所前綴。這是確保公式能正確運作最重要的一步。

In the “Ticker” column (e.g., cell A2), enter the full ticker symbol for each of your holdings, including the exchange prefix. This is the most important step for ensuring the formulas work correctly.

步驟 3：用公式抓取即時市場數據

Step 3: Pull Live Market Data with Formulas

現在，讓我們為您的試算表注入生命力。我們將使用 A 欄中的代碼來為每一行抓取對應的數據。

Now, let’s bring your sheet to life. We will use the ticker in column A to pull data for each corresponding row.

• 公司名稱 (儲存格 B2)：Company Name (Cell B2):

  =GOOGLEFINANCE(A2, "name")

• 目前價格 (儲存格 E2)：Current Price (Cell E2):

  =GOOGLEFINANCE(A2, "price")

當您在第一個資料列輸入完公式後，只需點擊儲存格右下角的小藍色方塊，然後向下拉動，即可將公式應用到您所有的持股上。

Once you’ve entered the formulas in the first data row, simply click the small blue square in the corner of the cell and drag it down to apply the formulas to all your holdings.

步驟 4：計算您的投資組合表現

Step 4: Calculate Your Portfolio’s Performance

隨著即時數據的流入，您現在可以用簡單的算術來計算您的關鍵績效指標。

With live data flowing in, you can now calculate your key performance metrics using simple arithmetic.

• 目前市值 (儲存格 F2)：

  =C2 * E2

  (持有股數 * 目前價格)Market Value (Cell F2):

  =C2 * E2

  (Shares Owned * Current Price)
• 損益 (儲存格 G2)：

  =(E2 - D2) * C2

  ((目前價格 – 買入價格) * 持有股數)Profit / Loss (Cell G2):

  =(E2 - D2) * C2

  ((Current Price – Purchase Price) * Shares Owned)

步驟 5：視覺化與功能增強

Step 5: Visualize and Enhance

為了讓您的追蹤器更加專業，可以添加一些視覺化元素：

To make your tracker even more professional, add visual elements:

• 條件式格式設定： 自動為您的「損益」欄位上色。綠色代表獲利，紅色代表虧損。(前往 格式 > 條件式格式設定)。Conditional Formatting: Automatically color-code your “Profit / Loss” column. Green for gains, red for losses. (Go to Format > Conditional formatting).
• 投資組合總覽： 使用 SUM() 函式來計算您整個投資組合的總市值和總損益。Portfolio Summary: Use the SUM() function to calculate the total market value and total profit/loss of your entire portfolio.
• 圓餅圖： 建立圖表來視覺化您按股票代碼分配的投資組合，幫助您了解是否過度集中在某一資產上。Pie Charts: Create a chart to visualize your portfolio allocation by ticker, helping you see if you are overly concentrated in one asset.

附錄：GOOGLEFINANCE 即時數據屬性

Appendix: GOOGLEFINANCE Real-Time Data Attributes