Download and Analyze SEC Comment Letters

          1
          !pip install -q sec-api
        

          1
          API_KEY = 'YOUR_API_KEY'
        

          1
          from sec_api import QueryApi
        

          2
          
        

          3
          queryApi = QueryApi(api_key=API_KEY)
        

          1
          query = {
        

          2
            "query": "formType:LETTER AND filedAt:[2021-01-01 TO 2021-12-31]",
        

          3
            "from": "0",
        

          4
            "size": "200",
        

          5
            "sort": [{ "filedAt": { "order": "desc" } }]
        

          6
          }
        

          7
          
        

          8
          response = queryApi.get_filings(query)
        

          1
          print('Number of LETTER filings published in 2021:', response['total']['value'])
        

          1
          import pandas as pd
        

          2
          
        

          3
          metadata = pd.DataFrame.from_records(response['filings'])
        

          4
          metadata.head(3)
        

          1
          def get_metadata(start_year=2021, end_year=2022):
        

          2
            form_type_filter  = 'formType:LETTER'
        

          3
            frames = []
        

          4
          
        

          5
            for year in range(start_year, end_year + 1):
        

          6
              for month in range(1, 13):
        

          7
                download_counter = 0
        

          8
                padded_month = str(month).zfill(2) # "1" -> "01"
        

          9
                date_range_filter = f'filedAt:[{year}-{padded_month}-01 TO {year}-{padded_month}-31]'
        

          10
                lucene_query = date_range_filter + ' AND ' + form_type_filter
        

          11
          
        

          12
                query_from = 0
        

          13
                query_size = 200
        

          14
          
        

          15
                while True:
        

          16
                  query = {
        

          17
                    "query": lucene_query,
        

          18
                    "from": query_from,
        

          19
                    "size": query_size,
        

          20
                    "sort": [{ "filedAt": { "order": "desc" } }]
        

          21
                  }
        

          22
          
        

          23
                  response = queryApi.get_filings(query)
        

          24
                  filings = response['filings']
        

          25
          
        

          26
                  if len(filings) == 0:
        

          27
                    break
        

          28
                  else:
        

          29
                    query_from += query_size
        

          30
          
        

          31
                  metadata = list(map(lambda f: {'ticker': f['ticker'],
        

          32
                                                 'cik': f['cik'],
        

          33
                                                 'formType': f['formType'],
        

          34
                                                 'filedAt': f['filedAt'],
        

          35
                                                 'filingUrl': f['linkToFilingDetails'],
        

          36
                                                 'linkToTxt': f['linkToTxt']
        

          37
                                                }, filings))
        

          38
          
        

          39
                  df = pd.DataFrame.from_records(metadata)
        

          40
                  # remove all entries without a ticker symbol
        

          41
                  df = df[df['ticker'].str.len() > 0]
        

          42
                  df.loc[:, 'filingUrl'] = df['filingUrl'].apply(lambda url: url.replace('ix?doc=/', ''))
        

          43
                  frames.append(df)
        

          44
                  download_counter += len(df)
        

          45
          
        

          46
                print(f'✅ Downloaded {download_counter} metadata objects for {year}-{padded_month}')
        

          47
          
        

          48
            result = pd.concat(frames)
        

          49
          
        

          50
            print(f'✅ Download completed. Metadata downloaded for {len(result)} filings.')
        

          51
          
        

          52
            return result
        

          1
          metadata_2021 = get_metadata(start_year=2021, end_year=2021)
        

          1
          metadata_2021.head(3)
        

          1
          !pip install -q pandarallel
        

          1
          from sec_api import RenderApi
        

          2
          
        

          3
          renderApi = RenderApi(api_key=API_KEY)
        

          1
          import re, os, time
        

          2
          
        

          3
          def download_content(filing, retry_counter=0):
        

          4
            url      = filing['linkToTxt']
        

          5
            ticker   = filing['ticker']
        

          6
            formType = filing['formType']
        

          7
          
        

          8
            try:
        

          9
              new_folder = './filings/' + ticker
        

          10
              date = filing['filedAt'][:10]
        

          11
              file_name = date + '_' + formType + '_' + url.split('/')[-1]
        

          12
              file_path = new_folder + '/' + file_name
        

          13
          
        

          14
              if not os.path.isdir(new_folder):
        

          15
                os.makedirs(new_folder)
        

          16
          
        

          17
              content = renderApi.get_filing(url)
        

          18
          
        

          19
              pattern = r"<TEXT>(.*?)</TEXT>"
        

          20
              matches = re.findall(pattern, content, re.DOTALL)
        

          21
          
        

          22
              if matches:
        

          23
                if '<PDF>' in matches[0]:
        

          24
                  extracted_text = matches[1]
        

          25
                else:
        

          26
                  extracted_text = matches[0]
        

          27
          
        

          28
                with open(file_path, 'w') as f:
        

          29
                  f.write(extracted_text)
        

          30
          
        

          31
                return extracted_text
        

          32
              else:
        

          33
                print("No text found between <TEXT> and </TEXT>.")
        

          34
                return ''
        

          35
          
        

          36
            except Exception as e:
        

          37
              print(f"❌ {ticker}: download failed: {url}")
        

          38
              print(f"Error: {str(e)}")
        

          39
          
        

          40
              if retry_counter < 4:
        

          41
                time.sleep(2)
        

          42
                return download_content(filing, retry_counter=retry_counter+1)
        

          43
              else:
        

          44
                return ''
        

          45
          
        

          46
          filing = metadata_2021.iloc[140]
        

          47
          content = download_content(filing)
        

          1
          print(content)
        

          1
          from pandarallel import pandarallel
        

          2
          
        

          3
          number_of_workers = 10
        

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

          1
          # download a simple of 500 comment letters
        

          2
          sample = metadata_2021.head(500)
        

          3
          sample['content'] = sample.parallel_apply(download_content, axis=1)
        

          4
          
        

          5
          # uncomment the line below to download all SEC comment letters
        

          6
          # sample = metadata_2021
        

          7
          # sample['content'] = metadata_2021.parallel_apply(download_content, axis=1)
        

          8
          
        

          9
          print('✅ Download complete')
        

          1
          def get_directory_size(directory):
        

          2
            total_size = 0
        

          3
          
        

          4
            for path, dirs, files in os.walk(directory):
        

          5
              for file in files:
        

          6
                file_path = os.path.join(path, file)
        

          7
                total_size += os.path.getsize(file_path)
        

          8
          
        

          9
            return total_size / (1024 * 1024)
        

          10
          
        

          11
          directory_path = './filings'
        

          12
          total_size_mb = get_directory_size(directory_path)
        

          13
          print(f"Total size of all downloaded SEC Comment letters: {total_size_mb:.2f} MB")
        

          1
          sample_content = sample['content'].values[50]
        

          2
          print(sample_content[:800], '...')
        

          1
          def extract_form_type(text):
        

          2
            try:
        

          3
              header_text_pattern = r".*Dear"
        

          4
              header_text = re.findall(header_text_pattern, text,  re.I | re.S)
        

          5
              header_text = header_text[0]
        

          6
          
        

          7
              pattern = r"(Statement .*|Form [^\s]+)"
        

          8
              matches = re.findall(pattern, header_text,  re.I)
        

          9
              matches = list(set(matches))
        

          10
              matches = [re.sub(r'Statement (on|off)\s?', '', x, flags=re.I) for x in matches]
        

          11
          
        

          12
              return matches
        

          13
          
        

          14
            except Exception as e:
        

          15
              print(e)
        

          16
              return []
        

          17
          
        

          18
          extract_form_type(sample_content)
        

          1
          # Blackberry letter refers to two form types: 10-K, 10-Q
        

          2
          sample_content = sample['content'].values[100]
        

          3
          print(sample_content[:800], '...')
        

          1
          print('Form types associated with the letter sent to Blackberry:')
        

          2
          print(extract_form_type(sample_content))
        

          1
          sample['refers_to'] = sample['content'].parallel_apply(extract_form_type)
        

          1
          df = sample['refers_to'].explode('refers_to')
        

          2
          df.reset_index(drop=True, inplace=True)
        

          3
          form_types = sorted(list(df.unique().astype(str)))
        

          4
          
        

          5
          print('EDGAR Form Types of SEC Comment Letters:')
        

          6
          print('\n'.join(form_types))
        

          1
          import matplotlib.pyplot as plt
        

          2
          
        

          3
          df = sample['refers_to'].explode('refers_to')
        

          4
          df.reset_index(drop=True, inplace=True)
        

          5
          
        

          6
          value_counts = df.value_counts()
        

          7
          
        

          8
          plt.figure(figsize=(10, 6))
        

          9
          value_counts.plot(kind='bar')
        

          10
          plt.xlabel('Form Type')
        

          11
          plt.ylabel('Number of SEC Comment Letters')
        

          12
          plt.title('Number of SEC Comment Letters per EDGAR Form Type (2021)')
        

          13
          plt.xticks(rotation=45)
        

          14
          plt.show()
        

          1
          def remove_header(letter):
        

          2
            cleaned_letter = re.sub(r'.*Dear', '', letter, flags=re.S)
        

          3
            return cleaned_letter
        

          4
          
        

          5
          sample['cleaned_content'] = sample['content'].parallel_apply(remove_header)
        

          1
          print('Example of a comment letter without its header')
        

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

          3
          print(sample['cleaned_content'].values[0][:1500], '...')
        

          1
          !pip install -q nltk
        

          1
          import nltk
        

          2
          
        

          3
          nltk.download('stopwords')
        

          4
          nltk.download('omw-1.4')
        

          5
          nltk.download('wordnet')
        

          6
          
        

          7
          wn = nltk.WordNetLemmatizer()
        

          1
          stopwords = nltk.corpus.stopwords.words('english')
        

          2
          print(f'There are {len(stopwords)} default stopwords. They are {stopwords}')
        

          1
          # remove stop words
        

          2
          sample['content_standardized'] = sample['cleaned_content'].apply(lambda x: ' '.join([w for w in x.split() if w.lower() not in stopwords]))
        

          3
          # lemmatize
        

          4
          sample['content_standardized'] = sample['content_standardized'].apply(lambda x: ' '.join([wn.lemmatize(w) for w in x.split() if w not in stopwords]))
        

          5
          # remove other non-informative words
        

          6
          wordlist = ['please', 'page', 'comment']
        

          7
          sample['content_standardized'] = sample['content_standardized'].apply(lambda x: ' '.join([w for w in x.split() if w.lower() not in wordlist]))
        

          1
          print(sample['content_standardized'].values[0][:300])
        

          1
          # docs = list(sample['cleaned_content'])
        

          2
          docs = list(sample['content_standardized'])
        

          1
          import collections
        

          2
          from tqdm import tqdm
        

          3
          from sklearn.feature_extraction.text import CountVectorizer
        

          4
          
        

          5
          # extract vocab to be used in BERTopic
        

          6
          vocab = collections.Counter()
        

          7
          tokenizer = CountVectorizer().build_tokenizer()
        

          8
          
        

          9
          for doc in tqdm(docs):
        

          10
            vocab.update(tokenizer(doc))
        

          11
          
        

          12
          vocab = [word for word, frequency in vocab.items() if frequency >= 15]; len(vocab)
        

          1
          import locale
        

          2
          locale.getpreferredencoding = lambda: 'UTF-8'
        

          1
          !pip install -q bertopic
        

          2
          from bertopic import BERTopic
        

          1
          # source: https://colab.research.google.com/drive/1W7aEdDPxC29jP99GGZphUlqjMFFVKtBC?usp=sharing#scrollTo=DIv6fRecTvTw
        

          2
          from sentence_transformers import SentenceTransformer
        

          3
          from umap import UMAP
        

          4
          from hdbscan import HDBSCAN
        

          5
          
        

          6
          # prepare sub-models
        

          7
          # see here for a list of all models: https://www.sbert.net/docs/pretrained_models.html
        

          8
          embedding_model = SentenceTransformer('all-mpnet-base-v2')
        

          9
          embeddings = embedding_model.encode(docs, show_progress_bar=True)
        

          10
          
        

          11
          umap_model = UMAP(n_components=5, n_neighbors=15, random_state=42, metric='cosine', verbose=True)
        

          12
          hdbscan_model = HDBSCAN(min_samples=20, gen_min_span_tree=True, prediction_data=False, min_cluster_size=20)
        

          13
          # vectorizer_model = CountVectorizer(vocabulary=vocab, stop_words="english")
        

          14
          
        

          15
          # fit BERTopic without actually performing any clustering
        

          16
          topic_model= BERTopic(
        

          17
                  embedding_model=embedding_model,
        

          18
                  umap_model=umap_model,
        

          19
                  hdbscan_model=hdbscan_model,
        

          20
                  # vectorizer_model=vectorizer_model, # causes divide by zero encountered in true_divide
        

          21
                  verbose=True
        

          22
          ).fit(docs, embeddings=embeddings)
        

          1
          topic_model.get_topic_info()
        

          1
          topic_model.get_topic(topic=1)
        

          1
          # reduce dimensionality of embeddings
        

          2
          umap_model = UMAP(n_components=5, n_neighbors=15, random_state=42, metric='cosine', verbose=True)
        

          3
          reduced_embeddings_2d = umap_model.fit_transform(embeddings)
        

          1
          import itertools
        

          2
          import pandas as pd
        

          3
          
        

          4
          # define colors for the visualization to iterate over
        

          5
          colors = itertools.cycle(['#e6194b', '#3cb44b', '#ffe119', '#4363d8', '#f58231',
        

          6
                                    '#911eb4', '#46f0f0', '#f032e6', '#bcf60c', '#fabebe',
        

          7
                                    '#008080', '#e6beff', '#9a6324', '#fffac8', '#800000',
        

          8
                                    '#aaffc3', '#808000', '#ffd8b1', '#000075', '#808080',
        

          9
                                    '#ffffff', '#000000'])
        

          10
          color_key = {str(topic): next(colors) for topic in set(topic_model.topics_) if topic != -1}
        

          11
          
        

          12
          # prepare dataframe and ignore outliers
        

          13
          df = pd.DataFrame({"x": reduced_embeddings_2d[:, 0], "y": reduced_embeddings_2d[:, 1], "Topic": [str(t) for t in topic_model.topics_]})
        

          14
          df['Length'] = [len(doc) for doc in docs]
        

          15
          df = df.loc[df.Topic != '-1']
        

          16
          df = df.loc[(df.y > -10) & (df.y < 10) & (df.x < 10) & (df.x > -10), :]
        

          17
          df['Topic'] = df['Topic'].astype('category')
        

          18
          
        

          19
          # get centroids of clusters
        

          20
          mean_df = df.groupby('Topic').mean().reset_index()
        

          21
          mean_df.Topic = mean_df.Topic.astype(int)
        

          22
          mean_df = mean_df.sort_values('Topic')
        

          1
          !pip install -q adjustText
        

          1
          import seaborn as sns
        

          2
          from matplotlib import pyplot as plt
        

          3
          from adjustText import adjust_text
        

          4
          import matplotlib.patheffects as pe
        

          5
          
        

          6
          fig = plt.figure(figsize=(10, 10))
        

          7
          sns.scatterplot(data=df, x='x', y='y', c=df['Topic'].map(color_key), alpha=0.4, sizes=(0.4, 10), size="Length")
        

          8
          
        

          9
          # annotate top 50 topics
        

          10
          texts, xs, ys = [], [], []
        

          11
          for row in mean_df.iterrows():
        

          12
            topic = row[1]["Topic"]
        

          13
            name = " - ".join(list(zip(*topic_model.get_topic(int(topic))))[0][:3])
        

          14
          
        

          15
            if int(topic) <= 50:
        

          16
              xs.append(row[1]["x"])
        

          17
              ys.append(row[1]["y"])
        

          18
              texts.append(plt.text(row[1]["x"], row[1]["y"], name, size=10, ha="center", color=color_key[str(int(topic))],
        

          19
                                    path_effects=[pe.withStroke(linewidth=0.5, foreground="black")]))
        

          20
          
        

          21
          # adjust annotations such that they do not overlap
        

          22
          adjust_text(texts, x=xs, y=ys, time_lim=1, force_text=(0.01, 0.02), force_static=(0.01, 0.02), force_pull=(0.5, 0.5))
        

          23
          plt.show()
        

          24
          # plt.savefig("visualization2.png", dpi=600)
        

          1
          topic_model.visualize_topics()
        

          1
          topic_model.visualize_barchart()
        

          1
          topic_model.visualize_heatmap()