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).
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.
