Search Form ADV Filings with Python

In this guide, we will explore how to utilize the Form ADV API with Python to access and search the Form ADV database. The database contains various sections, including Part 1A, 1B, and Schedules A, B, and D, as well as brochures, all available in JSON format.

Here are some of the functionalities we will cover:

• Finding an ADV filing by CRD number
• Retrieving the most recently filed and updated ADV filings
• Searching for ADV filings based on advisory activity, such as financial planning services
• Filtering ADV filings for large advisory firms with assets under management of $90+ million
• Identifying ADV filings for advisory firms with more than $500 million in assets under management
• Locating ADV filings for individual advisers
• Accessing brochures by CRD number
• Mapping CRD to LEI and vice versa
• Conducting searches for investment advisers based on various categories, such as portfolio management for businesses or pension consulting services, high net worth individual clients, and compensation arrangements
• Finding CRDs of investment advisers based on specific criteria, such as assets under management
• Obtaining information on the number of accounts and employees per adviser
• Creating a top list of advisers sorted by assets under management, starting with the highest AUM
• Exploring private funds data disclosed in Schedule D
• Accessing private funds data, including custodians and prime brokers

All Form ADV data is already converted to JSON format and indexed in our databases, making it easily accessible through the Form ADV API.

Quick Start

To begin, you will need to install the sec-api Python package and initialize the FormAdvApi class by providing your API key. The FormAdvApi class offers three methods that enable you to search and access the Form ADV database, allowing you to retrieve filings in JSON format.

The available methods are:

• .get_firms(search_query): This method allows you to search for ADV filings related to advisory firms based on a search query.
• .get_individuals(search_query): Use this method to search for ADV filings related to individual advisers based on a search query.
• .get_brochures(crd): This method retrieves brochures associated with a specific CRD (Central Registration Depository) number.

By utilizing these methods, you can easily access the desired information from the Form ADV database.

Find SEC and State Registered Firm Advisers

In our first example, we demonstrate how to retrieve a JSON-formatted ADV filing filed by the firm adviser with the CRD number 361 (Goldman Sachs) by first defining our search_query. The .get_firms(search_query) method sends our query to the Form ADV API, and returns a response that includes the filing matching our search criteria.

The return value of the .get_firms() method is a dictionary with two keys: total and filings. The total key informs us of the number of filings that matched our search criteria, while the filings key contains a list of all the matched filings. Each filing is represented as a dictionary and follows the same structure as the ADV API response, which is detailed in the documentation here.

The query Info.FirmCrdNb:361 used in our example is written in Lucene syntax. Lucene query syntax provides a flexible way to construct complex search queries. For an introduction to the syntax, you can refer to the Lucene query syntax overview here.

List CRDs of Firm Advisers with Recently Updated Form ADVs

In this section, we will demonstrate how to find and list all CRDs of firm advisers who have published or updated their Form ADVs within a specific date range.

Here are two sample queries for this scenario:

# Date range search
"query": "Filing.Dt:[2023-05-10 TO 2023-05-14]"

# Wildcard search to match any CRD
"query": "Info.FirmCrdNb:*"

Understanding Pagination

Pagination is a technique used to retrieve a large number of results by splitting them into smaller, manageable chunks. In our case, we will use pagination to retrieve all filings by incrementing the from parameter in our query.

To simplify this process, we define a function called get_filings(query) that handles the pagination logic for us. The function retrieves filings by repeatedly calling the API with different from and size parameters until all filings have been retrieved. The size_param determines the number of filings to be fetched in each request.

Here is an example of the get_filings() function:

With the get_filings() function, we can easily retrieve all filings by passing the query as a parameter. In this case, we will use the recently_updated_query as an example:

By executing this code, we will retrieve the JSON-formatted ADV filings for the recently updated Form ADVs of firm advisers within the specified date range.

Extracting the CRDs from the retrieved filings is a straightforward process.

Find Advisers by Advisory Activity

