Analysis of Accounting and Auditing Enforcement Releases

from sec_api import AaerApi

aaerApi = AaerApi("YOUR_API_KEY")

YEARS = range(2025, 1996, -1)  # from 2025 to 1997
TARGET_FILE = "./data/aaer-structured-data.jsonl"

if not os.path.exists(TARGET_FILE):
    for year in YEARS:
        done = False
        search_from = 0
        year_counter = 0

        # fetch all AAERs for the given year
        while not done:
            searchRequest = {
                "query": f"dateTime:[{year}-01-01 TO {year}-12-31]",
                "from": search_from,
                "size": "50",
                "sort": [{"dateTime": {"order": "desc"}}],
            }

            response = aaerApi.get_data(searchRequest)

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

            search_from += 50
            year_counter += len(response["data"])

            with open(TARGET_FILE, "a") as f:
                for entry in response["data"]:
                    f.write(json.dumps(entry) + "\n")

        print(f"Finished loading {year_counter} AAERs for year {year}")
else:
    print("File already exists, skipping download")

aaers = pd.read_json(TARGET_FILE, lines=True)

# convert data types
bool_cols = ["hasAgreedToSettlement", "hasAgreedToPayPenalty"]
aaers[bool_cols] = aaers[bool_cols].astype(bool)
aaers["dateTime"] = pd.to_datetime(aaers["dateTime"], utc=True)
aaers["dateTime"] = aaers["dateTime"].dt.tz_convert("US/Eastern")
aaers["dateTimeYear"] = aaers["dateTime"].dt.year
aaers["dateTimeMonth"] = aaers["dateTime"].dt.month
aaers["dateTimeYearMonth"] = aaers["dateTime"].dt.to_period("M")
# Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
aaers["dateTimeDay"] = aaers["dateTime"].dt.day_name()
# dateTimeClass: preMarket (4:00 AM to 9:30 AM), regularMarket (9:30 AM to 4:00 PM), afterMarket (4:00 PM to 8:00 PM), postMarket (8:00 PM to 10:00 PM)
aaers["dateTimeClass"] = aaers["dateTime"].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 "postMarket"
        )
    )
)

print(f"Loaded {len(aaers)} AAERs in total for {YEARS[-1]} to {YEARS[0]}")
print(aaers.info())

aaers.head()

df_year_month = aaers.pivot_table(
    index="dateTimeYear",
    columns="dateTimeMonth",
    values="id",
    aggfunc="count",
    fill_value=0,
)
df_year_month_pretty = df_year_month.copy()
# convert col 1 to 12 to month names, eg 1 => Jan, 2 => Feb, etc
df_year_month_pretty.columns = df_year_month_pretty.columns.map(
    lambda x: pd.to_datetime(str(x), format="%m").strftime("%b")
)

total_col = df_year_month_pretty.sum(axis=1)
mean_col = round(df_year_month_pretty.mean(axis=1), 0)
median_col = round(df_year_month_pretty.median(axis=1), 0)

df_year_month_pretty["total"] = total_col
df_year_month_pretty["mean"] = mean_col
df_year_month_pretty["median"] = median_col

total_row = df_year_month_pretty.sum(axis=0)
mean_row = round(df_year_month_pretty.mean(axis=0), 0)
median_row = round(df_year_month_pretty.median(axis=0), 0)

df_year_month_pretty.loc["total"] = total_row
df_year_month_pretty.loc["mean"] = mean_row
df_year_month_pretty.loc["median"] = median_row

df_year_month_pretty = df_year_month_pretty.astype(int)

print("Accounting and Auditing Enforcement Releases by Year and Month")
df_year_month_pretty

fig, ax = plt.subplots(figsize=(5, 3))

data_to_plot = df_year_month_pretty.loc[2000:2024].copy()
data_to_plot.index = data_to_plot.index.astype(int)
data_to_plot["total"].plot(kind="line", ax=ax)

ax.set_title("Audit and Accounting Enforcement Releases\nby Year (2000-2024)")
ax.set_xlabel("Year")
ax.set_ylabel("Number of AAERs")
ax.set_axisbelow(True)
ax.set_xticks(data_to_plot.index[::2])
plt.xticks(rotation=90)

for year in data_to_plot.index:
    year_y_max = data_to_plot.loc[year, "total"]
    ax.vlines(year, 0, year_y_max, linestyles=":", colors="grey", alpha=0.5, lw=1)

plt.grid(axis="x")
plt.tight_layout()
plt.show()

fig, ax = plt.subplots(figsize=(3.5, 3))

df_year_month.loc[2000:2024].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_title("AAERs by Month\n(2000 - 2024)")
ax.set_xlabel("Month")
ax.set_ylabel("AAERs Count")
xticklabels = [pd.to_datetime(str(x), format="%m").strftime("%b") for x in range(1, 13)]
ax.set_xticklabels(xticklabels)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

