Introduction
Keeping a close eye on the performance of a personal stock portfolio can feel like a full‑time job, especially when market data changes by the minute. Google Sheets offers a surprisingly powerful, low‑cost platform to aggregate ticker symbols, pull live quotes, and run the calculations you need to understand daily gains or losses. By coupling Sheets with Google Apps Script, you can automatically generate a concise performance snapshot and have it land in your inbox at a time that suits your routine. This article walks you through the entire workflow—from importing price data and computing portfolio metrics, to scripting the email and scheduling the delivery—so you can stay informed without opening a spreadsheet every day.
Setting Up Your Stock Data in Google Sheets
Start with a clean sheet that will serve as the backbone of your portfolio tracker. Create a table with the following columns:
- Symbol – the ticker (e.g., AAPL, GOOGL).
- Shares – the number of units you own.
- Purchase Price – the price paid per share.
- Current Price – will be filled automatically.
- Value – calculated as Shares × Current Price.
- Cost Basis – Shares × Purchase Price.
- Daily Return – the percentage change from the previous close.
Keep the table in the first sheet (named “Portfolio”) and reserve a second sheet (“Report”) for the formatted email content. This separation makes the script easier to read and prevents accidental overwrites.
Fetching Real‑Time Prices with GOOGLEFINANCE
The built‑in GOOGLEFINANCE function pulls live market data directly into cells. In the Current Price column, enter:
=GOOGLEFINANCE(A2,”price”)
where A2 references the ticker symbol. To capture yesterday’s closing price for return calculations, use:
=GOOGLEFINANCE(A2,”closeyest”)
Because Google updates these values roughly every two minutes, your sheet will always reflect near‑real‑time conditions without any external API keys. Remember to wrap the formulas in IFERROR so the sheet stays tidy when a symbol is temporarily unavailable.
Calculating Daily Returns and Portfolio Metrics
With price data in place, add formulas that turn raw numbers into actionable insights. Example calculations:
- Value: =B2*C2 (Shares × Current Price).
- Cost Basis: =B2*D2 (Shares × Purchase Price).
- Daily Return: =IFERROR((C2-E2)/E2,0) where E2 is yesterday’s close.
At the bottom of the table, use SUM to aggregate total portfolio value, total cost basis, and overall daily return. You can also compute weighted sector exposure or dividend yield by adding extra columns and using ARRAYFORMULA for bulk processing. The result is a living dashboard that updates automatically whenever the sheet refreshes.
Automating Email Delivery with Apps Script
Google Apps Script is a JavaScript‑based environment that can read your sheet, format a message, and send it via Gmail. Create a new script (Extensions → Apps Script) and paste the following skeleton:
function sendPortfolioReport() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var reportSheet = ss.getSheetByName(‘Report’);
var html = reportSheet.getRange(‘A1:D20’).getDisplayValues()
.map(row => row.join(‘ | ‘)).join(‘
‘);
MailApp.sendEmail({
to: ‘[email protected]’,
subject: ‘Daily Portfolio Summary’,
htmlBody: ‘Today\’s performance:
‘ + html
});
}
This script pulls the pre‑formatted “Report” range, converts it into a simple HTML table, and emails it. You can enrich the body with conditional formatting (e.g., red text for negative returns) by inserting <span style=”color:red”> tags around values that fall below zero.
Scheduling and Fine‑Tuning the Report
To have the email arrive at a convenient hour, set up a time‑driven trigger:
- Open the script editor, click the clock icon, and choose “Add Trigger.”
- Select sendPortfolioReport as the function.
- Choose “Time‑based” → “Day timer” → the hour you prefer (e.g., 7 AM).
After the trigger is saved, Google will execute the script automatically each day. If you need a weekend‑only schedule, add a simple check at the start of the function:
if ([‘Sat’,’Sun’].includes(new Date().toLocaleString(‘en-US’, {weekday:’short’}))) return;
Finally, test the workflow by running the function manually. Verify that the email displays the correct numbers, that formatting is intact, and that any edge cases (such as a missing ticker) are handled gracefully. Once satisfied, you have a hands‑free, daily portfolio briefing delivered straight to your inbox.
Conclusion
By leveraging Google Sheets, the GOOGLEFINANCE function, and a modest Apps Script, you can transform a simple spreadsheet into a fully automated stock‑monitoring system. The process begins with a well‑structured data table, progresses through real‑time price retrieval and metric calculation, and culminates in a scheduled email that summarizes daily performance. This approach eliminates the need for third‑party dashboards, reduces manual effort, and ensures you receive the most relevant information exactly when you need it. Implement the steps outlined above, customize the metrics to match your investment strategy, and enjoy the confidence that comes from staying continuously informed about your portfolio’s health.
