使用BeautifulSoup解析SEC EDGAR XML表单数据及其子节点

huangapple go评论62阅读模式
英文:

Parse SEC EDGAR XML Form Data with child nodes using BeautifulSoup

问题

我正在尝试使用Beautiful Soup和XML从SEC的N-PORT-P/A表单中抓取单独的基金持仓。一个典型的提交,如下所示,并在此处链接,外观如下:

<edgarSubmission xmlns="http://www.sec.gov/edgar/nport" xmlns:com="http://www.sec.gov/edgar/common" xmlns:ncom="http://www.sec.gov/edgar/nportcommon" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<headerData>
<submissionType>NPORT-P/A</submissionType>
<isConfidential>false</isConfidential>
<accessionNumber>0001145549-23-004025</accessionNumber>
<filerInfo>
<filer>
<issuerCredentials>
<cik>0001618627</cik>
<ccc>XXXXXXXX</ccc>
</issuerCredentials>
</filer>
<seriesClassInfo>
<seriesId>S000048029</seriesId>
<classId>C000151492</classId>
</seriesClassInfo>
</filerInfo>
</headerData>
    <formData>
        <genInfo>
        ...
        </genInfo>
        <fundInfo>
        ...
        </fundInfo>
        <invstOrSecs>
            <invstOrSec>
                <name>ARROW BIDCO LLC</name>
                <lei>549300YHZN08M0H3O128</lei>
                <title>Arrow Bidco LLC</title>
                <cusip>042728AA3</cusip>
                <identifiers>
                    <isin value="US042728AA35"/>
                </identifiers>
                <balance>115000.000000000000</balance>
                <units>PA</units>
                <curCd>USD</curCd>
                <valUSD>114754.170000000000</valUSD>
                <pctVal>0.3967552449</pctVal>
                <payoffProfile>Long</payoffProfile>
                <assetCat>DBT</assetCat>
                <issuerCat>CORP</issuerCat>
                <invCountry>US</invCountry>
                <isRestrictedSec>N</isRestrictedSec>
                <fairValLevel>2</fairValLevel>
                <debtSec>
                    <maturityDt>2024-03-15</maturityDt>
                    <couponKind>Fixed</couponKind>
                    <annualizedRt>9.500000000000</annualizedRt>
                    <isDefault>N</isDefault>
                    <areIntrstPmntsInArrs>N</areIntrstPmntsInArrs>
                    <isPaidKind>N</isPaidKind>
                </debtSec>
                <securityLending>
                    <isCashCollateral>N</isCashCollateral>
                    <isNonCashCollateral>N</isNonCashCollateral>
                    <isLoanByFund>N</isLoanByFund>
                </securityLending>
            </invstOrSec>
        </invstOrSecs>
    </formData>
</edgarSubmission>

Arrow Bidco LLC是投资组合内的债券,其中包含在提交中的某些特征(CUSIP、CIK、余额、到期日等)。我正在寻找通过每个个别安全性(investOrSec)进行迭代并将每个安全性的特征收集到数据框中的最佳方法。

我当前正在使用的代码是:

import numpy as np
import pandas as pd
import requests
from bs4 import BeautifulSoup

header = {"User-Agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.75 Safari/537.36", "X-Requested-With": "XMLHttpRequest"}

n_port_file = requests.get("https://www.sec.gov/Archives/edgar/data/1618627/000114554923004968/primary_doc.xml", headers=header, verify=False)
n_port_file_xml = n_port_file.content
soup = BeautifulSoup(n_port_file_xml,'xml')

names = soup.find_all('name')
lei = soup.find_all('lei')
title = soup.find_all('title')
cusip = soup.find_all('cusip')
....
maturityDt = soup.find_all('maturityDt')
couponKind = soup.find_all('couponKind')
annualizedRt = soup.find_all('annualizedRt')

然后遍历每个列表,根据每行的值创建数据框。

fixed_income_data = []
for i in range(0,len(names)):
    rows = [names[i].get_text(),lei[i].get_text(),
        title[i].get_text(),cusip[i].get_text(),
        balance[i].get_text(),units[i].get_text(),
        pctVal[i].get_text(),payoffProfile[i].get_text(),
        assetCat[i].get_text(),issuerCat[i].get_text(),
        invCountry[i].get_text(),couponKind[i].get_text()
        ]
    fixed_income_data.append(rows)

