In [1]:

import os
import requests
import polars as pl
from rich import print
from lets_plot import *
LetsPlot.setup_html()

Let's start with something we have have experience with: the earnings endpoints.

https://api.unusualwhales.com/docs#/operations/PublicApi.EarningsController.afterhours

We will collect tonight's earnings (Thu Dec 12th) for afterhours then thin out the responses a bit for a more concise summary:

In [2]:

uw_token = os.environ['UW_TOKEN']
headers = {'Accept': 'application/json, text/plain', 'Authorization': uw_token}

earnings_params = {
    'date': '2024-12-12'
}
earnings_url = 'https://api.unusualwhales.com/api/earnings/afterhours'
earnings_rsp = requests.get(earnings_url, headers=headers, params=earnings_params)

# Create a polars dataframe for easier data cleaning and analysis
raw_earnings_df = pl.DataFrame(earnings_rsp.json()['data'])

preferred_order = [
    'symbol',
    'full_name',
    'sector',
    'is_s_p_500',
    'ending_fiscal_quarter_dt',
    'report_date_dt',
    'report_time',
    'street_mean_est',
    'pre_earnings_close',
    'expected_move_perc',
    'expected_move',
    'expected_high',
    'expected_low'
]
clean_earnings_df = (
    raw_earnings_df
    .filter(pl.col('has_options') == True)
    .with_columns(
        pl.col('ending_fiscal_quarter').str.strptime(pl.Date, '%Y-%m-%d').alias('ending_fiscal_quarter_dt'),
        pl.col('pre_earnings_date').str.strptime(pl.Date, '%Y-%m-%d').alias('pre_earnings_date_dt'),
        pl.col('report_date').str.strptime(pl.Date, '%Y-%m-%d').alias('report_date_dt'),
        pl.col('expected_move').cast(pl.Float64),
        pl.col('expected_move_perc').cast(pl.Float64),
        pl.col('post_earnings_close').cast(pl.Float64),
        pl.col('pre_earnings_close').cast(pl.Float64),
        pl.col('reaction').cast(pl.Float64),
        pl.col('street_mean_est').cast(pl.Float64)
    )
    .with_columns(
        (pl.col('pre_earnings_close') + pl.col('expected_move')).alias('expected_high'),
        (pl.col('pre_earnings_close') - pl.col('expected_move')).alias('expected_low')
    )
    .select(preferred_order)
    .sort('expected_move_perc', descending=True)
)
clean_earnings_df

Out[2]:

shape: (5, 13)

symbol	full_name	sector	is_s_p_500	ending_fiscal_quarter_dt	report_date_dt	report_time	street_mean_est	pre_earnings_close	expected_move_perc	expected_move	expected_high	expected_low
str	str	str	bool	date	date	str	f64	f64	f64	f64	f64	f64
"NX"	"QUANEX BUILDIN…	"Industrials"	false	2024-10-31	2024-12-12	"postmarket"	0.53	28.93	0.134438	3.89	32.82	25.04
"RH"	"RH"	"Consumer Cycli…	false	2024-10-31	2024-12-12	"postmarket"	2.67	381.4	0.106404	40.58	421.98	340.82
"GTIM"	"GOOD TIMES RES…	"Consumer Cycli…	false	2024-09-30	2024-12-12	"postmarket"	null	2.69	0.090929	0.24	2.93	2.45
"AVGO"	"BROADCOM"	"Technology"	true	2024-10-31	2024-12-12	"postmarket"	1.39	180.92	0.062721	11.35	192.27	169.57
"COST"	"COSTCO WHOLESA…	"Consumer Defen…	true	2024-11-30	2024-12-12	"postmarket"	3.79	988.54	0.027572	27.26	1015.8	961.28

Only 5 records, but 3 of those 5 are big companies with potentially far-reaching implications:

RH is Restoration Hardware: how is the high-end consumer doing?
AVGO is Broadcom: how is the infrastructure semiconductor and software biz doing?
COST is Costco: how is the cost-sensitive consumer doing?

Let's use this as an opportunity to pull in some insider trading data and see if any of these names have seen recent transactions. Using the insider buying and selling endpoint:

https://api.unusualwhales.com/docs#/operations/PublicApi.TickerController.insider_buy_sell

will give us a json() response like this:

{
  "data": [
    {
      "filing_date": "2023-12-13",
      "purchases": 12,
      "purchases_notional": "14317122.490",
      "sells": 10,
      "sells_notional": "-1291692.4942"
    },
    {
      "filing_date": "2023-12-12",
      "purchases": 78,
      "purchases_notional": "46598915.1911",
      "sells": 211,
      "sells_notional": "-182466466.7165"
    },
    {
      "filing_date": "2023-12-11",
      "purchases": 96,
      "purchases_notional": "431722108.8184",
      "sells": 210,
      "sells_notional": "-1058043617.3548"
    }
  ]
}

