The Scenario
A new client handed you their portfolio spreadsheet. Forty positions, cost basis per share in column C, shares in column D. Before you can do anything with the cost basis data, you have to know whether any of these positions have had stock splits in the past five years — because if they have, the cost basis figures are wrong and every downstream calculation is off.
The previous advisor left no notes. The spreadsheet has no split adjustment column. You're starting from zero.
The bad version:
- Search each ticker individually on a financial data site to check whether a split occurred in the relevant timeframe.
- For the splits you find, note the date and ratio somewhere, then manually calculate the adjusted cost basis for each affected position.
- Discover that three tickers had multiple splits over five years, which means your manual ratio math is now a chain of multiplications you're not entirely confident in.
The audit is supposed to give you a clean starting point. What you have is a growing list of manual corrections and a nagging sense you missed something.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads the tickers in column A and, through its built-in Twelve Data integration, fetches all stock split events for each position over the past five years and writes split date and ratio into adjacent columns.
For each ticker in column A, fetch all stock split events from Twelve Data for the past 5 years and write split date into column B and split ratio into column C. If a ticker had no splits, write NONE in column B.
What You Get
- Split date and split ratio written into columns B and C for every position that had a split event.
- NONE in column B for positions with no splits in the five-year window, so you can distinguish "no data returned" from "no splits occurred."
- Multiple split events for the same ticker written as additional rows directly below that ticker's entry.
- The original ticker, shares, and cost basis in columns A, D, and C remain untouched — the split data is additive.
What If the Data Is Not Quite Ready
If you need from-factor and to-factor instead of a single ratio
For each ticker in column A, fetch stock split history from Twelve Data for the past 5 years. Write split date into column B, from factor into column C, and to factor into column D. If no splits occurred, write NONE in column B.
If you want to calculate the adjusted cost basis automatically after pulling the split data
Fetch all stock split events from Twelve Data for each ticker in column A over the past 5 years. Write split date and split ratio into columns B and C. Then for each ticker that had one or more splits, calculate the cumulative split adjustment factor (product of all ratios) and write it into column D. Divide the original cost basis in column E by the cumulative factor and write the adjusted cost basis into column F.
If the sheet has tickers in both column A and column G (two separate portfolio segments)
Fetch stock split history from Twelve Data for each unique ticker across both column A and column G for the past 5 years. Write split date and ratio into the two columns immediately to the right of each ticker's source column. Flag tickers that appear in both columns with DUPLICATE in a new column H.
Kill-chain: pull split history, calculate adjusted cost basis, and flag positions needing review
Fetch all stock split events from Twelve Data for each ticker in column A over the past 5 years. Write split date and ratio into columns B and C. Calculate the cumulative adjustment factor for each ticker (multiply all ratios for multi-split positions) and write it to column D. Divide the cost basis in column E by the adjustment factor and write the adjusted cost basis to column F. In column G, flag any position where the adjustment factor is not 1.0 with REVIEW REQUIRED.
Try It
Get the 7-day free trial of SheetXAI and open your client's cost-basis sheet with tickers in column A, then ask it to audit every position for historical splits in one run. Also see building a dividend and earnings calendar and the full Twelve Data overview.