Daily portfolio snapshot logger in Google Sheets
Log every position's closing price, value, and daily change to a Google Sheet right after the US market close, so you build a clean history for charting.
Google Sheets
YChartsFinanceOperationsData SyncDaily DigestsBuild me a deterministic daily portfolio snapshot logger. This is a pure data pipeline with no AI step, no narrative output, just structured data transformation and logging so a Google Sheet builds a clean daily history I can chart from.
Trigger: cron, every US trading weekday (Monday through Friday) at 4:15pm America/New_York, after the market close.
Step 1. Use google-sheets Get Values to read my portfolio sheet. The range covers a header row plus a body where each row has two columns: ticker symbol and share count. Stock tickers are plain (AAPL, MSFT). Mutual fund tickers are prefixed with M: (for example M:VFIAX). The spreadsheet ID and the source range should be workflow inputs so I can point it at any sheet.
Step 2. Partition the rows into two groups: company tickers (no prefix) and mutual fund tickers (M: prefix). Drop blank rows.
Step 3. For company tickers, call ycharts Get Company Data Points in batches of up to 100 symbols per request, asking for the latest closing price and the 1-day percent change. For mutual fund tickers, call ycharts Get Mutual Fund Data Points the same way, also in batches of up to 100. Use the correct YCharts metric codes for closing price and 1-day percent change. Per YCharts common docs: max 100 symbols per request, mutual funds keep their M: prefix in the call.
Step 4. For every holding, compute position value (shares times price) and dollar change for the day (shares times price times percent_change/100, or equivalently value minus prior value implied by the percent move). Round to two decimals.
Step 5. Use google-sheets Append Values to append one row per position to the Daily Snapshot tab in the same spreadsheet. Row schema: date (YYYY-MM-DD in ET), ticker (with M: prefix preserved for funds), shares, close_price, percent_change_1d, position_value, dollar_change_1d. Use USER_ENTERED so numbers and dates are stored as native types, not strings. Use insertDataOption INSERT_ROWS so the appended rows do not overwrite existing data.
Error handling: if YCharts returns an error in meta.status or a 414 due to too many symbols, log the failing batch and continue with the rest. Do not block the entire run on one bad ticker. If a single ticker comes back without a price (delisted, halted, no close yet), skip it but write a row with an explanatory note in a status column so I can see it in the log.
No AI step. No Slack or email output. The Google Sheet IS the output. Keep the code workflow deterministic and idempotent enough that re-running the same day appends a duplicate batch rather than corrupting prior history.
What does this prompt do?
- Runs every US trading weekday at 4:15pm ET, right after the market close.
- Reads your holdings from a Google Sheet where each row is a ticker and a share count.
- Fetches the day's closing price and one-day percent change for every stock and mutual fund using YCharts.
- Calculates each position's current value and dollar change, then appends one dated row per holding to a running log sheet.
What do I need to use this?
- A Google account with a portfolio sheet (one row per holding, with ticker and share count, mutual funds written with an M: prefix like M:VFIAX).
- A YCharts API subscription. If you do not have one, contact YCharts at sales@ycharts.com.
- A second tab in the same spreadsheet named Daily Snapshot, where new rows will be appended.
How can I customize it?
- Change the schedule (run earlier, run later, or only on specific days of the week).
- Add more columns to log, like market cap, P/E ratio, dividend yield, or 52-week high.
- Split the log by asset type (stocks vs. mutual funds) into separate tabs, or filter to a watchlist subset.
FAQs
Does this work for mutual funds, not just stocks?
What happens on market holidays?
How many holdings can it handle?
Do I need to be technical to use this?
Can I chart the history once it builds up?
Related templates
Stage a batch of filings overnight, then approve each completed form from a screenshot before anything is ever submitted.
Review every conversation Fin closed as resolved, judge which ones actually stuck, and see what the gap is worth against your bill.
Work every return, damage and warranty claim in one queue, with the order, the delivery date and a policy-backed recommendation already on screen.
See exactly which ingredients next week needs based on what you actually sold, adjust anything by hand, then build a one-click grocery cart.
See follower growth, posting cadence, format mix and engagement rate for your brand and every competitor you track, side by side on one board.
Open one screen to see how every service in your Jobber price book actually performed: how often it sold, what you charged, and where prices swing.
Stop hand-logging your portfolio every night.
Connect Google Sheets and YCharts once. Geni captures every position's closing price, value, and daily change for you at the bell.