The Scenario
You are building a back-adjusted total-return price series for a quantitative model. The model requires that every historical close price be adjusted for all prior stock splits. You have 15 tickers in column A of an Excel workbook — a mix of large-cap US equities, some of which have split multiple times over the past 20 years.
Before you can build the adjusted series, you need the split history for each ticker: split date and split factor. That data lives in Marketstack's splits endpoint.
The request for this dataset came from the portfolio manager on Tuesday. It is now Thursday morning.
The bad version:
- Call Marketstack's splits endpoint for the first ticker in a browser, inspect the JSON, note that the split factor is expressed as a string like "4:1" rather than a decimal, and plan to handle the parsing downstream
- Copy the split records for ticker 1 into the workbook, then move to ticker 2 and discover one of the mid-2000s splits has an unusual format that needs special handling
- Work through the remaining 13 tickers and realize at the end that the workbook has inconsistent date formats across tickers because each API response came back slightly differently
The model cannot run until this data is clean and complete. The data transport is the bottleneck, not the model.
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 the complete split history for every ticker and write it into the workbook in a consistent format.
For each ticker in column A, fetch all historical stock splits from Marketstack and write ticker, split date, and split factor into a combined worksheet called Split History, sorted by ticker and then by date ascending
What You Get
- A single worksheet called Split History with all records from every ticker combined
- Three columns: Ticker, Split Date, Split Factor
- Records sorted by ticker alphabetically, then by date ascending within each ticker
- Any ticker with no split history gets a single row noting that no splits were found
What If the Data Is Not Quite Ready
You need the split factor as a decimal multiplier, not a ratio string
For each ticker in column A, fetch all stock splits from Marketstack and write them into a Split History worksheet with columns for ticker, split date, and the split factor converted to a decimal multiplier — for example a 4:1 split should appear as 4.0
You only want splits from the last 10 years
Fetch all stock splits from Marketstack for every ticker in column A that occurred on or after January 1, 2015, and write ticker, split date, and split factor into the Split History worksheet
You want a second column showing the cumulative adjustment factor per ticker
Pull all splits for each ticker in column A from Marketstack, write them into a Split History worksheet, and add a Cumulative Factor column showing the running product of all split factors from oldest to most recent for each ticker
Full pipeline: fetch splits, calculate back-adjusted close for the past year, write to per-ticker worksheets
For each ticker in column A, fetch all historical splits from Marketstack plus the last 252 trading days of EOD close prices; write a back-adjusted close series into a new worksheet per ticker by applying all splits that occurred before each date; add a Summary worksheet showing the unadjusted and adjusted close as of the most recent date
Combining the split fetch, the price pull, and the back-adjustment calculation in one prompt means the adjusted series is ready for the model rather than being three separate tasks queued behind each other.
Try It
Get the 7-day free trial of SheetXAI and open the Excel workbook with your ticker list in column A, then ask it to pull the complete split history from Marketstack for every row. The Marketstack integration is included in every SheetXAI plan.
See also: pulling historical OHLCV data and building an adjusted total-return series.