Analysis of Audit Fees Data in SEC DEF 14A Definitive Proxy Statements

from sec_api import AuditFeesApi
import json

auditFeesApi = AuditFeesApi(api_key=API_KEY_SEC_API)

search_params = {
    "query": "entities.cik:1318605",
    "from": "0",
    "size": "1",
    "sort": [{"filedAt": {"order": "desc"}}],
}

# get proposed sales information, sales history, and other details
response = auditFeesApi.get_data(search_params)
filing = response["data"]

print(json.dumps(filing, indent=2))

import sys
import os
import time
import random

# from multiprocessing import Pool # use in .py files only
from concurrent.futures import ThreadPoolExecutor

YEARS = range(2025, 1999, -1)  # from 2025 to 2018
TEMP_FILE_TEMPLATE = "./temp_file_audit_fees_{}.jsonl"
TARGET_FILE = "./audit-fees-dataset.jsonl.gz"


def process_year(year):
    backoff_time = random.randint(10, 800) / 1000
    print(f"Starting year {year} with backoff time {backoff_time:,}s")
    sys.stdout.flush()
    time.sleep(backoff_time)

    tmp_filename = TEMP_FILE_TEMPLATE.format(year)
    tmp_file = open(tmp_filename, "a")

    for month in range(12, 0, -1):
        search_from = 0
        month_counter = 0

        while True:
            query = f"filedAt:[{year}-{month:02d}-01 TO {year}-{month:02d}-31]"
            searchRequest = {
                "query": query,
                "from": search_from,
                "size": "50",
                "sort": [{"filedAt": {"order": "desc"}}],
            }

            response = None
            try:
                response = auditFeesApi.get_data(searchRequest)
            except Exception as e:
                print(f"{year}-{month:02d} error: {e}")
                sys.stdout.flush()
                continue

            if response == None or len(response["data"]) == 0:
                break

            search_from += 50
            month_counter += len(response["data"])
            jsonl_data = "\n".join([json.dumps(entry) for entry in response["data"]])
            tmp_file.write(jsonl_data + "\n")

        print(f"Finished loading {month_counter} filings for {year}-{month:02d}")
        sys.stdout.flush()

    tmp_file.close()

    return year


if not os.path.exists(TARGET_FILE):
    with ThreadPoolExecutor(max_workers=4) as pool:
        processed_years = list(pool.map(process_year, YEARS))
    print("Finished processing all years.", processed_years)

    import gzip

    # Merge the temporary files into one final compressed file
    with gzip.open(TARGET_FILE, "wt", encoding="utf-8") as outfile:
        for year in YEARS:
            temp_file = TEMP_FILE_TEMPLATE.format(year)
            if os.path.exists(temp_file):
                with open(temp_file, "r", encoding="utf-8") as infile:
                    for line in infile:
                        outfile.write(line)  # Preserve JSONL format
else:
    print("File already exists. Skipping download.")

data_file = "../output/audit-fees-dataset-consumer.jsonl"

structured_data = pd.read_json(data_file, lines=True)

structured_data["firstEntityCik"] = structured_data.apply(
    lambda x: x["entities"][0]["cik"], axis=1
)

structured_data["filedAt"] = pd.to_datetime(structured_data["filedAt"], utc=True)
structured_data["filedAt"] = structured_data["filedAt"].dt.tz_convert("US/Eastern")
structured_data = structured_data.sort_values("filedAt", ascending=True).reset_index(
    drop=True
)

