In a previous blog post we’ve described some of the problems that are encountered when processing corporate filings from the SEC’s EDGAR database in XBRL. Today we present a system that overcomes these issues by downloading and processing XBRL filings on a daily basis, normalizing the financial data, computing common financial ratios, storing all data into a SQLite database, and then presenting the corporate reporting data for financial analysis through a mobile app for iOS, Android, and Windows Phone.
We are making all the sources for the data ingestion and normalization process available as Python scripts on GitHub under an Apache 2.0 license. The actual normalization rules as well as the financial ratios being computed are defined in external JSON files that can easily be modified without the need to edit the Python sources. In addition the MobileTogether Design file describing the mobile app is also available as open source in the same repository on GitHub so the mobile application can be easily customized as well to graph different data, show other financial ratios, or do more sophisticated financial analysis.
The system utilizes three Altova Server products running on a Windows 2012 server machine to provide data ingestion and processing as well as the back-end for the mobile application:
- FlowForce Server is used as a workflow scheduler to run a nightly batch job to download new XBRL filings from the SEC’s EDGAR database and then process them accordingly.
- Filings are analyzed and processed by RaptorXML+XBRL Server and financial data is normalized and extracted into a SQLite database via Python scripts, using the built-in Python interpreter in RaptorXML. In order to make the system easy to customize, the normalization logic is not directly encoded in Python, but described in the form of external JSON files that specify the processing rules separately for normalizing income statements, balance sheets, and cash flow statements. Similarly, JSON files are used to describe the calculations needed to compute the financial ratios that are stored in the database.
- MobileTogether Server is used to power a mobile app that provides easy access to the resulting data in the SQLite database for financial analysis from mobile devices. The MobileTogether client app can be used on mobile phone or tablet devices running iOS, Android, or Windows Phone, as well as on desktops or laptops running Windows 8, 8.1, or 10.
These three servers could also be run in the cloud, on a Linux box, or even a Mac, depending on your IT preferences, as the three server products are available for Windows, Linux, and MacOS platforms and can be deployed on-premises or in the cloud.
The mobile app was built with Altova MobileTogether Designer in order to provide a cross-platform mobile app and to make it easily extensible. While the current app only allows looking up one company and then viewing its financial reports, it can easily be expanded to do industry comparisons or run a stock screener.
We provide this app as an example that can easily be modified by people to create tailored mobile in-house apps for more sophisticated financial analysis as needed.
You can look up companies using either a name match and then selecting from a list, or by entering the ticker symbol.
Once a company is found, we show four columns of either quarterly or annual data for the normalized financial statements. The user can toggle the display in millions or thousands as appropriate. The device can be rotated to landscape mode, and the font size can be adjusted as needed.
For example, here is the quarterly balance sheet view with a graph of the asset and debt growth since 2009. We’ve decided to also plot Debt to Assets % as a line in the graph. You can easily change what is being plotted here, since we are also making the MobileTogether Design file available as open-source.
Similarly, here is the Cash Flow statement shown in the annual view. We’re graphing Operating, Investing, and Financing Cash Flows.
Last, but not least, we calculate a number of common financial ratios from the normalized financial statements, shown here as trailing twelve months view.
Since the Python code to build and fill the database is available as open-source, you can use this as a starting point and customize as needed.
We also show the list of 10-Q and 10-K filings that were used as the source of the presented information. The user can click or tap on any filing and go straight to the web browser to view the original filing on the SEC’s website.
Back-end Python scripts
The processing of XBRL filings from the SEC’s servers on our back-end system is done using RaptorXML+XBRL Server, a powerful XBRL processing engine that can be used as a platform for sophisticated financial systems. RaptorXML comes with a complete API and is fully scriptable using the built-in Python interpreter.
We have made all the Python scripts for this project available as open source on GitHub. These scripts include the following:
Download EDGAR RSS feeds
The SEC maintains RSS feeds for each month with the filings submitted during that month. These feeds can be accessed on the SEC website under https://www.sec.gov/Archives/edgar/monthly/. The script
download_feeds.py will download all the available RSS feeds (by default starting from 2005) and store them within the
feeds subfolder. The script can be re-run regularly as it will check for already present feeds and download only the latest or missing feeds.
The actual XBRL filings can also be downloaded as zip archives from the SEC. The EDGAR RSS feeds contain amongst other meta-information the URL to the zip archive for a given filing. The script
download_filings.py iterates over each feed item and downloads the zip archive into a
filings subfolder. Please note that there are over 150,000 filings to this date and downloading them will take several hours and require >30GB of hard-drive space. If you plan to download all of the filings, please do it during the nighttime or on weekends in order to not put too much strain on the SEC’s servers.
build_secdb.py analyzes quarterly (10-Q) and annual (10-K) corporate filings and computes a normalized summary view of the main financial statements. The data for the balance sheet, income statement and cash-flow statement as well as some common financial ratios are then stored into a database. Please refer to the additional documentation describing the database schema and tables in more detail.
In order to keep the SEC DB up-to-date one needs to refresh the EDGAR RSS feed, download all new XBRL filings and finally process those filings and store the data into the SEC DB. This entire workflow is automated by the
daily_update.py script. It performas all necessary tasks to keep the SEC DB up-to-date. The script can be run on a daily, weekly or monthly basis from Altova FlowForce Server as a
commandline function (or could be automated as a crontab batch job on Linux systems, if no other complex workflow logic is required).
For further information and more details, please see the full documentation of those scripts here on GitHub.
How to get started
If you’re happy with the way the mobile app looks and the data that is being displayed, you can start using the mobile app “as is” right away (and it’s free):
- Download the MobileTogether app from the AppStore on your phone. It’s available for iOS, Android, and Windows Phone.
- In the MobileTogether app add a new server with the address: secdb.altova.com and port 80 and leave the user as anonymous and the password empty.
- Done! You can now use the app to look up financial statements as shown here today.
If you want to customize the normalization, build your own database, add different financial analysis, or calculate other ratios:
- Download, clone, or fork the sources from GitHub:
- Download and install the RaptorXML+XBRL Server and FlowFore Server software from here: http://www.altova.com/download-trial-server.html
- Download and install the MobileTogether Server software from here:
- You can request a free 30-day license key-code for all Altova products directly from the license server that is being installed with these products.
If you want to customize the mobile application views and graphs:
- Download and install the free MobileTogether Designer software from:
- Download the SECdb.mtd file from the GitHub repository and open it with MobileTogether Designer. You can now customize the data that is being rendered in the graphs, as well as the tables being displayed, their formatting, and any additional calculations.
Further documentation for the Python scripts is available in the /docs subdirectory of the GitHub repository.
We hope this mobile app is helpful for overcoming some of the challenges associated with processing corporate filings from the SEC database. Please let us know how it works for you and how we can make it better. We’d also love to hear about any customization you do to extend the solution.