Build an Adjusted Total-Return Price Series From Marketstack in a Excel workbook

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 an Excel workbook. 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 Excel workbook. 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 workbook.

Pull 5 years of adjusted close prices for all tickers in column A of this Excel workbook from Marketstack and calculate the total return percentage from first to last adjusted close, writing the results into column B

What You Get

• Total return percentage in column B for each ticker — calculated from the first to last adjusted close in the 5-year series
• The adjusted close from Marketstack already incorporates both split adjustments and dividend reinvestment, so the total return is complete
• Any ticker with less than 5 years of history gets a note in column C with the actual date range used

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 worksheet; in the Summary worksheet, 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 worksheet; then in the Summary worksheet, 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 worksheet, and calculate the total return for each holding from acquisition date to today in the Summary worksheet

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 worksheet; in the Summary worksheet, 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 Excel workbook 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.