But there's an obvious problem. Stocks that fall 20% or more often rebound even when no insider buys anything. If we only measure what happened after CEO purchases, we may end up crediting the insider signal for a recovery that was already common among beaten-down stocks.

In this tutorial, we'll build a Python workflow to test that properly. We'll pull Form 4 transactions, isolate CEO purchases, collapse repeated filing rows into usable events, attach historical prices, calculate drawdowns and forward returns, and then compare the purchase episodes with similar no-purchase dates from the same stocks.

The interesting part isn't just the final return table. It's everything required to turn messy regulatory filings into a dataset that can support a fair comparison. Along the way, we'll deal with duplicate transaction rows, repeated purchases by the same CEO, trading-day alignment, incomplete price histories, and one-to-one control matching.

By the end, we'll have a full event-study workflow and a more useful answer than “the stock went up after the CEO bought.”

Table of Contents

• Prerequisites

• Import The Required Packages

• Build The Stock Universe

• Fetch CEO Purchases And Apply The Date Filter

• Turn Form 4 Rows Into Daily Purchase Events

• Add Historical Prices And Drawdowns

  • Calculate The Trailing High And Drawdown

  • Match Each Purchase With The Latest Available Price

• Convert Purchase Events Into Episodes

• Calculate Returns After CEO Purchases

  • Organize The Price History By Ticker

  • Find The Entry Date And Calculate Forward Returns

  • Summarize The Raw Returns

• Build The No-Purchase Control Group

  • Create The Control Candidates

  • Remove Dates Near CEO Purchases

  • Match Purchase Episodes With Controls

  • Build The Final Matched Dataset

• Compare CEO Purchases Against Similar No-Purchase Drawdowns

  • Calculate Forward Returns From Any Signal Date

  • Apply The Same Return Logic To Both Groups

  • Build The Final Comparison

• What The Case Study Found

• What This Test Can And Can't Say

Prerequisites

You don't need an advanced finance or quantitative background to follow this tutorial. A basic understanding of Python and pandas should be enough.

Before starting, make sure you have:

• Python installed locally, or access to a notebook environment such as Jupyter Notebook or Google Colab

• Basic familiarity with dataframes, functions, loops, and API requests

• An EODHD API key with access to the screener, Form 4 filings, and historical EOD endpoints

• Enough API credits to process the number of stocks you choose to analyze

The full case study uses Form 4 data from 500 securities. You can run the workflow on a smaller sample first if you want to understand the code without using as many API calls.

No prior knowledge of event studies or control matching is required. We'll build those parts step by step as they appear in the analysis.

Import The Required Packages

We only need a small set of packages for the full workflow. requests handles the API calls, pandas and NumPy do most of the data work, and SciPy gives us the one-to-one matching algorithm used later for the control group.

import json
import re
import numpy as np
import pandas as pd
import requests
from scipy.optimize import linear_sum_assignment

That's the full setup. We're only importing what the analysis actually needs, without adding extra libraries or unnecessary tooling. Make sure to install these packages using pip before importing them to your environment.

Build The Stock Universe

Before pulling insider filings, we need a list of companies to search.

Rather than starting with one market-cap segment, we'll build a mixed universe across micro-, small-, mid-, and large-cap stocks. This gives the analysis some variation instead of letting one part of the market dominate the sample.

The market-cap buckets are:

• micro_cap: $50 million to $300 million

• small_cap: $300 million to $2 billion

• mid_cap: $2 billion to $10 billion

• large_cap: $10 billion and above

For each bucket, we'll fetch 500 screener results, randomly select 250, and combine them into a 1,000-stock universe.

def fetch_stocks(filters, cap):
    api_key = 'YOUR EODHD API KEY'
    base_url = 'https://eodhd.com/api/screener'
    all_stocks = []
    for i in range(0,500,100):
        params = {
            "api_token": api_key,
            "filters": json.dumps(filters),
            "sort": "market_capitalization.desc",
            "limit": 100,
            "offset": i}
        resp = requests.get(base_url, params = params).json()
        stocks = list(pd.DataFrame(resp['data'])['code'])
        all_stocks.append(stocks)
    all_stocks = [item for sublist in all_stocks for item in sublist]
    df = pd.DataFrame(columns = ['ticker', f'cap'])
    df.ticker, df.cap = all_stocks, cap
    df = df.sample(n = 250, random_state = 42)
    return df

micro_filters = [
    ["exchange", "=", "us"],
    ["market_capitalization", ">=", 50_000_000],
    ["market_capitalization", "<", 300_000_000]
]

small_filters = [
    ["exchange", "=", "NYSE"],
    ["market_capitalization", ">=", 300_000_000],
    ["market_capitalization", "<", 2_000_000_000]
]

mid_filters = [
    ["exchange", "=", "NYSE"],
    ["market_capitalization", ">=", 2_000_000_000],
    ["market_capitalization", "<", 10_000_000_000]
]

large_filters = [
    ["exchange", "=", "NYSE"],
    ["market_capitalization", ">=", 10_000_000_000]
]

micro_stocks = fetch_stocks(micro_filters, 'micro_cap')
small_stocks = fetch_stocks(small_filters, 'small_cap')
mid_stocks = fetch_stocks(mid_filters, 'mid_cap')
large_stocks = fetch_stocks(large_filters, 'large_cap')

frames = [micro_stocks, small_stocks, mid_stocks, large_stocks]
stocks_1000 = pd.concat(frames, ignore_index = True)
stocks_1000 = stocks_1000.sample(frac = 1, random_state = 42).reset_index(drop = True)
stocks_1000

Note: Replace YOUR EODHD API KEY with your actual EODHD API key. If you don’t have one, you can obtain it by opening an EODHD developer account.

The screener returns at most 100 rows per request, so the loop moves through the first 500 results in five batches.

We then sample 250 tickers from those candidates. The fixed random seed makes the selection repeatable, so rerunning the cell produces the same sample. After that, we define the four market-cap filters and run the function for each one.

The final dataframe contains 1,000 tickers, with 250 from each bucket.

One caveat is worth stating now. The micro-cap filter uses the broader us exchange setting, while the other groups use NYSE. This is the screener sample used for the case study, but it shouldn't be treated as a perfectly representative sample of the entire US stock market.

Fetch CEO Purchases And Apply The Date Filter

With the stock universe ready, we can start searching the Form 4 filings for CEO purchases using EODHD’s Insider Transactions (SEC Form 4) API.

Form 4 data contains much more than straightforward insider buying. A filing can include sales, awards, option exercises, derivative transactions, and several rows belonging to the same trade. So we can't simply download every filing and treat every record as a buying signal.

For this analysis, a transaction must satisfy all of these conditions:

• appear under non-derivative transactions

• be reported by an officer

• have an officer title that identifies the person as a CEO

• use transaction code P

• represent acquired shares

• contain positive values for both shares and price

• refer to common stock

We also retain both the transaction date and filing date. The transaction date tells us when the CEO bought the shares, while the filing date tells us when outside investors could observe the purchase. Later in the analysis, the filing date will become the signal date.

The following block handles the complete extraction. It defines the filtering function, runs it across the first 500 stocks, and combines all qualifying rows into one dataframe.

def fetch_ceo_purchases(ticker):
    try:
        api_key = 'YOUR EODHD API KEY'

        all_form4 = []

        for i in range(0,1000,100):
            form4_url = f'https://eodhd.com/api/sec-filings/{ticker}/form4?api_token={api_key}&page[limit]=100&page[offset]={i}'
            resp = requests.get(form4_url).json()['data']
            all_form4.append(resp)

        all_form4 = [item for sublist in all_form4 for item in sublist]

        all_purchases = []

        ceo_pattern = re.compile(
            r'\bceo\b|chief executive officer|co-chief executive officer|co-ceo|chief exec officer',
            re.IGNORECASE
        )

        for filing in all_form4:
            footnote_map = {
                footnote['footnote_id']: footnote['text']
                for footnote in filing.get('footnotes', [])
            }

            for transaction in filing.get('non_derivative', []):
                officer_title = transaction.get('officer_title') or ''
                security_title = transaction.get('security_title') or ''
                shares_amount = transaction.get('shares_amount')
                price_per_share = transaction.get('price_per_share')

                is_ceo = bool(ceo_pattern.search(officer_title))

                is_purchase = (
                    transaction.get('is_officer') is True
                    and is_ceo
                    and transaction.get('transaction_code') == 'P'
                    and transaction.get('acquired_or_disposed') == 'A'
                    and shares_amount is not None
                    and shares_amount > 0
                    and price_per_share is not None
                    and price_per_share > 0
                    and 'common stock' in security_title.lower()
                )

                if not is_purchase:
                    continue

                linked_footnotes = ' '.join(
                    footnote_map.get(footnote_id, '')
                    for footnote_id in transaction.get('footnote_ids', [])
                )

                all_purchases.append({
                    'ticker': ticker,
                    'accession_number': filing['accession_number'],
                    'filed_at': filing['filed_at'],
                    'transaction_date': transaction['transaction_date'],
                    'reporting_owner_cik': transaction['reporting_owner_cik'],
                    'reporting_owner_name': transaction['reporting_owner_name'],
                    'officer_title': officer_title,
                    'security_title': security_title,
                    'shares_amount': shares_amount,
                    'price_per_share': price_per_share,
                    'total_value': transaction.get('total_value'),
                    'shares_owned_after': transaction.get('shares_owned_after'),
                    'footnotes': linked_footnotes
                })

        return all_purchases
    except:
        return None

all_ceo_purchases = []

for ticker in stocks_1000.ticker[:500]:
    ticker = ticker + '.US'
    ceo_purchases = fetch_ceo_purchases(ticker)
    if ceo_purchases:
        all_ceo_purchases.extend(ceo_purchases)
        print(f'{len(ceo_purchases)} ceo purchases found in {ticker}')
    else:
        print(f'no transaction found in {ticker}')
        
cp_df = pd.DataFrame(all_ceo_purchases)
cp_df.to_csv('ceo_purchases.csv')

The function requests the filings in batches of 100 and flattens the returned pages into one list. It then checks every non-derivative transaction against the CEO-purchase rules.

The CEO-title check uses a regular expression because filings don't use one perfectly consistent title. A CEO might appear as CEO, Chief Executive Officer, or Co-CEO, so matching only one exact string would miss valid records.

