The Scenario
A client has been invested for five years. They want to know their true performance — not just price appreciation, but total return including all dividends reinvested. You have eight holdings in column A of a Google Sheet. Some of them have split during the holding period. All of them have paid dividends. The performance number you hand to the client needs to reflect all of it.
Marketstack has both adjusted close prices and dividend records. What it does not have is a pre-built total-return series for your specific holding period and this specific list of stocks. Building that requires pulling two datasets per ticker — EOD history and dividend history — and combining them correctly.
The quarterly review is in three days.
The bad version:
- Pull 5 years of EOD data for ticker 1 from Marketstack, then pull the dividend history for ticker 1 separately, because they are different endpoints with different response shapes
- Figure out how to combine them into a total-return series — realizing this requires calculating the dividend reinvestment factor for each ex-dividend date and applying it to the cumulative price series
- Repeat for all eight tickers, then discover that ticker 4 had a 2:1 split in 2022 and the adjusted close from Marketstack is already split-adjusted while your dividend amounts are in pre-split terms for dates before the split
The total-return calculation is the advisory value. The data pipeline is the obstacle.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the ticker list in column A and through its Marketstack integration it can pull both the adjusted price history and the dividend data, combine them into a total-return series, and write the results into the sheet.
For each ticker in column A, fetch 5 years of EOD data including adj_close and dividends from Marketstack and write date and adj_close into a separate tab per ticker, then add a Summary tab showing the 5-year total return percentage for each ticker
What You Get
- A tab per ticker with two columns: Date and Adjusted Close — 5 years of daily data
- A Summary tab with each ticker symbol and its 5-year total return expressed as a percentage, calculated from the first to last adjusted close in the series
- The adjusted close from Marketstack already incorporates both split adjustments and dividend reinvestment, so the total return is complete
What If the Data Is Not Quite Ready
Some tickers have less than 5 years of history because they are newer listings
For each ticker in column A, fetch as much EOD adjusted close history as Marketstack has available — up to 5 years — and write date and adj_close into a per-ticker tab; in the Summary tab, note the actual date range used for each ticker so the client knows if any holding has a shorter history
You want the total return shown both with and without dividends
For each ticker in column A, fetch 5 years of EOD data from Marketstack and write both the raw close and the adj_close into the per-ticker tab; then in the Summary tab, calculate and show two return figures for each ticker: price-only return using the raw close series, and total return using the adj_close series
The holding period is different for each client position
Column A has tickers, column B has the date each position was first acquired — for each ticker, fetch EOD adj_close data from Marketstack starting from the date in column B through today, write the data into a per-ticker tab, and calculate the total return for each holding from acquisition date to today in the Summary tab
Full client report pipeline: fetch adj prices, calculate total return, add annualized return, add max drawdown
For each ticker in column A, fetch 5 years of adjusted close prices from Marketstack and write into a per-ticker tab; in the Summary tab, calculate and show for each ticker: total 5-year return, annualized return (CAGR), and maximum drawdown over the period — present the summary in a table formatted for sharing with a client
Producing the full performance metrics in one prompt means the quarterly review packet has the numbers rather than a placeholder where the numbers will go.
Try It
Get the 7-day free trial of SheetXAI and open the sheet with your client holdings in column A, then ask it to build the 5-year adjusted total-return series from Marketstack for every position. The Marketstack integration is included in every SheetXAI plan.
See also: building a dividend history table and fetching stock split history.