counts_dayOfWeek = (
    aaers[aaers["dateTimeYear"].between(2000, 2024)]
    .groupby(["dateTimeDay"])
    .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"AAER disclosures by day of the week (2000 - 2024).")
counts_dayOfWeek.loc[["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]]

counts_filedAtClass = (
    aaers[aaers["dateTimeYear"].between(2000, 2024)]
    .groupby(["dateTimeClass"])
    .size()
    .sort_values(ascending=False)
    .to_frame(name="Count")
).rename_axis("Publication Time")
counts_filedAtClass["Pct"] = (
    counts_filedAtClass["Count"].astype(int)
    / counts_filedAtClass["Count"].astype(int).sum()
).map("{:.0%}".format)
counts_filedAtClass["Count"] = counts_filedAtClass["Count"].map(lambda x: f"{x:,}")
counts_filedAtClass = counts_filedAtClass.reindex(
    ["preMarket", "regularMarket", "afterMarket"]
)
counts_filedAtClass.index = (
    counts_filedAtClass.index.str.replace("preMarket", "Pre-Market (4:00 - 9:30 AM)")
    .str.replace("regularMarket", "Market Hours (9:30 AM - 4:00 PM)")
    .str.replace("afterMarket", "After Market (4:00 - 8:00 PM)")
)

print(
    f"AAER disclosures by pre-market, regular market hours,\nand after-market publication time (2000 - 2024)."
)
counts_filedAtClass

counts_hasAgreedToPayPenalty = (
    aaers[aaers["dateTimeYear"].between(2000, 2024)]
    .groupby(["hasAgreedToPayPenalty"])
    .size()
    .to_frame(name="Count")
).rename_axis("Has Agreed to Pay Penalty")
counts_hasAgreedToPayPenalty["Pct"] = (
    counts_hasAgreedToPayPenalty["Count"].astype(int)
    / counts_hasAgreedToPayPenalty["Count"].astype(int).sum()
).map("{:.0%}".format)
counts_hasAgreedToPayPenalty["Count"] = counts_hasAgreedToPayPenalty["Count"].map(
    lambda x: f"{x:,}"
)

print(
    f"AAER disclosures by whether the company has agreed to pay a penalty (2000 - 2024)."
)
counts_hasAgreedToPayPenalty

penality_amounts = aaers.explode("penaltyAmounts").copy()
penality_amounts["penaltyAmount"] = penality_amounts["penaltyAmounts"].apply(
    lambda x: x["penaltyAmount"] if isinstance(x, dict) else np.nan
)
penality_amounts["penaltyAmount"] = penality_amounts["penaltyAmount"].astype(float)
penality_amounts["penaltyAmount"] = penality_amounts["penaltyAmount"] / 1000
penality_amounts["penaltyAmount"].describe().apply(lambda x: f"{x:,.0f}").to_frame()

penalties_year = penality_amounts.groupby("dateTimeYear")["penaltyAmount"].sum()
penalties_year = penalties_year.astype(int)
penalties_year = pd.DataFrame(penalties_year)
penalties_year["penaltyAmount"] = round(penalties_year["penaltyAmount"] / 1_000, 2)
print("Total Penalties in Million USD by Year")
penalties_year.map(lambda x: f"{x:,.1f}")

fig, ax = plt.subplots(figsize=(5, 3))

data_to_plot = penalties_year.loc[2000:2024].copy()
data_to_plot["penaltyAmount"].plot(kind="line", ax=ax)

for year in data_to_plot.index:
    year_y_max = data_to_plot.loc[year, "penaltyAmount"]
    ax.vlines(year, 0, year_y_max, linestyles=":", colors="grey", alpha=0.5, lw=1)

ax.set_xticks(data_to_plot.index[::2])
plt.xticks(rotation=90)
ax.get_yaxis().set_major_formatter(plt.FuncFormatter(lambda x, loc: "{:,}".format(int(x))))
ax.set_title("AAER Penalties per Year")
ax.set_xlabel("Year")
ax.set_ylabel("Penalty Amount\nin Million USD")
plt.tight_layout()
plt.grid(axis="x")
ax.set_axisbelow(True)
plt.show()

all_tags = []

for i, row in aaers.iterrows():
    tags = row["tags"]
    if isinstance(tags, list):
      all_tags.extend(tags)

all_tags = pd.Series(all_tags)
all_tags = all_tags.value_counts().reset_index()
all_tags.columns = ["tag", "count"]

print("Top 10 Tags in AAER Releases from 1997 to 2025")
all_tags.head(10)

# count all unique violatedSections
all_violated_sections = []

for i, row in aaers.iterrows():
    violatedSections = row["violatedSections"]
    if isinstance(violatedSections, list):
        all_violated_sections.extend(violatedSections)

all_violated_sections = pd.Series(all_violated_sections)
all_violated_sections = all_violated_sections.value_counts().reset_index()

all_violated_sections.columns = ["violatedSections", "count"]

print("Top 10 Violated Securities Laws in SEC Litigation Releases")
all_violated_sections.head(10)