structured_data.drop_duplicates("accessionNo", keep="first", inplace=True)
structured_data["filedAtYear"] = structured_data["filedAt"].dt.year
structured_data["filedAtMonth"] = structured_data["filedAt"].dt.month
structured_data["filedAtQtr"] = structured_data["filedAtMonth"].apply(
    lambda x: (x - 1) // 3 + 1
)
structured_data["filedAtDayOfWeek"] = structured_data["filedAt"].dt.day_name()
# filedAtClass: preMarket (4:00AM-9:30AM), regularMarket (9:30AM-4:00PM), afterMarket (4:00PM-8:00PM)
structured_data["filedAtClass"] = structured_data["filedAt"].apply(
    lambda x: (
        "preMarket"
        if x.hour < 9 or (x.hour == 9 and x.minute < 30)
        else (
            "regularMarket"
            if x.hour < 16
            else "afterMarket" if x.hour < 20 else "other"
        )
    )
)
structured_data["periodOfReportYear"] = structured_data["periodOfReport"].apply(
    lambda x: x.split("-")[0] if isinstance(x, str) else np.nan
)
structured_data["periodOfReportMonth"] = structured_data["periodOfReport"].apply(
    lambda x: x.split("-")[1] if isinstance(x, str) else np.nan
)


structured_data = structured_data.explode("records").reset_index(
    drop=True
)  # ← this fixes the issue

normalized_df = pd.json_normalize(structured_data["records"])

structured_data = pd.concat(
    [structured_data.drop(columns=["records"]), normalized_df], axis=1
)
structured_data.head(5)

unique_years = structured_data["filedAtYear"].nunique()
unique_companies = structured_data["firstEntityCik"].nunique()
unique_filings = structured_data["accessionNo"].nunique()
tickers = structured_data["entities"].apply(
    lambda x: (
        x[0]["ticker"]
        if len(x) > 0 and isinstance(x[0], dict) and "ticker" in x[0]
        else None
    )
)
unique_tickers = tickers.nunique()
min_year = structured_data["filedAtYear"].min()
max_year = structured_data["filedAtYear"].max()
print(f"Loaded dataframe with main documents of {form_name} filings")
print(f"Number of filings: {unique_filings:,}")
print(f"Number of records: {len(structured_data):,}")
print(f"Number of years: {unique_years:,} ({min_year}-{max_year})")
# print(f"Number of unique companies: {unique_companies:,}")
print(f"Number of unique issuers: {unique_companies:,}")
print(f"Number of unique ticker symbols found: {unique_tickers:,}")

structured_data.info()

def plot_timeseries(
    ts,
    title,
    keys=["count"],
    y_label=None,
    ylog_scale=False,
    yvalue_multiplier=1,
    yvalue_multiplier_name=None,
    ax=None,
    yticks_step=2,
):
    fig = None
    if ax is None:
        fig, ax = plt.subplots(figsize=(4, 2.5))
    else:
        fig = ax.figure

    # Auto y-label with unit
    if y_label is None:
        y_label = keys[0] if len(keys) == 1 else "Value"
    if yvalue_multiplier == "auto":
        yvalue_multiplier = 1
        max_value = ts[keys].max().max()
        if max_value > 1_000_000_000_000:
            yvalue_multiplier = 1_000_000_000_000
        elif max_value > 1_000_000_000:
            yvalue_multiplier = 1_000_000_000
        elif max_value > 1_000_000:
            yvalue_multiplier = 1_000_000
        elif max_value > 1_000:
            yvalue_multiplier = 1_000

    if yvalue_multiplier != 1:
        if yvalue_multiplier_name is not None:
            y_label = f"{y_label} ({yvalue_multiplier_name})"
        elif yvalue_multiplier == 1_000:
            y_label += " (Thousands)"
        elif yvalue_multiplier == 1_000_000:
            y_label += " (Millions)"
        elif yvalue_multiplier == 1_000_000_000:
            y_label += " (Billions)"
        elif yvalue_multiplier == 1_000_000_000_000:
            y_label += " (Trillions)"
        else:
            y_label += f" (÷{yvalue_multiplier:,})"

    # Apply multiplier
    ts_plot = ts.copy()
    ts_plot[keys] = ts_plot[keys] / yvalue_multiplier

    # Plot each key
    for key in keys:
        ts_plot[key].plot(ax=ax, label=key.capitalize())

    if ylog_scale:
        ax.set_yscale("log")

    ax.set_title(title)
    ax.set_xlabel("Year")
    ax.set_ylabel(y_label)
    ax.set_xticks(np.arange(min_year, max_year + 1, yticks_step))
    ax.grid(axis="x")
    ax.set_axisbelow(True)
    # ax.yaxis.set_major_formatter(mtick.StrMethodFormatter("{x:,.0f}"))

    # Add vertical lines at each year, height = max across all keys
    max_vals = ts_plot[keys].max(axis=1)
    for year in ts_plot.index:
        val = max_vals.get(year, 0)
        if not pd.isna(val):
            ax.vlines(year, 0, val, linestyles=":", colors="grey", alpha=0.5, lw=1)

    for label in ax.get_xticklabels():
        label.set_rotation(45)
    if len(keys) > 1:
        ax.legend()

    plt.tight_layout()
    return fig, ax


