Daily portfolio snapshot to Google Sheets with YCharts pricing

PromptCreate

Build 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?

Yes. Prefix mutual fund tickers with M: in your portfolio sheet (for example, M:VFIAX). The workflow routes those rows through a separate fund lookup so prices come back correctly.

What happens on market holidays?

The schedule fires every weekday, but you can tell the workflow to skip dates with no fresh closing price, or let the duplicate-day rows land and clean them up later. Either way, the log keeps building.

How many holdings can it handle?

YCharts allows up to 100 symbols per lookup, and the workflow batches your tickers automatically. Portfolios well into the hundreds of positions run without issue.

Do I need to be technical to use this?

No. Once you connect your Google account and paste your YCharts key, the daily run is fully automatic. You only touch the spreadsheet to add or remove holdings.

Can I chart the history once it builds up?

Yes, that is the whole point. Each day appends a fresh dated row per position, so you can plot portfolio value over time, build pivot tables by ticker, or chart winners vs. losers directly in Google Sheets.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.