Form ADV includes information about an advisor's advisory activities in Item 5.G. The Form ADV API returns this information in JSON format. The structure of the JSON data is as follows:

"FormInfo": {
  "Part1A": {
    // ...
    "Item5G": {
      "Q5G1": "N",
      "Q5G2": "N",
      "Q5G3": "Y",
      "Q5G4": "Y",
      "Q5G5": "Y",
      "Q5G6": "N",
      "Q5G7": "Y",
      "Q5G8": "Y",
      "Q5G9": "N",
      "Q5G10": "N",
      "Q5G11": "N",
      "Q5G12": "N"
    }
    // ... more fields here
  }
}

The advisory activities in Item 5.G. are represented by the keys Q5G1 to Q5G12. Each key corresponds to a specific advisory activity, and the values Y and N indicate whether the advisor performs that particular activity or not.

To facilitate understanding and mapping between the advisory activities and the corresponding keys, the table below provides the complete mapping:

For example, to search for advisers that perform security ratings or pricing services (Q5G9:Y), you can use the search query as shown below:

The following format_item_5g function renames the columns to make them more readable and removes the common prefix "FormInfo.Part1A.Item5G.". It also includes additional columns for "Adviser" and "CRD". The resulting dataframe will have the updated column names and the relevant columns for the Item 5.G. advisory activities.

If we want to find advisers offering pension consulting services (Q5G6:Y) in addition to our previous search query for "security ratings or pricing services", we can apply a filter mask to our dataframe using the following code:

Find Advisers by Business Activity

The following example demonstrates how to find Form ADVs and advisers by filtering the Form ADV database based on the business activity of advisers.

Business activities are disclosed in Item 6A of Part 1A in Form ADVs. The image below provides an example of the business activity section:

The same Form ADV section is represented as JSON data in the response from the Form ADV API, as shown below:

"FormInfo": {
  "Part1A": {
    "Item6A": {
      "Q6A1": "N",
      "Q6A2": "N",
      "Q6A3": "Y",
      "Q6A4": "N",
      "Q6A5": "N",
      "Q6A6": "N",
      "Q6A7": "N",
      "Q6A8": "N",
      "Q6A9": "N",
      "Q6A10": "N",
      "Q6A11": "N",
      "Q6A12": "N",
      "Q6A13": "N",
      "Q6A14": "N"
    },
    // ... more fields here
  }
}

The next code snippet demonstrates how to filter Form ADVs based on the business activity of commodity pool operators.

First, we define the commodity_pool_query which searches for Form ADVs where the business activity of commodity pool operators (Item 6A, Q6A3) is marked as "Y" (indicating "Yes"). This query is used to retrieve the relevant filings from the Form ADV database.

The form_advs_commodity_pool variable stores the result of calling the get_filings(commodity_pool_query) function. This retrieves the Form ADVs matching the specified business activity filter.

The format_item_6a(filings) function takes the retrieved filings and formats them to include only the relevant columns related to Item 6A. It filters the columns based on the "FormInfo.Part1A.Item6A" prefix, renames the additional columns for clarity, and returns the selected columns in a formatted dataframe.

Finally, the filtered Form ADVs are printed along with the number of filings found.

Find Advisers by Compensation Arrangements

Similar to the previous section, the following example demonstrates how to filter advisers by their compensation arrangements.

The compensation arrangements section in Item 5E of Part 1A of a Form ADV provides information about how advisers are compensated. The following image represents an example of Item 5E.

Here is the representation of the same compensation arrangements section in JSON format as returned by the Form ADV API:

"FormInfo": {
  "Part1A": {
    "Item5E": {
      "Q5E1": "Y",
      "Q5E2": "N",
      "Q5E3": "N",
      "Q5E4": "N",
      "Q5E5": "N",
      "Q5E6": "Y",
      "Q5E7": "Y",
      "Q5E7Oth": "PERCENTAGE OF REVENUE FROM AFFILIATES"
    },
    // ... more fields here
  }
}