We also preserve the linked footnotes. Transaction code P is useful, but it doesn't tell the complete story by itself. A footnote may reveal that the purchase involved a trading plan, an offering, or another arrangement that deserves closer inspection.

I ran this step on the first 500 securities in the universe because the Form 4 endpoint can consume API credits quickly. The same loop can be extended to all 1,000 stocks for a larger sample.

Once the rows are collected, we restrict the dataset to filings submitted between the beginning of 2022 and the end of 2025.

cp_df = cp_df[cp_df["filed_at"].between("2022-01-01","2025-12-31")]

cp_df.tail()

These are still raw filing rows rather than independent CEO-buying signals. One purchase can be split across several rows when different blocks of shares were acquired at different prices. The next step is to collapse those fragments into daily purchase events.

Turn Form 4 Rows Into Daily Purchase Events

A CEO might buy shares at several prices on the same day. The filing can record each price block as a separate row, even though those rows belong to one broader purchase. If we treated every row as an independent signal, one busy purchase day could receive far more weight than another simply because it was split into more price levels.

So the next step is to group rows that share the same ticker, CEO, filing, and transaction date.

For each group, we'll add up the shares and total purchase value. We'll then calculate a weighted-average price:

weighted average price = total purchase value / total shares purchased

The following block performs the full aggregation and produces one row per daily CEO-purchase event.

cp_df['purchase_value'] = (
    cp_df['shares_amount'] * cp_df['price_per_share']
)

group_columns = [
    'ticker',
    'reporting_owner_cik',
    'reporting_owner_name',
    'officer_title',
    'accession_number',
    'filed_at',
    'transaction_date'
]

daily_events = (
    cp_df.groupby(
        group_columns,
        as_index=False,
        dropna=False
    )
    .agg(
        shares_purchased=('shares_amount', 'sum'),
        total_purchase_value=('purchase_value', 'sum'),
        transaction_rows=('shares_amount', 'size'),
        shares_owned_after=('shares_owned_after', 'max')
    )
)

daily_events['weighted_average_price'] = (
    daily_events['total_purchase_value']
    / daily_events['shares_purchased']
)

daily_events.filed_at = pd.to_datetime(daily_events.filed_at)
daily_events.to_csv('ceo_purchases_grouped.csv')
daily_events.tail()

The purchase_value column gives every raw row a dollar value before aggregation. Once the rows are grouped, those values can be summed without losing the effect of the different purchase prices.

The transaction_rows column is useful for checking how much collapsing happened. A value of 1 means the daily event already appeared as one row. A value of 5 means five separate filing rows were combined into one purchase event.

The aggregation reduced the dataset from 625 raw transaction rows to 535 daily purchase events.

That difference isn't just housekeeping. It changes the unit of analysis from “one reported price block” to “one CEO purchase day,” which is much closer to the economic event we're trying to study.

We're still not ready to calculate returns, though. A purchase event tells us that the CEO bought, but not whether the stock was near its high, down slightly, or already deep in a drawdown. Next, we'll attach the price context that was available when each filing became public.

Add Historical Prices And Drawdowns

A CEO purchase only becomes interesting in this test when we know where the stock was trading at the time.

A purchase made 5% below the yearly high is very different from one made after the stock has fallen 40%. So we now need to bring historical price data into the workflow and measure how far each stock was below its recent high when the filing became public.

We'll use adjusted close rather than the raw closing price because adjusted prices account for events such as stock splits and dividends. That gives us a more consistent series for comparing prices across time.

We pull prices from 2021 through 2025, even though the purchase analysis begins in 2022. The extra year is needed because the first 2022 observations still require enough earlier data to calculate a trailing one-year high.

The following block fetches the daily historical prices EODHD’s historical EOD endpoint for every ticker represented in the CEO-purchase dataset and combines them into one dataframe.

tickers = list(cp_df.ticker.unique())
historical_eod_entries = []

def fetch_historical_eod(ticker):
    api_key = 'YOUR EODHD API KEY'
    historical_url = f'https://eodhd.com/api/eod/{ticker}?from=2021-01-01&to=2025-12-31&period=d&api_token={api_key}&fmt=json'
    historical_resp = requests.get(historical_url).json()
    historical_filtered = []
    for item in historical_resp:
        item['ticker'] = ticker
        keys = ['ticker', 'date', 'adjusted_close']
        item = {key: item.get(key) for key in keys}
        historical_filtered.append(item)
    return historical_filtered

for ticker in tickers:
    try:
        historical_eod = fetch_historical_eod(ticker)
        historical_eod_entries.extend(historical_eod)
        print(f'{ticker} done')
    except:
        print(f'{ticker} error')

This code fetches the historical data and gives us one price row per ticker per trading day. Next, we calculate the rolling high and drawdown.

Calculate The Trailing High And Drawdown

For every trading day, we want to know the highest adjusted close reached during the previous 252 trading sessions. That's roughly one trading year.

The drawdown is then calculated as:

drawdown = adjusted close / trailing 252-day high - 1

A value of -0.20 means the stock is 20% below its trailing high. A value of -0.35 means it's 35% below that high.

The next block sorts each stock’s price history, calculates the rolling high, and converts the result into both decimal and percentage drawdown columns.

historical_df = pd.DataFrame(historical_eod_entries)
historical_df['date'] = pd.to_datetime(historical_df.date)

historical_df['rolling_high_252'] = (historical_df.groupby('ticker')['adjusted_close'].transform
                                     (lambda prices: prices.rolling(window=252, min_periods=200).max()))

historical_df['drawdown'] = (historical_df['adjusted_close']/ historical_df['rolling_high_252']- 1)
historical_df['drawdown_pct'] = (historical_df['drawdown'] * 100)

The min_periods=200 argument deserves a quick explanation.

A full rolling window contains 252 trading days, but requiring exactly 252 observations would remove many early rows. Allowing the calculation after 200 sessions gives us some flexibility while still requiring a substantial amount of historical data.

Rows without enough history remain missing rather than receiving a weak drawdown estimate.

Match Each Purchase With The Latest Available Price

Now we need to attach the price context to each CEO-purchase event.

The filing date is the signal date, but we shouldn't use the closing price from that same date. A Form 4 can be submitted before, during, or after the trading session, so the same-day close may not have been known when the filing appeared.

Instead, we use the latest completed trading day strictly before the filing date.

The next block performs that match with merge_asof(). Unlike a normal merge, it can match each filing with the nearest earlier price date rather than requiring both dates to be identical.

cp_df.filed_at = pd.to_datetime(cp_df.filed_at)

price_columns = ['ticker', 'date', 'adjusted_close', 'rolling_high_252', 'drawdown', 'drawdown_pct']

analysis_df = pd.merge_asof(
    cp_df.sort_values(['filed_at', 'ticker']),
    historical_df[price_columns].sort_values(['date', 'ticker']),
    by='ticker',
    left_on='filed_at',
    right_on='date',
    direction='backward',
    allow_exact_matches=False
)

analysis_df = analysis_df.rename(columns={'date': 'price_date'})

The key setting is allow_exact_matches=False. It prevents a filing dated March 10 from using the March 10 closing price. The merge will instead use the latest available trading day before March 10.

The merged dataframe now looks like this:

The merged dataframe now contains the purchase information alongside:

• price_date: the trading day used for the match

• adjusted_close: the stock price on that day

• rolling_high_252: the trailing one-year high

• drawdown: the decline expressed as a decimal

• drawdown_pct: the same decline expressed as a percentage

For example, a drawdown_pct value of -32.4 means the stock was 32.4% below its trailing 252-day high on the last completed trading day before the filing.

We now know both that the CEO bought and how beaten down the stock was when the purchase became public.

The next problem is repeated buying. A CEO who buys several times over a few weeks shouldn't automatically create several independent signals.

Convert Purchase Events Into Episodes

At this point, the dataset has one row per CEO purchase day. That's better than working with raw Form 4 rows, but it can still overcount the same underlying decision.

Imagine a CEO buys shares on Monday, again the following week, and once more two weeks later. Technically, those are three purchase events. Economically, they may be one sustained buying campaign.

Treating all three as independent signals would give frequent buyers more weight than CEOs who completed the same idea in one trade. So before calculating returns, we'll group nearby purchases into buying episodes.

The rule is simple: purchases by the same CEO in the same stock belong to one episode when consecutive filing dates are no more than 28 calendar days apart.

We'll first remove purchase events without a usable drawdown, sort the remaining rows by ticker, CEO, and filing date, and calculate the number of days since the previous purchase.

purchase_events = analysis_df.dropna(subset=["drawdown"])
purchase_events.filed_at = pd.to_datetime(purchase_events.filed_at)
purchase_events = purchase_events.sort_values(["ticker", "reporting_owner_cik", "filed_at"])
purchase_events["days_since_previous"] = purchase_events.groupby(["ticker", "reporting_owner_cik"])["filed_at"].diff().dt.days
purchase_events["new_episode"] = purchase_events["days_since_previous"].isna() | (purchase_events["days_since_previous"] > 28)
purchase_events["episode_id"] = purchase_events.groupby(["ticker", "reporting_owner_cik"])["new_episode"].cumsum()

The first purchase for every ticker-CEO combination automatically starts a new episode because there's no earlier filing to compare it with.

After that, a new episode begins only when the gap from the previous filing exceeds 28 days. Purchases separated by 28 days or less stay inside the same episode.

The cumulative sum of new_episode gives every buying sequence its own identifier.

Now that each purchase event belongs to an episode, we can collapse the events into one row per buying sequence.

For each episode, we keep the first and last filing dates, add up the shares and purchase value, count the purchase activity, and preserve the drawdown from the first filing date.

episodes = purchase_events.groupby(["ticker", "reporting_owner_cik", "reporting_owner_name", "episode_id"], 
                               as_index=False).agg(first_filing_date=("filed_at", "min"), 
                                                   last_filing_date=("filed_at", "max"),
                                                   first_transaction_date=("transaction_date", "min"),
                                                   total_shares=("shares_purchased", "sum"),
                                                   total_purchase_value=("total_purchase_value", "sum"),
                                                   purchase_days=("filed_at", "nunique"),
                                                   transaction_events=("filed_at", "size"),
                                                   initial_drawdown=("drawdown", "first"), 
                                                   initial_drawdown_pct=("drawdown_pct", "first"))