So let's also add a ticker column to each (for easier bookkeeping):

In [3]:

tickers = ['RH', 'AVGO', 'COST']
insider_dfs = []
for ticker in tickers:
    insider_url = f'https://api.unusualwhales.com/api/stock/{ticker}/insider-buy-sells'
    insider_rsp = requests.get(insider_url, headers=headers)
    raw_df = pl.DataFrame(insider_rsp.json()['data'])
    df = raw_df.with_columns(pl.lit(ticker).alias('ticker'))
    insider_dfs.append(df)

OK now that we have our insider transactions for each of these names, let's concatenate the dataframes together and clean up the data a bit:

In [4]:

raw_insiders_df = pl.concat(insider_dfs)
clean_insiders_df = (
    raw_insiders_df
    .with_columns(
        pl.col('filing_date').str.strptime(pl.Date, '%Y-%m-%d').alias('date'),
        pl.col('purchases_notional').cast(pl.Float64),
        pl.col('sells_notional').cast(pl.Float64)
    )
    .filter((pl.col('purchases') + pl.col('sells')) > 0)
    .sort('date', descending=True)
)
clean_insiders_df.head(10)

Out[4]:

shape: (10, 7)

filing_date	purchases	purchases_notional	sells	sells_notional	ticker	date
str	i64	f64	i64	f64	str	date
"2024-10-24"	0	0.0	3	-3.2514e6	"COST"	2024-10-24
"2024-10-23"	0	0.0	1	-717232.0	"COST"	2024-10-23
"2024-10-18"	0	0.0	1	-707142.0	"RH"	2024-10-18
"2024-10-16"	0	0.0	2	-6.3235e6	"AVGO"	2024-10-16
"2024-10-15"	0	0.0	3	-7.5079e6	"COST"	2024-10-15
"2024-10-11"	0	0.0	3	-9.7797e6	"AVGO"	2024-10-11
"2024-10-11"	0	0.0	1	-3.92106e6	"COST"	2024-10-11
"2024-10-09"	0	0.0	2	-7.0480e6	"AVGO"	2024-10-09
"2024-10-07"	0	0.0	3	-1.0535568e7	"AVGO"	2024-10-07
"2024-10-04"	0	0.0	2	-4.4751e7	"AVGO"	2024-10-04

Hmm... nothing too recent, but notable that it's mostly selling. You know what might actually be interesting here? A view of price and insider buying vs. selling over time side-by-side with the actual price of the underlying -- maybe these insiders are good market timers, let's find out.

A little Unusual Whales "inside baseball", when I just need daily closing prices I actually use the realized volatility endpoint because it comes back with one year of prices as well as the daily implied volatility and realized volatility (which I might end up using anywayy), so let's get started there:

https://api.unusualwhales.com/docs#/operations/PublicApi.TickerController.realized_volatility

In [5]:

tickers = ['RH', 'AVGO', 'COST']
params = {
    'timeframe': '2Y'
}
rv_dfs = []
for ticker in tickers:
    url = f'https://api.unusualwhales.com/api/stock/{ticker}/volatility/realized'
    rsp = requests.get(url, headers=headers, params=params)
    raw_df = pl.DataFrame(rsp.json()['data'])
    df = raw_df.with_columns(pl.lit(ticker).alias('ticker'))
    rv_dfs.append(df)

Just like with the insider transaction dataframes, let's concatenate the results and clean up the data a bit:

In [6]:

raw_rv_df = pl.concat(rv_dfs)
clean_rv_df = (
    raw_rv_df
    .with_columns(
        pl.col('date').str.strptime(pl.Date, '%Y-%m-%d').alias('date'),
        pl.col('implied_volatility').cast(pl.Float64),
        pl.col('price').cast(pl.Float64),
        pl.col('realized_volatility').cast(pl.Float64),
    )
    .sort('date', descending=True)
)
clean_rv_df.head(10)

Out[6]:

shape: (10, 5)

date	implied_volatility	price	realized_volatility	ticker
date	f64	f64	f64	str
2024-12-12	0.700755	381.4	null	"RH"
2024-12-12	0.491755	180.92	null	"AVGO"
2024-12-12	0.252232	988.54	null	"COST"
2024-12-11	0.707012	398.95	null	"RH"
2024-12-11	0.469758	183.2	null	"AVGO"
2024-12-11	0.242718	994.69	null	"COST"
2024-12-10	0.685242	386.09	null	"RH"
2024-12-10	0.466877	171.81	null	"AVGO"
2024-12-10	0.244108	993.4	null	"COST"
2024-12-09	0.68751	396.78	null	"RH"

OK nice, now we have all the data we need to build these plots, let's create them in the same order as above:

In [7]:

