📊 Google Sheets Stock Portfolio Formula Generator
Generate ready-to-use GOOGLEFINANCE formulas for your Google Sheets stock portfolio. Automatically calculate current value, P&L, and return % for domestic and international stocks.
① Enter your stocks: exchange, ticker, quantity, and average purchase price.
② Click [Generate Formulas] → formulas are generated automatically.
③ Click [Copy to Clipboard] → paste into Google Sheets cell A1 (Ctrl+V).
| Exchange | Ticker | Name | Shares | Avg Cost | Currency |
|---|
| Attribute | Description | Example Formula |
|---|
| Exchange | Prefix | Example Formula |
|---|
📈 Case 1: Basic Portfolio Return Tracker
The essential portfolio template. Column E (Current Price) is fetched automatically by GOOGLEFINANCE. Columns F–I auto-calculate value, cost, P&L, and return %.
| Ticker(A) | Name(B) | Shares(C) | Avg Cost(D) | Price(E) | Market Val(F) | Cost Basis(G) | P&L(H) | Return%(I) |
|---|---|---|---|---|---|---|---|---|
| NASDAQ:AAPL | Apple | 10 | 180 | =GOOGLEFINANCE(A2,"price") | =C2*E2 | =C2*D2 | =F2-G2 | =IFERROR(H2/G2*100,0)&"%" |
| NASDAQ:MSFT | Microsoft | 8 | 380 | =GOOGLEFINANCE(A3,"price") | =C3*E3 | =C3*D3 | =F3-G3 | =IFERROR(H3/G3*100,0)&"%" |
| NYSE:BRK.B | Berkshire B | 20 | 350 | =GOOGLEFINANCE(A4,"price") | =C4*E4 | =C4*D4 | =F4-G4 | =IFERROR(H4/G4*100,0)&"%" |
| Total | =SUM(F2:F4) | =SUM(G2:G4) | =SUM(H2:H4) | =IFERROR(SUM(H2:H4)/SUM(G2:G4)*100,0)&"%" | ||||
💱 Case 2: Multi-Currency Portfolio (USD + KRW)
Track a mixed portfolio with automatic currency conversion. =GOOGLEFINANCE("CURRENCY:USDKRW") fetches the live USD/KRW rate to convert USD positions to KRW.
| Ticker(A) | Shares(B) | Avg Cost$(C) | Price$(D) | USD/KRW(E) | Value₩(F) | Cost₩(G) | P&L₩(H) |
|---|---|---|---|---|---|---|---|
| NASDAQ:AAPL | 10 | 180 | =GOOGLEFINANCE(A2,"price") | =GOOGLEFINANCE("CURRENCY:USDKRW") | =B2*D2*E2 | =B2*C2*E2 | =F2-G2 |
| NYSE:TSLA | 5 | 220 | =GOOGLEFINANCE(A3,"price") | =GOOGLEFINANCE("CURRENCY:USDKRW") | =B3*D3*E3 | =B3*C3*E3 | =F3-G3 |
| NASDAQ:NVDA | 6 | 450 | =GOOGLEFINANCE(A4,"price") | =GOOGLEFINANCE("CURRENCY:USDKRW") | =B4*D4*E4 | =B4*C4*E4 | =F4-G4 |
| Total | =SUM(F2:F4) | =SUM(G2:G4) | =SUM(H2:H4) | ||||
📉 Case 3: 52-Week High/Low Analysis
See where each stock sits relative to its 52-week range. Useful for spotting potential buy-near-low or caution-near-high setups.
| Ticker(A) | Name(B) | Price(C) | 52W High(D) | 52W Low(E) | vs High(F) | vs Low(G) | Position%(H) |
|---|---|---|---|---|---|---|---|
| NASDAQ:AAPL | Apple | =GOOGLEFINANCE(A2,"price") | =GOOGLEFINANCE(A2,"high52") | =GOOGLEFINANCE(A2,"low52") | =TEXT(C2/D2-1,"0.00%") | =TEXT(C2/E2-1,"0.00%") | =TEXT((C2-E2)/(D2-E2),"0%") |
| NASDAQ:NVDA | NVIDIA | =GOOGLEFINANCE(A3,"price") | =GOOGLEFINANCE(A3,"high52") | =GOOGLEFINANCE(A3,"low52") | =TEXT(C3/D3-1,"0.00%") | =TEXT(C3/E3-1,"0.00%") | =TEXT((C3-E3)/(D3-E3),"0%") |
| NYSE:JPM | JPMorgan | =GOOGLEFINANCE(A4,"price") | =GOOGLEFINANCE(A4,"high52") | =GOOGLEFINANCE(A4,"low52") | =TEXT(C4/D4-1,"0.00%") | =TEXT(C4/E4-1,"0.00%") | =TEXT((C4-E4)/(D4-E4),"0%") |
📆 Case 4: Historical Price Data
Use GOOGLEFINANCE's optional 3rd–5th arguments to pull daily or weekly historical prices into an array. Great for charting or backtesting returns.
| Formula | Description |
|---|---|
| =GOOGLEFINANCE("AAPL","price",DATE(2024,1,1),DATE(2024,12,31),"DAILY") | Apple daily close prices for 2024 |
| =GOOGLEFINANCE("AAPL","price",DATE(2024,1,1),30) | Apple prices for 30 days from 2024-01-01 |
| =GOOGLEFINANCE("AAPL","price",DATE(2024,1,1),DATE(2024,12,31),"WEEKLY") | Apple weekly prices for 2024 |
| =GOOGLEFINANCE("CURRENCY:USDKRW","price",DATE(2024,1,1),DATE(2024,12,31),"DAILY") | Daily USD/KRW exchange rate history for 2024 |
| =GOOGLEFINANCE("AAPL","all",DATE(2024,6,1),DATE(2024,6,30),"DAILY") | Apple OHLCV data for June 2024 |
📊 Case 5: Sector Allocation Analysis
Calculate each position's weight in your total portfolio. Pair with a Google Sheets pie chart to visualize sector diversification.
| Ticker(A) | Sector(B) | Shares(C) | Price(D) | Market Val(E) | Weight%(F) |
|---|---|---|---|---|---|
| NASDAQ:AAPL | Technology | 10 | =GOOGLEFINANCE(A2,"price") | =C2*D2 | =TEXT(E2/SUM($E$2:$E$5),"0.0%") |
| NASDAQ:MSFT | Technology | 8 | =GOOGLEFINANCE(A3,"price") | =C3*D3 | =TEXT(E3/SUM($E$2:$E$5),"0.0%") |
| NYSE:JPM | Financials | 15 | =GOOGLEFINANCE(A4,"price") | =C4*D4 | =TEXT(E4/SUM($E$2:$E$5),"0.0%") |
| NYSE:XOM | Energy | 20 | =GOOGLEFINANCE(A5,"price") | =C5*D5 | =TEXT(E5/SUM($E$2:$E$5),"0.0%") |
| Total | =SUM(E2:E5) | 100.0% | |||