Syncing another company's financial year to another company using EDGAR database

Question

I'm working on a school project that was built by a previous group, and one of my tasks is to synchronize the fiscal year's start date according to the start date of the company of focus. This is the code used to grab the json from EDGAR using their API.

d = requests.get(f"https://data.sec.gov/api/xbrl/companyconcept/CIK{cik}/us-gaap/{kpi}.json",
                             headers=self.HEADER).json()

Which is then converted into a dataframe using pandas and then printed out

df = pd.DataFrame.from_dict(d)
print(df.to_string())

I grab financial year data of a company using their CKI, for a certain KPI, in this case its AccountsPayableCurrent, but when I print out the data I don't see columns that relate to a start date, only end dates and other columns. Sample output below:

cik taxonomy tag label description entityName units USD 866787 us-gaap AccountsPayableCurrent Accounts Payable, Current Carrying value as of the balance sheet date of liabilities incurred (and for which invoices have typically been received) and payable to vendors for goods and services received that are used in an entity's business. Used to reflect the current portion of the liabilities (due within one year or within the normal operating cycle if longer). AUTOZONE INC [{'end': '2009-08-29', 'val': 2118746000, 'accn': '0000950123-09-071593', 'fy': 2010, 'fp': 'Q1', 'form': '10-Q', 'filed': '2009-12-17'}, {'end': '2009-08-29', 'val': 2118746000, 'accn': '0000950123-10-025907', 'fy': 2010, 'fp': 'Q2', 'form': '10-Q', 'filed': '2010-03-18'}, {'end': '2009-08-29', 'val': 2118746000, 'accn': '0000950123-10-058650', 'fy': 2010, 'fp': 'Q3', 'form': '10-Q', 'filed': '2010-06-16'}, {'end': '2009-08-29', 'val': 2118746000, 'accn': '0000950123-10-095687', 'fy': 2010, 'fp': 'FY', 'form': '10-K', 'filed': '2010-10-25', 'frame': 'CY2009Q3I'}, {'end': '2009-11-21', 'val': 2187347000, 'accn': '0000950123-09-071593', 'fy': 2010, 'fp': 'Q1', 'form': '10-Q', 'filed': '2009-12-17', 'frame': 'CY2009Q4I'}, {'end': '2010-02-13', 'val': 2144995000, 'accn': '0000950123-10-025907', 'fy': 2010, 'fp': 'Q2', 'form': '10-Q', 'filed': '2010-03-18'}, {'end': '2010-05-08', 'val': 2235766000, 'accn': '0000950123-10-058650', 'fy': 2010, 'fp': 'Q3', 'form': '10-Q', 'filed': '2010-06-16', 'frame': 'CY2010Q1I'}, {'end': '2010-08-28', 'val': 2433050000, 'accn': '0000950123-10-095687', 'fy': 2010, 'fp': 'FY', 'form': '10-K', 'filed': '2010-10-25'}

Is there any way I would go about syncing up the fiscal years using the data provided? The only way I could think to sync it is by grabbing the start date and telling it to print it out according to the other company's start date, but I see no start date.

Answer 1

I had to solve a similar problem. Here is my solution.

You're looking for the period of report attribute reported in annual corporate filings aka 10-K filings. Even though the attribute refers to a "period", in actuallity it defines the end of the financial year and represents a single date instead of a period.

The attribute is also included in other SEC form types, such as 10-Q (= quarterly report), 4 (insider trading activity), 13F (institutional ownership), and more. For your use case, it's important to consider annual reports (10-K filings) only as otherwise you're ending up with "financial years" not being a year long, but a couple of months.

Our first objective is to find all 10-K filings filed by the companies you're interested in. For simplicity reasons, we consider all 10-K filings here. You can add a simple CIK filter to the search query later on. The Python dict query defines the search query parameters. We are only interested in 10-K submissions (formType:\"10-K\") and ignore amended 10-Ks (AND NOT formType:\"10-K/A\") as well as late filing notifications of 10-Ks (AND NOT formType:\"NT 10-K\").

from sec_api import QueryApi

queryApi = QueryApi(api_key="YOUR_API_KEY")


query = {
  "query": { "query_string": { 
      "query": "formType:\"10-K\" " + 
               "AND NOT formType:\"NT 10-K\" " + 
               "AND NOT formType:\"10-K/A\" " +
               "AND filedAt:[2021-01-01 TO 2021-12-31]",
      "time_zone": "America/New_York"
  } },
  "from": "0",
  "size": "10",
  "sort": [{ "filedAt": { "order": "desc" } }]
}

response = queryApi.get_filings(query)

The response dict holds the 10-K filings we were looking for in response["filings"] and includes the periodOfReport and cik property for every filing. Printing the response yields the following:

list(map(lambda x: {"formType": x["formType"], "periodOfReport": x["periodOfReport"]}, response["filings"]))

