Audit an Excel workbook Cost-Basis Record for Stock Splits Using Twelve Data

The Scenario

A portfolio accountant handed you an Excel cost-basis workbook last week. Forty positions, original purchase price in column C, shares in column D. Your job is to verify the cost basis before the client's year-end tax filing. Step one is checking every position for stock splits over the past five years — because if any occurred, the cost basis per share is wrong and the tax calculations built on top of it are wrong.

The workbook has no split history. The previous accountant left no notes. You're starting from a blank column B.

The bad version:

1. Open a financial data site, search the first ticker, navigate to the historical splits page, check whether any split occurred in the five-year window, and note the date and ratio.
2. Repeat for 39 more positions. A handful of large-cap names had multiple splits over five years, which means you're recording more than one event per ticker and tracking cumulative adjustment factors in your head.
3. Return to the workbook with 25 tickers documented, 15 still blank, and realize you need to start the adjusted cost-basis calculations before you can finish the audit — which means working from an incomplete split history.

Tax deadline doesn't move because the audit wasn't finished on time.

The Easy Way: One Prompt in SheetXAI

SheetXAI is an AI agent inside your Excel workbook. It reads the 40 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 in columns B and C for every position with a split event.
• NONE in column B for positions with no splits, so you can confirm the pull ran for every ticker.
• Multiple split events for the same ticker written as additional rows beneath the ticker's first entry.
• Original cost basis and share count in columns C and D remain untouched.

What If the Data Is Not Quite Ready

If you need from-factor and to-factor separately rather than a combined ratio

Fetch stock split history from Twelve Data for each ticker in column A for the past 5 years. Write split date into column B, from factor into column C, and to factor into column D. Write NONE in column B for tickers with no splits.

If you want to automatically calculate the cumulative adjustment factor and adjusted cost basis

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. For each ticker with one or more splits, calculate the cumulative adjustment factor (product of all ratios for multi-split tickers) and write into column D. Divide the original cost basis in column E by the cumulative factor and write the adjusted cost basis per share into column F.

If the workbook has two separate portfolio tabs that both need auditing

Fetch stock split history from Twelve Data for each unique ticker across the Portfolio A worksheet (column A) and the Portfolio B worksheet (column A) for the past 5 years. Write split date and ratio immediately to the right of each ticker in each tab. Flag any ticker that appears in both tabs with CROSS-PORTFOLIO in an annotation column.

Kill-chain: pull split history, calculate adjusted basis, and flag positions needing client review

Fetch all split events from Twelve Data for each ticker in column A over 5 years. Write split date and ratio to columns B and C. Calculate cumulative adjustment factor per ticker and write to column D. Divide original cost basis in column E by the adjustment factor and write adjusted basis to column F. In column G, flag any position where the adjustment factor differs from 1.0 with REQUIRES REVIEW and any position with no split data from Twelve Data with VERIFY MANUALLY.

Try It

Get the 7-day free trial of SheetXAI and open your client's cost-basis workbook with tickers in column A, then ask it to audit every position for historical splits and calculate adjusted cost basis in one pass. Also see building a dividend and earnings calendar and the full Twelve Data overview.