Search Form ADV Filings with Python

   

On this page:

• Search Form ADV Filings with Python
  • Quick Start
  • Find SEC and State Registered Firm Advisers
    • List CRDs of Firm Advisers with Recently Updated Form ADVs
    • Find Advisers by Advisory Activity
    • Find Advisers by Business Activity
    • Find Advisers by Compensation Arrangements
    • Sort Advisers by Assets Under Mangement
    • Sort Advisers by Number of Accounts
  • Find Individual Advisers
    • Find Individual Advisers by CRD
  • Get Brochures from Part 2 of an ADV Filing
  • Access Private Funds Data in Schedule D
    • List CRDs of Advisers disclosing Private Funds Data
    • List Prime Brokers of Private Funds
    • List Custodians of Private Funds
  • Access Schedule D (Private Funds) from Thousands of Advisers
  • Map CRD to LEI and LEI to CRD
    • Map CRD to LEI
    • Map LEI to CRD

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.

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.

API_KEY = 'YOUR_API_KEY'

!pip install -q sec-api

from sec_api import FormAdvApi

formAdvApi = FormAdvApi(API_KEY)

search_query_firms = {
    "query": "Info.FirmCrdNb:361",
    "from": "0",
    "size": "50"
}

search_query_individuals = {
    "query": "Info.indvlPK:7696734",
    "from": "0",
    "size": "50"
}

response_firms       = formAdvApi.get_firms(search_query_firms)
response_individuals = formAdvApi.get_individuals(search_query_individuals)
response_brochures   = formAdvApi.get_brochures(149777)

response_firms['filings']
response_individuals['filings']
response_brochures['brochures']

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.

firm_advisers_query = {
    "query": "Info.FirmCrdNb:361",
    "from": "0",
    "size": "1",
    "sort": [{"Info.FirmCrdNb": {"order": "desc"}}],
}

response_firms_1 = formAdvApi.get_firms(firm_advisers_query)

print('Total matches found:', response_firms_1['total']['value'])
print('Filing')
print('------')
response_firms_1['filings']

Total matches found: 1
Filing
------

