Acquiring Data for Corporate Model

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

 

Videos on 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.