The Scenario
You are an income investor. Your annual yield-on-cost review is due by end of week. You have 30 dividend stocks in an Excel workbook on the Portfolio tab and you want to calculate total dividends received per ticker over the last five years so you can compare your actual yield on cost for each position.
The slow version:
- You find a dividend history site, look up ticker one, find the list of ex-dividend dates and amounts
- You sum the last five years of payments manually, write the total into the workbook
- You do it again for ticker two
- Around ticker eight you realize you have been using the wrong date range on two tickers
- You start over on those two, lose thirty minutes, and still have 22 tickers left.
The fast version is one prompt before end of day Tuesday.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your workbook that pulls dividend history from Alpha Vantage for every ticker and does the summing for you.
Open the SheetXAI sidebar and type:
For each ticker in column A of the Portfolio tab, fetch dividend history from Alpha Vantage for the last 5 years and write ex-dividend date, amount, and payment date into a stacked table on a new tab called 'Dividends'. Then sum total dividends per ticker over the last 5 years and write the totals into column B of the Portfolio tab.
SheetXAI fetches the full dividend history for all 30 tickers, builds the stacked detail table on the Dividends tab, and writes the five-year total for each ticker back to column B on the Portfolio tab.
What You Get
Two outputs from one prompt:
- Dividends tab — a stacked detail table with ex-dividend date, amount per share, and payment date for every dividend payment across all 30 tickers for the last five years
- Column B on the Portfolio tab — five-year total dividends per share for each ticker
The totals in column B are what you divide by your cost basis to get yield on cost. If your cost basis is already in column C, add "calculate yield on cost as column B divided by column C and write it into column D" to the prompt.
What If the Data Is Not Quite Ready
Dividend history analysis has some common complications. SheetXAI handles them in the same prompt.
When some tickers started paying dividends less than 5 years ago
A few positions initiated their dividend within the last five years. The total will be for a partial period.
For each ticker in column A of the Portfolio tab, fetch dividend history from Alpha Vantage for the last 5 years. Sum total dividends per ticker and write into column B. In column E, write the number of years of dividend data available. If fewer than 5 years of data exist, note the actual period covered.
When you want to see the annual dividend trend per ticker
You want to see whether dividends are growing, flat, or being cut, not just the five-year total.
After writing total five-year dividends into column B, also fetch the annual dividend total for each of the last 5 years per ticker and write them into columns F through J (one year per column). In column K, mark whether each ticker's dividend has grown, stayed flat, or declined over the 5-year period.
When you want to flag dividend cutters
Any ticker that cut its dividend during the five-year period is a yellow flag for income investors.
After calculating the annual dividend totals for each year per ticker, flag any ticker where any year's dividend was lower than the prior year's dividend in column L with "DIVIDEND CUT DETECTED." Include the year and the cut amount in the flag text.
When you need the full yield-on-cost review package in one pass
End of week is tomorrow. You need the five-year totals, the annual dividend trend, the yield-on-cost calculation, a dividend cut flag, and a brief summary of the top 5 income producers and any names that have cut or reduced.
For each ticker in column A of the Portfolio tab, fetch 5 years of dividend history from Alpha Vantage. Sum total dividends per ticker into column B. Calculate annual dividend totals for each year into columns F through J. Flag any year-over-year cut in column K. Divide column B by column C (cost basis) to get yield on cost in column D. Sort the Portfolio tab by column D descending. Below the main table, write a summary paragraph identifying the top 5 yield-on-cost performers and listing any tickers that show a dividend cut in their history.
The pattern: the dividend fetch, the annual trend, the yield-on-cost, the cut flag, and the summary all come out of one prompt.
Try It
Get the 7-day free trial of SheetXAI and open any workbook with a dividend stock portfolio, then ask it to pull Alpha Vantage dividend history and calculate your yield on cost. The Alpha Vantage integration is included in every SheetXAI plan. For related workflows, see how to enrich a watchlist with fundamentals in Excel or the Alpha Vantage in Excel overview.