Practical Problems in Acquiring Historic Data from PDF Files and SEC Reports
One of the real world challenges in creating a financial model is to acquire historic accounting data in an efficient and flexible manner. I believe it is is instructive to put together as many historic years as possible to evaluate trends, volatility of returns and develop downside cases in corporate models. There are a few ways to get historic data these days. One way is to go the an internet site like marketwatch.com and put together the data. If your company must register with the U.S. securities and exchange commission, you can get data back through 1994.
This lesson set walks you through acquiring data from PDF files and putting together many years of data in a consistent format. In addition to acquiring data from PDF files, the lesson set demonstrates how to acquire data from companies that make filings to the Securities and Exchange Commission in the U.S. You can receive credit for this lesson set simply by sending in a completed set of historical financial statements along with the original source of the data. If you do this and send me the files I will put your name on the page of the website that lists the star financial analysts who have completed a lesson. Here are the steps for getting a nice set of financial statements put together:
1. Find the financial statements and get them with Chrome (it works better than Firefox)
2. Copy the data into excel and arrange statements together (e.g. all income statements in first set of sheets, followed by balance sheet etc.)
3. Use the Read PDF excel file to format the financial statements.
4. Put all of the statements together — put all income statements in one file; balance sheets in second file etc.
5. Get the UNION function and the INDMAT function into your file from the Read PDF file.
6. Arrange sections of each statement with a few lines and have a lot of blanks. eg. operating section of income statement, current assets of balance sheet.
7. Use the UNION function to put together a common set of titles.
8. Use the INDMAT function to put together data for different years with common titles.
9. Copy and paste as values in new file for analysis.
Innovations in Corporate Modelling
You will also find a lot of innovations in corporate modelling. These include:
- Use of Historic Switch to Make Incorporation of New Financial Statements
- Evaluation of ROIC and Invested Capital Using Switches and SUMPRODUCT
- Development of INTERPOLATE Function to Evaluate Assumptions
- Automation of Scenario Analysis with Scenario Reporter
- Effective Automation of Historic Data Graphs with Flexible Spinner Box
- Resolution of Circular References Related to Interest Expense and Taxes
- Deprecation Techniques that Account for Changing Growth and Implied Retirements
- Development of Techniques to Automate Constant Capital Structure in Financial Models
- Dynamic Goal Seek Functions for Evaluation of Cost of Capital Using P/E Ratios
- User Defined Functions for Computing Stable Capital Expenditures to Depreciation and Other Items
Innovations in Corporate Data Analysis
- Creation of techniques to download stock price data, financial statement data and economic data
- Stock price database that allows you to evaluate IRR’s, volatility and beta for stocks, stock price indices, economic series and commodity prices.
- Financial Database that allows you to extract and evaluate financial data, financial ratios, and cost of capital across companies.
- Extraction of Data that Enables you to have Historic Basis for Creating Financial Models.
- Interest Rate, Exchange Rate and Commodity Price Databases that Include Historic Evaluation of Term Structures, Volatility and Other Statistics.
- Comprehensive Country by Country Database to Evaluate Growth and Risks Across the World.
Videos Associated with Lesson Set 2: Reading Financial Statements
Videos with this lesson set are boring and have some sound quality problems and I admit this, sorry. I have left them on the website anyway. Perhaps the idea of reading in financial statements from PDF files and from the SEC is not really interesting. However, efficient use of: (1) the READ PDF file to get data from the PDF’s on the internet and get the data as quickly as possible into excel is a very practical issue; (2) putting financial statements together from different years when the titles change is a very real world pain and is solved with the important UNION function; (3) quickly arranging the data itself after putting together the titles with the UNION function is very helpful and is accomplished with the INDMAT function. These three things are all made possible with functions and macros that are included in the READ PDF file.
Even with all of these tools, putting together financial statements is a pain. But it is a very real problem and anything to help this problem is I think useful. If you go through the videos and then try an example I think you can really improve your efficiency.
The first two videos below explain how to use the read_pdf excel file. It covers the subject of downloading data from financial reports using alternative sources.
.
If you are going to put financial statements together using the read_pdf file, there are inevitably changes in the format of the income statement, balance sheet and cash flow from year to year. It is a real pain to arrange the data in an easy manner without the horror of moving things around manually in excel. To solve this problem of different titles, I have created a union function that is intended to create long list of accounts. After you have created the accounts, you can use the match and index with the long list and the individual accounts to create a common format. This is the technique presented in the video.
The union function is a UDF. you can find the UNION function in the file named read_pdf
The final two videos in this set address cleaning up and graphing the data after you have put it together with the UNION udf. The first video demonstrates how to put the financials together in a structured manner and the second video shows how to use the INDEX function to make flexible graphs with financial data.