This page describes how to create and use a database that scrapes a whole bunch of financial data from the internet and creates an historic database of Financial Statements, P/E ratios, EV/EBITDA, operating ratios and other multiples for financial statement analysis, valuation, modelling and cost of capital analysis. You can get this kind of stuff from Bloomberg, but you have to pay a lot for a single terminal and you are stuck with black boxes for how to compute things like the cost of equity capital and presentation of the meaning of different financial ratios. The financial statements, ratio analysis and cost of capital analysis that you can create from downloading various information contains a lot of detail and can be used as the basis corporate financial models (comprehensive financial statements are included). All you do is enter some ticker symbols, then wait as the download proceeds, and you will get a database with valuation multiples and financial statements. The database can be used to generate historic financial statements for a financial models and it will present different multiples such as the P/E, P/B, EV/Invested capital and EV/EBITDA. I have spent many years on trying to get this database correct and I hope it is in good shape. But I do continually adjust the database. I begin the discussion on this page by explaining how to use the database. Later, I discuss some issues about creating the database.
The database is created by download and retrieving data from the internet (Market Watch and Yahoo finance) as well as a couple of data series from FRED for interest rates and for exchange rates. The real advantage of the process explained on this page is that if you were to go to MarketWatch that has very nice historic data it would take you a very long time to re-type the data by hand. You would also have to change the B that is in MarketWatch to billions and M to millions and so forth. Finally, you would not be able to easily compare the financial data to stock prices because MarketWatch has nice financial statement data but no stock prices. With the data downloading procedure you can present historic multiples for at least five historic years along with financial data like ROIC, ROE, Price to Book and other ratios like the growth rate in revenues and EBITDA. You can also evaluate the cost of capital using the horrible CAPM as well as more interesting methods that use a regression analysis of the market to book ratio and the implied P/E ratio. You can also go to the financial statement page and get a big head start on creating financial models. The databases are flexible meaning that you can pick your ticker symbols, press a button, patiently wait for a while and you get a comprehensive financial database. Please note that you may have to sometimes update the database when yahoo changes the URL or when I add some enhancements to the file. You can download the Financial Database with examples and without tickers filled by clicking on the button below. Please not that when yahoo changes the format of its reports the database must have little revisions. You can try this yourself or send me an e-mail at firstname.lastname@example.org where you find snags.
General Description of the Financial Database
You maybe saying to yourself what is the big deal; I can get so much data from the internet anyway (or maybe you pay scads of money to Bloomberg). You can indeed get amazing financial data websites like Market Watch for free including historic financial statements and analyst earnings projections. But key information is missing. Take the case of Market Watch. While historic financials are reported, Market Watch does not allow you to download stock prices so you can compute historic P/E ratios etc. Also Market Watch reports 2.5 billion as 2.5B and you need to adjust for the B,M and even T for trillions. Yahoo Finance has a lot of data also, in particular for stock prices. But the financial statement data is not as detailed and, again, the historic ratios for things ranging from ROIC to forward P/E are not available. Further, after may 2017, yahoo data is a pain to download.
The ability to scrape valuation multiples, cost of capital and historic financial data is, I believe, one of the most important items on my website although few of you seem very interested. As shown below, in one of the pages you can set-up your financial forecast; in another one of the pages you can review a whole bunch of ratios for a company to get a background for valuation and forecasting; in other pages you can create valuation analyses from comparative multiples; in yet other pages you can develop cost of capital analysis that is far better than the traditional CAPM B.S.
Using the Database File for Scrapping Financial Statements and Creating Historic Analysis for Financial Models
One of the really painful things about creating a corporate model with history is getting the financial data and then arranging it in a consistent format across years. I have made other videos explaining how you can use the Read_PDF.xlsm file and put data together with the a UDF called the UNION function in cases where you can only get data from financial reports in PDF format. If you can find tickers and MarketWatch publishes the financials, you can use the financial database. When you create the financial database, the process of acquiring data is much easier and all you have to do is to press the button to download the data. An example of the historic data for modelling analysis is shown in the screenshot below. This idea of starting with historic data is absolutely crucial in my opinion and is discussed in the A-Z corporate modelling section. If you don’t understand history and use history as a basis for your forecasts, your forecasts are utterly worthless (just like your politics would be). As the amount of detail provided by Market Watch is comprehensive, the financial database file can be used to download data that you can then use to create a financial model.
When I made this analysis I wanted to make a comparison across different companies. I still believe that this is the most important way to use the file. But if you want to get started with the file, it may be a good idea to begin with a single company and make sure everything is working. When you do this, you can begin with a company that presents its data in USD. The screenshot below illustrates how to begin the process. You go to the sheet named “Menu Sheet” and just put in the ticker for one company. In the example I have used Macy’s department stores that has a ticker symbol of M. The other thing that you should make sure is correct is the date of the financial statements. After you have put in the ticker symbol, you can press the button to read the financial date and read the stock price data. You should first clear the sheets with the clear sheets button. The start time and the end time in lines 9 and 10 show how long the reading process takes.
After you press the read button for one company you can watch as the data is read into the file. Then you will see separate sheets for the data that has been read into the file. The screenshot below illustrates one of these sheets for the income statement. There are a whole lot of these sheets for each company and if you include many different companies, there will be very many sheets. Once you have all of the data collected including the stock prices, the rest of the sheets organize data and compute ratios from the data using INDIRECT function along with LOOKUP and MATCH. In the screenshot below you can see that the data still has M for million and B for billion etc. The file includes functions to fix this and multiply the numbers to make sure the numbers have a common currency.
Once you read in the data you can look at some financial ratios. A good sheet to look at is the is the sheet named “Single Company Graph”. This sheet evaluate various ratios from equity perspective and free cash flow perspective. The first sheet illustrates the EPS and EBITDA. The top graph is the equity perspective and the bottom sheet is the free cash flow perspective. I have include a few graphs that illustrate the types of ratios you can use once you have the data loaded. This analysis of graphs can be more effective if you compare graphs within and industry. You could also work through the calculations and create your own ratios.
Let’s say that you are in the middle of a year and second quarter results have been published. If you use the last full historic year when making your forecast, the historic data may be stale. To rectify this situation you when you go to the page for downloading data, I have put a couple of techniques to use the quarterly data and create a forecast of the current year. As shown in the screenshot below, you can just use the quarterly data to create a year to date numbers (maybe this is ok when you are in the third quarter). Alternatively, you can evaluate the growth rate in quarterly data compared to the same quarters of the prior year and then create a growth rate. Finally, you can use the EPS forecast and the revenue forecast published by MarketWatch and Yahoo and try to derive the remaining expenses and revenues. These methods are illustrated on the screenshot below. There are a few different ways to use the quarterly data and some of these are illustrated in the page. The video below the screenshot explains how to find the ticker symbols and create a database that you can then use to create a corporate model.
Using the Financial Database File to Compare Financial Performance of Individual Companies
The excerpt below is an example of how you can look at different companies to try and tell a story. You can use drop down boxes for selecting different ratios and selecting different companies. Then you can do things like quickly working through different companies and observe the ROIC, growth in EBITDA, forward P/E ratios etc.
Using the Financial Database File to Evaluate Time Series and Comparative Data and the Proposition of Stability.
If multiples are used in valuation, they should be stable across time and stable within an industry. One of the main advantages of this database is that you can evaluate this proposition of stability. Most of the analyses demonstrate that the proposition of stability is false. You can use the drop down box to compare EV/EBITDA multiples, P/E multiples, P/B multiples and so forth. The first screen shot from the file demonstrates how you can compare ROIC across companies. If a company has a low ROIC, it may be difficult to increase to the industry average.
The second example demonstrates comparison of the P/E ratio across companies. The first question from looking at a table like this is determining whether you believe the ratios are constant across time and across the sample. As explained in the corporate finance theory pages, there are very good reasons that P/E ratios should should not be constant across either companies or across time. In this case, the median P/E ratio is pretty stable. But don’t be mislead. The individual companies have wide swings.
Using the Database Files for Analysis of Cost of Capital
I originally created the database file to analyse cost of capital using price to book ratios and market to book ratios. For years I have been going crazy about finding alternatives to the CAPM that do not depend on the estimated equity market risk premium, the beta or the alternative possible risk free rates. The screenshots below illustrate a couple examples of how to use the financial database in computing the cost of capital. The first screenshot is for utility companies in the U.S. where I make a regression equation from the price to book ratio and the forward return on equity. When you try this, you should understand that the relationship can be affected by changes in expected return and growth rates. The simple formula for price to book is P/B = (ROE-g)/(k-g). In solving for k, the formula is k-g = (ROE-g)/(P/B) or k = (ROE – g)/(P/B)+g. The screenshot below illustrates computation of the cost of capital using the price to book ratio with a regression equation and with the growth rate formula. In the screenshot you can see how to change the sample of companies to come up with different cost of capital estimates.
The screenshot below illustrates how cost of capital is computed using the value driver formula. The formula for the P/E ratio is P/E = (1-g/ROE)/(k-g). This formula can be re-arranged as k-g = (1-g/ROE)/P/E or k = (1-g/ROE)/PE + g. In the analysis below the growth is adjusted and the long-term growth is estimated. The formula is very sensitive to the growth rate. A user defined function is used to compute the cost of capital with different long-term and short-term growth rates.
Creating a Database: Step by Step Case with A Couple of Companies without Exchange Rate Issues (All Data is in USD)
In this section I describe how to use the financial database in a simple case. I hope this takes away any intimidation you may have about using the file. If you want to follow along with this you can start from a blank sheet. Putting a few companies into the file should only take a few minutes. You can download the Financial Database with examples and without tickers filled by clicking on the button below.
You can create the financial database that includes historic multiples, cost of capital analysis and historic financial data structured for financial models by entering ticker symbols on the first page of the spreadsheets available for download below. The historic database can also be used for cost of capital analysis and many other analytical tasks.
Step 1: Clear the Sheets
To work through a case using the financial database from A-Z first clear the data in the sheets. You can do this by pressing the box named “Clear Sheets” on the menu sheet as shown in the screenshot below. When you press this button, all of the sheets after the sheet named “Break Sheet” will be deleted. (Do not delete the sheet named Break Sheet or the Clear Sheets macro will not operate properly.)
Step 2: Enter Ticker Symbols in Menu Sheet
After clearing the sheets, enter the ticker symbols on the menu sheet. Finding the appropriate ticker symbols can be the most time consuming part of the process. This can be a bit painful for companies outside of the US where the company has an ADR stock price as well as a stock price in local currency. For the simple example to start with, I have entered three ticker symbols for U.S. companies as shown in the screenshot below. As the currency is USD and no special adjustments are necessary, you can use 1.0 in column C. Finally, change the TRUE and in column H and use TRUE for selected companies that you want to download information for.
If you want to use and existing set of tickers such as for the Dow 30 stocks, you can also go to the bottom of the sheet where I have put in some examples of companies to use. To use the selected companies, just copy the ticker symbols in to column D and column F of the menu sheet and then copy the company names into column B. You can also copy the currency adjustment factors into column C which will normally be 1.0 except for UK companies and the pence/pounds problem as well as companies that do not have stocks in local currency on yahoo.
For the selected companies I have included the ticker symbols and potential adjustments for currencies (one of the horrible examples is that Yahoo Finance uses Pence for UK companies while Market Watch uses Pounds. This means when comparing earnings to stock price and other statistics, an adjustment is required.) Another problem occurs for Japanese companies where Yahoo does not report stock prices in Yen.
Step 3: Download Data by Pressing Buttons on Menu Sheet
After you have collected the ticker symbols, you can run the three downloading buttons (the third of which is not be necessary unless you are making a CAPM analysis). The screenshot below shows the buttons that you press on the menu sheet. After you press the buttons, there should be a message at the bottom of the sheet that shows which URL is being scrapped. This is illustrated in the second screenshot. Please do not forget to clear the sheets first. Reading the financial data may take a long time and it may be a good idea to even re-start your computer or get more RAM.
Finding the correct tickers on Market Watch and Yahoo can be the most time consuming part of the process for companies outside of the US where the company has an ADR stock price as well as a stock traded on a local exchange. As explained below, the Market Watch financials are expressed in the local currency of the company. This means that to compare the company to stock prices, the stock prices must also be expressed in the same local currency.
Step 4: Review the Data
The screenshot below shows the results of one of the website scrapes that is put in a separate sheet. Note that the revenues have a B behind the numbers and other numbers have M behind. Because of this, a function is used to summarise the data.
Explanation of How to Collect Tickers and Download Data
The thing that takes me the most time in structuring the database on historic financial data is simply to find the appropriate ticker symbols. For companies like those in the Dow 30, you can just google the Dow 30 and get the list of companies. The ticker symbols for these companies are the same in Yahoo and Market Watch. So, you can google the name of the company along with yahoo finance. For companies that are not in the US and primarily trade on other exchanges, the process is a little more complex. Take for example the case of EDF in France. You can google EDF Market Watch and find the ADR. When you look at the financials in Market Watch you will see that financial statements are in Euro and not in USD. This means when you get the stock price from Yahoo, you must also get the data in Euro. So you must get the ticker symbol from Yahoo that uses Euro for stock prices (rather than the ADR).
The case is illustrated below for a UK company called Interserve. When you make a google search for the company with Market Watch, a couple of options appear. Note in the screenshot below there are different ticker symbols for Interserve. You cannot simply take the first option without checking the Market Watch site to see which of the tickers includes the financial statements.
The screenshot below shows the Market Watch site using the IRV ticker symbol. Note that you do not see any financial statements. The URL in the screenshot shows that the URL is IRV. But when you look at the page, you can see a menu that includes items for “OVERVIEW” and for “CHARTS.” But in the menu, there is no “FINANCIALS” option. When you see no financial statements, there will be nothing to download and if you use this IRV ticker in the financial database, there will be no financial statements for the company.
In the screenshot below, the ISVJF ticker is used as shown in the URL at the top. Now the menu on the page includes FINANCIALS after the “OVERVIEW”. Because the FINANCIALS are included, this ticker ISVJF should work in the file while the ticker in the above file will not work. Note that because the company is in London, the financials are in Sterling and not in USD.
The financials in GBP are shown in the screenshot below. I have circled the currency of the financials. As the financials are in GBP, the stock price must be in the same currency as stated above.
One of the main aspects of the financial database is combining stock prices with the financials to compute things like P/E, EV/EBITDA and Price to Book. Market Watch does not (to my knowledge) have stock prices that can be easily downloaded. This is why I use the Yahoo website. When you look for the Interserve stock price you find something like the following. Notice that I have circled the ticker with the .L at the end.
The screenshot below shows the IRV.L in Yahoo. Note how the price is in GBP. Note that even though the Yahoo site states that the price is in GBP. But the price is really in pence. This means that when you make calculations like Price to Book Ratio, the book value per share is pounds and the stock price is in pence. This means the ratio must be divided by 100.
For some companies such as TEPCO in Japan, you can get the financials from Market Watch in Yen. But for Yahoo, the stock prices are not reported in Yahoo (only in USD). In this case you can enter an exchange rate to adjust the downloaded stock prices. A similar problem arises with companies in the UK. Market Watch reports data in GBP but Yahoo reports data in Pence. To resolve these issues you can enter a currency adjustment in the menu page of the file. This currency adjustment is shown in column C of the excerpt below. These adjustments and the general way ticker symbols are entered into the file are illustrated in the excerpt below. Note that the Yahoo and Market Watch symbols are the same for companies traded in the U.S. but the ticker symbols are different for companies traded outside the U.S.
If you want to see the process of finding ticker symbols along with some discussion of the file, you can watch the video below. In this video I simply show you how to do a search for the ticker symbols and then enter the ticker symbols into the file to create a new database. The challenging issues are for companies that are primarily traded in markets outside the U.S.
Preliminary Step: Make Sure the WinHttp Services Add-in to VBA is Installed
To read the stock price data from finance.yahoo you need to have one of the VBA references installed. You can find this on the VBA menu by going first to Tools and References. Then make sure the item has a check box. Your can find more details of this on the stock price page.
The VBA menu is illustrated on the screenshot below. When this page appears, select the Tools menu as illustrated with my attempted circle. After selecting the tools menu, a sub-menu appears that is not shown. The first option is References. After selecting References, a set of check boxes appears as shown below. Note that “Microsoft WinHTTP Services, version 5.1” is listed and it does not have a check box. You need to make sure that this box is checked.
Don’t Worry if Marketwatch or Yahoo Formats Change
Sometimes the format of the Yahoo finance sheet changes or the format of the MarketWatch growth page changes. Don’t worry about this. You can go to the page and then modify the extraction from the source data. You can change the column and the row numbers using the MATCH and the INDEX. This is illustrated in the screenshot below. Note that to find the data I split the screens using the Windows add new window and then arrange all.
Once you have split the screen, you can then modify the MATCH and the INDEX with the INDIRECT function. I have set-up the sheet name until the ! as shown above. Then you can try to find the row number in the detail as in the above screenshot which is the MarketWatch summary. This is illustrated in the screenshot below. Note that the data is summarised in the sheet named “Data Current.”
Downloading Comprehensive Financial Data for Different Industries and Selected Groups of Companies
You can create your own files for different industries by entering ticker symbols in the menu page of the files that are available for download below. This ticker symbol business can be a bit tricky if the companies report financial statements in different currencies than the USD. I have included a few examples of how you can download files below for various industries that are attached to the buttons below so you can see how the programs work before you try things yourself. After you look through the stuff that is produced, you can to press the buttons to download and arrange the data. The file for insurance companies is different from the other files because of financial reporting. For insurance and financial companies, financial statements are reported in a different format from industrial companies. The file to download named insurance companies includes an example of how the process can work when there are different financial statements. . Please note: When you open the files, run the macro titled “RECALCULATE ALL” (find the button on the second page). .
Videos that Demonstrate how to Collect Ticker Symbols in Different Countries and Put Together a Database
The video below demonstrates how to use the file once the tickers have been entered and the types of information you can get from the file including: (1) comparative reports of P/E, EV/EBITDA, P/B, ROE etc.; (2) a historic layout of data that you can use as the basis of financial models; (3) an interactive report for single companies; and (4) cost of capital analysis.
Technical Discussion of the Financial Database File
Macros are used in the files that allow you to enter ticker symbols and then get a whole lot of data. The process adjusts URLs market watch and yahoo.com according to ticker symbols and then puts financial data into a lot of sheets. The INDIRECT function is then used to gather data together and summarize data in different ways. You can update the data like with other files although depending on your internet speed it may take a bit longer to read all of the stuff when you update your analysis. It has taken a lot of iterations to develop this file and I will probably change it in the future. I had thought you may be interested in the method for reading the data into the file and then arranging the data for comparison. I am told by young people that this is not interesting. People seem to want to press a button and just be finished. Apparently you do not want to look at any VBA code or other techniques.
Despite what I have been told, I have made various videos that describe technical details of how to develop the databases. I have also put a set of power point slides together that documents technical details of developing the file.