Extract Financial Statements from SEC Filings with Python

          1
          API_KEY = 'YOUR_API_KEY'
        

          1
          !pip install -q sec-api
        

          1
          from sec_api import XbrlApi
        

          2
          
        

          3
          xbrlApi = XbrlApi(API_KEY)
        

          1
          # URL of Apple's 10-K filings
        

          2
          url_10k_aapl = 'https://www.sec.gov/Archives/edgar/data/320193/000032019322000108/aapl-20220924.htm'
        

          3
          
        

          4
          aapl_xbrl_json = xbrlApi.xbrl_to_json(htm_url=url_10k_aapl)
        

          1
          print("Keys of income statement dictionary in XBRL from Apple's 10-K filing")
        

          2
          print('--------------------------------------------------------------------')
        

          3
          print(*list(aapl_xbrl_json['StatementsOfIncome'].keys()), sep='\n')
        

          1
          import pandas as pd 
        

          2
          
        

          3
          # convert XBRL-JSON of income statement to pandas dataframe
        

          4
          def get_income_statement(xbrl_json):
        

          5
              income_statement_store = {}
        

          6
          
        

          7
              # iterate over each US GAAP item in the income statement
        

          8
              for usGaapItem in xbrl_json['StatementsOfIncome']:
        

          9
                  values = []
        

          10
                  indicies = []
        

          11
          
        

          12
                  for fact in xbrl_json['StatementsOfIncome'][usGaapItem]:
        

          13
                      # only consider items without segment. not required for our analysis.
        

          14
                      if 'segment' not in fact:
        

          15
                          index = fact['period']['startDate'] + '-' + fact['period']['endDate']
        

          16
                          # ensure no index duplicates are created
        

          17
                          if index not in indicies:
        

          18
                              values.append(fact['value'])
        

          19
                              indicies.append(index)                    
        

          20
          
        

          21
                  income_statement_store[usGaapItem] = pd.Series(values, index=indicies) 
        

          22
          
        

          23
              income_statement = pd.DataFrame(income_statement_store)
        

          24
              # switch columns and rows so that US GAAP items are rows and each column header represents a date range
        

          25
              return income_statement.T 
        

          26
          
        

          27
          income_statement_aapl = get_income_statement(aapl_xbrl_json)
        

          28
          
        

          29
          print("Income statement of Apple's 2022 10-K filing as dataframe")
        

          30
          print('---------------------------------------------------------')
        

          31
          income_statement_aapl
        

          1
          url_10k_aapl = 'https://www.sec.gov/Archives/edgar/data/320193/000032019322000108/aapl-20220924.htm'
        

          2
          
        

          3
          xbrl_json_aapl = xbrlApi.xbrl_to_json(htm_url=url_10k_aapl)
        

          4
          
        

          5
          income_statement_aapl = get_income_statement(xbrl_json_aapl)
        

          6
          
        

          7
          income_statement_aapl.to_excel('income-statement-aapl.xlsx')
        

          1
          # Apple's 10Ks of the last 4 years, 2019 to 2022
        

          2
          url_10k_2019 = 'https://www.sec.gov/Archives/edgar/data/320193/000032019319000119/a10-k20199282019.htm'
        

          3
          url_10k_2020 = 'https://www.sec.gov/Archives/edgar/data/320193/000032019320000096/aapl-20200926.htm'
        

          4
          url_10k_2021 = 'https://www.sec.gov/Archives/edgar/data/320193/000032019321000105/aapl-20210925.htm'
        

          5
          url_10k_2022 = 'https://www.sec.gov/Archives/edgar/data/320193/000032019322000108/aapl-20220924.htm'
        

          6
          
        

          7
          xbrl_json_2019 = xbrlApi.xbrl_to_json(htm_url=url_10k_2019)
        

          8
          xbrl_json_2020 = xbrlApi.xbrl_to_json(htm_url=url_10k_2020)
        

          9
          xbrl_json_2021 = xbrlApi.xbrl_to_json(htm_url=url_10k_2021)
        

          10
          xbrl_json_2022 = xbrlApi.xbrl_to_json(htm_url=url_10k_2022)
        

          1
          income_statement_2019 = get_income_statement(xbrl_json_2019)
        

          2
          income_statement_2020 = get_income_statement(xbrl_json_2020)
        

          3
          income_statement_2021 = get_income_statement(xbrl_json_2021)
        

          4
          income_statement_2022 = get_income_statement(xbrl_json_2022)
        

          1
          income_statements_merged = pd.concat([income_statement_2019, income_statement_2020, income_statement_2021, income_statement_2022], axis=0, sort=False)
        

          2
          
        

          3
          # sort & reset the index of the merged dataframe
        

          4
          income_statements_merged = income_statements_merged.sort_index().reset_index()
        

          5
          
        

          6
          # convert cells to float
        

          7
          income_statements_merged = income_statements_merged.applymap(lambda x: pd.to_numeric(x, errors='ignore'))
        

          8
          
        

          9
          print("Merged, uncleaned financials of all income statements")
        

          10
          print('-----------------------------------------------------')
        

          11
          income_statements_merged.head(10)
        

          1
          income_statements = income_statements_merged.groupby('index').max()
        

          2
          
        

          3
          # reindex the merged dataframe using the index of the first dataframe
        

          4
          income_statements = income_statements.reindex(income_statement_2019.index)
        

          5
          
        

          6
          # loop over the columns
        

          7
          for col in income_statements.columns[1:]:
        

          8
              # extract start and end dates from the column label
        

          9
              splitted = col.split('-')
        

          10
              start = '-'.join(splitted[:3])
        

          11
              end = '-'.join(splitted[3:])
        

          12
          
        

          13
              # convert start and end dates to datetime objects
        

          14
              start_date = pd.to_datetime(start)
        

          15
              end_date = pd.to_datetime(end)
        

          16
          
        

          17
              # calculate the duration between start and end dates
        

          18
              duration = (end_date - start_date).days / 360
        

          19
          
        

          20
              # drop the column if duration is less than a year
        

          21
              if duration < 1:
        

          22
                  income_statements.drop(columns=[col], inplace=True)
        

          23
          
        

          24
          # convert datatype of cells to readable format, e.g. "2.235460e+11" becomes "223546000000"
        

          25
          income_statements = income_statements.apply(lambda row: pd.to_numeric(row, errors='coerce', downcast='integer').astype(str), axis=1) 
        

          26
          
        

          27
          print("Income statements from Apple's 10-K filings (2016 to 2022) as dataframe")
        

          28
          print('------------------------------------------------------------------------')
        

          29
          income_statements
        

          1
          all_revenues_json = xbrl_json_2019['StatementsOfIncome']['RevenueFromContractWithCustomerExcludingAssessedTax'] + \
        

          2
                              xbrl_json_2020['StatementsOfIncome']['RevenueFromContractWithCustomerExcludingAssessedTax'] + \
        

          3
                              xbrl_json_2021['StatementsOfIncome']['RevenueFromContractWithCustomerExcludingAssessedTax'] + \
        

          4
                              xbrl_json_2022['StatementsOfIncome']['RevenueFromContractWithCustomerExcludingAssessedTax']
        

          5
          
        

          6
          all_revenues = pd.json_normalize(all_revenues_json)
        

          7
          
        

          8
          all_revenues.drop_duplicates(inplace=True)
        

          1
          # convert the 'value' column to a numeric type
        

          2
          all_revenues['value'] = all_revenues['value'].astype(int)
        

          3
          
        

          4
          mask_iphone = all_revenues['segment.value'] == 'aapl:IPhoneMember'
        

          5
          mask_ipad = all_revenues['segment.value'] == 'aapl:IPadMember'
        

          6
          mask_mac = all_revenues['segment.value'] == 'aapl:MacMember'
        

          7
          mask_wearables = all_revenues['segment.value'] == 'aapl:WearablesHomeandAccessoriesMember'
        

          8
          
        

          9
          revenue_product = all_revenues[(mask_iphone | mask_ipad | mask_mac | mask_wearables)]
        

          10
          
        

          11
          # pivot the dataframe to create a new dataframe with period.endDate as the index, 
        

          12
          # segment.value as the columns, and value as the values
        

          13
          revenue_product_pivot = revenue_product.pivot(index='period.endDate', columns='segment.value', values='value')
        

          14
          
        

          15
          print("Apple's revenues by product from 2017 to 2022")
        

          16
          print('---------------------------------------------')
        

          17
          revenue_product_pivot
        

          1
          import matplotlib.pyplot as plt
        

          2
          import matplotlib.ticker as ticker
        

          3
          
        

          4
          # plot the histogram bar chart
        

          5
          ax = revenue_product_pivot.plot(kind='bar', stacked=True, figsize=(8, 6))
        

          6
          
        

          7
          # rotate the x-axis labels by 0 degrees
        

          8
          plt.xticks(rotation=0)
        

          9
          
        

          10
          # set the title and labels for the chart
        

          11
          ax.set_title("Apple's Revenue by Product Category", fontsize=16, fontweight='bold')
        

          12
          ax.set_xlabel('Period End Date', fontsize=12)
        

          13
          ax.set_ylabel('Revenue (USD)', fontsize=12)
        

          14
          
        

          15
          # set the legend properties
        

          16
          ax.legend(title='Product Category', loc='upper left', fontsize='small', title_fontsize=10)
        

          17
          
        

          18
          # add gridlines
        

          19
          ax.grid(axis='y', linestyle='--', alpha=0.3)
        

          20
          
        

          21
          # remove the top and right spines
        

          22
          ax.spines['top'].set_visible(False)
        

          23
          ax.spines['right'].set_visible(False)
        

          24
          
        

          25
          # format y-axis ticks to show values in millions in dollars
        

          26
          formatter = ticker.FuncFormatter(lambda x, pos: '$%1.0fB' % (x*1e-9))
        

          27
          plt.gca().yaxis.set_major_formatter(formatter)
        

          28
          
        

          29
          # map the original labels to new labels
        

          30
          label_map = {
        

          31
              'aapl:IPhoneMember': 'iPhone',
        

          32
              'aapl:MacMember': 'Mac',
        

          33
              'aapl:IPadMember': 'iPad',
        

          34
              'aapl:WearablesHomeandAccessoriesMember': 'Wearables & Home'
        

          35
          }
        

          36
          
        

          37
          # create a list of new labels based on the original labels
        

          38
          new_labels = [label_map[label] for label in sorted(revenue_product['segment.value'].unique())]
        

          39
          handles, _ = ax.get_legend_handles_labels()
        

          40
          plt.legend(handles=handles[::-1], labels=new_labels[::-1])
        

          41
          
        

          42
          # add the values in billions of dollars to each part of the bar
        

          43
          for p in ax.containers:
        

          44
              ax.bar_label(p, labels=['%.1f' % (v/1e9) for v in p.datavalues], 
        

          45
                           label_type='center', fontsize=8)
        

          46
          
        

          47
          plt.show()
        

          1
          mask_america = all_revenues['segment.value'] == 'aapl:AmericasSegmentMember'
        

          2
          mask_europe = all_revenues['segment.value'] == 'aapl:EuropeSegmentMember'
        

          3
          mask_china = all_revenues['segment.value'] == 'aapl:GreaterChinaSegmentMember'
        

          4
          mask_japan = all_revenues['segment.value'] == 'aapl:JapanSegmentMember'
        

          5
          mask_asia_rest = all_revenues['segment.value'] == 'aapl:RestOfAsiaPacificSegmentMember'
        

          6
          
        

          7
          revenue_geo = all_revenues[(mask_america | mask_europe | mask_china | mask_japan | mask_asia_rest)]
        

          8
          
        

          9
          # pivot the dataframe to create a new dataframe with period.endDate as the index, segment.value as the columns, and value as the values
        

          10
          revenue_geo_pivot = revenue_geo.pivot(index='period.endDate', columns='segment.value', values='value')
        

          11
          
        

          12
          print("Apple's revenues by region from 2017 to 2022")
        

          13
          print('---------------------------------------------')
        

          14
          revenue_geo_pivot
        

          1
          # plot the histogram bar chart
        

          2
          ax = revenue_geo_pivot.plot(kind='bar', stacked=True, figsize=(8, 6))
        

          3
          
        

          4
          # rotate the x-axis labels by 0 degrees
        

          5
          plt.xticks(rotation=0)
        

          6
          
        

          7
          # set the title and labels for the chart
        

          8
          ax.set_title("Apple's Revenue by Region", fontsize=16, fontweight='bold')
        

          9
          ax.set_xlabel('Period End Date', fontsize=12)
        

          10
          ax.set_ylabel('Revenue (USD)', fontsize=12)
        

          11
          
        

          12
          # set the legend properties
        

          13
          ax.legend(title='Region', loc='upper left', fontsize='small', title_fontsize=10)
        

          14
          
        

          15
          # add gridlines
        

          16
          ax.grid(axis='y', linestyle='--', alpha=0.3)
        

          17
          
        

          18
          # remove the top and right spines
        

          19
          ax.spines['top'].set_visible(False)
        

          20
          ax.spines['right'].set_visible(False)
        

          21
          
        

          22
          # format y-axis ticks to show values in millions in dollars
        

          23
          formatter = ticker.FuncFormatter(lambda x, pos: '$%1.0fB' % (x*1e-9))
        

          24
          plt.gca().yaxis.set_major_formatter(formatter)
        

          25
          
        

          26
          # map the original labels to new labels
        

          27
          label_map = {
        

          28
              'aapl:AmericasSegmentMember': 'America',
        

          29
              'aapl:EuropeSegmentMember': 'Europe',
        

          30
              'aapl:GreaterChinaSegmentMember': 'China',
        

          31
              'aapl:JapanSegmentMember': 'Japan',
        

          32
              'aapl:RestOfAsiaPacificSegmentMember': 'Asia Rest'
        

          33
          }
        

          34
          
        

          35
          # create a list of new labels based on the original labels
        

          36
          new_labels = [label_map[label] for label in sorted(revenue_geo['segment.value'].unique())]
        

          37
          handles, _ = ax.get_legend_handles_labels()
        

          38
          plt.legend(handles=handles[::-1], labels=new_labels[::-1])
        

          39
          
        

          40
          # add the values in billions of dollars to each part of the bar
        

          41
          for p in ax.containers:
        

          42
              ax.bar_label(p, labels=['%.1f' % (v/1e9) for v in p.datavalues], 
        

          43
                           label_type='center', fontsize=8)
        

          44
          
        

          45
          plt.show()