I am looking for advice on how to smartly get from unstructured financial data to a clean summary of balance sheets. I think of this not as a coding problem but of a question how to approach the task - and whether anyone has some information that might help.

I use the following SEC Data base to the 10-K and all 10-Q report of thousands of companies. It is relatively simple to filter each quaterly datafile by adsh (unique ID for all reports) to retrieve something akin to the below (Apple 20210 10-K).

Apple 2010 10-K Balance Sheet Items

In this picture the "tag" column reports the balance sheet item, "ddate" marks the end of the reporting period, "qtrs"=0 implies a point in time account (aka balance sheet item) and "value" is the USD amount in the balance sheet.

Amazingly, I can loop this process over all companies in the SEC data base to get the data for all companies. However, each 10-K for larger companies has hundreds of entries. My goal would be to have a relatively complete balance sheet in a readable format, that clusters balance sheet items into relevant groups (i.e. Working capital, debt, etc.) as you would know from standard financial models and is ideally flexible enough to apply this to a variety of companies through time.

My question is: How would you approach this?

Please see here for my notebook where I try this out, note that the process requires the user to download the files first and set up the folder structure.

  • $\begingroup$ Split companies sector wise would be a good place to start. It would be difficult to have a general model to collate all tags in the SEC data and allot them or group them in standard financial elements directly. For example, companies have significant leeway in naming line items of cash flow statement. Banks would have a different balance sheet structure than non-banking companies. $\endgroup$ Jun 24 at 19:22
  • $\begingroup$ Thank you for the suggestion, I could basially loop through sectors, look for common B/S and I/S items and then group those. Good idea! $\endgroup$
    – Jan Felix
    Jun 25 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.