[{'formType': '10-K', 'periodOfReport': '2021-11-30'},
 {'formType': '10-K', 'periodOfReport': '2021-10-31'},
 {'formType': '10-K', 'periodOfReport': '2021-10-31'},
 {'formType': '10-K', 'periodOfReport': '2021-09-30'},
 {'formType': '10-K', 'periodOfReport': '2021-09-30'},
 {'formType': '10-K', 'periodOfReport': '2021-09-30'},
 {'formType': '10-K', 'periodOfReport': '2021-09-30'},
 {'formType': '10-K', 'periodOfReport': '2021-09-30'},
 {'formType': '10-K', 'periodOfReport': '2021-09-30'},
 {'formType': '10-K', 'periodOfReport': '2021-09-30'}]

Generate a list of company CIKs and corresponding financial years with this code:

period_by_cik = {}
period_by_ticker = {}

for filing in response["filings"]:
  cik, ticker, periodOfReport = filing["cik"], filing["ticker"], filing["periodOfReport"]

  if not cik in period_by_cik:
    period_by_cik[cik] = []

  if not periodOfReport in period_by_cik[cik]:
    period_by_cik[cik].append(periodOfReport)

  if len(ticker) > 0:
    if not ticker in period_by_ticker:
      period_by_ticker[ticker] = []

    if not periodOfReport in period_by_ticker[ticker]:
      period_by_ticker[ticker].append(periodOfReport)

period_by_cik maps a CIK to its recently reported fiscal financial year.

{'1206942': ['2021-10-31'],
 '1341726': ['2021-09-30'],
 '1377167': ['2021-09-30'],
 '1435181': ['2021-09-30'],
 '1502966': ['2021-09-30'],
 '1592782': ['2021-09-30'],
 '1725516': ['2021-11-30'],
 '1844817': ['2021-09-30'],
 '1853314': ['2021-09-30'],
 '72633': ['2021-10-31']}

period_by_ticker maps a ticker symbol to its recently reported fiscal financial year.

{'AACI': ['2021-09-30'],
 'DIGP': ['2021-09-30'],
 'FGCO': ['2021-09-30'],
 'GIAC': ['2021-09-30'],
 'GSPE': ['2021-09-30'],
 'NBLD': ['2021-11-30'],
 'NRT': ['2021-10-31'],
 'NUKK': ['2021-09-30'],
 'PHBI': ['2021-09-30']}

Answer 2

I don't have a programming solution, but I do know something about this that may help you figure one out.

You will need to find out what month of the year the company ends its fiscal year. The majority of companies that report to the SEC use the calendar ending date (December 31) as their fiscal year end date, but there are a significant number that use other month ending dates. Autozone (AZO) ends it's fiscal year on 8/31 of each year. Target (TGT) ends its fiscal year on 1/31 of each year. This will drive you mad if you let it. Remember, all you need to do is identify the month of the fiscal year end, the exact fiscal year ending date will be the last day of that month. From this you can calculate the quarter end dates.

Companies are require to submit their reports within XX number of days after the end of their fiscal period, depending on their filing "status". The status time frames are here... https://www.sec.gov/corpfin/cf-manual/topic-1#:~:text=90%20days%20after%20the%20fiscal,summary%20of%20accelerated%20filer%20rule Scroll down to section 1330.1 for the relevant table.

If you read down a little more you find that there are provisions for weekends and holidays, extensions, etc. What this means is that the "filed" dates are almost useless in identifying the actual fiscal year ending date unless you know the "status" of the company, if there were extension(s) issued and for how long, how many, etc. I don't think any of this information is available in the Facts file. All is not lost, though.

Companies must identify their fiscal year ending month when they file to become a public company. They also must submit an 8K if they want to change their fiscal year ending date. I don't know if this piece of data (fiscal year end month) is available in EDGAR, but if so its what you need to synchronize the dates. I get my fiscal year end month from a third party vendor (Zack's Research) free of charge. I think Zack's gets it from the SEC or from the Treasury itself. Sorry for the long winded reply. I hope it helps!

The following is a link to Zack's screener. https://www.zacks.com/screening/stock-screener Go to the Company Descriptors category and you will see "Month of Fiscal Yr End"

Answer 3

You could try the following. I selected cik based on the name given through a backward search. I chose the kpi by random.

import requests
import pandas as pd

cik = "0000866787"
kpi = "AccountsPayableCurrent"

url = f"https://data.sec.gov/api/xbrl/companyconcept/CIK{cik}/us-gaap/{kpi}.json"
response = requests.get(url, headers={"User-Agent":"Java-http-client/"})

response = requests.get(url, headers=headers)
json = response.json()

df = pd.DataFrame(json['units']['USD'])

Output:

end val accn    fy  fp  form    filed   frame
0   2009-08-29  2118746000  0000950123-09-071593    2010    Q1  10-Q    2009-12-17  NaN
1   2009-08-29  2118746000  0000950123-10-025907    2010    Q2  10-Q    2010-03-18  NaN
2   2009-08-29  2118746000  0000950123-10-058650    2010    Q3  10-Q    2010-06-16  NaN
3   2009-08-29  2118746000  0000950123-10-095687    2010    FY  10-K    2010-10-25  CY2009Q3I
4   2009-11-21  2187347000  0000950123-09-071593    2010    Q1  10-Q    2009-12-17  CY2009Q4I

You can then selelect the relevant columns using df[['fy', 'fp','val']]. You could also create a datetime index from the fy and fp columns.