def build_lets_plot_ready_df(price_df: pl.DataFrame,
                              insider_df: pl.DataFrame,
                              ticker: str,
                              cutoff_date: pl.date) -> pl.DataFrame:
    '''
    Filter and combine the broad price and insider dataframes to create a
    single, melted dataframe specifically meant for plotting in Lets-Plot.

    Inputs:
    - price_df: Polars DataFrame containing price data (from RV endpoint)
    - insider_df: Polars DataFrame containing insider data (from Insider endpoint)
    - ticker: str representing the stock ticker
    - cutoff_date: pl.Date representing the earliest date to consider
    '''
    ticker_price_df = (
        price_df
        .filter((pl.col('ticker') == ticker) & (pl.col('date') >= cutoff_date))
        .with_columns(
            pl.col('date').dt.strftime('%Y-%m-%d').alias('filing_date'),
        )
        .sort('date')
    )
    ticker_insider_df = (
        insider_df
        .filter((pl.col('ticker') == ticker) & (pl.col('date') >= cutoff_date))
        .sort('date')
    )
    return (
        ticker_price_df
        .join(ticker_insider_df, on='date', how='left')
        .select(['filing_date', 'price', 'purchases', 'sells'])
        .with_columns(
            pl.when(pl.col('purchases') > 0)
            .then(pl.col('price'))
            .otherwise(None)
            .alias('insider_buy')
        )
        .with_columns(
            pl.when(pl.col('sells') > 0)
            .then(pl.col('price'))
            .otherwise(None)
            .alias('insider_sell')
        )
        .melt(
            id_vars=['filing_date'],
            value_vars=['price', 'insider_buy', 'insider_sell'],
        )
    )


def build_insider_trading_plot(lp_df: pl.DataFrame,
                               ticker: str,
                               color_mapping: dict,
                               width: int=800,
                               height: int=500) -> ggplot:
    '''
    Build a Lets-Plot ggplot object representing the insider trading data
    for a given stock ticker.

    Inputs:
    - lp_df: Polars DataFrame containing the melted price and insider data
    - color_mapping: dict mapping variable names to colors (or hex color codes)
    - ticker: str representing the stock ticker
    - (optional) width: int representing the width of the plot
    - (optional) height: int representing the height of the plot
    '''
    return (
    ggplot(data=lp_df, mapping=aes(x='filing_date', y='value', color='variable'))
    + geom_line(data=lp_df.filter(pl.col('variable') == 'price'))
    + geom_point(data=lp_df.filter(pl.col('variable') != 'price'), size=3)
    + ggtitle(f'{ticker} Price and Insider Activity')
    + scale_color_manual(values=color_mapping)
    + ggsize(width, height)
    + theme(
        plot_title=element_text(hjust=0.5),
        legend_position='right'
    )
)

Starting with RH we can see a lot of red dots -- first item on the "Dig Deeper" list is going to be figuring out how much of this selling is planned out in advance as part of a total compensation plan (it seems like there is a "periodicity" to some clusters of red dots) and how much of it is discretionary.

Next up is finding our lone wolf buyer. N=1 so it is impossible to put a lot of weight on this, but that single buy was objectively great, coming in very close to $240/share (which is near the bottom of RH's trading range).

In [8]:

rh_combo_df = build_lets_plot_ready_df(clean_rv_df, clean_insiders_df, 'RH', pl.date(2022, 12, 13))
rh_color_mapping = {
    'price': 'black',
    'insider_buy': 'dark_green',
    'insider_sell': 'red'
}
rh_combo_plot = build_insider_trading_plot(rh_combo_df, 'RH', rh_color_mapping)
rh_combo_plot.show()

AVGO is a very interesting case compared to RH because AVGO has gone more or less "up and to the right" on autopilot over the last two years. Nearly every sale has, with the benefit of hindsight, been poorly-timed, and the max drawdown the Sep'23 buyers felt was a few dollars at most.

In [9]:

avgo_combo_df = build_lets_plot_ready_df(clean_rv_df, clean_insiders_df, 'AVGO', pl.date(2022, 12, 13))
avgo_color_mapping = {
    'price': 'black',
    'insider_buy': 'dark_green',
    'insider_sell': 'red'
}
avgo_combo_plot = build_insider_trading_plot(avgo_combo_df, 'AVGO', avgo_color_mapping)
avgo_combo_plot.show()

COST only did a 2X (compared to AVGO's 3x) over the last two years, but the takeaways are very similar to what we learned from AVGO: thanks to hindsight, every sale has been poorly timed as COST price continually marches up-and-to-the-right.

In [10]:

cost_combo_df = build_lets_plot_ready_df(clean_rv_df, clean_insiders_df, 'COST', pl.date(2022, 12, 13))
cost_color_mapping = {
    'price': 'black',
    'insider_buy': 'dark_green',
    'insider_sell': 'red'
}
cost_combo_plot = build_insider_trading_plot(cost_combo_df, 'COST', cost_color_mapping)
cost_combo_plot.show()