filing_counts = (
    structured_data.drop_duplicates(subset=["accessionNo"])
    .groupby(["filedAtYear"])
    .size()
    .to_frame(name="count")
)

fig, ax = plot_timeseries(
    filing_counts,
    title=f"Processed {form_name} filings per Year ({min_year} - {max_year})",
)

form_counts_by_type_and_year = (
    structured_data.drop_duplicates(subset=["accessionNo"])
    .groupby(["filedAtYear", "formType"])
    .size()
    .to_frame(name="count")
    .unstack(fill_value=0)
)


print(f"{form_name} counts from {min_year} to {max_year}.")
form_counts_by_type_and_year

counts_qtr_yr_piv = (
    structured_data.groupby(["filedAtYear", "filedAtQtr"]).size().unstack().fillna(0)
).astype(int)

print(f"{form_name} counts by quarter from {min_year} to {max_year}.")
counts_qtr_yr_piv.T

plt.figure(figsize=(8, 3))

sns.heatmap(
    counts_qtr_yr_piv.T,
    annot=True,  # Display the cell values
    fmt="d",  # Integer formatting
    cmap="magma",  # Color map
    cbar_kws={"label": "Count"},  # Colorbar label
    mask=counts_qtr_yr_piv.T == 0,  # Mask the cells with value 0
    cbar=False,
    annot_kws={"fontsize": 7},
)
plt.grid(False)
plt.title(f"{form_name} Counts by Quarter {min_year} to {max_year}")
plt.xlabel("Year")
plt.ylabel("Quarter")
plt.tight_layout()
plt.show()

counts_qtr_yr = counts_qtr_yr_piv.stack().reset_index(name="count")

fig, ax = plt.subplots(figsize=(7, 2.5))
counts_qtr_yr_piv.plot(kind="bar", ax=ax, legend=True)
ax.legend(title="Quarter", loc="upper right", bbox_to_anchor=(1.15, 1))
ax.set_title(f"Number of {form_name} Filings per Quarter\n({min_year}-{max_year})")
ax.set_xlabel("Year")
ax.set_ylabel(f"Number of\n{form_name} Filings")
ax.yaxis.set_major_formatter(mtick.StrMethodFormatter("{x:,.0f}"))
ax.grid(axis="x")
ax.set_axisbelow(True)
plt.tight_layout()
plt.show()

counts_month_yr_piv = (
    structured_data.groupby(["filedAtYear", "filedAtMonth"]).size().unstack().fillna(0)
).astype(int)

plt.figure(figsize=(7, 5))
sns.heatmap(
    counts_month_yr_piv,
    annot=True,
    fmt="d",
    cmap="magma",
    cbar_kws={"label": "Count"},
    mask=counts_month_yr_piv == 0,
    cbar=False,
    annot_kws={"size": 7},
)
# convert x-labels to month names: 1 => Jan, 2 => Feb, etc.
plt.xticks(
    ticks=np.arange(0.5, 12.5, 1),
    labels=[pd.to_datetime(str(i), format="%m").strftime("%b") for i in range(1, 13)],
)
plt.grid(False)
plt.title(f"{form_name} Counts by Month ({min_year} - {max_year})")
plt.xlabel("")
plt.ylabel("Year")
plt.tight_layout()
plt.show()

