<jupyter_start><jupyter_text>Senator Filings Analysis ***  Imports<jupyter_code>from collections import defaultdict
import datetime as dt
from functools import lru_cache
import json
from os import path
import pickle

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import yfinance as yf<jupyter_output><empty_output><jupyter_text>IntroductionIn this notebook, we explore stock orders that were publicly filed by U.S. senators. The filings are scraped from https://efdsearch.senate.gov/search/. We calculate the returns of each senator by mimicking their buys and sells. ***  Loading dataThe `senators.pickle` file is scraped using the script in the root of the repository.<jupyter_code>with open('senators.pickle', 'rb') as f:
    raw_senators_tx = pickle.load(f)<jupyter_output><empty_output><jupyter_text>Data cleaning  Filling in missing tickers In this section, we fill in as many of the missing ticker symbols as we can.<jupyter_code>def tokenize(asset_name):
    """ Convert an asset name into useful tokens. """
    token_string = asset_name\
        .replace('(', '')\
        .replace(')', '')\
        .replace('-', ' ')\
        .replace('.', '')
    return token_string.split(' ')

def token_is_ticker(token, token_blacklist):
    return len(token) <= 4 and token.upper() not in token_blacklist

# These generic words do not help us determine the ticker
with open('blacklist.json', 'r') as f:
    blacklist = set(json.load(f))

missing_tickers = set(raw_senators_tx[
    (raw_senators_tx['ticker'] == '--')
    | (raw_senators_tx['ticker'] == '')
]['asset_name'])

ticker_map = {}
unmapped_tickers = set()
for m in missing_tickers:
    tokens = tokenize(m)
    if token_is_ticker(tokens[0], blacklist):
        ticker_map[m] = tokens[0].upper()
    elif token_is_ticker(tokens[-1], blacklist):
        ticker_map[m] = tokens[-1].upper()
    else:
        unmapped_tickers.add(m)<jupyter_output><empty_output><jupyter_text>As a second pass, we assign tickers to asset names that have any of the specified keywords.<jupyter_code>phrase_to_ticker = {
    'FOX': 'FOX',
    'AMAZON': 'AMZN',
    'AARON': 'AAN',
    'ALTRIA': 'MO',
    'APPLE': 'AAPL',
    'CHEVRON': 'CVX',
    'DUPONT': 'DD',
    'ALPHABET': 'GOOGL',
    'GOOG': 'GOOGL',
    'GENERAL ELECTRIC': 'GE',
    'JOHNSON': 'JNJ',
    'NEWELL': 'NWL',
    'OWENS': 'OMI',
    'PFIZER': 'PFE',
    'TYSON': 'TSN',
    'UNDER ARMOUR': 'UAA',
    'VERIZON': 'VZ',
    'WALT': 'DIS'
}

for m in unmapped_tickers:
    for t in phrase_to_ticker:
        if t in m.upper():
            ticker_map[m] = phrase_to_ticker[t]

tx_with_tickers = raw_senators_tx.copy()
for a, t in ticker_map.items():
    tx_with_tickers.loc[tx_with_tickers['asset_name'] == a, 'ticker'] = t<jupyter_output><empty_output><jupyter_text>Filtering rows and columns We filter out useless rows and missing symbols, and then add some useful columns for the final dataset.<jupyter_code>filtered_tx = tx_with_tickers[tx_with_tickers['ticker'] != '--']
filtered_tx = filtered_tx.assign(
    ticker=filtered_tx['ticker'].map(
        lambda s: s.replace('--', '').replace('\n', '')))

filtered_tx = filtered_tx[filtered_tx['order_type'] != 'Exchange']
def parse_tx_amount(amt):
    """ Get the lower bound for the transaction amount. """
    return int(amt.replace('Over $50,000,000', '50000000')
               .split(' - ')[0]
               .replace(',', '')
               .replace('$', ''))

senators_tx = filtered_tx.assign(
    tx_estimate=filtered_tx['tx_amount'].map(parse_tx_amount))
senators_tx = senators_tx.assign(
    full_name=senators_tx['first_name']
        .str
        .cat(senators_tx['last_name'], sep=' ')
)
useful_cols = [
    'file_date',
    'tx_date',
    'full_name',
    'order_type',
    'ticker',
    'tx_estimate'
]
senators_tx = senators_tx[useful_cols]
senators_tx = senators_tx.assign(
    tx_date=senators_tx['tx_date'].map(
        lambda v: dt.datetime.strptime(v, '%m/%d/%Y')))
senators_tx = senators_tx.assign(
    file_date=senators_tx['file_date'].map(
        lambda v: dt.datetime.strptime(v, '%m/%d/%Y')))
