How to get all 13F-HR holdings per ticker?

[paulommaia]

Can we easily aggregate the 13F-HR holdings per ticker to know which companies are associated with a ticker ? For example, similar to how SEC-API does here

https://sec-api.io/sandbox/13f-filings-holding-tesla

I got to the point where I have all the issuers, but need to map this to a ticker

[dgunning]

Hi Paulo, Would you want to this aggregation per ticker for a single 13F-HR .. or do you want to aggregate across multiple 13F-HRs?

[paulommaia]

Hi! I wanted to aggregate accross multiple 13F-HRs, to know all the companies holding Tesla, for example, and the corresponding 13F URLs.

[dgunning]

Paulo,

Bear in mind that the library does not do this out of the box because it's just a library and has no storage. I am working on another project that does ETL tasks to collect and store data.

sec-api would probably have code that does something similar to this.

from tqdm.notebook import tqdm
from edgar import *
import pandas as pd
from tqdm.notebook import tqdm

set_identity("Mike Garrity mgarrity@gmail.com")

tesla_cusip = '023135106'
filings = get_filings(form="13F-HR", filing_date="2023-12-12:")
#filings = filings.head(100)
def extract_holders(filings, cusip:str):
    for filing in tqdm(filings):
        hr = ThirteenF(filing, False)
        holding = hr.infotable.query(f"Cusip=='{cusip}'").copy()
        if not holding.empty:
            holding['AccessionNumber'] = filing.accession_no
            yield (holding
                   .assign(AccessionNumber = filing.accession_no, 
                           Cik=filing.cik,
                           Company=filing.company, 
                           ReportPeriod=hr.report_period,
                           FilingDate=filing.filing_date,
                           Shares=lambda df: df.SharesPrnAmount)
                   .filter(['Company', 'ReportPeriod', 'FilingDate', 'AccessionNumber', 'UsedAccession', 'Period', 'Class', 'Cusip', 'Value', 'Shares'])      
            )

companies_holding_tesla = (pd.concat(list(extract_holders(filings, tesla_cusip)), ignore_index=True)
                           .sort_values(['Company', 'ReportPeriod'], ascending=[True, False]))    
companies_holding_tesla

BTW ... filing.obj() is kind of wrong for ThirteenF, because of a poor assumption I made. Which is why the code above uses hr = ThirteenF(filing, False). I wouldn't really know without feedback from users, so thanks. I'll do a fix shortly.

Let me know if this helps

[paulommaia]

Thank you @dgunning , that seems to do the trick! Does the library have a mapping from Ticker to CUSIP?

[dgunning]

I just found this (like 20 minutes ago), which looks to have all the information you need. https://www.sec.gov/dera/data/form-13f

So, I will probably change the tool to use this as a base, and the append the daily data.