---
title: "Working with Form AP data"
author: Ian D. Gow
date: 2024-05-16
date-modified: 2026-10-25
date-format: "D MMMM YYYY"
categories: [PCAOB, Form AP, R, DuckDB, Data curation]
keywords: [PCAOB, Form AP, R, DuckDB, Data curation]
format:
  html:
    colorlinks: true
    fig-format: png
    include-in-header:
      text: |
        <style>
        main .cell-output-display { overflow-x: auto; }
        main :not(pre) > code { white-space: normal; overflow-wrap: anywhere; }
        </style>
  pdf: 
    colorlinks: true
    geometry:
      - left=2.5cm
      - right=2.5cm
    papersize: a4
    mainfont: TeX Gyre Pagella
    mathfont: TeX Gyre Pagella Math
bibliography: papers.bib
execute:
  freeze: auto
---

This note was first shared on [LinkedIn on 16 May 2024](https://www.linkedin.com/posts/iangow_working-with-form-ap-data-activity-7196971381010432001-Isyc).
This restored HTML version retains the original case study, with updated code and data-location instructions.
The [original PDF](form_ap_names.pdf) preserves the earlier output; counts below depend on the version of the PCAOB data used to run the code.
The follow-up note, [Missing Form APs?](published/missing_form_aps.html), compares Form AP filings with auditor information in SEC filings.

The United States Public Company Accounting Oversight Board (PCAOB) requires registered public accounting firms to file on Form AP for each audit report.^[Discussion of the history of the PCAOB is provided by @Gow:2018vf.]
Data from these filings are made available by the PCAOB as part of its AuditorSearch page.
[The PCAOB](https://pcaobus.org/resources/auditorsearch) describes AuditorSearch as "a public database of engagement partners and audit firms participating in audits of U.S. public companies."

These Form AP data recently featured in a tongue-in-cheek *Financial Times* [article by George Steer](https://www.ft.com/content/2556fab5-d168-4d68-aedb-9be4b5ab739d) that showed that the auditor Ben Borgers had used 14 different names---including the name "Ben F orgers"---on Form AP filings.
The analysis of the Form AP data in the FT article had been conducted by independent researcher [Stephen Walker](https://www.stephenwalker.me) and below I show how to reproduce Stephen's analysis.

The purpose of this note is to demonstrate how one can work with Form AP data using R.
Working with these data is a nice exercise for developing some basic data science skills.
All the software needed to produce the analyses in this note is free and can be installed in less than ten minutes.
So it should be easy to work through everything I do here.^[An internet connection is required to download the data and software.]
A fast-paced hands-on tutorial for R and some of the ideas here is provided in Chapter 2 of *Empirical Research in Accounting: Tools and Methods* [here](https://iangow.github.io/far_book/r-intro.html).^[*Empirical Research in Accounting: Tools and Methods* was published in print form by CRC Press in 2024 and remains free online.]

In writing this note, I use the packages listed below.^[Execute `install.packages(c("tidyverse", "DBI", "dbplyr", "duckdb", "jsonlite", "arrow"))` within R to install all the packages you need to run the code in this note.]
This note was written using [Quarto](https://quarto.org) and compiled with [RStudio](https://posit.co/products/open-source/rstudio/), an integrated development environment (IDE) for working with R.
The source code for this note is available [here](https://raw.githubusercontent.com/iangow/notes/main/form_ap_names.qmd).

```{r}
#| include: false
options(width = 75)
options(tibble.width = 75)
```

```{r}
#| message: false
#| include: false
library(tidyverse)
library(DBI)
library(dbplyr)
library(jsonlite)
library(arrow)
```

## Getting Form AP data

The Form AP data are made available by the PCAOB as a single compressed comma-separated value (CSV) file.
Set the `HTTPUserAgent` option to include your email address when downloading data using a script, as in the R code below.^[Use your actual email address here.]

```{r}
#| eval: false
options(HTTPUserAgent = "your_name@some_email.com")
```

The download script uses two environment variables: `RAW_DATA_DIR` for downloaded source files and `DATA_DIR` for processed Parquet files.
If you already use these variables, keep your existing settings.
Otherwise, choose directories on your computer, for example:

```{r}
#| eval: false
Sys.setenv(RAW_DATA_DIR = "~/research/raw_data",
           DATA_DIR = "~/research/parquet")
```

The script creates a `pcaob` subdirectory under each directory.
It saves the original download as `RAW_DATA_DIR/pcaob/FirmFilings.zip` and the processed data as `DATA_DIR/pcaob/form_aps.parquet`.
An existing ZIP is reused, so rerunning the script does not replace the raw download.

```{r}
#| include: false
fix_names <- function(names) {
  names <- tolower(gsub("\\s+", "_", names))
  names <- gsub("<", "lt", names)
  names <- gsub(">", "gt", names)
  names <- gsub("[)(_]+", "_", names)
  names
}
```

Once we have set `HTTPUserAgent`, we can run the script available [here](https://raw.githubusercontent.com/iangow/notes/main/get_form_aps.R) by executing the following line in R.
This script takes about 10 seconds to run for me, but it may take longer if you have a slower connection to the PCAOB website than I do.

```{r}
#| eval: false
source("https://raw.githubusercontent.com/iangow/notes/main/get_form_aps.R")
```

This script downloads and processes the Form AP data into a parquet file.
The parquet format is described in *R for Data Science* [@Wickham:2023aa, p. 393] as "an open standards-based format widely used by big data systems."
Parquet files provide a format optimized for data analysis, with a rich type system.
More details on the parquet format can be found in [Chapter 22](https://r4ds.hadley.nz/arrow) of *R for Data Science* and a guide to creating a parquet data repository are provided [here](https://iangow.github.io/far_book/parquet-wrds.html).

For reasons we will explain below, we use a DuckDB database connection to work with the data.
We start by creating that connection and loading the parquet file we just created.

```{r}
#| message: false
db <- dbConnect(duckdb::duckdb())
data_dir <- Sys.getenv("DATA_DIR")
if (!nzchar(data_dir)) stop("Set DATA_DIR before running this note.")
parquet_file <- file.path(path.expand(data_dir), "pcaob", "form_aps.parquet")
if (!file.exists(parquet_file)) {
  stop("Run get_form_aps.R to create ", parquet_file)
}
form_aps <- tbl(db, sql(paste0("SELECT * FROM read_parquet(",
                             dbQuoteString(db, parquet_file), ")")))
```

For the most part, the data in the Form AP file are straightforward.
However, two sets of fields create more work than the others.

### JSON data

The first set of fields in the Form AP data that require more effort are the three stored as JSON:

 - `audit_fund_series`
 - `audit_not_divided_percent_information`
 - `audit_divided_information`

JSON stands for "Javascript object notation" and is a common form of data on the internet.
For example, many web APIs return data in JSON form.
More on JSON can be found in [Chapter 23](https://r4ds.hadley.nz/rectangling.html#json) of *R for Data Science*.

The script we used to create `form_aps.parquet` does not process the JSON data; these are retained as simple text data.
However, we could easily process these columns if we wanted to do so.
DuckDB also has functions for processing JSON values stored in table columns.
Here I bring the data into R (using the `collect()` function) and use the `parse_json()` function from the `jsonlite` library to illustrate an alternative approach.

Here we illustrate one approach using the column `audit_fund_series`.
We use `rowwise()` because `parse_json()` is designed to work with one value at a time and `list()` to allow multiple values to be stored in a single row.
To understand what the `unnest_longer()` and `unnest_wider()` functions are doing, it may be helpful to examine the input to and output from those functions.
This can be achieved by selecting and executing the code preceding the pipes (i.e., `|>`) before and after the respective functions.
More about pipes can be found in [Chapter 4](https://r4ds.hadley.nz/workflow-style.html#sec-pipes) of *R for Data Science*.

```{r}
fund_series <-
  form_aps |>
  select(form_filing_id, audit_fund_series) |> 
  filter(!is.na(audit_fund_series)) |>
  collect() |>
  rowwise() |>
  mutate(json = list(parse_json(audit_fund_series))) |>
  unnest_longer(json) |> 
  unnest_wider(json) |>
  select(-audit_fund_series)
```

In effect, we process the data in this column into a new table, `fund_series`.
This `fund_series` table could be copied to DuckDB and joined with `form_aps` as needed.

```{r}
fund_series
```  

### Dates and times

The second set of columns that requires careful handling are the five columns containing dates or date-times.

 - `audit_report_date`: Date of the audit report (`mm/dd/yyyy`)
 - `fiscal_period_end_date`: The end date of the most recent period's financial statements identified in the audit report
(`mm/dd/yyyy`)
 - `signed_date`: Date of typed and manual signatures (`mm/dd/yyyy`)
 - `audit_dual_date`: The date of the dual-date information, if any.
 - `filing_date`: Date and time that the Form AP was filed.
 
Some clean-up is required for the first three fields, as these are not always in the indicated `mm/dd/yyyy` format.
The `audit_dual_date` field can contain multiple dates and requires special handling.
Finally, `filing_date` contains both a date and a time and I process it accordingly.
According to the PCAOB-provided [data dictionary](https://assets.pcaobus.org/pcaob-dev/docs/default-source/rusdocuments/auditorsearch-form-ap-data-references.pdf), "dates and times are provided based on the U.S. Eastern time zone" and I process these fields using that assumption.

The following code shows the most recent dates in the local data.
These dates describe the downloaded snapshot; rerunning the analysis with a newer download will change the results.

```{r}
form_aps |> 
  select(ends_with("date")) |>
  select(-audit_dual_date) |>
  summarize(across(everything(), \(x) max(x, na.rm = TRUE))) |>
  collect()
```

The anonymous function is needed to exclude missing values (`NA` in R).
More on missing values is found in [Chapter 18](https://r4ds.hadley.nz/missing-values) of *R for Data Science*.

Note that the `unnest()` function is needed to analyse `audit_dual_date`, as `audit_dual_date` contains a list of dates.
More on list-columns can be found in [Chapter 23](https://r4ds.hadley.nz/rectangling.html#lists) of *R for Data Science*.

```{r}
form_aps |> 
  select(audit_dual_date) |>
  mutate(audit_dual_date = unnest(audit_dual_date)) |>
  filter(!is.na(audit_dual_date)) |>
  summarize(max = max(audit_dual_date, na.rm = TRUE)) |>
  collect()
```



## Partner names

We now combine the first, middle, and last names of each partner into a single name.
This step is facilitated by our use of DuckDB, as missing values (e.g., cases where partners have no middle name) are quietly ignored.^[If we working with `dplyr` and native data frames, missing values would be "infectious" with `str_c()`.
Such infectiousness is often the desired behaviour, but in this case, we want to quietly discard missing components of names. 
That is we want `str_c("Ian", NA, "Gow")` to be `"Ian Gow"` rather than the `NA` it would be if we used `str_c()` with native R data.
More on missing values can be found in [Chapter 18](https://r4ds.hadley.nz/missing-values) of *R for Data Science*.]


```{r}
form_aps_names <-
  form_aps |> 
  mutate(engagement_partner_name = 
           str_c(engagement_partner_first_name,
                 engagement_partner_middle_name,
                 engagement_partner_last_name, sep = " ")) |>
  select(form_filing_id, engagement_partner_id, engagement_partner_name)
```

Note that `dplyr` is quietly translating our code into SQL, as can be seen by applying `show_query()` to `form_aps_names`.
Here `str_c()` is translated into the SQL function `concat_ws()` (see [here](https://duckdb.org/docs/sql/functions/char#concat_wsseparator-string-) for details).
For display below, I abbreviate the configured data directory as `DATA_DIR`.

```{r}
#| eval: false
form_aps_names |>
  show_query()
```
```{r}
#| echo: false
sql <- remote_query(form_aps_names)
sql <- str_replace(sql, fixed(parquet_file), "DATA_DIR/pcaob/form_aps.parquet")
cat("<SQL>\n", str_wrap(sql, width = 80, exdent = 4))
```

SQL is a specialized language for manipulating and retrieving tabular data used by almost all modern database systems.
More on SQL is provided in [an appendix](https://iangow.github.io/far_book/sql-primer.html) to *Empirical Research in Accounting: Tools and Methods* and in [Chapter 21](https://r4ds.hadley.nz/databases) of *R for Data Science*.

In @tbl-mult-names, we show the number of different names associated with a sample of engagement partners.
While we could provide a table the number of names associated with each value of `engagement_partner_id`, `engagement_partner_id` has no real meaning for readers.
Instead, for each value of `engagement_partner_id`, we identify a unique name (the most frequent value of `engagement_partner_name`) that we use for this purpose and store this value in `most_common_names`.^[We use `group_by(engagement_partner_id)` then `window_order(desc(n_forms))` to organize the data in descending order of the frequency a name appears for a given `engagement_partner_id` and then `filter(row_number() == 1)` to pick off the first row for each value of `engagement_partner_id`.]

```{r}
most_common_names <-
  form_aps_names |>
  count(engagement_partner_id, engagement_partner_name, name = "n_forms") |>
  group_by(engagement_partner_id) |>
  window_order(desc(n_forms)) |>
  filter(row_number() == 1) |>
  ungroup() |>
  select(engagement_partner_id, engagement_partner_name)
```

We count the number of unique names for each `engagement_partner_id` using `n_distinct()` and then merge the result with `most_common_names` to create the data frame `names_df` that we can use to produce tables and figures.

```{r}
names_df <-
  form_aps_names |> 
  group_by(engagement_partner_id) |>
  summarize(n_names = n_distinct(engagement_partner_name)) |>
  inner_join(most_common_names, by = "engagement_partner_id") 
```

We first produce @tbl-mult-names, where we can already see that Ben F Borgers is an outlier.

```{r}
#| label: tbl-mult-names
#| tbl-cap: Auditors with most reported spellings
#| render: !expr function(x, ...) knitr::knit_print(knitr::kable(x))
names_df |> 
  filter(n_names >= 6) |>
  select(engagement_partner_name, n_names) |>
  arrange(desc(n_names)) 
```

We next examine the various names under which Ben F Borgers has filed in @tbl-ben-names.
To be fair to "Ben F orgers", the vast majority of filings by Ben F Borgers use the name "Ben F Borgers" and most of the rest use the name "Ben Borgers".

```{r}
#| label: tbl-ben-names
#| tbl-cap: The many names of Ben F orgers
#| render: !expr function(x, ...) knitr::knit_print(knitr::kable(x))
form_aps_names |> 
  filter(engagement_partner_id == "504100001") |> 
  count(engagement_partner_name) |> 
  mutate(perc = round(n / sum(n, na.rm = TRUE) * 100, 2)) |>
  arrange(desc(n))
```
I next produce @fig-ft, which is almost identical to the plot in the [FT article](https://www.ft.com/content/2556fab5-d168-4d68-aedb-9be4b5ab739d) cited above.

```{r}
#| label: fig-ft
#| fig-cap: Distribution of auditors by number of reported spellings
#| echo: false
names_df |>  
  count(n_names, name = "count") |>
  ggplot(aes(x = n_names, y = count)) +
  geom_col()
```

A weakness of @fig-ft is that it is difficult to discern any information for values of `n_names` greater than 5.
One approach to address this is to use a log-transformation.
@fig-ft-log1p depicts the result of transforming the $y$-axis using $log(1 + y)$.

```{r}
#| label: fig-ft-log1p
#| fig-cap: Distribution of auditors by number of reported spellings ($log(1 + y)$)
#| echo: false
names_df |>  
  count(n_names, name = "count") |>
  ggplot(aes(x = n_names, y = count)) +
  geom_col() +
  scale_y_continuous(transform = "log1p")
```

## Busy auditors

One possible red flag with the case of Ben F Borgers is the sheer number of audit engagements he has been involved in.
Is Borgers an outlier in this regard too?

```{r}
#| label: tbl-many-audits
#| tbl-cap: Auditors with the most audits
#| render: !expr function(x, ...) knitr::knit_print(knitr::kable(x))
form_aps |>
  group_by(engagement_partner_id) |>
  summarize(n_audits = n(), .groups = "drop") |>
  inner_join(most_common_names, by = "engagement_partner_id") |>
  arrange(desc(n_audits)) |>
  select(engagement_partner_name, n_audits) |>
  collect(n = 10)
```
In the original analysis, Borgers barely cracked the top ten.
The ranking changes as more filings become available; @tbl-many-audits shows the result for the local snapshot.
What's going on here?

In @tbl-many-audits-type, I add information about the value of `audit_report_type`.
It turns out that the "top auditors" are generally auditing investment companies.
It seems plausible that such audits are quite perfunctory and a single engagement covers many companies (e.g., audits of ETFs or mutual funds).

```{r}
#| label: tbl-many-audits-type
#| tbl-cap: Auditors with the most audits by type
#| render: !expr function(x, ...) knitr::knit_print(knitr::kable(x))
form_aps |>
  mutate(audit_report_type = str_replace(audit_report_type, ",.*$", "")) |>
  group_by(engagement_partner_id, audit_report_type) |>
  summarize(n_audits = n(), .groups = "drop") |>
  inner_join(most_common_names, by = "engagement_partner_id") |>
  arrange(desc(n_audits)) |>
  select(engagement_partner_name, audit_report_type, n_audits) |>
  collect(n = 10)
```
So, in @tbl-many-audits-issuer, I exclude audits of investment companies and there it can be seen that Borgers is once again an outlier.

```{r}
#| label: tbl-many-audits-issuer
#| tbl-cap: Auditors with the most audits (excluding investment companies)
#| render: !expr function(x, ...) knitr::knit_print(knitr::kable(x))
form_aps |>
  mutate(audit_report_type = str_replace(audit_report_type, ",.*$", ""),
         firm_name = str_replace(firm_name, ",.*$", "")) |>
  filter(audit_report_type != "Investment Company") |>
  group_by(engagement_partner_id, firm_name, audit_report_type) |>
  summarize(n_audits = n(), .groups = "drop") |>
  inner_join(most_common_names, by = "engagement_partner_id") |>
  arrange(desc(n_audits)) |>
  select(engagement_partner_name, firm_name, audit_report_type, n_audits) |>
  collect(n = 10)
```
## Trump Media's new auditor

According to the [*Wall Street Journal*](https://www.wsj.com/business/media/trump-media-hires-new-auditor-after-previous-firm-gets-banned-5ee7489b), Semple, Marchal & Cooper was appointed as the new financial auditor for Trump Media & Technology Group early in May 2024.

We can search `form_aps` to identify the `firm_id` for this new audit firm.

```{r}
djt_new_auditor <-
  form_aps |>
  filter(str_detect(firm_name, "^Semple")) |>
  distinct(firm_id, firm_name) 

djt_new_auditor |>
  collect()
```
@tbl-djt-new suggests that Semple, Marchal & Cooper, LLP, has a much more manageable client roster than Ben F Borgers had.

```{r}
#| label: tbl-djt-new
#| tbl-cap: Audit clients of Semple, Marchal & Cooper, LLP
#| render: !expr function(x, ...) knitr::knit_print(knitr::kable(x))
form_aps |>
  semi_join(djt_new_auditor, join_by(firm_id, firm_name)) |>
  mutate(fiscal_year = year(fiscal_period_end_date)) |>
  group_by(issuer_name) |>
  summarize(n_audits = n(),
            first_year = min(fiscal_year, na.rm = TRUE),
            last_year = max(fiscal_year, na.rm = TRUE)) |>
  arrange(desc(last_year), desc(n_audits)) |>
  collect()
```

Having completed our analysis, we can disconnect from our in-memory DuckDB database.

```{r}
dbDisconnect(db)
```

## References {-}