There are two activity counts here:

• purchase_days counts the number of distinct filing dates in the episode.

• transaction_events counts the daily purchase events that were grouped together.

The total shares and purchase value cover the entire episode. But the drawdown comes only from the first filing date because that's when the signal begins.

That detail matters for the 20% filter.

Suppose an episode starts when the stock is 18% below its high, then the CEO buys again after the drawdown reaches 24%. It would be misleading to call that an episode that began after a 20% decline.

So we group first, then apply the threshold using initial_drawdown.

episodes_20 = episodes[episodes["initial_drawdown"] <= -0.20].copy()

print("All purchase episodes:", len(episodes))
print("20% drawdown episodes:", len(episodes_20))
print("Tickers represented:", episodes_20["ticker"].nunique())

episodes_20

We have now reduced repeated purchases into 137 distinct CEO-buying episodes that started while the stock was at least 20% below its trailing high.

These episodes are the actual signals we'll follow through time. Next, we'll enter on the first trading day after the initial filing and measure what happened over one, three, six, and twelve months.

Calculate Returns After CEO Purchases

We now have 137 CEO-buying episodes that began while the stock was at least 20% below its trailing 252-day high.

The next question is straightforward: what happened after those filings became public?

We'll use the first trading day after the episode’s initial filing as the entry date. That keeps the test realistic. An outside investor couldn't act before the Form 4 appeared, and using the next trading session also handles filings submitted on weekends or market holidays.

The return horizons are:

• 1 Month: 21 trading days

• 3 Months: 63 trading days

• 6 Months: 126 trading days

• 12 Months: 252 trading days

Organize The Price History By Ticker

Before calculating returns, we'll create a separate, chronologically ordered price series for each stock.

This lets the return function find the correct entry date and then move forward by a fixed number of trading sessions without repeatedly filtering the full historical dataframe.

episodes_20 = episodes_20.reset_index(drop = True)
episodes_20['first_filing_date'] = pd.to_datetime(episodes_20['first_filing_date'])
historical_df['date'] = pd.to_datetime(historical_df['date'])

prices = historical_df[['ticker', 'date', 'adjusted_close']].dropna().sort_values(['ticker', 'date'])
price_map = {ticker: group.reset_index(drop=True) for ticker, group in prices.groupby('ticker')}

price_map is a dictionary where each ticker points to its own dataframe of dates and adjusted closing prices.

For example, price_map['AAT.US'] contains only the historical prices for AAT.US, already sorted from oldest to newest.

Find The Entry Date And Calculate Forward Returns

Now we can write a function that handles one purchase episode at a time.

The function will:

1. locate the stock’s price history

2. find the first trading day strictly after the filing date

3. save that day’s adjusted close as the entry price

4. move forward by 21, 63, 126, and 252 trading sessions

5. calculate the return at each horizon

def calculate_forward_returns(row):
    ticker_prices = price_map.get(row['ticker'])

    if ticker_prices is None:
        return pd.Series(dtype='object')

    dates = ticker_prices['date'].to_numpy(dtype='datetime64[ns]')
    entry_index = np.searchsorted(dates, np.datetime64(row['first_filing_date']), side='right')

    if entry_index >= len(ticker_prices):
        return pd.Series(dtype='object')

    entry_date = ticker_prices.loc[entry_index, 'date']
    entry_price = ticker_prices.loc[entry_index, 'adjusted_close']

    result = {'entry_date': entry_date, 'entry_price': entry_price}
    horizons = {'1m': 21, '3m': 63, '6m': 126, '12m': 252}

    for label, days in horizons.items():
        target_index = entry_index + days

        if target_index < len(ticker_prices):
            target_price = ticker_prices.loc[target_index, 'adjusted_close']
            result[f'date_{label}'] = ticker_prices.loc[target_index, 'date']
            result[f'return_{label}'] = target_price / entry_price - 1
        else:
            result[f'date_{label}'] = pd.NaT
            result[f'return_{label}'] = np.nan

    return pd.Series(result)

forward_returns = episodes_20.apply(calculate_forward_returns, axis=1)
episode_returns = pd.concat([episodes_20.reset_index(drop=True), forward_returns], axis=1)

episode_returns

The resulting dataframe contains the episode details, entry date, entry price, and forward returns at each horizon:

Summarize The Raw Returns

Looking at individual episodes is useful, but we also need a compact view of the full sample.

For each horizon, we'll calculate:

• the number of available observations

• the mean return

• the median return

• the percentage of returns above zero

summary = []

for horizon in ['1m', '3m', '6m', '12m']:
    returns = episode_returns[f'return_{horizon}'].dropna()

    summary.append({
        'horizon': horizon,
        'observations': len(returns),
        'mean_return': returns.mean(),
        'median_return': returns.median(),
        'positive_rate': (returns > 0).mean()
    })

summary_df = pd.DataFrame(summary)
summary_df

At first glance, the results look promising. The average return reached 11.9% after three months and 35.4% after twelve months. Most twelve-month observations were also positive.

But this is exactly where it's easy to jump to the wrong conclusion.

These numbers tell us what happened after CEOs bought. They don't tell us how much of that performance came from the CEO purchase itself.

The stocks were already down at least 20%. Some of them may have rebounded simply because beaten-down stocks sometimes recover.

To separate those two effects, we need a comparison group made from similar drawdown dates where no CEO purchase occurred nearby.

Build The No-Purchase Control Group

The raw return table looked encouraging, but it still gave CEO buying all the credit.

That's not a fair test. Every stock in the sample was already down at least 20%, and beaten-down stocks can rebound without any insider activity. We need to compare each CEO-purchase episode with another date where the same stock was under similar pressure but no CEO purchase happened nearby.

A valid control must satisfy six rules:

• same ticker

• same calendar year

• drawdown within five percentage points

• no more than 180 calendar days away

• no CEO purchase within 28 days before or after

• used only once

Each CEO-purchase episode is also matched only once.

Create The Control Candidates

We'll start by finding every trading day between 2022 and 2025 when a stock was at least 20% below its trailing high.

There's one problem, though. A stock can stay below that threshold for months. If we kept every trading day, one long decline could create hundreds of nearly identical control candidates.

To avoid that, we'll split each continuous drawdown period into 28-day blocks and keep one candidate from each block.

hist = historical_df[['ticker', 'date', 'adjusted_close', 'rolling_high_252', 'drawdown', 'drawdown_pct']].dropna(subset=['drawdown'])

hist['date'] = pd.to_datetime(hist['date'])
hist = hist[hist['date'].between('2022-01-01', '2025-12-31')].sort_values(['ticker', 'date'])

hist['below_20'] = hist['drawdown'] <= -0.20
hist['previous_below_20'] = hist.groupby('ticker')['below_20'].shift().fillna(False)
hist['new_state'] = hist['below_20'].ne(hist['previous_below_20'])
hist['drawdown_segment'] = hist.groupby('ticker')['new_state'].cumsum()

control_candidates = hist[hist['below_20']].copy()