In the JSON response, "Y" indicates that the adviser is compensated according to the respective type of arrangement. For example, Q5E1:Y indicates that the adviser is compensated by a percentage of assets under management.

The table below maps the compensation arrangements to the keys in the JSON response:

To filter advisers based on their compensation arrangements, you can adjust your search query accordingly. For example, to find advisers who are compensated by a percentage of assets under management (Q5E1:Y), have performance-based fees (Q5E6:Y) and other arrangements (Q5E7), you define the following query:

Sort Advisers by Assets Under Mangement

The next example illustrates how to sort and filter advisers based on their assets under management (AUM).

The AUM information of firm advisers is disclosed in Item 5.F of the Form ADV. Here is an example section of Fidelity's Form ADV:

The same information is provided in JSON format by the Form ADV API as follows:

"FormInfo": {
  "Part1A": {
    // ...
    "Item5F": {
      "Q5F1": "Y",
      "Q5F2A": 3148421739247,
      "Q5F2B": 0,
      "Q5F2C": 3148421739247,
      "Q5F2D": 23333,
      "Q5F2E": 0,
      "Q5F2F": 23333,
      "Q5F3": 93001157963
    }
    // ...
  }
}

There are three fields that provide information about assets under management:

• Q5F2A represents the discretionary AUM in US dollars.
• Q5F2C holds the total AUM in US dollars.
• Q5F3 represents the amount of assets attributable to non-US clients.

For this example, we will filter advisers based on the total AUM (Q5F2C). To demonstrate how to filter by AUM, we will use a range query syntax:

Q5F2C:[min_value TO max_value]

In this syntax, min_value represents the lower boundary, indicating that an adviser must have at least min_value AUM to be included in the response. The max_value is set as a wildcard * to indicate that there is no upper limit on the AUM.

To sort and filter advisers with at least $1bn AUM, we can use the following code snippet:

Sort Advisers by Number of Accounts

The total number of accounts is also disclosed in Item 5F of the Form ADV. Building on the example from the previous section, we can modify the code to sort the advisers based on the number of accounts (FormInfo.Part1A.Item5F.Q5F2F). This will allow us to create a top 20 list of firm advisers sorted by the total number of accounts they have.

Here's an updated code snippet to achieve this:

Find Individual Advisers

This section demonstrates how to access and search individual investment advisers using the .get_individuals(query) function of the Form ADV Search API. This function allows you to send a search query to the individual adviser lookup endpoint and retrieve all matching filings.

The return value of the .get_individuals() function follows the structure outlined in the Individual Adviser Lookup API response, as documented here.

In the following example, we illustrate how to list and find the JSON formatted Form ADVs of all advisers working for Morgan Stanley with CRD 149777. Please note that this is just an illustration, and the response will only include 50 ADV filings per request. We are performing a single request with "from": 0 and "size": 50. If you need to fetch all forms, you would need to implement a pagination function.

In the code snippet above, we define the search query to filter advisers based on their current employer's CRD number (CrntEmps.CrntEmp.orgPK). We set the value to "149777", which corresponds to Morgan Stanley. The JSON response is then converted into a pandas dataframe using pd.json_normalize() for further analysis.

Find Individual Advisers by CRD

The following example demonstrates how to find the Form ADV filing of an adviser by their CRD number.

In the code snippet below, we define the search query to filter advisers based on their CRD number (Info.indvlPK). We set the value to "7696734", which corresponds to the specific CRD number of the adviser you are searching for. The response will include the JSON formatted ADV filing associated with that CRD number.

Get Brochures from Part 2 of an ADV Filing

The following example demonstrates how to retrieve all brochures attached to an ADV filing using the .get_brochures(crd) function. This function accepts the CRD number of the firm as an argument and returns a list of all brochures filed by the adviser.

The structure of the return value adheres to the response of the Brochures API as documented here.

Access Private Funds Data in Schedule D