[{'Info': {'SECRgnCD': 'NYRO',
   'FirmCrdNb': 361,
   'SECNb': '801-16048',
   'BusNm': 'GOLDMAN SACHS & CO. LLC',
   'LegalNm': 'GOLDMAN SACHS & CO. LLC',
   'UmbrRgstn': 'N'},
  'MainAddr': {'Strt1': '200 WEST STREET',
   'City': 'NEW YORK',
   'State': 'NY',
   'Cntry': 'United States',
   'PostlCd': '10282',
   'PhNb': '212-902-1000'},
  'MailingAddr': {},
  'Rgstn': [{'FirmType': 'Registered', 'St': 'APPROVED', 'Dt': '1981-05-13'}],
  'NoticeFiled': {'States': [{'RgltrCd': 'AL',
     'St': 'FILED',
     'Dt': '1992-10-28'},
    {'RgltrCd': 'AK', 'St': 'FILED', 'Dt': '1997-11-21'},
    {'RgltrCd': 'AZ', 'St': 'FILED', 'Dt': '1997-11-26'},
    {'RgltrCd': 'AR', 'St': 'FILED', 'Dt': '1988-10-19'},
    {'RgltrCd': 'CA', 'St': 'FILED', 'Dt': '1997-07-08'},
    {'RgltrCd': 'CT', 'St': 'FILED', 'Dt': '1997-09-26'},
    {'RgltrCd': 'DE', 'St': 'FILED', 'Dt': '2011-01-24'},
    {'RgltrCd': 'GA', 'St': 'FILED', 'Dt': '2003-02-27'},
    {'RgltrCd': 'HI', 'St': 'FILED', 'Dt': '1997-11-14'},
    {'RgltrCd': 'ID', 'St': 'FILED', 'Dt': '1997-11-14'},
    {'RgltrCd': 'IL', 'St': 'FILED', 'Dt': '1988-12-12'},
    {'RgltrCd': 'IN', 'St': 'FILED', 'Dt': '1988-09-06'},
    {'RgltrCd': 'IA', 'St': 'FILED', 'Dt': '1999-01-01'},
    {'RgltrCd': 'KS', 'St': 'FILED', 'Dt': '1997-11-18'},
    {'RgltrCd': 'KY', 'St': 'FILED', 'Dt': '1997-11-24'},
    {'RgltrCd': 'LA', 'St': 'FILED', 'Dt': '2001-10-08'},
    {'RgltrCd': 'ME', 'St': 'FILED', 'Dt': '1989-01-20'},
    {'RgltrCd': 'MD', 'St': 'FILED', 'Dt': '1992-01-01'},
    {'RgltrCd': 'MA', 'St': 'FILED', 'Dt': '2001-04-30'},
    {'RgltrCd': 'CO', 'St': 'FILED', 'Dt': '2001-04-30'},
    {'RgltrCd': 'DC', 'St': 'FILED', 'Dt': '2001-04-30'},
    {'RgltrCd': 'FL', 'St': 'FILED', 'Dt': '1993-06-14'},
    {'RgltrCd': 'MI', 'St': 'FILED', 'Dt': '2001-04-30'},
    {'RgltrCd': 'MN', 'St': 'FILED', 'Dt': '1998-01-02'},
    {'RgltrCd': 'MS', 'St': 'FILED', 'Dt': '1997-11-17'},
    {'RgltrCd': 'MO', 'St': 'FILED', 'Dt': '1997-11-19'},
    {'RgltrCd': 'MT', 'St': 'FILED', 'Dt': '1997-11-26'},
    {'RgltrCd': 'NE', 'St': 'FILED', 'Dt': '1999-11-24'},
    {'RgltrCd': 'NV', 'St': 'FILED', 'Dt': '1991-01-02'},
    {'RgltrCd': 'NH', 'St': 'FILED', 'Dt': '1988-10-26'},
    {'RgltrCd': 'NJ', 'St': 'FILED', 'Dt': '1988-09-15'},
    {'RgltrCd': 'NM', 'St': 'FILED', 'Dt': '1989-01-25'},
    {'RgltrCd': 'NY', 'St': 'FILED', 'Dt': '1988-10-26'},
    {'RgltrCd': 'NC', 'St': 'FILED', 'Dt': '2001-04-03'},
    {'RgltrCd': 'ND', 'St': 'FILED', 'Dt': '1988-10-18'},
    {'RgltrCd': 'OH', 'St': 'FILED', 'Dt': '1999-11-19'},
    {'RgltrCd': 'OK', 'St': 'FILED', 'Dt': '1998-01-01'},
    {'RgltrCd': 'OR', 'St': 'FILED', 'Dt': '1988-11-18'},
    {'RgltrCd': 'PA', 'St': 'FILED', 'Dt': '1998-01-01'},
    {'RgltrCd': 'PR', 'St': 'FILED', 'Dt': '1996-11-01'},
    {'RgltrCd': 'RI', 'St': 'FILED', 'Dt': '1997-11-17'},
    {'RgltrCd': 'SC', 'St': 'FILED', 'Dt': '1988-10-24'},
    {'RgltrCd': 'SD', 'St': 'FILED', 'Dt': '1988-10-21'},
    {'RgltrCd': 'TN', 'St': 'FILED', 'Dt': '1997-09-19'},
    {'RgltrCd': 'TX', 'St': 'FILED', 'Dt': '2001-04-30'},
    {'RgltrCd': 'UT', 'St': 'FILED', 'Dt': '1997-10-09'},
    {'RgltrCd': 'VT', 'St': 'FILED', 'Dt': '1997-11-24'},
    {'RgltrCd': 'VA', 'St': 'FILED', 'Dt': '1998-02-03'},
    {'RgltrCd': 'WA', 'St': 'FILED', 'Dt': '1998-12-29'},
    {'RgltrCd': 'WV', 'St': 'FILED', 'Dt': '1988-09-06'},
    {'RgltrCd': 'WI', 'St': 'FILED', 'Dt': '1997-11-17'},
    {'RgltrCd': 'WY', 'St': 'FILED', 'Dt': '2018-01-16'},
    {'RgltrCd': 'VI', 'St': 'FILED', 'Dt': '2008-02-28'}]},
  'Filing': [{'Dt': '2023-04-13', 'FormVrsn': '10/2021'}],
  'FormInfo': {'Part1A': {'Item1': {'WebAddrs': {'WebAddr': 'https://www.linkedin.com/showcase/marcus-by-go'},
     'Q1F5': 20,
     'Q1I': 'Y',
     'Q1M': 'Y',
     'Q1N': 'N',
     'Q1O': 'Y',
     'Q1ODesc': 'More than $50 billion',
     'Q1P': 'FOR8UP27PHTHYVLBNG30'},
    'Item2A': {'Q2A1': 'Y',
     'Q2A2': 'N',
     'Q2A4': 'N',
     'Q2A5': 'N',
     'Q2A6': 'N',
     'Q2A7': 'N',
     'Q2A8': 'N',
     'Q2A9': 'N',
     'Q2A10': 'N',
     'Q2A11': 'N',
     'Q2A12': 'N',
     'Q2A13': 'N'},
    'Item2B': {},
    'Item3A': {'OrgFormNm': 'Limited Liability Company'},
    'Item3B': {'Q3B': 'DECEMBER'},
    'Item3C': {'StateCD': 'NY', 'CntryNm': 'United States'},
    'Item5A': {'TtlEmp': 3932},
    'Item5B': {'Q5B1': 2260,
     'Q5B2': 1827,
     'Q5B3': 1,
     'Q5B4': 4,
     'Q5B5': 37,
     'Q5B6': 2},
    'Item5C': {'Q5C1': '1627', 'Q5C2': 1},
    'Item5D': {'Q5DA1': 37898,
     'Q5DA3': 597854066,
     'Q5DB1': 21314,
     'Q5DB3': 32954583407,
     'Q5DC2': 'Fewer than 5 clients',
     'Q5DD1': 0,
     'Q5DE1': 0,
     'Q5DF1': 320,
     'Q5DF3': 72199970074,
     'Q5DG2': 'Fewer than 5 clients',
     'Q5DG3': 596081,
     'Q5DH1': 987,
     'Q5DH3': 9687176630,
     'Q5DI2': 'Fewer than 5 clients',
     'Q5DI3': 3450357,
     'Q5DJ2': 'Fewer than 5 clients',
     'Q5DJ3': 0,
     'Q5DK1': 22,
     'Q5DK3': 757657076,
     'Q5DL2': 'Fewer than 5 clients',
     'Q5DL3': 8704730,
     'Q5DM1': 401,
     'Q5DM3': 8049384948,
     'Q5DN1': 12183,
     'Q5DN3': 87159172841,
     'Q5DN3Oth': 'GS TRUST COMPANY, INDIAN TRIBES'},
    'Item5E': {'Q5E1': 'Y',
     'Q5E2': 'N',
     'Q5E3': 'N',
     'Q5E4': 'Y',
     'Q5E5': 'Y',
     'Q5E6': 'Y',
     'Q5E7': 'Y',
     'Q5E7Oth': 'EXECUTION CHARGES, CUSTODY, MANAGEMENT FEE'},
    'Item5F': {'Q5F1': 'Y',
     'Q5F2A': 206878508638,
     'Q5F2B': 4540041572,
     'Q5F2C': 211418550210,
     'Q5F2D': 71180,
     'Q5F2E': 19,
     'Q5F2F': 71199,
     'Q5F3': 69638102925},
    'Item5G': {'Q5G1': 'Y',
     'Q5G2': 'Y',
     'Q5G3': 'N',
     'Q5G4': 'Y',
     'Q5G5': 'Y',
     'Q5G6': 'N',
     'Q5G7': 'Y',
     'Q5G8': 'Y',
     'Q5G9': 'N',
     'Q5G10': 'N',
     'Q5G11': 'Y',
     'Q5G12': 'N'},
    'Item5H': {'Q5H': '1-10'},
    'Item5I': {'Q5I1': 'Y', 'Q5I2A': 0, 'Q5I2B': 0, 'Q5I2C': 620639145},
    'Item5J': {'Q5J1': 'Y', 'Q5J2': 'Y'},
    'Item5K': {'Q5K1': 'Y', 'Q5K2': 'Y', 'Q5K3': 'Y', 'Q5K4': 'Y'},
    'Item5L': {'Q5L1A': 'Y',
     'Q5L1B': 'Y',
     'Q5L1C': 'Y',
     'Q5L1D': 'N',
     'Q5L1E': 'Y',
     'Q5L2': 'Y',
     'Q5L3': 'Y',
     'Q5L4': 'N'},
    'Item6A': {'Q6A1': 'Y',
     'Q6A2': 'N',
     'Q6A3': 'Y',
     'Q6A4': 'Y',
     'Q6A5': 'N',
     'Q6A6': 'N',
     'Q6A7': 'N',
     'Q6A8': 'N',
     'Q6A9': 'Y',
     'Q6A10': 'Y',
     'Q6A11': 'N',
     'Q6A12': 'N',
     'Q6A13': 'N',
     'Q6A14': 'N'},
    'Item6B': {'Q6B1': 'Y', 'Q6B2': 'N', 'Q6B3': 'Y'},
    'Item7A': {'Q7A1': 'Y',
     'Q7A2': 'Y',
     'Q7A3': 'N',
     'Q7A4': 'Y',
     'Q7A5': 'N',
     'Q7A6': 'Y',
     'Q7A7': 'Y',
     'Q7A8': 'Y',
     'Q7A9': 'Y',
     'Q7A10': 'N',
     'Q7A11': 'N',
     'Q7A12': 'Y',
     'Q7A13': 'Y',
     'Q7A14': 'N',
     'Q7A15': 'N',
     'Q7A16': 'Y'},
    'Item7B': {'Q7B': 'Y'},
    'Item8A': {'Q8A1': 'Y', 'Q8A2': 'Y', 'Q8A3': 'Y'},
    'Item8B': {'Q8B1': 'Y', 'Q8B2': 'Y', 'Q8B3': 'Y'},
    'Item8C': {'Q8C1': 'Y', 'Q8C2': 'Y', 'Q8C3': 'Y', 'Q8C4': 'Y'},
    'Item8D': {'Q8D': 'Y'},
    'Item8E': {'Q8E': 'Y'},
    'Item8F': {'Q8F': 'Y'},
    'Item8G': {'Q8G1': 'Y', 'Q8G2': 'Y'},
    'Item8H': {'Q8H1': 'Y', 'Q8H2': 'Y'},
    'Item8I': {'Q8I': 'N'},
    'Item9A': {'Q9A1A': 'Y',
     'Q9A1B': 'Y',
     'Q9A2A': 118298241959,
     'Q9A2B': 35087},
    'Item9B': {'Q9B1A': 'Y', 'Q9B1B': 'Y', 'Q9B2A': 1215029045, 'Q9B2B': 152},
    'Item9C': {'Q9C1': 'Y', 'Q9C2': 'Y', 'Q9C3': 'Y', 'Q9C4': 'Y'},
    'Item9D': {'Q9D1': 'Y', 'Q9D2': 'Y'},
    'Item9E': {'Q9E': '2022-10'},
    'Item9F': {'Q9F': 100},
    'Item10A': {'Q10A': 'N'},
    'Item11': {'Q11': 'Y'},
    'Item11A': {'Q11A1': 'Y', 'Q11A2': 'Y'},
    'Item11B': {'Q11B1': 'Y', 'Q11B2': 'Y'},
    'Item11C': {'Q11C1': 'Y',
     'Q11C2': 'Y',
     'Q11C3': 'N',
     'Q11C4': 'Y',
     'Q11C5': 'Y'},
    'Item11D': {'Q11D1': 'Y',
     'Q11D2': 'Y',
     'Q11D3': 'N',
     'Q11D4': 'Y',
     'Q11D5': 'Y'},
    'Item11E': {'Q11E1': 'Y', 'Q11E2': 'Y', 'Q11E3': 'N', 'Q11E4': 'N'},
    'Item11F': {'Q11F': 'Y'},
    'Item11G': {'Q11G': 'Y'},
    'Item11H': {'Q11H1A': 'Y', 'Q11H1B': 'Y', 'Q11H1C': 'Y', 'Q11H2': 'Y'}}},
  'id': 361}]

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:

import pandas as pd

def get_filings(query):
  from_param = 0
  size_param = 50
  all_filings = []

  while True:
    query['from'] = from_param
    query['size'] = size_param
    
    response = formAdvApi.get_firms(query)
    filings = response['filings']

    if len(filings) == 0:
      break

    all_filings.extend(filings)

    from_param += size_param

  return pd.json_normalize(all_filings)

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:

recently_updated_query = {
    "query": "Filing.Dt:[2023-05-10 TO 2023-05-14]",
    "from": "0",
    "size": "50",
    "sort": [{ "Filing.Dt": { "order": "desc" }}]
}

filings = get_filings(recently_updated_query)

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.

print('Form ADVs updated between 10-14 May 2023')
print('----------------------------------------')
print('Number of Form ADVs found:', len(filings))
filings.sort_values('Info.FirmCrdNb')

Form ADVs updated between 10-14 May 2023
----------------------------------------
Number of Form ADVs found: 620

	Rgstn	Filing	id	Info.SECRgnCD	Info.FirmCrdNb	Info.SECNb	Info.BusNm	Info.LegalNm	MainAddr.Strt1	MainAddr.City	...	MailingAddr.Cntry	MailingAddr.PostlCd	FormInfo.Part1A.Item1.Q1ODesc	FormInfo.Part1A.Item5H.Q5HMT500	FormInfo.Part1B.ItemH.Q1B2HNScrtsNvsmtAm	FormInfo.Part1B.ItemJ.Q1BJ1A	FormInfo.Part1B.ItemJ.Q1BJ1B	FormInfo.Part1B.ItemJ.Q1BJ2A	FormInfo.Part1B.ItemI.Q1B2I2AiiOthTx	FormInfo.Part1A.Item6A.Q6A14Oth