senators_tx<jupyter_output><empty_output><jupyter_text>Returns calculation These cells help us download the market data for the specified tickers. We store the market data in files so we don't need to repeatedly download the same information.<jupyter_code>def download_for_ticker(ticker, check_cache=True):
    """ Download a file of stock prices for this ticker to disk. """
    if check_cache and path.exists('stocks/{0}.pickle'.format(ticker)):
        return
    d = yf.Ticker(ticker)
    with open('stocks/{0}.pickle'.format(ticker), 'wb') as f:
        pickle.dump({
            'price': d.history(period='max').reset_index()
        }, f)

def load_for_ticker(ticker):
    """ Load the file of stock prices for this ticker. """
    with open('stocks/{0}.pickle'.format(ticker), 'rb') as f:
        dump = pickle.load(f)
    raw = dump['price']
    return raw[['Date', 'Close']]\
        .rename(columns={'Date': 'date', 'Close': 'price'})

def _price_for_date(df, date):
    """ Helper function for `ticker_at_date`. """
    df = df[df['date'] >= date].sort_values(by='date')
    return df['price'].iloc[0]

@lru_cache(maxsize=128)
def ticker_at_date(ticker, date):
    """
    Price of a ticker at a given date. Raise an IndexError if there is no
    such price.
    """
    try:
        data = load_for_ticker(ticker)
        # Sell at the next opportunity possible
        return _price_for_date(data, date)
    except Exception:
        # If any exception occurs, refresh the cache
        download_for_ticker(ticker, check_cache=False)
        data = load_for_ticker(ticker)
        return _price_for_date(data, date)
all_tickers = set(senators_tx['ticker'])
for i, t in enumerate(all_tickers):
    if i % 100 == 0:
        print('Working on ticker {0}'.format(i))
    try:
        download_for_ticker(t)
    except Exception as e:
        print('Ticker {0} failed with exception: {1}'.format(t, e))<jupyter_output><empty_output><jupyter_text>Mimicking buy + sell ordersWe calculate a given senator's return by calculating the return between each buy or sell order, and then solving for the cumulative return. We convert that to a CAGR given the time period the senator was investing.We keep track of how many units of each stock a senator is holding. If we ever see a filing that indicates the senator sold more than we estimated they are holding, we just sell all of the units we have on record. (We do not allow the senator to go short.)<jupyter_code>buckets = [
    (1000, 15000),
    (15000, 50000),
    (50000, 100000),
    (100000, 250000),
    (250000, 500000),
    (500000, 1000000),
    (1000000, 5000000),
    (5000000, 25000000),
    (25000000, 50000000),
    (50000000, float('inf'))
]

def same_bucket(dollar_value_a, dollar_value_b):
    """
    If the dollar value of the stock units is roughly the same, sell all
    units.
    """
    for v1, v2 in buckets:
        if dollar_value_a >= v1 and dollar_value_a < v2:
            return dollar_value_b >= v1 and dollar_value_b < v2
    return False

def portfolio_value(stocks, date):
    """
    Value of a portfolio if each ticker has the specified number of units.
    """
    v = 0
    for s, units in stocks.items():
        if units == 0:
            continue
        try:
            v += ticker_at_date(s, date) * units
        except IndexError as e:
            # Swallow missing ticker data exception
            pass
    return v

def calculate_return(before_values,
                     after_values,
                     begin_date,
                     end_date,
                     tx_dates):
    """
    Calculate cumulative return and CAGR given the senators portfolio
    value over time.
    """
    before_values.pop(0)
    after_values.pop(-1)
    # We calculate the total return by calculating the return
    # between each transaction, and solving for the cumulative
    # return.
    growth = np.array(before_values) / np.array(after_values)
    portfolio_return = np.prod(growth[~np.isnan(growth)])
    years = (end_date - begin_date).days / 365
    if years == 0:
        cagr = 0
    else:
        cagr = portfolio_return**(1 / years)
    # DataFrame of cumulative return
    tx_dates.pop(0)
    tx_dates = np.array(tx_dates)
    tx_dates = tx_dates[~np.isnan(growth)]
    cumulative_growth = np.cumprod(growth[~np.isnan(growth)])
    growth_df = pd.DataFrame({
        'date': tx_dates,
        'cumulative_growth': cumulative_growth
    })
    return {
        'portfolio_return': portfolio_return,
        'annual_cagr': cagr,
        'growth': growth_df
    }