Investment advisers publish information about their private fund clients in Item 7.B.1 of Schedule D in Form ADV filings.

The following examples explore how to utilize the Private Funds API to access all data points disclosed in Item 7.B.1:

• Information about the private fund
• Ownership
• Advisory services
• Private offering
• Auditors
• Prime brokers
• Custodians
• Administrators
• Marketers

Let's start with a simple example and list all information of private funds advised by Goldman Sachs with CRD 107738.

List CRDs of Advisers disclosing Private Funds Data

To filter and identify advisers who disclose private funds data in their Form ADV filings, we can utilize the search query functionality of the Form ADV API. By querying the answer to Item 7.B, "Are you an adviser to any private fund?", we can identify advisers who answered "Yes" to this question.

The search syntax for filtering advisers who answered "Yes" to Item 7.B is as follows:

FormInfo.Part1A.Item7B.Q7B:Y

By applying this query, we can retrieve a list of advisers who advise private funds and disclose relevant data in Schedule D, Item 7.B of their Form ADV filings. It's important to note that this query would yield a large number of advisers, potentially more than 10,000.

To narrow down the search and focus on advisers with $5 billion or more in assets under management (AUM), we can combine the query with an additional search parameter:

FormInfo.Part1A.Item7B.Q7B:Y AND FormInfo.Part1A.Item5F.Q5F2C:[5000000000 TO *]

This combined query filters for advisers who disclose private funds data (Item 7.B, Q7B) and have assets under management of $5 billion or more (Item 5.F, Q5F2C). By adding this criterion, we can refine the search universe and target advisers who meet both criteria.

List Prime Brokers of Private Funds

To extract information about prime brokers of private funds, we can utilize the Private Funds endpoint of the Form ADV API. The example below demonstrates how to retrieve and format the prime broker data of private funds advised by Goldman Sachs (CRD 107738).

In this example, the function get_private_funds('107738') retrieves the private fund data for Goldman Sachs using their CRD number (107738). The resulting data includes information about prime brokers of the private funds.

The pd.json_normalize() function is used to flatten the nested JSON data structure, specifically the 24b-e-primeBrokers field. This field contains the prime broker information for each private fund. The resulting dataframe, prime_brokers, is then processed to remove any missing values, eliminate duplicates, sort the data by the prime broker name, and reset the index.

Please note that the actual number of prime brokers and the specific information displayed may vary based on the data available for the private funds advised by Goldman Sachs.

List Custodians of Private Funds

For each private fund, we can extract and create a list of custodians with information such as the custodian's name, SEC registration number, CRD, LEI, and location details. Here is an example of how to achieve this:

Access Schedule D (Private Funds) from Thousands of Advisers

Accessing and parsing private funds data at scale can be challenging, especially when dealing with large volumes of data that can quickly exceed multiple gigabytes. In this section, we will explore a solution to speed up the process by leveraging parallel processing to download and access private funds data more efficiently.

As an example application, we will create a list of the top 10 most used prime brokers by private funds advised by firms with assets under management (AUM) of $5 billion or more. This will give us insights into the prime brokers used by more than 10,000 private funds.

Please note that advisers may not consistently adhere to a standard naming convention for the legal names of prime brokers, and there may be variations in how they provide information such as the LEI. Additionally, it is possible that advisers may misspell the name of a prime broker or use a slightly different variation of the name. As a result, when working with prime broker data, it is important to account for potential inconsistencies and variations in the data provided.

Map CRD to LEI and LEI to CRD

This section illustrates the process of mapping a CRD (Central Registration Depository) number to a legal entity identifier (LEI), as well as the reverse mapping from LEI to CRD. This mapping is based on the information disclosed in Item 1.P of Form ADV filings.

Map CRD to LEI

Example: CRD 107738 of Goldman Sachs.

Map LEI to CRD

Example: LEI 5493006YBH9Z1VHYNT79 of Morgan Stanley.