print(
    f"Descriptive statistics for {form_name} filing counts by month from {min_year} to {max_year}."
)
month_stats = (
    counts_month_yr_piv.loc[2004:]
    .describe(percentiles=[0.025, 0.975])
    .round(0)
    .astype(int)
)
month_stats

def plot_box_plot_as_line(
    data: pd.DataFrame,
    x_months=True,
    title="",
    x_label="",
    x_pos_mean_label=2,
    pos_labels=None,
    pos_high_low=None,
    y_label="",
    y_formatter=lambda x, p: "{:.0f}".format(int(x) / 1000),
    show_high_low_labels=True,
    show_inline_labels=True,
    show_bands=True,
    figsize=(4, 2.5),
    line_source="mean",
):
    fig, ax = plt.subplots(figsize=figsize)

    line_to_plot = data[line_source]
    lower_label = "2.5%"
    upper_label = "97.5%"
    lower = data[lower_label]
    upper = data[upper_label]

    line_to_plot.plot(ax=ax)

    if show_bands:
        ax.fill_between(line_to_plot.index, lower, upper, alpha=0.2)

    if x_months:
        ax.set_xlim(0.5, 12.5)
        ax.set_xticks(range(1, 13))
        ax.set_xticklabels(["J", "F", "M", "A", "M", "J", "J", "A", "S", "O", "N", "D"])

    ax.yaxis.set_major_formatter(mtick.FuncFormatter(y_formatter))
    ax.set_ylabel(y_label)
    ax.set_xlabel(x_label)
    ax.set_title(title)

    ymin, ymax = ax.get_ylim()
    y_scale = ymax - ymin

    max_x = int(line_to_plot.idxmax())
    max_y = line_to_plot.max()
    min_x = int(line_to_plot.idxmin())
    min_y = line_to_plot.min()

    ax.axvline(
        max_x,
        ymin=0,
        ymax=((max_y - ymin) / (ymax - ymin)),
        linestyle="dashed",
        color="tab:blue",
        alpha=0.5,
    )
    ax.scatter(max_x, max_y, color="tab:blue", s=10)
    ax.axvline(
        min_x,
        ymin=0,
        ymax=((min_y - ymin) / (ymax - ymin)),
        linestyle="dashed",
        color="tab:blue",
        alpha=0.5,
    )
    ax.scatter(min_x, min_y, color="tab:blue", s=10)

    x_pos_mean_label_int = int(x_pos_mean_label)
    if show_inline_labels:
        mean_x = x_pos_mean_label
        mean_y = line_to_plot.iloc[x_pos_mean_label_int] * 1.02
        upper_x = x_pos_mean_label
        upper_y = upper.iloc[x_pos_mean_label_int]
        lower_x = x_pos_mean_label
        lower_y = lower.iloc[x_pos_mean_label_int] * 0.95

        if pos_labels:
            mean_x = pos_labels["mean"]["x"]
            mean_y = pos_labels["mean"]["y"]
            upper_x = pos_labels["upper"]["x"]
            upper_y = pos_labels["upper"]["y"]
            lower_x = pos_labels["lower"]["x"]
            lower_y = pos_labels["lower"]["y"]

        ax.text(mean_x, mean_y, "Mean", color="tab:blue", fontsize=8)
        ax.text(upper_x, upper_y, upper_label, color="tab:blue", fontsize=8)
        ax.text(lower_x, lower_y, lower_label, color="tab:blue", fontsize=8)

    if show_high_low_labels:
        high_x_origin = max_x
        high_y_origin = max_y
        high_x_label = high_x_origin + 0.5
        high_y_label = high_y_origin + 0.1 * y_scale
        if pos_high_low:
            high_x_label = pos_high_low["high"]["x"]
            high_y_label = pos_high_low["high"]["y"]
        ax.annotate(
            "High",
            (high_x_origin, high_y_origin),
            xytext=(high_x_label, high_y_label),
            arrowprops=dict(facecolor="black", arrowstyle="->"),
        )

        low_x_origin = min_x * 1.01
        low_y_origin = min_y
        low_x_label = low_x_origin + 1.5
        low_y_label = low_y_origin - 0.1 * y_scale
        if pos_high_low:
            low_x_label = pos_high_low["low"]["x"]
            low_y_label = pos_high_low["low"]["y"]
        ax.annotate(
            "Low",
            (low_x_origin, low_y_origin),
            xytext=(low_x_label, low_y_label),
            arrowprops=dict(facecolor="black", arrowstyle="->"),
        )

    ax.grid(axis="x")
    ax.set_axisbelow(True)

    plt.tight_layout()
    plt.show()