def return_for_senator(rows, date_col='tx_date'):
    """
    Simulate a senator's buy and sell orders, and calculate the
    return.
    """
    stocks = defaultdict(int)
    # Value of portfolio at various timepoints to calculate return
    portfolio_value_before_tx = []
    portfolio_value_after_tx = []
    tx_dates = []
    rows = rows.sort_values(by=date_col)
    for _, row in rows.iterrows():
        date = row[date_col]
        if date_col == 'file_date':
            # We can't execute the trade the same day
            date += dt.timedelta(days=1)
        try:
            stock_price = ticker_at_date(row['ticker'], date)
        except IndexError as e:
            # Skip the row if we're missing ticker data
            continue
        value_before_tx = portfolio_value(stocks, date)
        if 'Purchase' in row['order_type']:
            tx_amt = row['tx_estimate']
            n_units = tx_amt / ticker_at_date(row['ticker'], date)
            stocks[row['ticker']] += n_units
        elif 'Sale' in row['order_type']:
            current_value = stock_price * stocks[row['ticker']]
            if 'Full' in row['order_type'] or\
                same_bucket(row['tx_estimate'], current_value):
                stocks[row['ticker']] = 0
            else:
                new_n_units = stocks[row['ticker']] - \
                    row['tx_estimate'] / stock_price
                stocks[row['ticker']] = max(0, new_n_units)
        portfolio_value_before_tx.append(value_before_tx)
        portfolio_value_after_tx.append(portfolio_value(stocks, date))
        tx_dates.append(date)
    return calculate_return(
        portfolio_value_before_tx,
        portfolio_value_after_tx,
        begin_date=min(rows[date_col]),
        end_date=max(rows[date_col]),
        tx_dates=tx_dates
    )
senator_returns = []
senator_tx_growth = {}
senator_file_growth = {}
senator_names = set(senators_tx['full_name'])<jupyter_output><empty_output><jupyter_text>The following cell took my laptop about three hours to run.<jupyter_code>failed_senators = {}
print('{} senators total'.format(len(senator_names)))
for n in senator_names:
    print('Starting {}'.format(n))
    if n in senator_tx_growth:
        # Don't re-calculate for a given senator
        continue
    try:
        tx_return = return_for_senator(
            senators_tx[senators_tx['full_name'] == n],
            date_col='tx_date')
        file_return = return_for_senator(
            senators_tx[senators_tx['full_name'] == n],
            date_col='file_date')
        senator_returns.append({
            'full_name': n,
            'tx_total_return': tx_return['portfolio_return'],
            'tx_cagr': tx_return['annual_cagr'],
            'file_total_return': file_return['portfolio_return'],
            'file_cagr': file_return['annual_cagr']
        })
        senator_tx_growth[n] = tx_return['growth']
        senator_file_growth[n] = file_return['growth']
    except Exception as e:
        print('Failed senator {0} with exception {1}'.format(n, e))
        failed_senators[n] = e<jupyter_output><empty_output><jupyter_text>We look at the results to see the senators that outperformed the market.<jupyter_code>def plot_senator_growth(growth):
    """ Plot the senator's portfolio growth against the S&P 500. """
    plt.plot_date(growth['date'], growth['cumulative_growth'], '-')
    download_for_ticker('SPY')
    spy = load_for_ticker('SPY')
    spy = spy[(spy['date'] >= min(growth['date']))
              & (spy['date'] <= max(growth['date']))]
    spy_prices = spy['price']
    spy_growth = np.cumprod(np.diff(spy_prices) / spy_prices[1:] + 1)
    dates = spy['date'].iloc[1:]
    plt.plot_date(dates, spy_growth, '-')
    plt.show()
    print('Earliest date: {}'.format(min(growth['date'])))
    print('Latest date: {}'.format(max(growth['date'])))
    print('Market return: {}'.format(
        spy_prices.iloc[-1] / spy_prices.iloc[0]))
    senator_growth = growth['cumulative_growth']
    print('Senator return: {}'.format(
        senator_growth.iloc[-1] / senator_growth.iloc[0]))
returns = pd.DataFrame(senator_returns)
returns = returns[(returns['tx_total_return'] > returns['tx_cagr'])
                  & (returns['tx_cagr'] > 0)]
returns.sort_values('tx_cagr')
plot_senator_growth(senator_tx_growth['<NAME>, Jr.'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12633356358891704, 'fraction_numerical': 0.014506101772967994, 'mean_word_length': 3.2005157962604773, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 44, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '256320', 'license': 'MIT', 'n_tokens_mistral': 4484, 'n_tokens_neox': 4279, 'n_words': 1052, 'path': 'notebooks/Senator Filings Analysis.ipynb', 'repo_name': 'radimallari/senator-filings', 'size': '13035'}