earnings with recent insider transactions
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.
We will collect tonight's earnings (Thu Dec 12th) for afterhours then thin out the responses a bit for a more concise summary:
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
| 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:
-
RHis Restoration Hardware: how is the high-end consumer doing? -
AVGOis Broadcom: how is the infrastructure semiconductor and software biz doing? -
COSTis 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):
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:
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)
| 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
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:
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)
| 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:
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).
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.
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.
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()