49	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-05-12', 'FormVrsn': '10/2021'}]	421	CHRO	421	801-54905	WOODBURY FINANCIAL SERVICES, INC.	WOODBURY FINANCIAL SERVICES, INC.	7755 3RD STREET NORTH	OAKDALE	...	United States	55164-0284	NaN	1000.0	NaN	NaN	NaN	NaN	NaN	NaN
116	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-05-12', 'FormVrsn': '10/2021'}]	7461	ARO	7461	801-42017	FSC SECURITIES CORPORATION	FSC SECURITIES CORPORATION	2300 WINDY RIDGE PKWY	ATLANTA	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
323	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-05-11', 'FormVrsn': '10/2021'}]	12963	MIRO	12963	801-39736	MONEY CONCEPTS ADVISORY SERVICE	MONEY CONCEPTS CAPITAL CORP	11440 NORTH JOG ROAD	PALM BEACH GARDENS	...	NaN	NaN	NaN	1000.0	NaN	NaN	NaN	NaN	NaN	NaN
405	NaN	[{'Dt': '2023-05-11', 'FormVrsn': '10/2021'}]	12963	NaN	12963	801-39736	MONEY CONCEPTS ADVISORY SERVICE	MONEY CONCEPTS CAPITAL CORP	11440 NORTH JOG ROAD	PALM BEACH GARDENS	...	NaN	NaN	NaN	1000.0	NaN	NaN	NaN	NaN	NaN	NaN
496	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-05-10', 'FormVrsn': '10/2021'}]	14762	CHRO	14762	801-111070	COORDINATED CAPITAL SECURITIES, INC.	COORDINATED CAPITAL SECURITIES, INC.	704 RIVERPLACE COMMERCE CENTER III	MADISON	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
581	NaN	[{'Dt': '2023-05-10', 'FormVrsn': '10/2021'}]	326416	NaN	326416	802-128136	LEAP FORWARD VENTURES, LLC	LEAP FORWARD VENTURES, LLC	700 LARKSPUR LANDING CIRCLE	LARKSPUR	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
302	[{'FirmType': 'ERA', 'St': 'ACTIVE', 'Dt': '20...	[{'Dt': '2023-05-11', 'FormVrsn': '10/2021'}]	326659	NYRO	326659	802-128142	MATERIAL	MATERIAL LLC	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
165	NaN	[{'Dt': '2023-05-12', 'FormVrsn': '10/2021'}]	326772	NaN	326772	NaN	WESTERN RESERVE WEALTH MANAGEMENT LLC	WESTERN RESERVE WEALTH MANAGEMENT LLC	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
431	[{'FirmType': 'ERA', 'St': 'ACTIVE', 'Dt': '20...	[{'Dt': '2023-05-10', 'FormVrsn': '10/2021'}]	326807	NYRO	326807	802-128130	ROUND2 INVESTMENT PARTNERS LLC	ROUND2 INVESTMENT PARTNERS LLC	410 PARK AVENUE, SUITE 510	NEW YORK	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
549	NaN	[{'Dt': '2023-05-10', 'FormVrsn': '10/2021'}]	326866	NaN	326866	NaN	SEAPLANE VENTURES, LLC	SEAPLANE VENTURES, LLC	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

620 rows × 293 columns

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

crds = filings['Info.FirmCrdNb'].to_list()
crds[:15]

[324295,
 166547,
 281976,
 308992,
 132095,
 218518,
 311686,
 300571,
 306209,
 305255,
 314985,
 277137,
 299742,
 165124,
 316961]

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:

ID	Key	Advisory Activity
1	Q5G1	Financial planning services
2	Q5G2	Portfolio management (PM) for individuals and/or small businesses
3	Q5G3	PM for investment companies and "business development companies"
4	Q5G4	PM for pooled investment vehicles (other than investment companies)
5	Q5G5	PM for businesses (other than small businesses) or institutional clients
6	Q5G6	Pension consulting services
7	Q5G7	Selection of other advisers (including private fund managers)
8	Q5G8	Publication of periodicals or newsletters
9	Q5G9	Security ratings or pricing services
10	Q5G10	Market timing services
11	Q5G11	Educational seminars/workshops
12	Q5G12	Other

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

ratings_query = {
    "query": "FormInfo.Part1A.Item5G.Q5G9:Y",
    "sort": [{ "Filing.Dt": { "order": "desc" }}]
}

form_advs_ratings = get_filings(ratings_query)

print('Form ADVs filtered by security ratings or pricing services')
print('----------------------------------------------------------')
print('Number of Form ADVs found:', len(form_advs_ratings))
form_advs_ratings.sort_values('Info.FirmCrdNb').head()

Form ADVs filtered by security ratings or pricing services
----------------------------------------------------------
Number of Form ADVs found: 91

	Rgstn	Filing	id	Info.SECRgnCD	Info.FirmCrdNb	Info.SECNb	Info.BusNm	Info.LegalNm	Info.UmbrRgstn	MainAddr.Strt1	...	FormInfo.Part1A.Item5D.Q5D2E	FormInfo.Part1A.Item5D.Q5D2F	FormInfo.Part1A.Item5D.Q5D2G	FormInfo.Part1A.Item5D.Q5D2H	FormInfo.Part1A.Item5D.Q5D2I	FormInfo.Part1A.Item5D.Q5D2J	FormInfo.Part1A.Item5D.Q5D2K	FormInfo.Part1A.Item5D.Q5D2L	FormInfo.Part1A.Item5D.Q5D2M	FormInfo.Part1A.Item8H.Q8H
9	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-04-07', 'FormVrsn': '10/2021'}]	2525	NYRO	2525	801-9638	DEUTSCHE BANK SECURITIES INC.	DEUTSCHE BANK SECURITIES INC.	N	1 COLUMBUS CIRCLE	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
5	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-04-25', 'FormVrsn': '10/2021'}]	7059	NYRO	7059	801-3387	CITIGROUP GLOBAL MARKETS INC.	CITIGROUP GLOBAL MARKETS INC.	N	388 GREENWICH STREET	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
54	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-01-18', 'FormVrsn': '10/2021'}]	7524	CHRO	7524	801-67742	FIRST KENTUCKY SECURITIES CORP	FIRST KENTUCKY SECURITIES CORPORATION	N	4360 BROWNSBORO ROAD	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
10	[{'FirmType': 'Registered', 'St': 'APPROVED', ...	[{'Dt': '2023-04-06', 'FormVrsn': '10/2021'}]	8174	NYRO	8174	801-7163	UBS FINANCIAL SERVICES INC.	UBS FINANCIAL SERVICES INC.	N	1200 HARBOR BOULEVARD	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
84	NaN	[{'Dt': '2018-03-18', 'FormVrsn': '10/2017'}]	36105	NaN	36105	NaN	UNIVEST SECURITIES	UNIVEST SECURITIES, LLC	N	3687 MEADOW LANE	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5 rows × 311 columns

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.

def format_item_5g(filings):
  filtered_columns = filings.filter(like='FormInfo.Part1A.Item5G')
  additional_columns = filings[['Info.BusNm', 'Info.FirmCrdNb']]
  additional_columns = additional_columns.rename(columns={'Info.BusNm': 'Adviser', 'Info.FirmCrdNb': 'CRD'})
  selected_columns = pd.concat([additional_columns, filtered_columns], axis=1)
  selected_columns.columns = selected_columns.columns.str.replace(r'^FormInfo\.Part1A\.Item5G\.', '', regex=True)
  return selected_columns


format_item_5g(form_advs_ratings)

	Adviser	CRD	Q5G1	Q5G2	Q5G3	Q5G4	Q5G5	Q5G6	Q5G7	Q5G8	Q5G9	Q5G10	Q5G11	Q5G12	Q5G12Oth
0	GILLILAND JETER WEALTH MANAGEMENT, LLC	324403	Y	Y	N	N	Y	Y	Y	N	Y	N	N	N	NaN
1	SNIDER ADVISORS	120943	N	Y	N	N	N	N	N	Y	Y	N	Y	Y	INVESTMENT-RELATED CONSULTING
2	LANGFORD INVESTMENT COMPANY	116472	N	Y	N	N	Y	Y	Y	N	Y	Y	N	N	NaN
3	ONEAMERICA ASSET MANAGEMENT, LLC	165929	N	N	N	Y	N	N	Y	Y	Y	N	Y	Y	THIRD PARTY INVESTMENT ADVISER SERVICES AND PR...
4	WITTFIT FINANCIAL LLC	321961	Y	Y	N	N	N	N	N	Y	Y	Y	Y	N	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
86	RECONDITE CAPITAL LLC	281268	Y	Y	N	N	Y	N	N	Y	Y	N	N	N	NaN
87	DEER ISLE RESEARCH & MANAGEMENT, LLC	156636	N	N	N	N	N	N	N	N	Y	N	N	Y	INVESTMENT RESEARCH
88	HARRISON SECURITIES, INC.	144685	N	N	N	N	N	N	N	N	Y	N	N	Y	FISCAL AGENT TO POLITICAL SUBDIVISIONS
89	INTELLIVEST SECURITIES RESEARCH, INC	147288	Y	N	N	N	N	N	N	Y	Y	N	N	N	NaN
90	INTELLIVEST SECURITIES, INC.	145022	Y	N	N	N	N	N	N	Y	Y	N	N	N	NaN

91 rows × 15 columns

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:

filter_mask = form_advs_ratings['FormInfo.Part1A.Item5G.Q5G6'] == 'Y'
ratings_and_pension = form_advs_ratings[filter_mask]

print('Form ADVs filtered by ratings/pricing and pension consulting services')
print('---------------------------------------------------------------------')
print('Number of Form ADVs found:', len(ratings_and_pension))
format_item_5g(ratings_and_pension)

Form ADVs filtered by ratings/pricing and pension consulting services
---------------------------------------------------------------------
Number of Form ADVs found: 22

	Adviser	CRD	Q5G1	Q5G2	Q5G3	Q5G4	Q5G5	Q5G6	Q5G7	Q5G8	Q5G9	Q5G10	Q5G11	Q5G12	Q5G12Oth
0	GILLILAND JETER WEALTH MANAGEMENT, LLC	324403	Y	Y	N	N	Y	Y	Y	N	Y	N	N	N	NaN
2	LANGFORD INVESTMENT COMPANY	116472	N	Y	N	N	Y	Y	Y	N	Y	Y	N	N	NaN
5	CITIGROUP GLOBAL MARKETS INC.	7059	Y	Y	N	N	Y	Y	Y	Y	Y	N	N	Y	ASSET ALLOCATION ADVICE
10	UBS FINANCIAL SERVICES INC.	8174	Y	Y	N	N	Y	Y	Y	Y	Y	N	Y	N	NaN
16	INSTITUTIONAL SHAREHOLDER SERVICES INC.	111940	N	N	N	N	N	Y	N	Y	Y	N	N	Y	PROXY ANALYSIS, PROXY VOTING RECOMMENDATIONS, ...
17	BEST LIFE INFINITY, LLC	319232	Y	Y	N	N	N	Y	N	Y	Y	N	Y	N	NaN
19	NETHERBY ADVISORS LLC	148827	Y	Y	N	N	Y	Y	N	Y	Y	N	Y	N	NaN
23	MANAGED ASSET PORTFOLIOS, LLC	109574	N	Y	Y	Y	Y	Y	N	N	Y	N	N	Y	PORTFOLIO MANAGEMENT FOR TRUSTS AND OFFSHORE V...
26	JRM CAPITAL MANAGEMENT INC.	142039	Y	Y	N	Y	Y	Y	N	Y	Y	N	Y	N	NaN
28	STERLING FINANCIAL ADVISORS, LLC	107783	N	Y	N	N	N	Y	N	N	Y	N	N	N	NaN
32	BLUE ARIS	314727	Y	Y	N	N	N	Y	Y	Y	Y	Y	Y	N	NaN
33	THE BALANCE OF TRADE	291982	Y	N	N	N	N	Y	N	Y	Y	Y	Y	N	NaN
53	MODERN MONEY MANAGEMENT LLC	311307	Y	Y	N	N	N	Y	N	N	Y	N	N	N	NaN
58	SKYY FINANCIAL GROUP, LLC	282593	N	Y	N	Y	Y	Y	Y	N	Y	Y	Y	Y	INVESTMENT PLANNING
59	NED DAVIS RESEARCH, INC.	112260	N	N	N	N	N	Y	N	Y	Y	Y	Y	Y	ISSUES CHARTS, GRAPHS, ETC & CRS, MODEL PORTFO...
62	LIGHTHOUSE FINANCIAL	142481	Y	Y	N	N	N	Y	N	N	Y	Y	N	N	NaN
64	FINELY CAPITAL	315270	Y	N	N	Y	Y	Y	Y	Y	Y	Y	Y	N	NaN
65	PAUL M. WENDEE & ASSOCIATES	299188	Y	Y	N	Y	Y	Y	Y	Y	Y	Y	Y	N	NaN
68	WILSON, CLARK ALLEN	121925	N	Y	N	Y	Y	Y	N	Y	Y	Y	Y	N	NaN
74	P3 CAPITAL	310229	Y	Y	N	Y	Y	Y	Y	N	Y	N	Y	N	NaN
80	BETTER OPTIONS LLC	308312	N	Y	N	Y	Y	Y	Y	Y	Y	Y	Y	N	NaN
82	SAVYON ADVISORY, LLC	298256	Y	Y	Y	N	N	Y	Y	N	Y	Y	Y	N	NaN

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.

commodity_pool_query = {
    "query": "FormInfo.Part1A.Item6A.Q6A3:Y",
    "sort": [{ "Filing.Dt": { "order": "desc" }}]
}

form_advs_commodity_pool = get_filings(commodity_pool_query)

def format_item_6a(filings):
  filtered_columns = filings.filter(like='FormInfo.Part1A.Item6A')
  additional_columns = filings[['Info.BusNm', 'Info.FirmCrdNb']]
  additional_columns = additional_columns.rename(columns={'Info.BusNm': 'Adviser', 'Info.FirmCrdNb': 'CRD'})
  selected_columns = pd.concat([additional_columns, filtered_columns], axis=1)
  selected_columns.columns = selected_columns.columns.str.replace(r'^FormInfo\.Part1A\.Item6A\.', '', regex=True)
  return selected_columns


print('Form ADVs filtered by commodity pool operators')
print('----------------------------------------------')
print('Number of Form ADVs found:', len(form_advs_commodity_pool))
format_item_6a(form_advs_commodity_pool)

Form ADVs filtered by commodity pool operators
----------------------------------------------
Number of Form ADVs found: 3539

	Adviser	CRD	Q6A1	Q6A2	Q6A3	Q6A4	Q6A5	Q6A6	Q6A7	Q6A8	Q6A9	Q6A10	Q6A11	Q6A12	Q6A13	Q6A14	Q6A14Oth
0	MORGAN STANLEY PRIVATE EQUITY ASIA, INC.	134366	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
1	NXG INVESTMENT MANAGEMENT	131517	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
2	LIDO	269866	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
3	FIERA CAPITAL INC.	113638	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
4	ALPHA WAVE GLOBAL, LP	160894	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
3534	NEOWAVE EQUITY FUND, LLC	171767	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
3535	SIRI CAPITAL ADVISORS LLC	170778	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
3536	IRON LION CAPITAL	157946	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
3537	VICUNA ADVISORS	165705	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN
3538	SENTINEL ALTERNATIVE ASSET MANAGEMENT, LLC	162233	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	NaN

3539 rows × 17 columns

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:

ID	JSON Key	Compensation Arrangement
1	Q5E1	A percentage of assets under your management
2	Q5E2	Hourly charges
3	Q5E3	Subscription fees (for a newsletter or periodical)
4	Q5E4	Fixed fees (other than subscription fees)
5	Q5E5	Commissions
6	Q5E6	Performance-based fees
7	Q5E7	Other (specify)
7	Q5E7Oth	Description for Q5E7

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:

compensation_query = {
    "query": "FormInfo.Part1A.Item5E.Q5E1:Y AND FormInfo.Part1A.Item5E.Q5E6:Y AND FormInfo.Part1A.Item5E.Q5E7:Y",
    "sort": [{ "Filing.Dt": { "order": "desc" }}]
}

form_advs_filtered_by_compensation = get_filings(compensation_query)

def format_item_5e(filings):
  filtered_columns = filings.filter(like='FormInfo.Part1A.Item5E')
  additional_columns = filings[['Info.BusNm', 'Info.FirmCrdNb']]
  additional_columns = additional_columns.rename(columns={'Info.BusNm': 'Adviser', 'Info.FirmCrdNb': 'CRD'})
  selected_columns = pd.concat([additional_columns, filtered_columns], axis=1)
  selected_columns.columns = selected_columns.columns.str.replace(r'^FormInfo\.Part1A\.Item5E\.', '', regex=True)
  return selected_columns


print('Form ADVs filtered by compensation arrangements')
print('-----------------------------------------------')
print('Number of Form ADVs found:', len(form_advs_filtered_by_compensation))
format_item_5e(form_advs_filtered_by_compensation)

Form ADVs filtered by compensation arrangements
-----------------------------------------------
Number of Form ADVs found: 902

	Adviser	CRD	Q5E1	Q5E2	Q5E3	Q5E4	Q5E5	Q5E6	Q5E7	Q5E7Oth
0	MORGAN STANLEY INFRASTRUCTURE INC.	142824	Y	N	N	N	N	Y	Y	ACQUISITION FEES
1	NCH CAPITAL INC.	157288	Y	N	N	N	N	Y	Y	CERTAIN FUNDS PAY FOR OVERHEAD EXPENSES. SEE ...
2	KKR CREDIT ADVISORS (US) LLC	146629	Y	N	N	N	N	Y	Y	MANAGEMENT FEES
3	OWNER RESOURCE GROUP, LLC	164028	Y	N	N	N	N	Y	Y	CONSULTING FEES
4	EATON VANCE MANAGEMENT	104859	Y	N	N	N	N	Y	Y	PERCENTAGE OF PORTFOLIO INCOME
...	...	...	...	...	...	...	...	...	...	...
897	TCA FUND MANAGEMENT GROUP	169163	Y	N	N	N	N	Y	Y	CLIENTS REIMBURSE CERTAIN INVESTMENT MANAGEMEN...
898	RHYTHMIC CAPITAL MANAGEMENT LLC	154165	Y	Y	N	N	N	Y	Y	PERCENTAGE OF ASSETS REVIEWED
899	COASTAL PARTNERS LTD.	140584	Y	N	N	N	N	Y	Y	SOLICITORS FEES PAID BY 3RD PARTY ADVISERS
900	FISHER FINANCIAL, P.A.	139088	Y	Y	N	Y	N	Y	Y	A % OF ASSETS MANAGED BY OTHERS
901	SINTRA CAPITAL CORP	113059	Y	N	N	N	N	Y	Y	DETERMINED BY AGREEMENT

902 rows × 10 columns

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:

aum_query = {
  "query": "FormInfo.Part1A.Item5F.Q5F2C:[1000000000 TO *]",
  "sort": [{ "Info.FirmCrdNb": { "order": "desc" }}]
}

aum_filings = get_filings(aum_query)

print('Advisers with $1bn+ AUM:', len(aum_filings))

Advisers with $1bn+ AUM: 4605

sort_column = 'FormInfo.Part1A.Item5F.Q5F2C'

sorted_by_aum = aum_filings.sort_values(sort_column, ascending=False)

sorted_by_aum = sorted_by_aum[[sort_column,
                              'Info.BusNm', 
                              'Info.FirmCrdNb', 
                              'FormInfo.Part1A.Item5F.Q5F2F',
                              'FormInfo.Part1A.Item5A.TtlEmp']]

sorted_by_aum = sorted_by_aum.rename(columns={
                                'FormInfo.Part1A.Item5F.Q5F2C': 'AUM ($)', 
                                'Info.FirmCrdNb': 'CRD',
                                'Info.BusNm': 'Name',
                                'FormInfo.Part1A.Item5F.Q5F2F': 'Accounts',
                                'FormInfo.Part1A.Item5A.TtlEmp': 'Employees' 
                                })

sorted_by_aum['AUM per Account'] = sorted_by_aum['AUM ($)'].astype(int) / sorted_by_aum['Accounts'].astype(int)

columns_to_convert = ['AUM per Account', 'AUM ($)', 'Accounts', 'Employees']
sorted_by_aum[columns_to_convert] = sorted_by_aum[columns_to_convert].applymap(lambda x: '{:,.0f}'.format(x))

# drop incorrect 1st row
# 11,491,898,634,658 VISTA FINANCIAL ADVISORS, LLC 316306 32
sorted_by_aum = sorted_by_aum.drop(sorted_by_aum.index[0]).reset_index(drop=True)

top_20 = sorted_by_aum[:20]

print('Top 20 Firm Advisers by AUM')
print('---------------------------')
top_20

Top 20 Firm Advisers by AUM
---------------------------

	AUM ($)	Name	CRD	Accounts	Employees	AUM per Account
0	6,649,219,111,273	VANGUARD GROUP INC	105958	208	675	31,967,399,573
1	3,148,421,739,247	FIDELITY MANAGEMENT & RESEARCH COMPANY LLC	108281	23,333	1,108	134,934,288
2	2,750,178,273,164	CAPITAL RESEARCH AND MANAGEMENT COMPANY	110885	16,876	1,391	162,963,870
3	2,406,691,634,676	BLACKROCK FUND ADVISORS	105247	481	754	5,003,516,912
4	2,248,547,388,863	PACIFIC INVESTMENT MANAGEMENT COMPANY LLC	104559	2,782	3,177	808,248,522
5	2,011,986,210,191	J.P. MORGAN INVESTMENT MANAGEMENT INC.	107038	52,157	3,116	38,575,574
6	1,577,579,815,722	GOLDMAN SACHS ASSET MANAGEMENT, L.P.	107738	171,341	1,351	9,207,252
7	1,506,538,334,571	T. ROWE PRICE ASSOCIATES, INC.	105496	3,783	3,722	398,239,052
8	1,204,691,064,459	WELLINGTON MANAGEMENT COMPANY LLP	106595	1,338	2,139	900,367,014
9	1,185,061,196,999	MORGAN STANLEY	149777	2,233,188	28,000	530,659
10	1,163,046,943,604	BLACKROCK FINANCIAL MANAGEMENT, INC	107105	2,220	6,491	523,895,020
11	1,093,689,238,206	MERRILL LYNCH, PIERCE, FENNER & SMITH INCORPOR...	7691	2,864,234	36,687	381,844
12	983,853,862,895	PGIM, INC.	105676	874	1,658	1,125,690,919
13	902,656,985,580	NORTHERN TRUST INVESTMENTS, INCORPORATED	105780	41,219	726	21,899,051
14	901,548,041,456	HAMILTON LANE	107876	304	506	2,965,618,557
15	841,529,842,790	SSGA FUNDS MANAGEMENT, INC.	111242	247	250	3,407,003,412
16	803,470,618,544	GEODE CAPITAL MANAGEMENT, LLC	115504	174	156	4,617,647,233
17	782,620,910,160	CHARLES SCHWAB INVESTMENT MANAGEMENT, INC	106753	242,041	1,007	3,233,423
18	745,826,453,479	BLACKROCK ADVISORS, LLC	106614	322	256	2,316,231,222
19	738,307,405,774	INVESCO ADVISERS, INC.	105360	32,911	3,060	22,433,454

aggregated_aum = sorted_by_aum['AUM ($)'].str.replace(',', '').astype(int).sum()

print('Aggregated AUM of adviers: ${:,.0f}'.format(aggregated_aum))

Aggregated AUM of adviers: $112,492,234,622,227

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:

sort_column = 'FormInfo.Part1A.Item5F.Q5F2F' # Number of accounts, total

sorted_by_accounts = aum_filings.sort_values(sort_column, ascending=False)

# drop: 5,435,607,326 MARINER INVESTMENT GROUP, LLC 124744 180,493,766,178
sorted_by_accounts = sorted_by_accounts.drop(sorted_by_accounts.index[0]) \
                                       .reset_index(drop=True)

top_20_by_accounts = sorted_by_accounts[[sort_column,
                                        'Info.BusNm', 
                                        'Info.FirmCrdNb', 
                                        'FormInfo.Part1A.Item5F.Q5F2C',
                                        'FormInfo.Part1A.Item5A.TtlEmp' ]][:20]

top_20_by_accounts = top_20_by_accounts.rename(columns={
                                                    'FormInfo.Part1A.Item5F.Q5F2C': 'AUM ($)', 
                                                    'Info.FirmCrdNb': 'CRD',
                                                    'Info.BusNm': 'Name',
                                                    'FormInfo.Part1A.Item5F.Q5F2F': 'Accounts',
                                                    'FormInfo.Part1A.Item5A.TtlEmp': 'Employees'
                                                })

columns_to_convert = ['AUM ($)', 'Accounts', 'Employees']
top_20_by_accounts[columns_to_convert] = top_20_by_accounts[columns_to_convert].applymap(lambda x: '{:,.0f}'.format(x))

print('Top 20 Firm Advisers by Accounts')
print('--------------------------------')
top_20_by_accounts

Top 20 Firm Advisers by Accounts
--------------------------------

	Accounts	Name	CRD	AUM ($)	Employees
0	4,558,331	EDWARD JONES	250	593,126,816,828	28,607
1	3,374,948	STASH INVEST	226550	2,610,411,753	420
2	2,864,234	MERRILL LYNCH, PIERCE, FENNER & SMITH INCORPOR...	7691	1,093,689,238,206	36,687
3	2,233,188	MORGAN STANLEY	149777	1,185,061,196,999	28,000
4	1,953,584	AMERIPRISE FINANCIAL SERVICES, LLC	6363	414,380,098,355	14,187
5	1,736,317	MANAGED ACCOUNT ADVISORS LLC	142558	576,264,919,539	64
6	1,713,518	ENVESTNET PMC	111694	346,833,814,021	479
7	1,712,300	STRATEGIC ADVISERS LLC	104555	659,550,224,982	219
8	1,712,247	FIDELITY PERSONAL AND WORKPLACE ADVISORS	288590	652,548,367,853	13,507
9	1,688,501	LPL FINANCIAL LLC	6413	368,018,536,946	31,366
10	1,594,707	FINANCIAL ENGINES ADVISORS L.L.C.	104510	241,970,176,743	1,514
11	1,428,999	WELLS FARGO CLEARING SERVICES, LLC	19616	524,940,620,141	20,587
12	1,077,471	UBS FINANCIAL SERVICES INC.	8174	600,808,847,012	13,059
13	1,028,431	BETTERMENT	149117	36,630,802,858	449
14	982,795	J.P. MORGAN SECURITIES LLC	79	212,928,874,840	7,098
15	950,978	RAYMOND JAMES & ASSOCIATES, INC.	705	306,121,310,120	11,789
16	932,793	RAYMOND JAMES FINANCIAL SERVICES ADVISORS, INC	149018	245,249,234,083	6,517
17	871,626	J.P. MORGAN PRIVATE INVESTMENTS INC.	110186	238,864,704,749	709
18	634,470	VANGUARD ADVISERS, INC.	106715	299,191,714,649	2,124
19	591,801	WILSHIRE ADVISORS LLC	6210	95,554,572,990	273

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.

individual_adviser_search_query = {
  "query": "CrntEmps.CrntEmp.orgPK:149777",
  "from": "0",
  "size": "50",
  "sort": [{"id": {"order": "desc"}}]
}

individuals_response = formAdvApi.get_individuals(individual_adviser_search_query)

indiviual_advisers = pd.json_normalize(individuals_response['filings'])

print('Advisers working for Morgan Stanley')
print('-----------------------------------')
indiviual_advisers.head()

Advisers working for Morgan Stanley
-----------------------------------

	id	Info.lastNm	Info.firstNm	Info.midNm	Info.indvlPK	Info.actvAGReg	Info.link	CrntEmps.CrntEmp	Exms.Exm	EmpHss.EmpHs	OthrNms.OthrNm	OthrBuss.OthrBus.desc	DRPs.DRP	Info.sufNm
0	7696734	Bloom	Caitlin	E	7696734	Y	https://adviserinfo.sec.gov/individual/summary...	[{'CrntRgstns': {'CrntRgstn': [{'regAuth': 'CA...	[{'exmCd': 'S65', 'exmNm': 'Uniform Investment...	[{'fromDt': '02/2023', 'orgNm': 'MORGAN STANLE...	NaN	NaN	NaN	NaN
1	7686461	Kroll	Scott	Martin	7686461	Y	https://adviserinfo.sec.gov/individual/summary...	[{'CrntRgstns': {'CrntRgstn': [{'regAuth': 'IL...	[{'exmCd': 'S66', 'exmNm': 'Uniform Combined S...	[{'fromDt': '01/2023', 'orgNm': 'Morgan Stanle...	[{'lastNm': 'Kroll', 'firstNm': 'Scott', 'midN...	NaN	NaN	NaN
2	7685226	Reyes	Eddie	NaN	7685226	Y	https://adviserinfo.sec.gov/individual/summary...	[{'CrntRgstns': {'CrntRgstn': [{'regAuth': 'FL...	[{'exmCd': 'S66', 'exmNm': 'Uniform Combined S...	[{'fromDt': '02/2023', 'orgNm': 'MORGAN STANLE...	NaN	NaN	NaN	NaN
3	7684543	Kim	Ann	M	7684543	Y	https://adviserinfo.sec.gov/individual/summary...	[{'CrntRgstns': {'CrntRgstn': [{'regAuth': 'IL...	[{'exmCd': 'S66', 'exmNm': 'Uniform Combined S...	[{'fromDt': '02/2023', 'orgNm': 'MORGAN STANLE...	[{'lastNm': 'Hommer', 'firstNm': 'Ann', 'midNm...	*542870 - Rental Properties and Investment P...	NaN	NaN
4	7682781	Nelson	Rachel	Theresa	7682781	Y	https://adviserinfo.sec.gov/individual/summary...	[{'CrntRgstns': {'CrntRgstn': [{'regAuth': 'AZ...	[{'exmCd': 'S66', 'exmNm': 'Uniform Combined S...	[{'fromDt': '03/2023', 'orgNm': 'MORGAN STANLE...	NaN	NaN	NaN	NaN

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.

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.

search_by_crd_query = {
  "query": "Info.indvlPK:7696734",
  "from": "0",
  "size": "1",
  "sort": [{"id": {"order": "desc"}}],
}

crd_response = formAdvApi.get_individuals(search_by_crd_query)

crd_response['filings']

[{'Info': {'lastNm': 'Bloom',
   'firstNm': 'Caitlin',
   'midNm': 'E',
   'indvlPK': 7696734,
   'actvAGReg': 'Y',
   'link': 'https://adviserinfo.sec.gov/individual/summary/7696734'},
  'OthrNms': {},
  'CrntEmps': {'CrntEmp': [{'CrntRgstns': {'CrntRgstn': [{'regAuth': 'CA',
        'regCat': 'RA',
        'st': 'APPROVED',
        'stDt': '2023-04-10'},
       {'regAuth': 'TX',
        'regCat': 'RA',
        'st': 'APPROVED',
        'stDt': '2023-04-28'}]},
     'BrnchOfLocs': {'BrnchOfLoc': [{'str1': '15260 Ventura Boulevard',
        'str2': 'Suite 1900',
        'city': 'Sherman Oaks',
        'state': 'CA',
        'cntry': 'United States',
        'postlCd': '91403'}]},
     'orgNm': 'MORGAN STANLEY',
     'orgPK': 149777,
     'str1': '2000 WESTCHESTER AVENUE',
     'city': 'PURCHASE',
     'state': 'NY',
     'cntry': 'United States',
     'postlCd': '10577-2530'}]},
  'Exms': {'Exm': [{'exmCd': 'S65',
     'exmNm': 'Uniform Investment Adviser Law Examination',
     'exmDt': '2022-10-10'},
    {'exmCd': 'S63',
     'exmNm': 'Uniform Securities Agent State Law Examination',
     'exmDt': '2022-09-30'}]},
  'Dsgntns': {},
  'PrevRgstns': {},
  'EmpHss': {'EmpHs': [{'fromDt': '02/2023',
     'orgNm': 'MORGAN STANLEY',
     'city': 'Sherman Oaks',
     'state': 'CA'},
    {'fromDt': '12/2020',
     'toDt': '02/2023',
     'orgNm': 'Mitchell D. Becker MD',
     'city': 'Santa Monica',
     'state': 'CA'},
    {'fromDt': '02/2021',
     'toDt': '12/2022',
     'orgNm': 'Nomi Health',
     'city': 'Orem',
     'state': 'UT'},
    {'fromDt': '06/2019',
     'toDt': '08/2019',
     'orgNm': 'DaVita',
     'city': 'Denver',
     'state': 'CO'},
    {'fromDt': '07/2017',
     'toDt': '06/2018',
     'orgNm': 'Chandler Chicco Agency / Syneos Health',
     'city': 'Santa Monica',
     'state': 'CA'},
    {'fromDt': '07/2013',
     'toDt': '06/2017',
     'orgNm': 'Edelman',
     'city': 'Los Angeles',
     'state': 'CA'},
    {'fromDt': '08/2018',
     'toDt': '06/2019',
     'orgNm': 'UCLA Anderson School of Management',
     'city': 'Los Angeles',
     'state': 'CA'},
    {'fromDt': '09/2019',
     'toDt': '06/2020',
     'orgNm': 'UCLA Anderson School of Management',
     'city': 'Los Angeles',
     'state': 'CA'},
    {'fromDt': '09/2020',
     'toDt': '01/2021',
     'orgNm': 'Freelance',
     'city': 'Los Angeles',
     'state': 'CA'},
    {'fromDt': '10/2012',
     'toDt': '06/2013',
     'orgNm': 'Felker Toczek Gellman Suddleson LLP',
     'city': 'Los Angeles',
     'state': 'CA'}]},
  'OthrBuss': {},
  'DRPs': {},
  'id': 7696734}]

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.

response = formAdvApi.get_brochures(149777)

response['brochures']

[{'versionId': 844803,
  'name': 'SELECT UMA PROGRAM BROCHURE',
  'dateSubmitted': '2023-04-14',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=844803'},
 {'versionId': 836480,
  'name': 'PORTFOLIO MANAGEMENT AND INSTITUTIONAL CASH ADVISORY PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836480'},
 {'versionId': 836481,
  'name': 'ALTERNATIVE INVESTMENTS WRAP PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836481'},
 {'versionId': 836482,
  'name': 'OUTSOURCED CHIEF INVESTMENT OFFICE (OCIO)',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836482'},
 {'versionId': 836483,
  'name': 'FINANCIAL PLANNING SERVICES PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836483'},
 {'versionId': 836484,
  'name': 'GLOBAL INVESTMENT SOLUTIONS PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836484'},
 {'versionId': 836492,
  'name': 'MORGAN STANLEY CORE PORTFOLIOS',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836492'},
 {'versionId': 836486,
  'name': 'GRAYSTONE CONSULTING PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836486'},
 {'versionId': 836487,
  'name': 'SEPARATE MANAGED ACCOUNT WRAP PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836487'},
 {'versionId': 836488,
  'name': 'INSTITUTIONAL SERVICES PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836488'},
 {'versionId': 836489,
  'name': 'SEPARATE MANAGED ACCOUNT COMMISSION-BASED PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836489'},
 {'versionId': 836491,
  'name': 'ACCESS INVESTING PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836491'},
 {'versionId': 836479,
  'name': 'CONSULTING GROUP ADVISOR PROGRAM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836479'},
 {'versionId': 836485,
  'name': 'PRIVATE WEALTH MANAGEMENT FIRM BROCHURE',
  'dateSubmitted': '2023-03-30',
  'url': 'https://files.adviserinfo.sec.gov/IAPD/Content/Common/crd_iapd_Brochure.aspx?BRCHR_VRSN_ID=836485'}]

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.

import pandas as pd
import requests
import time

def get_private_funds(crd):
  url = f"https://api.sec-api.io/form-adv/schedule-d-7-b-1/{crd}?token={API_KEY}"
  response_json = {}

  for x in range(3):
    response = requests.get(url)
    if response.status_code == 200:
      response_json = response.json()
    elif response.status_code == 429:
      time.sleep(0.5 * (x + 1))
    else:
      raise Exception("API error: {} - {}".format(response.status_code, response.text))
  
  if isinstance(response_json, list):
    funds = pd.json_normalize(response_json)
    return funds
  return []

gs_private_funds = get_private_funds('107738')

print('Private fund clients of Goldman Sachs (CRD 107738)')
print('--------------------------------------------------')
print('Number of funds:', len(gs_private_funds))
gs_private_funds

Private fund clients of Goldman Sachs (CRD 107738)
--------------------------------------------------
Number of funds: 677

	1a-nameOfFund	1b-fundIdentificationNumber	3a-namesOfGeneralPartnerManagerTrusteeDirector	3b-filingAdvisers	4-1-exclusionUnder3c1	4-2-exclusionUnder3c7	5-nameCountryOfForeignFinancialRegAuthority	6a-isMasterFundInMasterFeederArrangement	6b-nameIdOfFeederFunds	6c-isFeederFundInMasterFeederAgreement	...	25b-g-custodians	26a-fundUsesAdministrators	26b-f-administrators	27-percentageOfAssetsValuedNotByRelatedPerson	28a-fundUsesMarketers	28b-g-marketers	2-lawOrganizedUnder.state	2-lawOrganizedUnder.country	10-typeOfFund.selectedTypes	10-typeOfFund.otherFundType
0	8VC OPPORTUNITIES ACCESS LLC	805-2131830807	[8VC OPPORTUNITIES ACCESS ADVISORS LLC]	No Information Filed	False	True	[]	False	[]	False	...	[{'25b-legalName': 'STATE STREET BANK AND TRUS...	True	[{'26b-name': 'STATE STREET BANK AND TRUST COM...	100	True	[{'28b-isRelatedPerson': True, '28c-name': 'GO...	Delaware	United States	[other private fund]	FUND OF PRIVATE EQUITY FUNDS
1	8VC OPPORTUNITIES ACCESS OFFSHORE HOLDINGS LP	805-7612837154	[8VC OPPORTUNITIES ACCESS ADVISORS LLC]	No Information Filed	False	True	[Cayman Islands - Cayman Islands Monetary Auth...	True	[{'name': '8VC OPPORTUNITIES ACCESS OFFSHORE L...	False	...	[{'25b-legalName': 'STATE STREET BANK AND TRUS...	True	[{'26b-name': 'STATE STREET BANK AND TRUST COM...	100	True	[{'28b-isRelatedPerson': True, '28c-name': 'GO...		Cayman Islands	[other private fund]	FUND OF PRIVATE EQUITY FUNDS
2	8VC OPPORTUNITIES ACCESS OFFSHORE LP	805-4369838486	[8VC OPPORTUNITIES ACCESS ADVISORS LLC]	No Information Filed	False	True	[Cayman Islands - Cayman Islands Monetary Auth...	False	[]	True	...	[{'25b-legalName': 'STATE STREET BANK AND TRUS...	True	[{'26b-name': 'STATE STREET BANK AND TRUST COM...	100	True	[{'28b-isRelatedPerson': True, '28c-name': 'GO...		Cayman Islands	[other private fund]	FUND OF PRIVATE EQUITY FUNDS
3	AC VREP OFFSHORE LP	805-2107060269	[AC RES ADVISORS, L.L.C.]	No Information Filed	False	True	[Cayman Islands - Cayman Islands Monetary Auth...	False	[]	False	...	[{'25b-legalName': 'STATE STREET BANK & TRUST ...	True	[{'26b-name': 'STATE STREET BANK AND TRUST COM...	98	True	[{'28b-isRelatedPerson': True, '28c-name': 'GO...		Cayman Islands	[private equity fund]	NaN
4	AEA INVESTORS VIII ACCESS LLC	805-1469991967	[AEA INVESTORS VIII ACCESS ADVISORS LLC]	No Information Filed	False	True	[]	False	[]	False	...	[{'25b-legalName': 'STATE STREET BANK & TRUST ...	True	[{'26b-name': 'STATE STREET BANK AND TRUST COM...	100	True	[{'28b-isRelatedPerson': True, '28c-name': 'GO...	Delaware	United States	[other private fund]	FUND OF PRIVATE EQUITY FUNDS
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
672	ZALICO VARIABLE SERIES I CORE FIXED INCOME	805-1816229353	[ZURICH AMERICAN LIFE INSURANCE COMPANY]	No Information Filed	False	True	[]	False	[]	False	...	[{'25b-legalName': 'BNY MELLON', '25c-business...	True	[{'26b-name': 'BENEFIT FINANCE PARTNERS', '26c...	100	False	[]	Illinois	United States	[other private fund]	MANAGED SEPARATE ACCOUNT OF A LIFE INSURANCE C...
673	ZALICO VARIABLE SERIES I MORTGAGE BACKED SECUR...	805-8234185769	[ZURICH AMERICAN LIFE INSURANCE COMPANY]	No Information Filed	False	True	[]	False	[]	False	...	[{'25b-legalName': 'BNY MELLON', '25c-business...	True	[{'26b-name': 'BENEFIT FINANCE PARTNERS', '26c...	100	False	[]	Illinois	United States	[other private fund]	MANAGED SEPARATE ACCOUNT OF A LIFE INSURANCE C...
674	ZALICO VL SERIES ACCOUNT 1 DIVERSIFIED FIXED I...	805-1431062510	[]	No Information Filed	False	True	[]	False	[]	False	...	[{'25b-legalName': 'BNY MELLON', '25c-business...	True	[{'26b-name': 'BENEFIT FINANCE PARTNERS', '26c...	100	False	[]	Illinois	United States	[other private fund]	MANAGED SEPARATE ACCOUNT OF A LIFE INSURANCE C...
675	ZALICO VL SERIES ACCOUNT - 2 MORTGAGE BACKED S...	805-3454739900	[ZURICH AMERICAN LIFE INSURANCE COMPANY]	No Information Filed	False	True	[]	False	[]	False	...	[{'25b-legalName': 'BNY MELLON', '25c-business...	True	[{'26b-name': 'BENEFIT FINANCE PARTNERS', '26c...	1	False	[]	Illinois	United States	[other private fund]	MANAGED SEPARATE ACCOUNT OF A LIFE INSURANCE C...
676	ZC RESOURCE INVESTMENT TRUST MORTGAGE BACKED S...	805-2961545268	[ZC RESOURCE INVESTMENT TRUST]	No Information Filed	False	True	[]	False	[]	False	...	[{'25b-legalName': 'STATE STREET', '25c-busine...	True	[{'26b-name': 'BENEFIT FINANCE PARTNERS', '26c...	100	False	[]	Delaware	United States	[other private fund]	MANAGED SEPARATE ACCOUNT OF A LIFE INSURANCE C...

677 rows × 48 columns

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.

private_funds_query = {
  "query": "FormInfo.Part1A.Item7B.Q7B:Y AND FormInfo.Part1A.Item5F.Q5F2C:[5000000000 TO *]",
  "sort": [{ "Info.FirmCrdNb": { "order": "desc" }}]
}

form_advs_with_private_funds = get_filings(private_funds_query)

print('Advisers with $5bn+ AUM that advise private funds:', len(form_advs_with_private_funds))

Advisers with $5bn+ AUM that advise private funds: 1216

crds = list(form_advs_with_private_funds['Info.FirmCrdNb'])

print('CRDs of advisers advising private funds')
print('---------------------------------------')
crds[:10]

CRDs of advisers advising private funds
---------------------------------------

[325281,
 324457,
 324015,
 323617,
 322923,
 322260,
 321999,
 321116,
 317952,
 317825]

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).

gs_private_funds = get_private_funds('107738')

prime_brokers = pd.json_normalize(list(gs_private_funds['24b-e-primeBrokers'].explode()))
prime_brokers = prime_brokers.dropna() \
                             .drop_duplicates() \
                             .sort_values('24b-name') \
                             .reset_index(drop=True)


print('Prime brokers of private funds advised by Goldman Sachs (CRD 107738)')
print('--------------------------------------------------------------------')
print('Number of prime brokers:', len(prime_brokers))
prime_brokers

Prime brokers of private funds advised by Goldman Sachs (CRD 107738)
--------------------------------------------------------------------
Number of prime brokers: 6

	24b-name	24c-1-secRegistrationNumber	24c-2-crdNumber	24e-actsAsCustodian	24d-location.city	24d-location.state	24d-location.country
0	ABN AMRO SECURITIES (USA) LLC	8 - 68398	151796	True	NEW YORK	New York	United States
1	BNP PARIBAS	-		True	PARIS		France
2	CITIGROUP GLOBAL MARKETS INC.	8 - 8177	7059	True	NEW YORK	New York	United States
3	CREDIT SUISSE SECURITIES (USA) LLC	8 - 422	816	True	NEW YORK	New York	United States
4	MORGAN STANLEY	-		True	NEW YORK	New York	United States
5	MORGAN STANLEY & CO. LLC	8 - 15869	8209	True	NEW YORK	New York	United States

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.

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:

custodians = pd.json_normalize(list(gs_private_funds['25b-g-custodians'].explode()))
custodians = custodians.dropna() \
                       .drop_duplicates(subset=['25g-legalEntityIdentifier']) \
                       .sort_values('25b-legalName') \
                       .reset_index(drop=True)


print('Custodians of private funds advised by Goldman Sachs (CRD 107738)')
print('-----------------------------------------------------------------')
print('Number of custodians:', len(custodians))
custodians

Custodians of private funds advised by Goldman Sachs (CRD 107738)
-----------------------------------------------------------------
Number of custodians: 14

	25b-legalName	25c-businessName	25e-isRelatedPerson	25f-1-secRegistrationNumber	25f-2-crdNumber	25g-legalEntityIdentifier	25d-location.city	25d-location.state	25d-location.country
0	ABN AMRO BANK N.V	ABN AMRO BANK N.V	False	-			GUSTAV MAHLERLAAN		Netherlands
1	BANK OF NEW YORK MELLON	BANK OF NEW YORK MELLON	False	-		HPFHU0OQ28E4N0NFVK49	NEW YORK	New York	United States
2	BNY MELLON	BNY MELLON	False	-		WFLLPEPC7FZXENRZV188	NEW YORK	New York	United States
3	BROWN BROTHERS HARRIMAN & CO.	BROWN BROTHERS HARRIMAN & CO.	False	-		5493006KMX1VFTPYPW14	BOSTON	Massachusetts	United States
4	CITIBANK, N.A.	CITIBANK, N.A.	False	-		E57ODZWZ7FF32TWEFA76	NEW YORK	New York	United States
5	CREDIT SUISSE SECURITIES (USA) LLC	CREDIT SUISSE SECURITIES (USA) LLC	False	8 - 422	816	1V8Y6QCX6YMJ2OELII46	NEW YORK	New York	United States
6	GOLDMAN SACHS & CO. LLC	GOLDMAN SACHS & CO. LLC	True	-		FOR8UP27PHTHYVLBNG30	NEW YORK	New York	United States
7	GOLDMAN SACHS BANK ZURICH	GOLDMAN SACHS BANK ZURICH	True	-		S81F8KH474EY7PUWI149	ZURICH		Switzerland
8	GOLDMAN SACHS INTERNATIONAL	GOLDMAN SACHS INTERNATIONAL	True	-		W22LROWP2IHZNBB6K528	LONDON		United Kingdom
9	JP MORGAN CHASE BANK, NATIONAL ASSOCIATION	JP MORGAN CHASE BANK, NATIONAL ASSOCIATION	False	-		7H6GLXDRUGQFU57RNE97	NEW YORK	New York	United States
10	STATE STREET BANK AND TRUST COMPANY	STATE STREET BANK AND TRUST COMPANY	False	-		571474TGEMMWANRLN572	BOSTON	Massachusetts	United States
11	STATE STREET BANK INTERNATIONAL GMBH (LUXEMBOU...	STATE STREET BANK INTERNATIONAL GMBH (LUXEMBOU...	False	-		549300NTJWXBTICDZY51	LUXEMBOURG		Luxembourg
12	U.S. BANK	U.S. BANK	False	-		6BYL5QZYBDK8S7L73M02	CINCINNATI	Ohio	United States
13	WELLS FARGO N/A	WELLS FARGO N/A	False	-		KB1H1DSPRFMYMCUFXT09	PHOENIX	Arizona	United States

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.

private_funds_query = {
  "query": "FormInfo.Part1A.Item7B.Q7B:Y AND FormInfo.Part1A.Item5F.Q5F2C:[5000000000 TO *]",
  "sort": [{ "Info.FirmCrdNb": { "order": "desc" }}]
}

form_advs_with_private_funds = get_filings(private_funds_query)

def private_funds_to_prime_stats(private_funds):
  has_no_primes = (private_funds['24a-fundUsesPrimeBrokers'] == False).all()
  if has_no_primes:
    return None

  prime_brokers = pd.json_normalize(list(private_funds['24b-e-primeBrokers'].explode()))

  prime_brokers['clients'] = 0

  broker_stats = prime_brokers.groupby(['24b-name', '24c-2-crdNumber', '24c-1-secRegistrationNumber']) \
                              .count() \
                              .reset_index()[['24b-name', 
                                              '24c-2-crdNumber', 
                                              '24c-1-secRegistrationNumber', 
                                              'clients']]
  
  broker_stats = broker_stats.rename(columns={'24b-name': 'name', 
                                              '24c-2-crdNumber': 'crd',
                                              '24c-1-secRegistrationNumber': 'secRegNumber',
                                              })
  return broker_stats


gs_private_funds = get_private_funds('107738')

private_funds_to_prime_stats(gs_private_funds)

	name	crd	secRegNumber	clients
0	ABN AMRO SECURITIES (USA) LLC	151796	8 - 68398	1
1	BNP PARIBAS		-	1
2	CITIGROUP GLOBAL MARKETS INC.	7059	8 - 8177	3
3	CREDIT SUISSE SECURITIES (USA) LLC	816	8 - 422	3
4	MORGAN STANLEY		-	1
5	MORGAN STANLEY & CO. LLC	8209	8 - 15869	2

def process_crd(crd):
  private_funds = get_private_funds(crd)

  if len(private_funds) == 0:
    stats_per_firm = None
  else:
    try:
      stats_per_firm = private_funds_to_prime_stats(private_funds)
    except Exception as e:
      stats_per_firm = None
      print(f'{crd} - error: {str(e)}')

  return stats_per_firm

!pip install -q pandarallel

from pandarallel import pandarallel

number_of_workers = 5
pandarallel.initialize(progress_bar=True, nb_workers=number_of_workers, verbose=0)

primes_stats_list = list(form_advs_with_private_funds[:]['Info.FirmCrdNb'].parallel_apply(process_crd))
primes_stats = pd.concat([pd.DataFrame(d) for d in primes_stats_list if d is not None])

VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=244), Label(value='0 / 244'))), HB…

sum_clients = primes_stats.groupby(['name'])['clients'] \
                          .sum() \
                          .reset_index() \
                          .sort_values('clients', ascending=False) \
                          .reset_index(drop=True)

print('Top 10 Most Used Prime Brokers')
print('------------------------------')
print('Number of prime brokers:', len(sum_clients))
sum_clients.head(10)

Top 10 Most Used Prime Brokers
------------------------------
Number of prime brokers: 354

	name	clients
0	GOLDMAN SACHS & CO. LLC	1002
1	J.P. MORGAN SECURITIES LLC	866
2	MORGAN STANLEY & CO. LLC	678
3	BARCLAYS CAPITAL INC.	354
4	CITIGROUP GLOBAL MARKETS INC.	352
5	MERRILL LYNCH PROFESSIONAL CLEARING CORP.	309
6	J.P. MORGAN CLEARING CORP.	225
7	UBS SECURITIES LLC	181
8	BNP PARIBAS SECURITIES CORP.	160
9	CREDIT SUISSE SECURITIES (USA) LLC	160

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.

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.

Example: CRD 107738 of Goldman Sachs.

crd_query = {
  "query": "Info.FirmCrdNb:107738",
  "from": "0",
  "size": "1"
}

response = formAdvApi.get_firms(crd_query)

filings = pd.json_normalize(response['filings'])

lei = filings['FormInfo.Part1A.Item1.Q1P'][0]
print('LEI of CRD 107738:', lei)

LEI of CRD 107738: CF5M58QA35CFPUX70H17

Example: LEI 5493006YBH9Z1VHYNT79 of Morgan Stanley.

lei_query = {
  "query": "FormInfo.Part1A.Item1.Q1P:5493006YBH9Z1VHYNT79",
  "from": "0",
  "size": "1"
}

response = formAdvApi.get_firms(lei_query)

filings = pd.json_normalize(response['filings'])

crd = filings['Info.FirmCrdNb'][0]
print('CRD of LEI 5493006YBH9Z1VHYNT79:', crd)

CRD of LEI 5493006YBH9Z1VHYNT79: 134366