fixed_income_df = pd.DataFrame(equity_data,columns = ['name',
                         'lei',
                         'title',
                         'cusip
英文:

I am attempting to scrape individual fund holdings from the SEC's N-PORT-P/A form using beautiful soup and xml. A typical submission, outlined below and linked here, looks like:

<edgarSubmission xmlns="http://www.sec.gov/edgar/nport" xmlns:com="http://www.sec.gov/edgar/common" xmlns:ncom="http://www.sec.gov/edgar/nportcommon" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<headerData>
<submissionType>NPORT-P/A</submissionType>
<isConfidential>false</isConfidential>
<accessionNumber>0001145549-23-004025</accessionNumber>
<filerInfo>
<filer>
<issuerCredentials>
<cik>0001618627</cik>
<ccc>XXXXXXXX</ccc>
</issuerCredentials>
</filer>
<seriesClassInfo>
<seriesId>S000048029</seriesId>
<classId>C000151492</classId>
</seriesClassInfo>
</filerInfo>
</headerData>
    <formData>
        <genInfo>
        ...
        </genInfo>
        <fundInfo>
        ...
        </fundInfo>
        <invstOrSecs>
            <invstOrSec>
                <name>ARROW BIDCO LLC</name>
                <lei>549300YHZN08M0H3O128</lei>
                <title>Arrow Bidco LLC</title>
                <cusip>042728AA3</cusip>
                <identifiers>
                    <isin value="US042728AA35"/>
                </identifiers>
                <balance>115000.000000000000</balance>
                <units>PA</units>
                <curCd>USD</curCd>
                <valUSD>114754.170000000000</valUSD>
                <pctVal>0.3967552449</pctVal>
                <payoffProfile>Long</payoffProfile>
                <assetCat>DBT</assetCat>
                <issuerCat>CORP</issuerCat>
                <invCountry>US</invCountry>
                <isRestrictedSec>N</isRestrictedSec>
                <fairValLevel>2</fairValLevel>
                <debtSec>
                    <maturityDt>2024-03-15</maturityDt>
                    <couponKind>Fixed</couponKind>
                    <annualizedRt>9.500000000000</annualizedRt>
                    <isDefault>N</isDefault>
                    <areIntrstPmntsInArrs>N</areIntrstPmntsInArrs>
                    <isPaidKind>N</isPaidKind>
                </debtSec>
                <securityLending>
                    <isCashCollateral>N</isCashCollateral>
                    <isNonCashCollateral>N</isNonCashCollateral>
                    <isLoanByFund>N</isLoanByFund>
                </securityLending>
            </invstOrSec>

With Arrow Bidco LLC being a bond within the portfolio, with some of its characteristics included within the filing (CUSIP, CIK, balance, maturity date, etc.). I am looking for the best way to iterate through each individual security (investOrSec) and collect the characteristics of each security in a dataframe.
The code I am currently using is:

import numpy as np
import pandas as pd
import requests
from bs4 import BeautifulSoup

header = {"User-Agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.75 Safari/537.36", "X-Requested-With": "XMLHttpRequest"}

n_port_file = requests.get("https://www.sec.gov/Archives/edgar/data/1618627/000114554923004968/primary_doc.xml", headers=header, verify=False)
n_port_file_xml = n_port_file.content
soup = BeautifulSoup(n_port_file_xml,'xml')

names = soup.find_all('name')
lei = soup.find_all('lei')
title = soup.find_all('title')
cusip = soup.find_all('cusip')
....
maturityDt = soup.find_all('maturityDt')
couponKind = soup.find_all('couponKind')
annualizedRt = soup.find_all('annualizedRt')

Then iterating through each list to create a dataframe based on the values in each row.

fixed_income_data = []
for i in range(0,len(names)):
    rows = [names[i].get_text(),lei[i].get_text(),
        title[i].get_text(),cusip[i].get_text(),
        balance[i].get_text(),units[i].get_text(),
        pctVal[i].get_text(),payoffProfile[i].get_text(),
        assetCat[i].get_text(),issuerCat[i].get_text(),
        invCountry[i].get_text(),couponKind[i].get_text()
        ]
    fixed_income_data.append(rows)

fixed_income_df = pd.DataFrame(equity_data,columns = ['name',
                         'lei',
                         'title',
                         'cusip',
                         'balance',
                         'units',
                         'pctVal',
                         'payoffProfile',
                         'assetCat',
                         'issuerCat',
                         'invCountry'
                         'maturityDt',
                         'couponKind',
                         'annualizedRt'
                         ], dtype = float)

This works fine when all pieces of information are included, but often there is one variable that is not accounted for. A piece of the form might be blank, or an issuer category might not have been filled out incorrectly, leading to an IndexError. This portfolio has 127 securities that I was able to parse, but might be missing an annualized return for a single security, throwing off the ability to neatly create a dataframe.

Additionally, for portfolios that hold both fixed income and equity securities, the equity securities do not return information for the debtSecs child. Is there a way to iterate through this data while simultaneously cleaning it in the easiest way possible? Even adding "NaN" for the debtSec children that equity securities don't reference would be a valid response. Any help would be much appreciated!
1: https://www.sec.gov/Archives/edgar/data/1618627/000114554923004968/primary_doc.xml

答案1

得分: 1

以下是我认为处理这个问题的最佳方式。一般来说,EDGAR提交文件往往很难解析,所以下面的方法可能适用于其他提交文件,即使是来自同一提交者也可能不一定适用。

为了让操作更容易,由于这是一个XML文件,你应该使用XML解析器和XPath。考虑到你要创建一个数据框,最合适的工具应该是pandas的read_xml()方法。

因为XML是嵌套的,你需要创建两个不同的数据框,然后将它们连接起来(也许其他人会有更好的方法来处理)。最后,虽然read_xml()可以直接从URL读取,但在这种情况下,EDGAR要求使用用户代理,这意味着你还需要使用requests库。

所以,总结一下:

# 导入所需的库
import pandas as pd
import requests

url = 'https://www.sec.gov/Archives/edgar/data/1618627/000114554923004968/primary_doc.xml'
# 设置带有用户代理的标头
headers = {"User-agent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36"}
req = requests.get(url, headers=headers)

# 定义要删除的列(根据你问题中的数据)
to_drop = ['identifiers', 'curCd','valUSD','isRestrictedSec','fairValLevel','debtSec','securityLending']

# 提交使用了命名空间(这里不方便详细讨论),所以你需要定义它
namespaces = {"nport": "http://www.sec.gov/edgar/nport"}

# 创建第一个数据框,用于债务工具
invest = pd.read_xml(req.text, xpath="//nport:invstOrSec[.//nport:debtSec]", namespaces=namespaces).drop(to_drop, axis=1)

# 创建第二个数据框,用于债务详细信息:
debt = pd.read_xml(req.text, xpath="//nport:debtSec", namespaces=namespaces).iloc[:, 0:3]

# 最后,将两者合并成一个数据框:
pd.concat([invest, debt], axis=1)

这应该会输出你的126个债务工具(请原谅格式):

lei 	title 	cusip 	balance 	units 	pctVal 	payoffProfile 	assetCat 	issuerCat 	invCountry 	maturityDt 	couponKind 	annualizedRt
0 	ARROW BIDCO LLC 	549300YHZN08M0H3O128 	Arrow Bidco LLC 	042728AA3 	115000.00 	PA 	0.396755 	Long 	DBT 	CORP 	US 	2024-03-15 	Fixed 	9.50000
1 	CD&R SMOKEY BUYER INC 	NaN 	CD&R Smokey Buyer Inc 	12510CAA9 	165000.00 	PA 	0.505585 	Long 	DBT 	CORP 	US 	2025-07-15 	Fixed 	6.75000

然后,你可以对最终的数据框进行操作,添加或删除列等。

英文:

Here is the best way, in my opinion, to handle the problem. Generally speaking, EDGAR filings are notoriously difficult to parse, so the following may or may not work on other filings, even from the same filer.

To make it easier on yourself, since this is an XML file, you should use an xml parser and xpath. Given that you're looking to create a dataframe, the most appropriate tool would be the pandas read_xml() method.

Because the XML is nested, you will need to create two different dataframes and concatenate them (maybe others will have a better idea on how to approach it). And, finally, although read_xml() can read directly from a url, in this case, EDGAR requires using a user-agent, meaning you also need to use the requests library as well.

So, all together:

#import required libraries
import pandas as pd
import requests

url = 'https://www.sec.gov/Archives/edgar/data/1618627/000114554923004968/primary_doc.xml'
#set headers with a user-agent
headers = {"User-agent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36"}    
req =  requests.get(url, headers=headers)

#define the columns you want to drop (based on the data in your question)
to_drop = ['identifiers', 'curCd','valUSD','isRestrictedSec','fairValLevel','debtSec','securityLending']

#the filing uses namespaces (too complicated to get into here), so you need to define that as well
namespaces = {"nport": "http://www.sec.gov/edgar/nport"}

#create the first df, for the securities which are debt instruments
invest = pd.read_xml(req.text,xpath="//nport:invstOrSec[.//nport:debtSec]",namespaces=namespaces).drop(to_drop, axis=1)

#crete the 2nd df, for the debt details:
debt = pd.read_xml(req.text,xpath="//nport:debtSec",namespaces=namespaces).iloc[:,0:3]

#finally, concatenate the two into one df:
pd.concat([invest, debt], axis=1)

This should output your 126 debt securities (pardon the formatting):

lei 	title 	cusip 	balance 	units 	pctVal 	payoffProfile 	assetCat 	issuerCat 	invCountry 	maturityDt 	couponKind 	annualizedRt
0 	ARROW BIDCO LLC 	549300YHZN08M0H3O128 	Arrow Bidco LLC 	042728AA3 	115000.00 	PA 	0.396755 	Long 	DBT 	CORP 	US 	2024-03-15 	Fixed 	9.50000
1 	CD&R SMOKEY BUYER INC 	NaN 	CD&R Smokey Buyer Inc 	12510CAA9 	165000.00 	PA 	0.505585 	Long 	DBT 	CORP 	US 	2025-07-15 	Fixed 	6.75000

You can then play with the final df, add or drop columns, etc

huangapple
  • 本文由 发表于 2023年2月7日 03:47:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/75365892.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定