plot_box_plot_as_line(
    data=month_stats.T,
    title=f"Descriptive Statistics for {form_name} Filings by Month\n({min_year} - {max_year})",
    x_label="Month",
    y_label="Number of\n{form_name} Filings",
    y_formatter=lambda x, p: "{:.0f}".format(int(x)),
    x_pos_mean_label=2,
)

fig, ax = plt.subplots(1, 1, figsize=(4, 2))

cnt = 0

data = (
    structured_data.groupby(["filedAtYear", "filedAtMonth"])
    .size()
    .unstack()
    .fillna(0)
    .reindex(columns=range(1, 13), fill_value=0)  # ensure all month are included
)

# if data.sum().sum() < 100:
#     continue
try:
    data.boxplot(
        ax=ax,
        grid=False,
        showfliers=False,
        flierprops=dict(marker="o", markersize=3),
        patch_artist=True,
        boxprops=dict(facecolor="white", color="tab:blue"),
        showmeans=True,
        meanline=True,
        meanprops={"color": "tab:blue", "linestyle": ":"},
        medianprops={"color": "black"},
        capprops={"color": "none"},
    )

    ax.set_xlabel("")
    ax.set_ylabel(f"Form {form_name} Count")
    xticklabels = [
        pd.to_datetime(str(x), format="%m").strftime("%b") for x in range(1, 13)
    ]
    ax.set_xticklabels(xticklabels)
    ax.tick_params(axis="x", rotation=45)
except Exception as e:
    print(f"Error: {e}")


fig.suptitle(f"{form_name} Filings by Month\n({min_year} - {max_year})")
plt.tight_layout()
plt.show()

counts_dayOfWeek = (
    structured_data.drop_duplicates(subset=["accessionNo"])
    .groupby(["filedAtDayOfWeek"])
    .size()
    .to_frame(name="Count")
).rename_axis("Day of the Week")
counts_dayOfWeek["Pct"] = (
    counts_dayOfWeek["Count"].astype(int) / counts_dayOfWeek["Count"].astype(int).sum()
).map("{:.0%}".format)
counts_dayOfWeek["Count"] = counts_dayOfWeek["Count"].map(lambda x: f"{x:,}")