control_candidates['segment_start'] = control_candidates.groupby(['ticker', 'drawdown_segment'])['date'].transform('min')
control_candidates['anchor_block'] = ((control_candidates['date'] - control_candidates['segment_start']).dt.days // 28)
control_candidates = control_candidates.sort_values(['ticker', 'date']).drop_duplicates(['ticker', 'drawdown_segment', 'anchor_block'])

control_candidates = control_candidates.reset_index(drop = True)
control_candidates

below_20 marks the dates that pass the drawdown threshold.

drawdown_segment then separates one continuous decline from another. If the stock recovers above the threshold and falls below it again later, that becomes a new segment.

Inside each segment, anchor_block counts 28-day windows from the day the drawdown began. Keeping one row per block gives us a manageable set of dates without treating every session in the same decline as a fresh event.

These dates are only potential controls. We still need to remove any that occurred close to CEO buying.

Remove Dates Near CEO Purchases

A no-purchase control should be genuinely separate from the insider signal.

For example, a drawdown date three days before a CEO filing would be a poor control. The transaction may already have happened, and the filing may simply not have appeared yet.

We therefore collect every CEO purchase filing date in the event dataset, not only the 137 episodes that passed the 20% threshold.

purchase_dates = analysis_df[['ticker', 'filed_at']].dropna().drop_duplicates()
purchase_dates['filed_at'] = pd.to_datetime(purchase_dates['filed_at'])
purchase_dates = purchase_dates.rename(columns={'filed_at': 'purchase_date'})
purchase_dates

For each candidate, we now need to find the closest purchase filing before it and the closest purchase filing after it.

Two merge_asof() operations handle that. The first searches backward, while the second searches forward.

control_candidates = pd.merge_asof(
    control_candidates.sort_values('date'),
    purchase_dates.sort_values('purchase_date'),
    by='ticker',
    left_on='date',
    right_on='purchase_date',
    direction='backward'
)

control_candidates = control_candidates.rename(columns={'purchase_date': 'previous_purchase_date'})

control_candidates = pd.merge_asof(
    control_candidates.sort_values('date'),
    purchase_dates.sort_values('purchase_date'),
    by='ticker',
    left_on='date',
    right_on='purchase_date',
    direction='forward'
)

control_candidates = control_candidates.rename(columns={'purchase_date': 'next_purchase_date'})

Each candidate now knows the nearest CEO purchase on either side.

We can calculate the distance from those filings and keep only dates that are more than 28 calendar days away from both.

days_from_previous = (control_candidates['date'] - control_candidates['previous_purchase_date']).dt.days
days_to_next = (control_candidates['next_purchase_date'] - control_candidates['date']).dt.days

far_from_previous = control_candidates['previous_purchase_date'].isna() | (days_from_previous > 28)
far_from_next = control_candidates['next_purchase_date'].isna() | (days_to_next > 28)

control_candidates = control_candidates[far_from_previous & far_from_next]
control_candidates

A missing previous or next filing is fine. It simply means there was no CEO purchase on that side of the candidate within the available dataset.

At this point, every remaining row represents a date when:

• the stock was down at least 20%

• the date wasn't part of the immediate neighborhood of a CEO purchase

• the stock had enough historical price data for the drawdown calculation

Match Purchase Episodes With Controls

Now comes the actual matching.

We first give every CEO-purchase episode and every control candidate a unique identifier. We also extract the calendar year because matches must come from the same stock and year.

purchase_pool = episodes_20.reset_index(drop = True)

purchase_pool['first_filing_date'] = pd.to_datetime(purchase_pool['first_filing_date'])
purchase_pool['year'] = purchase_pool['first_filing_date'].dt.year
purchase_pool['purchase_id'] = np.arange(len(purchase_pool))

control_candidates['year'] = control_candidates['date'].dt.year
control_candidates['control_id'] = np.arange(len(control_candidates))

The matching happens separately inside each ticker-year group.

Suppose a CEO bought when a stock was down 32%. We search for a no-purchase date in the same stock and year where the drawdown was close to 32%, while also keeping the dates no more than 180 calendar days apart.

A pair is valid only when:

• drawdown difference <= 0.05

• calendar distance <= 180 days

The next block builds the possible pairings and uses linear_sum_assignment() to select a one-to-one set of matches.

matches = []
max_drawdown_gap = 0.05
max_calendar_gap = 180

for (ticker, year), purchases in purchase_pool.groupby(['ticker', 'year']):
    controls = control_candidates[(control_candidates['ticker'] == ticker) & (control_candidates['year'] == year)].copy()

    if controls.empty:
        continue

    purchase_drawdowns = purchases['initial_drawdown'].to_numpy()[:, None]
    control_drawdowns = controls['drawdown'].to_numpy()[None, :]
    drawdown_cost = np.abs(purchase_drawdowns - control_drawdowns)

    purchase_dates = purchases['first_filing_date'].to_numpy(dtype='datetime64[D]')
    control_dates = controls['date'].to_numpy(dtype='datetime64[D]')
    calendar_gap = np.abs((purchase_dates[:, None] - control_dates[None, :]).astype('timedelta64[D]').astype(int))

    valid = (drawdown_cost <= max_drawdown_gap) & (calendar_gap <= max_calendar_gap)

    if not valid.any():
        continue

    cost = drawdown_cost + calendar_gap / 1000000
    cost[~valid] = 1000000

    row_indices, column_indices = linear_sum_assignment(cost)
    keep = cost[row_indices, column_indices] < 1000000

    selected = pd.DataFrame({
        'purchase_id': purchases.iloc[row_indices[keep]]['purchase_id'].to_numpy(),
        'control_id': controls.iloc[column_indices[keep]]['control_id'].to_numpy(),
        'drawdown_gap': drawdown_cost[row_indices[keep], column_indices[keep]],
        'calendar_gap_days': calendar_gap[row_indices[keep], column_indices[keep]]
    })

    matches.append(selected)

matched_pairs = pd.concat(matches, ignore_index=True)

The central idea is easier than the code first makes it look.

drawdown_cost measures how far apart the two drawdowns are. A purchase at -0.32 and a control at -0.34 have a difference of 0.02, or two percentage points.

The calendar distance is added as a very small tie-breaker. Drawdown similarity remains the main priority, but when two controls are almost equally close, the nearer date is preferred.

linear_sum_assignment() prevents the same control from being handed to several purchase episodes. It looks for a set of one-to-one matches that minimizes the combined cost across the group.

Build The Final Matched Dataset

The matching result currently contains only the purchase IDs, control IDs, and distance measures.

The final step is to merge the original purchase and control details back into those pairs so we can calculate returns for both sides.

selected_controls = control_candidates[['control_id', 'ticker', 'date', 'adjusted_close', 'drawdown', 'drawdown_pct']].rename(columns={'ticker': 'control_ticker',
                                                                                                                                       'date': 'control_date',
                                                                                                                                       'adjusted_close': 'control_signal_price',
                                                                                                                                       'drawdown': 'control_drawdown',
                                                                                                                                       'drawdown_pct': 'control_drawdown_pct'})

matched_sample = matched_pairs.merge(purchase_pool,on='purchase_id',how='left').merge(selected_controls,on='control_id',how='left')
matched_sample

Each row now contains one CEO-purchase episode and its matched no-purchase drawdown date:

We now have the two groups we actually wanted from the beginning: CEO purchases after large drawdowns and similar drawdowns in the same stocks without nearby CEO buying.

Compare CEO Purchases Against Similar No-Purchase Drawdowns

This is where the workflow finally earns its keep.

Each row in matched_sample contains two dates from the same stock:

• the first filing date of a CEO-buying episode

• a similar drawdown date with no nearby CEO purchase

From this point onward, both sides must be treated exactly the same. The CEO side enters on the first trading day after the filing date. The control side enters on the first trading day after the matched drawdown date. Both use the same adjusted prices and the same return horizons.

We'll first prepare the two signal-date columns and rebuild the ticker-level price map used earlier.

matched_sample['first_filing_date'] = pd.to_datetime(matched_sample['first_filing_date'])
matched_sample['control_date'] = pd.to_datetime(matched_sample['control_date'])
historical_df['date'] = pd.to_datetime(historical_df['date'])

prices = historical_df[['ticker', 'date', 'adjusted_close']].dropna().sort_values(['ticker', 'date'])
price_map = {ticker: group.reset_index(drop=True) for ticker, group in prices.groupby('ticker')}

The price map gives every ticker its own ordered history. That lets us use one return function for both the purchase and control dates instead of writing separate logic for each group.

Calculate Forward Returns From Any Signal Date

The next function takes only two inputs: a ticker and a signal date.

It finds the first trading session after that date, uses the adjusted close as the entry price, and calculates returns after 21, 63, 126, and 252 trading days.

def get_forward_returns(ticker, signal_date):
    result = {
        'entry_date': pd.NaT,
        'entry_price': np.nan,
        'return_1m': np.nan,
        'return_3m': np.nan,
        'return_6m': np.nan,
        'return_12m': np.nan
    }

    ticker_prices = price_map.get(ticker)

    if ticker_prices is None or pd.isna(signal_date):
        return pd.Series(result)

    dates = ticker_prices['date'].to_numpy(dtype='datetime64[ns]')
    entry_index = np.searchsorted(dates, np.datetime64(signal_date), side='right')

    if entry_index >= len(ticker_prices):
        return pd.Series(result)

    entry_price = ticker_prices.loc[entry_index, 'adjusted_close']
    result['entry_date'] = ticker_prices.loc[entry_index, 'date']
    result['entry_price'] = entry_price

    for label, days in {'1m': 21, '3m': 63, '6m': 126, '12m': 252}.items():
        target_index = entry_index + days

        if target_index < len(ticker_prices):
            target_price = ticker_prices.loc[target_index, 'adjusted_close']
            result[f'return_{label}'] = target_price / entry_price - 1

    return pd.Series(result)

The important detail is side='right'.

It prevents either group from entering on its signal date. The CEO-purchase return starts after the filing, and the control return starts after the matched drawdown date.

The function begins with missing values for every output. If a ticker is unavailable or there's not enough future price history for a horizon, that return simply stays as NaN.

Apply The Same Return Logic To Both Groups

Now we run the function twice for every matched pair.

The first pass uses the CEO-purchase ticker and filing date. The second uses the control ticker and control date. The returned columns are prefixed so the two sets remain easy to distinguish.

purchase_returns = matched_sample.apply(lambda row: get_forward_returns(row['ticker'], row['first_filing_date']), axis=1).add_prefix('purchase_')
control_returns = matched_sample.apply(lambda row: get_forward_returns(row['control_ticker'], row['control_date']), axis=1).add_prefix('control_')
matched_returns = pd.concat([
    matched_sample.reset_index(drop=True),
    purchase_returns.reset_index(drop=True),
    control_returns.reset_index(drop=True)], axis=1)

The resulting dataframe now places both outcomes side by side:

• CEO-purchase entry date and price

• control entry date and price

• CEO-purchase returns

• control returns

Not every pair survives at every horizon. A pair is usable only when both sides have enough future price history. That's why the number of observations falls as we move toward twelve months.

Build The Final Comparison

The last step is to compare the two return series at each horizon.

We 'll calculate:

• mean return for each group

• median return for each group

• mean return difference within the matched pairs

• median return difference within the matched pairs

• positive-return rate

• percentage of pairs where the CEO-purchase side beat the control

comparison = []

for horizon in ['1m', '3m', '6m', '12m']:
    purchase_col = f'purchase_return_{horizon}'
    control_col = f'control_return_{horizon}'
    valid = matched_returns[[purchase_col, control_col]].dropna()
    differences = valid[purchase_col] - valid[control_col]

    comparison.append({
        'horizon': horizon,
        'matched_pairs': len(valid),
        'purchase_mean': valid[purchase_col].mean(),
        'control_mean': valid[control_col].mean(),
        'mean_difference': differences.mean(),
        'purchase_median': valid[purchase_col].median(),
        'control_median': valid[control_col].median(),
        'median_difference': differences.median(),
        'purchase_positive_rate': (valid[purchase_col] > 0).mean(),
        'control_positive_rate': (valid[control_col] > 0).mean(),
        'purchase_win_rate': (valid[purchase_col] > valid[control_col]).mean()
    })

comparison_df = pd.DataFrame(comparison)
comparison_df

The paired statistics matter here.

median_difference is the median of:

CEO-purchase return - matched control return

for every pair. It's not simply the CEO median minus the control median.

The win rate asks an even more direct question: in what percentage of matched pairs did the CEO-purchase episode actually perform better?

The one-month result was weak. CEO-purchase episodes trailed the controls on the mean, median pair gap, positive-return rate, and win rate.

Three months was the one window where the result stayed consistent across the table. The CEO-purchase group returned 6.2 percentage points more on average, had a positive median paired advantage of 3.9 points, and won 59.6% of the matches.

The six- and twelve-month averages looked stronger than the typical pair. At twelve months, the CEO group returned nearly 10 percentage points more on average, yet it beat the control in only 37.9% of the comparisons.

That combination usually means a smaller number of large winners are pulling the average upward.

So the final answer is not that CEO buying always worked, or that it never mattered. The apparent edge depended heavily on the horizon, and three months was the only period where the different measures pointed in the same direction.

What The Case Study Found

The raw numbers made CEO buying look broadly bullish. After twelve months, the average return was 35.4%, and nearly two-thirds of the available observations were positive.

But once we added matched no-purchase drawdowns, the story became much narrower.

• One month showed no edge. CEO-purchase episodes underperformed their controls on the mean, median pair gap, positive-return rate, and win rate.

• Three months was the strongest window. The CEO group returned 6.2 percentage points more on average and beat its matched control in 59.6% of the pairs. This was the only horizon where the major measures pointed in the same direction.

• Six and twelve months were harder to trust. The averages were higher for the CEO-purchase group, but the median pair gaps were negative and most individual episodes lost to their controls.

• The drawdown itself explained a lot. Beaten-down stocks often rebounded even without CEO buying, so the raw post-purchase returns overstated the signal.

The most defensible conclusion is not that CEO buying predicts a long-term recovery. In this sample, it looked more like a possible three-month reversal signal, and even that result should be treated as exploratory rather than a trading rule.

What This Test Can And Can't Say

This was a 500-stock, screener-based sample, not the full market. The universe may carry survivorship bias, some code-P purchases may not have been fully discretionary, and matching similar drawdowns doesn't prove that CEO buying caused the returns. This is an exploratory case study, not a trading strategy.

The most useful part of the workflow was separating the insider signal from what beaten-down stocks already do on their own. Before adding controls, CEO purchases looked broadly bullish across several horizons. After adding them, the result became much narrower: a possible three-month edge, but no clean long-term guarantee.

That may feel less exciting than proving that CEO buying predicts a recovery. But it's a better answer. The workflow forced us to test the story we wanted to believe against a baseline, and the baseline changed the conclusion.

That works, but it flattens the data.

The average estimate is only the center of the range. Behind it, there is usually a low estimate, a high estimate, and the number of analysts contributing to the view. Two companies can have the same average estimate but very different levels of agreement behind it.

So I wanted to test a simple idea: what happens if we stop treating consensus as one number and start looking at its shape?

Not to predict stock returns or build a trading signal. Just to see whether the range around estimates tells us where analysts actually disagree.

Table of Contents

• Prerequisites

• The Data I Needed To Test This

• Pulling Analyst Estimates Across A Mixed Universe

• Turning Estimate Ranges Into Spread Metrics

• First View: Analyst Coverage Does Not Guarantee Agreement

• A Few Names Made The Pattern Obvious

• What This Changes In A Forecasting Workflow

• What I Would Not Overclaim

• Final Takeaway: Consensus Has Structure

Prerequisites

To follow along, you should be comfortable with basic Python, pandas DataFrames, dictionaries, loops, and simple plotting with matplotlib.

You’ll also need:

• Python 3.9 or later

• An FMP API key

• The following Python libraries: requests, pandas, numpy, and matplotlib

• Basic familiarity with analyst estimates, revenue, EPS, P/E-style forecasting inputs, and analyst coverage

You don't need advanced financial modeling knowledge. The goal is to show how low, average, high estimates, and analyst counts can reveal the shape of consensus instead of treating analyst estimates as one flat number.

The Data I Needed to Test This

To test this properly, the average estimate wasn't enough. I needed the full estimate range.

For each company, I wanted:

• revenue low, average, and high

• EPS low, average, and high

• number of analysts behind the revenue estimate

• number of analysts behind the EPS estimate

That gives two useful views. The average shows the center of expectations. The low and high estimates show how wide the expectation range is. The analyst count gives a rough sense of how deep the consensus is.

I also wanted a mixed universe. If the sample only includes mega-cap tech names, the result can easily become too clean because most of those companies are heavily covered. So I used a mix of mega-cap tech, semiconductors, energy, financials, healthcare, consumer names, and higher-uncertainty growth companies.

For the data source, I used FMP’s analyst estimates data because it provides the low, high, average, and analyst count fields needed for this experiment.

Pulling Analyst Estimates Across A Mixed Universe

I started by importing the basic packages and defining the stock universe.

import requests
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime
from time import sleep

api_key = 'YOUR FMP API KEY'
base_url = 'https://financialmodelingprep.com/stable'

tickers = [
    'AAPL', 'MSFT', 'NVDA', 'AMZN', 'META', 'GOOGL',
    'TSLA', 'PLTR', 'COIN', 'RBLX', 'SNOW', 'UBER',
    'AMD', 'INTC', 'MU', 'AVGO', 'QCOM',
    'CAT', 'DE', 'BA', 'GE', 'XOM', 'CVX',
    'WMT', 'COST', 'NKE', 'SBUX', 'MCD', 'TGT',
    'JPM', 'BAC', 'GS', 'MS', 'V', 'MA',
    'UNH', 'PFE', 'LLY', 'MRK', 'ABBV',
    'ROKU', 'SHOP', 'SQ', 'PYPL', 'ZM'
]

The next step was to pull annual analyst estimates for every ticker. I used the nearest usable future estimate period for each company, because estimate endpoints can return multiple periods and some far-out periods may not be fully populated.

all_rows = []

today = pd.Timestamp.today().normalize()

for ticker in tickers:
    url = f'{base_url}/analyst-estimates'

    params = {
        'symbol': ticker,
        'period': 'annual',
        'limit': 10,
        'apikey': api_key
    }

    response = requests.get(url, params=params)
    data = response.json()

    df = pd.DataFrame(data)

    if len(df) == 0:
        print(f'{ticker}: no data')
        continue

    df['date'] = pd.to_datetime(df['date'])
    df = df.sort_values('date')

    df = df[
        (df['date'] > today) &
        (df['revenueAvg'].notna()) &
        (df['revenueLow'].notna()) &
        (df['revenueHigh'].notna()) &
        (df['epsAvg'].notna()) &
        (df['epsLow'].notna()) &
        (df['epsHigh'].notna())
    ].copy()

    if len(df) == 0:
        print(f'{ticker}: no usable future estimates')
        continue

    row = df.iloc[0].copy()
    all_rows.append(row)
    print(f'{ticker} done')
    
    sleep(0.2)

estimates = pd.DataFrame(all_rows)
estimates.head()

The output gave one usable forward estimate row per company.

This table is already more useful than a normal average estimate pull. It gives the center of the estimate, the range around it, and the analyst count behind it. That's enough to start measuring the shape of consensus instead of only storing the average.

Turning Estimate Ranges Into Spread Metrics

Once the estimate data was in place, I needed a way to compare estimate ranges across companies.

Raw ranges aren't enough. A \(10 billion revenue range means something very different for a company expected to generate \)50 billion in revenue versus one expected to generate $500 billion. So I normalized the range by the average estimate.

estimates['revenue_spread'] = ((estimates['revenueHigh'] - estimates['revenueLow']) / estimates['revenueAvg'])
estimates['eps_spread'] = ((estimates['epsHigh'] - estimates['epsLow']) / estimates['epsAvg'].abs())
shape_df = estimates[['symbol','date','revenueLow','revenueAvg','revenueHigh','revenue_spread','numAnalystsRevenue',
                      'epsLow','epsAvg','epsHigh','eps_spread','numAnalystsEps']].copy()

shape_df.head()

The logic is simple. revenue_spread tells us how wide the revenue estimate range is relative to the average revenue estimate. eps_spread does the same for EPS.

But EPS needs one extra check. If average EPS is close to zero, even a normal estimate range can create a huge spread. That doesn't always mean analysts are wildly uncertain. Sometimes it just means the denominator is too small.

So I kept the original EPS spread, but created a cleaner version for plotting.

shape_df['eps_spread_clean'] = shape_df['eps_spread']

shape_df.loc[shape_df['epsAvg'].abs() < 1, 'eps_spread_clean'] = np.nan
shape_df.loc[shape_df['eps_spread_clean'] > 3, 'eps_spread_clean'] = np.nan

After that, I checked the widest and tightest ranges.

shape_df.sort_values('revenue_spread', ascending=False)[
    [
        'symbol',
        'revenueLow',
        'revenueAvg',
        'revenueHigh',
        'revenue_spread',
        'numAnalystsRevenue'
    ]
].head(10)

This was the first sign that the idea might be useful. Some names had wide revenue estimate ranges despite meaningful analyst coverage. TSLA had 35 analysts behind revenue estimates, NVDA had 39, and INTC had 31, but their revenue ranges were still relatively wide.

Then I checked the cleaned EPS spread.

shape_df.sort_values('eps_spread_clean', ascending=False)[
    [
        'symbol',
        'epsLow',
        'epsAvg',
        'epsHigh',
        'eps_spread_clean',
        'numAnalystsEps'
    ]
].head(10)

This made the analysis more interesting. Revenue and EPS weren't behaving the same way. TSLA had wide ranges on both. SQ had a very high EPS spread, even though its revenue spread was much tighter. That started to suggest something useful: consensus disagreement can sit in different parts of the model.

First View: Analyst Coverage Does Not Guarantee Agreement

The first thing I wanted to check was whether deeper analyst coverage automatically meant tighter consensus.

So I used two simple dimensions:

• number of analysts covering revenue

• revenue estimate spread

Then I split the data using median thresholds. This isn't meant to be a formal model. It's just a quick way to separate different consensus shapes.

analyst_threshold = shape_df['numAnalystsRevenue'].median()
spread_threshold = shape_df['revenue_spread'].median()

analyst_threshold, spread_threshold

Then I created coverage and spread buckets:

shape_df['coverage_bucket'] = np.where(
    shape_df['numAnalystsRevenue'] >= analyst_threshold,
    'high coverage',
    'low coverage'
)

shape_df['spread_bucket'] = np.where(
    shape_df['revenue_spread'] <= spread_threshold,
    'low spread',
    'high spread'
)

From there, each company falls into one of four simple categories:

conditions = [
    (shape_df['coverage_bucket'] == 'high coverage') & (shape_df['spread_bucket'] == 'low spread'),
    (shape_df['coverage_bucket'] == 'high coverage') & (shape_df['spread_bucket'] == 'high spread'),
    (shape_df['coverage_bucket'] == 'low coverage') & (shape_df['spread_bucket'] == 'low spread'),
    (shape_df['coverage_bucket'] == 'low coverage') & (shape_df['spread_bucket'] == 'high spread')
]

labels = [
    'tight consensus',
    'watched but uncertain',
    'thin but stable',
    'weak consensus'
]

shape_df['revenue_consensus_shape'] = np.select(conditions, labels)

The split came out more balanced than I expected:

That was useful because the labels weren't collapsing into one obvious bucket. The universe actually had different consensus shapes.

Then I plotted coverage against revenue spread.

plt.figure(figsize=(12, 7))

for label in shape_df['revenue_consensus_shape'].unique():
    temp = shape_df[shape_df['revenue_consensus_shape'] == label]

    plt.scatter(
        temp['numAnalystsRevenue'],
        temp['revenue_spread'],
        s=80,
        label=label,
        alpha=0.8
    )

plt.axvline(analyst_threshold, linestyle='--', linewidth=1)
plt.axhline(spread_threshold, linestyle='--', linewidth=1)

for i, row in shape_df.iterrows():
    if row['revenue_spread'] > spread_threshold or row['numAnalystsRevenue'] > analyst_threshold:
        plt.text(
            row['numAnalystsRevenue'] + 0.3,
            row['revenue_spread'],
            row['symbol'],
            fontsize=9
        )

plt.title('Analyst Coverage vs Revenue Estimate Spread')
plt.xlabel('Number of Analysts Covering Revenue')
plt.ylabel('Revenue Estimate Spread')

plt.legend()
plt.show()

The chart made one thing clear: more analyst coverage doesn't always mean tighter agreement.

MSFT, AAPL, MA, WMT, and META sat closer to the tight consensus area. They had higher coverage and relatively narrow revenue ranges.

But TSLA, AVGO, NVDA, INTC, AMD, MU, and GOOGL were also heavily covered, yet their revenue estimate spreads were wider. These are the “watched but uncertain” names. The market isn't ignoring them. Analysts are looking at them closely, but the forecast range is still wide.

The weaker consensus area was also useful. CVX, XOM, and COIN had wide revenue ranges with lower coverage compared to the mega-cap names. That's a different kind of uncertainty. It's not just disagreement. It's disagreement with less analyst depth behind it.

This first view was helpful, but it still only looked at revenue. The next question was more interesting: does the uncertainty sit in revenue, EPS, or both?

plot_df = shape_df.dropna(subset=['revenue_spread', 'eps_spread_clean']).copy()

plt.figure(figsize=(12, 7))

plt.scatter(
    plot_df['revenue_spread'],
    plot_df['eps_spread_clean'],
    s=plot_df['numAnalystsRevenue'] * 3,
    alpha=0.75
)

for i, row in plot_df.iterrows():
    plt.text(
        row['revenue_spread'] + 0.002,
        row['eps_spread_clean'],
        row['symbol'],
        fontsize=9
    )

plt.title('Revenue Estimate Spread vs EPS Estimate Spread')
plt.xlabel('Revenue Estimate Spread')
plt.ylabel('EPS Estimate Spread')

plt.show()

This was the more useful view.

The chart showed that consensus uncertainty doesn't sit in the same place for every company. Some names had both revenue and EPS clustered tightly. Some had wide ranges across both. And a few had a much more specific kind of disagreement.

SQ was the clearest example. Its revenue spread was low, but its EPS spread was high. That suggests analysts were much closer on the revenue side than on the earnings side.

TSLA showed the opposite kind of extreme. Both revenue and EPS spreads were wide, so the average estimate was hiding disagreement across more than one part of the model.

At this point, I wanted to turn this into a simple classification. Again, this isn't a formal risk model. I used median thresholds only to separate the shapes clearly.

revenue_spread_threshold = plot_df['revenue_spread'].median()
eps_spread_threshold = plot_df['eps_spread_clean'].median()

plot_df['revenue_uncertainty'] = np.where(
    plot_df['revenue_spread'] <= revenue_spread_threshold,
    'low revenue uncertainty',
    'high revenue uncertainty'
)

plot_df['eps_uncertainty'] = np.where(
    plot_df['eps_spread_clean'] <= eps_spread_threshold,
    'low EPS uncertainty',
    'high EPS uncertainty'
)

Then I combined the two buckets into four forecast shapes.

conditions = [
    (plot_df['revenue_uncertainty'] == 'low revenue uncertainty') & (plot_df['eps_uncertainty'] == 'low EPS uncertainty'),
    (plot_df['revenue_uncertainty'] == 'low revenue uncertainty') & (plot_df['eps_uncertainty'] == 'high EPS uncertainty'),
    (plot_df['revenue_uncertainty'] == 'high revenue uncertainty') & (plot_df['eps_uncertainty'] == 'low EPS uncertainty'),
    (plot_df['revenue_uncertainty'] == 'high revenue uncertainty') & (plot_df['eps_uncertainty'] == 'high EPS uncertainty')
]

labels = [
    'stable forecast shape',
    'profitability uncertainty',
    'top-line uncertainty',
    'broad forecast uncertainty'
]

plot_df['forecast_shape'] = np.select(conditions, labels)

The distribution looked like this:

That split was more useful than the first one because it showed where the disagreement was located.

A stable forecast shape means both revenue and EPS ranges are relatively tight. Profitability uncertainty means revenue estimates are tighter, but EPS estimates are wider. Top-line uncertainty means the revenue range is wider while EPS is relatively tighter. Broad forecast uncertainty means both sides are wide.

Then I plotted the same chart again with these labels:

plt.figure(figsize=(12, 7))

for label in plot_df['forecast_shape'].unique():
    temp = plot_df[plot_df['forecast_shape'] == label]

    plt.scatter(
        temp['revenue_spread'],
        temp['eps_spread_clean'],
        s=temp['numAnalystsRevenue'] * 3,
        label=label,
        alpha=0.75
    )

plt.axvline(revenue_spread_threshold, linestyle='--', linewidth=1)
plt.axhline(eps_spread_threshold, linestyle='--', linewidth=1)

for i, row in plot_df.iterrows():
    if (
        row['revenue_spread'] > revenue_spread_threshold or
        row['eps_spread_clean'] > eps_spread_threshold
    ):
        plt.text(
            row['revenue_spread'] + 0.002,
            row['eps_spread_clean'],
            row['symbol'],
            fontsize=9
        )

plt.title('Revenue Uncertainty vs EPS Uncertainty')
plt.xlabel('Revenue Estimate Spread')
plt.ylabel('EPS Estimate Spread')

plt.legend()
plt.show()

This became the main chart for the analysis.

The average estimate hides the center of expectations, but this chart shows the structure around it. For a forecasting workflow, that matters. A model shouldn't treat a tight consensus estimate and a wide consensus estimate as if they carry the same level of agreement.

A Few Names Made The Pattern Obvious

Once the companies were grouped by forecast shape, the pattern became easier to read.

plot_df[
    [
        'symbol',
        'revenue_spread',
        'eps_spread_clean',
        'numAnalystsRevenue',
        'numAnalystsEps',
        'forecast_shape'
    ]
].sort_values(['forecast_shape', 'eps_spread_clean'], ascending=[True, False])

The full table was useful, but for the article, the more important part is the examples from each bucket.

broad_uncertainty = final_view[
    final_view['forecast_shape'] == 'broad forecast uncertainty'
].sort_values('eps_spread_pct', ascending=False)

broad_uncertainty.head(10)

TSLA was the obvious outlier. The revenue estimate spread was around 21.8%, and the EPS spread was over 104%. That's not just a wide range around one line item. It's disagreement across both the top line and bottom line.

CVX and XOM were also interesting, but for a different reason. Their revenue spreads were very wide, and analyst coverage was lower than many tech names in the sample. That makes their consensus shape different from a name like TSLA, where coverage is deeper but disagreement still remains.

Then I looked at the profitability uncertainty bucket.

profitability_uncertainty = final_view[
    final_view['forecast_shape'] == 'profitability uncertainty'
].sort_values('eps_spread_pct', ascending=False)

profitability_uncertainty

This was the most useful bucket conceptually.

SQ had only about 1.1% revenue spread, but nearly 73.8% EPS spread. That's a very different shape from TSLA. Here, analysts were much closer on revenue, but far apart on earnings.

That matters for a model. If I only store the average revenue estimate and average EPS estimate, I lose that distinction. The model can't see that the revenue estimate is relatively tight while the EPS estimate carries much more disagreement.

SNOW and PLTR showed a similar pattern, though not as extreme. Revenue expectations were relatively close together, but EPS expectations had a wider range. That points to uncertainty around profitability, margins, or earnings conversion rather than pure revenue growth.

The stable bucket gave the contrast.

stable_shape = final_view[
    final_view['forecast_shape'] == 'stable forecast shape'
].sort_values(['revenue_spread_pct', 'eps_spread_pct'])

stable_shape.head(10)

MSFT was the cleanest example here. Its revenue spread was around 0.4%, and its EPS spread was around 3.0%. MA, BAC, ABBV, and TGT also stayed in the stable zone, with relatively tight ranges across both revenue and EPS.

That doesn't mean these estimates will be right. It only means analysts are clustered more tightly around the forward numbers.

Finally, the top-line uncertainty bucket was smaller.

topline_uncertainty = final_view[
    final_view['forecast_shape'] == 'top-line uncertainty'
].sort_values('revenue_spread_pct', ascending=False)

topline_uncertainty

This group was smaller, but it completed the picture. These were cases where revenue uncertainty was more visible than EPS uncertainty.

The broader point is simple: consensus doesn't have one shape. Averages hide that. The range around the average shows whether disagreement sits around revenue, EPS, or both.

What This Changes In A Forecasting Workflow

The practical takeaway isn't that every model needs a new complicated uncertainty system. It's simpler than that.

If a model already stores analyst estimates, it should probably store the range around those estimates too.

Instead of keeping only this:

symbol | estimated_revenue | estimated_eps

I would rather keep this:

symbol | estimated_revenue | estimated_eps | revenue_spread | eps_spread | analyst_count | forecast_shape

That gives the model more context about the forecast input it's already using.

To make this usable, I created a final table with the estimate period, revenue spread, EPS spread, analyst coverage, revenue consensus shape, and overall forecast shape.

final_df = plot_df[
    [
        'symbol',
        'date',
        'revenueAvg',
        'revenueLow',
        'revenueHigh',
        'revenue_spread',
        'epsAvg',
        'epsLow',
        'epsHigh',
        'eps_spread_clean',
        'numAnalystsRevenue',
        'numAnalystsEps',
        'revenue_consensus_shape',
        'forecast_shape'
    ]
].copy()

final_df = final_df.rename(
    columns={
        'date': 'estimate_period',
        'revenueAvg': 'revenue_avg',
        'revenueLow': 'revenue_low',
        'revenueHigh': 'revenue_high',
        'epsAvg': 'eps_avg',
        'epsLow': 'eps_low',
        'epsHigh': 'eps_high',
        'eps_spread_clean': 'eps_spread',
        'numAnalystsRevenue': 'revenue_analysts',
        'numAnalystsEps': 'eps_analysts'
    }
)

final_df['revenue_spread_pct'] = final_df['revenue_spread'] * 100
final_df['eps_spread_pct'] = final_df['eps_spread'] * 100

final_view = final_df[
    [
        'symbol',
        'estimate_period',
        'revenue_spread_pct',
        'eps_spread_pct',
        'revenue_analysts',
        'eps_analysts',
        'revenue_consensus_shape',
        'forecast_shape'
    ]
].copy()

final_view = final_view.sort_values('eps_spread_pct', ascending=False)

final_view.head(15)

The output looked like this:

This table is mainly useful for spotting where the average estimate hides the most disagreement.

TSLA is the clearest broad uncertainty case. Both revenue and EPS spreads are wide, so storing only the average estimate would flatten too much of the forecast structure.

SQ is different. Its revenue spread is only about 1.1%, but its EPS spread is about 73.8%. That suggests the disagreement is much less about revenue and much more about profitability or earnings conversion.

SNOW and PLTR show a similar pattern, though less extreme. Their revenue spreads are relatively tight, while EPS spreads are much wider. That's a useful distinction for any model using estimates as inputs.

The point isn't to decide which estimate is right. The point is to avoid treating every consensus average as if it carries the same level of agreement. The average gives the center. The spread shows how much disagreement sits around that center.

What I Would Not Overclaim

I wouldn't treat these labels as a final model.

The stock universe here is handpicked, not the full market. The cutoffs are also simple median thresholds, not a statistical confidence model. They're useful for separating the data into readable groups, but they shouldn't be treated as exact boundaries.

EPS spread also needs care. If average EPS is close to zero, the spread can become distorted, which is why I cleaned extreme EPS cases before plotting.

Most importantly, this doesn't tell us which estimate is right. A wide range doesn't automatically mean the company is bad, and a tight range does not mean the forecast will be accurate.

The useful part is more basic: the model stops pretending that every average estimate carries the same level of agreement.

Final Takeaway: Consensus Has Structure

The average estimate is still useful. I wouldn't remove it from a forecasting model.

But after looking at the low, high, average, and analyst count together, using only the average feels incomplete.

Consensus has structure. Some estimates are tight. Some are wide. Sometimes disagreement sits around revenue. Sometimes it sits around EPS. Sometimes it shows up across both.

A better forecasting workflow should preserve that structure instead of flattening it away. It doesn't need to become complicated. Even a few extra fields, like revenue spread, EPS spread, analyst count, and forecast shape, can make the estimate layer more honest.

At first, you only need a few prices. You send a request, get JSON back, load it into pandas, and move on. But the moment that API starts powering a backtester, dashboard, screener, valuation tool, or AI assistant, the decision becomes much more serious.

A backtester needs adjusted historical prices, splits, dividends, and stable time series. A dashboard needs fresh quotes, clean fields, and reliable responses. A stock screener needs fundamentals, ratios, and company metadata. An AI agent needs structured data that it can retrieve and use without guessing.

That's why I wouldn't start by comparing endpoint counts or pricing pages. Those matter, but they're not the first question.

The first question is: what are you building?

In this article, we’ll walk through how to choose a stock market API based on the workflow it needs to support. Then we’ll build a practical stock research workflow in Python using Alpha Vantage to see how prices, fundamentals, technical indicators, and AI-ready access can fit together in one project.

Table of Contents

• Why Stock API Choice Depends On The Workflow

  • 1. If You Are Building A Backtester

  • 2. If You Are Building A Dashboard

  • 3. If You Are Building A Stock Screener

  • 4. If You Are Building A Valuation Or Research Tool

  • 5. If You Are Building An AI Assistant Or Agent

• What A Modern Stock Market Data Workflow Actually Requires

• Building A Practical Stock Research Workflow With Alpha Vantage

  • Step 1: Fetch Adjusted Historical Prices

  • Step 2: Add Company Or Fundamental Data

  • Step 3: Add Technical Indicators

  • Step 4: Combine Everything Into A Research-Ready Table

  • Step 5: Connect The Workflow To AI Agents With MCP

• Where Each Provider Fits In The Stock API Workflow

• Provider Breakdown Through A Workflow Lens

  • 1. When The Project Needs Several Data Layers: Alpha Vantage

  • 2. When The Workflow Is Institutional: Bloomberg API

  • 3. When The Product Needs Investor Relations Widgets: QuoteMedia

  • 4. When The Workflow Is Global Historical Research: EODHD

  • 5. When The Workflow Needs US Fundamentals: Intrinio

  • 6. When The Workflow Needs Enterprise Data Delivery: Xignite

• Final Checklist Before Choosing A Stock API

• Final Thoughts

Why Stock API Choice Depends On The Workflow

A stock API should be judged by the workflow it supports, not by how long its feature list looks. The same provider can be a good fit for one project and a weak fit for another.

A clean historical dataset matters more for a backtester than a live quote endpoint. A dashboard has different problems. It needs fresh responses, predictable fields, and rate limits that don't collapse once users start refreshing the page.

Here is how I would think about it.

1. If You Are Building A Backtester

Start with historical data quality.

A backtest needs adjusted prices, splits, dividends, long history, and stable time series. If those pieces are wrong, the backtest can still run, but the results may be misleading.

For this workflow, real-time data is usually secondary. Clean historical data matters more than fast quotes.

2. If You Are Building A Dashboard

Start with freshness and reliability.

A dashboard needs quote data that updates consistently, fields that don't change unexpectedly, and rate limits that can handle repeated requests. A failed request in a notebook is annoying. A failed request in a user-facing dashboard is a product problem.

You also need to check whether the data can be displayed to users. Licensing becomes part of the workflow once the dashboard is public.

3. If You Are Building A Stock Screener

Start with fundamentals and structured fields.

A screener needs more than prices. It may need ratios, company profiles, sector data, market cap, earnings, and symbol coverage across many companies.

The hard part is comparison. If fields are inconsistent across tickers, the screener becomes a cleanup project before it becomes a useful tool.

4. If You Are Building A Valuation Or Research Tool

Start with financial statements.

A valuation workflow usually needs income statements, balance sheets, cash flow statements, earnings history, and historical fundamentals. Price data gives market context, but the business data does the heavier work.

This is where depth matters. The latest numbers are useful, but trends across multiple periods are often more important.

5. If You Are Building An AI Assistant Or Agent

Start with structure.

An AI agent shouldn't guess financial data from memory. It needs predictable API responses, clear schemas, and tool access it can use reliably.

This is where MCP-style workflows matter. If an agent can call a tool, retrieve a quote, pull fundamentals, or fetch a time series cleanly, the API becomes part of the agent’s reasoning loop.

The practical point is simple: choose the API around the system you're building. Once the workflow is clear, the rest of the decision becomes much easier.

What A Modern Stock Market Data Workflow Actually Requires

A modern stock data workflow is rarely just one API call.

You might start with market data, but most useful projects eventually need more layers. A research dashboard may need fundamentals. A screener may need technical indicators. An AI assistant may need structured responses that it can retrieve through a tool.

A simple way to think about the workflow is:

Market Data -> Fundamentals -> Indicators -> Structured Responses -> Programmatic Workflow -> AI/Agent Access

Each layer solves a different problem.

• Market data gives you prices, volume, returns, and historical movement.

• Fundamentals add business context through revenue, margins, cash flow, earnings, and company details.

• Indicators help convert raw prices into features that can support screening, research, or signal testing.

• Structured responses make the data easier to parse, join, and reuse.

• Programmatic workflows turn the raw API response into tables, charts, models, dashboards, or research outputs.

• AI or agent access lets an assistant call tools, retrieve current data, and work with structured financial context instead of relying only on static knowledge.

This is why stock API choice matters beyond the first request. The API is not only there to return data but to support the way the project grows after the prototype.

Building A Practical Stock Research Workflow With Alpha Vantage

Now let’s turn the framework into something practical.

For this section, we’ll use Alpha Vantage as the implementation API because it gives us the main layers we need for this workflow: adjusted historical prices, company data, technical indicators, and MCP-style access for AI agents.

The goal isn't to test every endpoint. The goal is to build a small research workflow that shows what a useful stock API should help us do.

We’ll build this in five steps:

1. Fetch adjusted historical prices.

2. Add company or fundamental data.

3. Add a technical indicator.

4. Combine everything into a research-ready table.

5. Connect the workflow to an AI-agent setup using MCP.

By the end, we should have a simple but practical stock research table that can support a screener, dashboard, research notebook, or AI assistant.

Step 1: Fetch Adjusted Historical Prices

Adjusted prices are the first thing I would check for any research or backtesting workflow. Raw prices can break around stock splits or dividends, while adjusted prices keep the series more useful for return calculations.

Let’s fetch daily adjusted price data for Apple.

import requests
import pandas as pd

api_key = 'YOUR ALPHA VANTAGE API KEY'

symbol = 'AAPL'

url = f'https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol={symbol}&outputsize=compact&apikey={api_key}'

response = requests.get(url)
data = response.json()

prices = pd.DataFrame(data['Time Series (Daily)']).T

prices.index = pd.to_datetime(prices.index)
prices = prices.sort_index()

prices = prices.rename(columns={
    '1. open': 'open',
    '2. high': 'high',
    '3. low': 'low',
    '4. close': 'close',
    '5. adjusted close': 'adjusted_close',
    '6. volume': 'volume',
    '7. dividend amount': 'dividend',
    '8. split coefficient': 'split'
})

price_cols = ['open', 'high', 'low', 'close', 'adjusted_close', 'volume', 'dividend', 'split']
prices[price_cols] = prices[price_cols].astype(float)

prices.tail()

The output gives us a clean daily price table as you can see in the image below:

For a chart, you may only need close. For research or backtesting, I would usually work with adjusted_close because it handles corporate actions more safely. Next, we can convert the time series into a few basic price features.

latest_price = prices['adjusted_close'].iloc[-1] 
return_30d = prices['adjusted_close'].pct_change(30).iloc[-1] 
volatility_30d = prices['adjusted_close'].pct_change().tail(30).std() 

price_features = {'symbol': symbol, 'latest_price': latest_price, 'return_30d': return_30d, 'volatility_30d': volatility_30d}
price_features

This returns:

{'symbol': 'AAPL',
 'latest_price': 312.06,
 'return_30d': 0.18583097277442007,
 'volatility_30d': 0.012845143800989936}

This is already more useful than a raw API response. We now have a small set of price features that can feed a dashboard, screener, research table, or AI-assisted stock analysis workflow.

Step 2: Add Company Or Fundamental Data

Price data tells us how the stock moved, but it doesn't tell us much about the company behind the ticker. For a screener, valuation tool, or research workflow, we need some business context too.

Alpha Vantage’s OVERVIEW endpoint gives company-level fields like sector, industry, market cap, PE ratio, EPS, profit margin, and other summary metrics. Let’s pull those fields and keep only the ones we need for this workflow.

overview_url = f'https://www.alphavantage.co/query?function=OVERVIEW&symbol={symbol}&apikey={api_key}'

response = requests.get(overview_url)
overview = response.json()

fundamental_features = {
    'symbol': symbol,
    'name': overview.get('Name'),
    'sector': overview.get('Sector'),
    'industry': overview.get('Industry'),
    'market_cap': overview.get('MarketCapitalization'),
    'pe_ratio': overview.get('PERatio'),
    'eps': overview.get('EPS'),
    'profit_margin': overview.get('ProfitMargin'),
    'beta': overview.get('Beta')
}

fundamental_features

This returns:

{'symbol': 'AAPL',
 'name': 'Apple Inc',
 'sector': 'TECHNOLOGY',
 'industry': 'CONSUMER ELECTRONICS',
 'market_cap': 4583336182000.0,
 'pe_ratio': 37.73,
 'eps': 8.27,
 'profit_margin': 0.272,
 'beta': 1.065}

Now we have two layers: price behavior from the time series data and business context from the company overview. The next step is to add a technical indicator so the table includes a market-derived signal as well.

Step 3: Add Technical Indicators

Fundamentals give us business context, but many research workflows also need market-derived signals. A simple example is the relative strength index, or RSI, which is often used to measure recent momentum.

Alpha Vantage has a RSI endpoint, so we can pull the indicator directly instead of calculating it from scratch.

rsi_url = f'https://www.alphavantage.co/query?function=RSI&symbol={symbol}&interval=daily&time_period=14&series_type=close&apikey={api_key}'

response = requests.get(rsi_url)
rsi_data = response.json()

rsi = pd.DataFrame(rsi_data['Technical Analysis: RSI']).T

rsi.index = pd.to_datetime(rsi.index)
rsi = rsi.sort_index()
rsi['RSI'] = rsi['RSI'].astype(float)

latest_rsi = rsi['RSI'].iloc[-1]

indicator_features = {
    'symbol': symbol,
    'rsi_14': latest_rsi
}

indicator_features

This returns:

{'symbol': 'AAPL', 'rsi_14': 79.0043}

Now the workflow has three layers:

• price behavior from adjusted historical data

• business context from company fundamentals

• momentum context from a technical indicator

None of these is enough on its own. Together, they start to look like a usable research workflow instead of a raw API test.

Step 4: Combine Everything Into A Research-Ready Table

Now we can combine the price, fundamentals, and indicator layers into one table.

This is the part that matters for most real projects. A dashboard, screener, notebook, or AI assistant usually needs a clean object it can reuse, not three separate raw API responses.

research_row = {
    **price_features,
    **fundamental_features,
    **indicator_features
}

research_table = pd.DataFrame([research_row])

research_table

This gives us a single-row research table:

This table is simple, but it already supports several use cases.

A screener can filter on pe_ratio, profit_margin, or rsi_14. A dashboard can show price, returns, sector, and market cap. A research notebook can add more tickers and compare them. An AI assistant can receive this as a compact context object instead of parsing multiple API responses on its own.

That's the real benefit of building the workflow this way. The API calls are only the beginning. The useful output is the structured table you create from them.

Step 5: Connect The Workflow To AI Agents With MCP

The table we created is useful because it has a predictable structure, which is exactly what AI workflows need.

If an agent needs stock context, it shouldn't guess from memory or parse several raw API responses every time. It should call a tool, retrieve the data, and receive something clean enough to use.

A simplified MCP workflow looks like this:

User question -> AI agent -> MCP tool call -> Stock API data -> Structured response -> Final answer

For example, a user might ask:

Is Apple looking expensive compared with its recent momentum?

An agent could retrieve price data, fundamentals, and an indicator such as RSI before answering. The important part is not that the model already “knows” the answer. It's that the model can call the right tool and work with current data.

That is where our research table helps:

research_table.to_dict(orient='records')[0]

This returns a compact dictionary:

{'symbol': 'AAPL',
 'latest_price': 312.06,
 'return_30d': 0.18583097277442007,
 'volatility_30d': 0.012845143800989936,
 'name': 'Apple Inc',
 'sector': 'TECHNOLOGY',
 'industry': 'CONSUMER ELECTRONICS',
 'market_cap': 4583336182000.0,
 'pe_ratio': 37.73,
 'eps': 8.27,
 'profit_margin': 0.272,
 'beta': 1.065,
 'rsi_14': 79.0043}

This doesn't replace proper analysis, and it shouldn't be treated as investment advice. But it gives an AI assistant a cleaner starting point than raw JSON, stale model knowledge, or a vague prompt with no data attached.

AI readiness isn't just about saying an API supports agents. The API has to return data that can be retrieved, structured, checked, and passed into a workflow without fragile glue code at every step.

Where Each Provider Fits In The Stock API Workflow

The workflow we built above is one version of a modern stock data project: prices, fundamentals, indicators, programmatic analysis, and AI-agent access working together.

Other projects may need a narrower or more specialized provider. Here's a practical way to compare the fit:

No provider fits every workflow equally well. The point of this table is to show where the fit is strongest.

Alpha Vantage works well when a project needs several layers together, especially market data, fundamentals, indicators, developer usability, and AI-agent access. EODHD is stronger when the workflow is centered on global historical research. Intrinio fits better when standardized US fundamentals are the main requirement. Bloomberg API and Xignite are more natural for institutional or enterprise environments, while QuoteMedia is more specialized around investor relations and embedded market data widgets.

This is the right way to think about stock APIs: not as one universal winner, but as different tools for different workflow shapes.

Provider Breakdown Through A Workflow Lens

The table gives a quick comparison. This section explains what that means in practice.

Instead of asking which provider is “best” in general, it is better to ask: what kind of workflow is this provider naturally built for?

1. When The Project Needs Several Data Layers: Alpha Vantage

Alpha Vantage fits well when the project needs more than one type of market data in the same workflow.

In the workflow we built earlier, we used:

• adjusted historical prices

• company data

• technical indicators

• structured output for programmatic analysis

• a format that can also support AI-agent workflows

That makes Alpha Vantage a flexible fit for stock research notebooks, screeners, dashboards, backtesting workflows, and AI assistants that need market data through tools or MCP-style access.

The main caveat is specialization. If your project needs direct exchange infrastructure, co-location, or a highly specialized institutional setup, you may need a more specialized provider. But for most research, fintech apps, and AI workflows, Alpha Vantage gives enough breadth without forcing you to combine several APIs too early.

2. When The Workflow Is Institutional: Bloomberg API

Bloomberg API makes sense when the organization already uses Bloomberg internally.

It's best suited for firms that want to connect Bloomberg data with internal tools, reports, models, and risk systems.

This isn't usually the right fit for solo developers or small teams. The cost, licensing, and ecosystem dependency make it more suitable for institutions.

3. When The Product Needs Investor Relations Widgets: QuoteMedia

QuoteMedia fits products where the main need is public-facing market data display.

That can include:

• investor relations pages

• quote widgets

• embedded charts

• company stock pages

• market data modules for public websites

This is different from building a programmatic research workflow. QuoteMedia makes more sense when presentation and embedded financial data are the core product requirement.

4. When The Workflow Is Global Historical Research: EODHD

EODHD fits well when the project needs broad historical data across global markets.

It's useful for long-horizon backtesting, global screeners, and research workflows that depend on end-of-day data from many exchanges.

The tradeoff is cleanup. Global data often brings differences in symbols, exchange calendars, currencies, and local market conventions. That's manageable, but it should be expected.

5. When The Workflow Needs US Fundamentals: Intrinio

Intrinio fits well when standardized US fundamentals are the center of the product.

It's useful for:

• valuation tools

• earnings dashboards

• fundamentals-based screeners

• professional US equity research workflows

The main thing to check is dataset fit. Before building around Intrinio, I would look closely at the specific datasets, access terms, and coverage levels the product needs.

6. When The Workflow Needs Enterprise Data Delivery: Xignite

Xignite fits larger financial applications that need formal vendor support.

This can include banks, brokerages, wealth platforms, and enterprise fintech products where support, contracts, reliability, and data relationships matter as much as the endpoint itself.

For smaller developer projects, it may feel heavier than necessary. For enterprise products, that structure can be exactly the point.

Final Checklist Before Choosing A Stock API

Before choosing a provider, I would run through this checklist.

Final Thoughts

A good stock API should reduce project risk, not just return data.

If you're building a small chart, almost any clean price endpoint can work. But once the same API starts supporting a backtester, screener, dashboard, valuation tool, or AI assistant, the decision becomes more important. The provider affects your data quality, parsing logic, refresh jobs, licensing choices, and future product direction.

This is why workflow fit matters more than endpoint count. For projects that need several layers together, such as real-time and historical market data, fundamentals, indicators, developer-friendly access, spreadsheet support, and MCP-style AI workflows, Alpha Vantage fits well. For narrower workflow needs, another provider may make more sense.

Choose the API as part of your project’s data infrastructure, not just as a list of endpoints.