print(f"{form_name} filing counts by day of the week ({min_year} - {max_year}).")
counts_dayOfWeek.loc[["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]]

structured_data[
    (structured_data["firstEntityCik"] == "1318605")
    & (structured_data["filedAtYear"].isin(range(2016, 2020)))
]

structured_data["rank"] = structured_data.groupby(["year", "firstEntityCik"])[
    "filedAt"
].rank(method="dense", ascending=False)

# Keep only the most recent records (rank 1)
structured_data = (
    structured_data[structured_data["rank"] == 1]
    .drop(columns="rank")
    .reset_index(drop=True)
)
structured_data.info()
structured_data.head(5)

analysis_df = structured_data.copy()
analysis_df = analysis_df[analysis_df["year"].between(2002, 2026)]

# Calculate percentiles per year
percentiles = analysis_df.groupby("year")["auditFees"].transform

analysis_df["audit_fees_5th"] = percentiles(lambda x: x.quantile(0.05))
analysis_df["audit_fees_95th"] = percentiles(lambda x: x.quantile(0.95))
print(f"5th percentile (min): {analysis_df['audit_fees_5th'].min()}")
print(f"95th percentile (max): {analysis_df['audit_fees_95th'].max()}")

# Filter rows within 5th and 95th percentile for their year
analysis_df = analysis_df[
    (analysis_df["auditFees"] >= analysis_df["audit_fees_5th"])
    & (analysis_df["auditFees"] <= analysis_df["audit_fees_95th"])
]

# Optional: drop temporary columns if no longer needed
analysis_df.drop(columns=["audit_fees_5th", "audit_fees_95th"], inplace=True)

data = analysis_df["auditFees"].dropna()

plot_split_histograms(
    data,
    value_splits=[5_000_000],
    x_label="Audit Fees in USD",
    y_label="Count",
    title=f"Audit Fees Distribution",
    value_multiplier="auto",
)

basic_stats_table(data).T

# Group by year and calculate stats
audit_fees = analysis_df.groupby("year")["auditFees"].agg(
    min="min", max="max", mean="mean", median="median"
)

fig, axes = plt.subplots(nrows=3, figsize=(3.5, 5))
fig.suptitle(f"Audit Fees\nreported in {form_name} Filings ({min_year} - {max_year})")
axes = axes.flatten()
for ax, key in zip(axes, ["mean", "median", "max"]):
    pretty_key = key.capitalize()
    plot_timeseries(
        ts=audit_fees,
        title=f"{pretty_key}",
        keys=[key],
        ylog_scale=False,
        yvalue_multiplier=1_000_000,
        ax=ax,
        yticks_step=2,
    )

data = analysis_df["auditRelatedFees"].dropna()

plot_split_histograms(
    data,
    value_splits=[100_000, 1_000_000],
    x_label="Audit-related fees in USD",
    y_label="Count",
    title=f"Audit-related fees Distribution",
    value_multiplier="auto",
)

basic_stats_table(data).T

# Group by year and calculate stats
audit_related_fees = (
    analysis_df[analysis_df["year"].between(2000, 2026)]
    .groupby("year")["auditRelatedFees"]
    .agg(min="min", max="max", mean="mean", median="median")
)

fig, axes = plt.subplots(nrows=3, figsize=(3.5, 5))
fig.suptitle(
    f"Audit-related fees\nreported in {form_name} Filings ({min_year} - {max_year})"
)
axes = axes.flatten()
for ax, key in zip(axes, ["mean", "median", "max"]):
    pretty_key = key.capitalize()
    plot_timeseries(
        ts=audit_related_fees,
        title=f"{pretty_key}",
        keys=[key],
        ylog_scale=False,
        yvalue_multiplier=1_000_000,
        ax=ax,
        yticks_step=2,
    )

data = analysis_df["taxFees"].dropna()
data = data[data >= 0]

plot_split_histograms(
    data,
    value_splits=[100_000, 1_000_000],
    x_label="Tax Fees in USD",
    y_label="Count",
    title=f"Tax Fees Distribution",
    value_multiplier="auto",
)

basic_stats_table(data).T

# Group by year and calculate stats
tax_fees = (
    analysis_df[analysis_df["year"].between(2000, 2026)]
    .groupby("year")["taxFees"]
    .agg(min="min", max="max", mean="mean", median="median")
)

fig, axes = plt.subplots(nrows=3, figsize=(3.5, 5))
fig.suptitle(f"Tax Fees\nreported in {form_name} Filings ({min_year} - {max_year})")
axes = axes.flatten()
for ax, key in zip(axes, ["mean", "median", "max"]):
    pretty_key = key.capitalize()
    plot_timeseries(
        ts=tax_fees,
        title=f"{pretty_key}",
        keys=[key],
        ylog_scale=False,
        yvalue_multiplier=1_000_000,
        ax=ax,
        yticks_step=2,
    )