I have been working on an approach to retrieve financial data and then use the data as a basis for financial models or in valuation analysis for many years. If you have Bloomberg or something similar you may think this file is not very useful because I access free websites to get the data. But even if you have Bloomberg I hope you will take a look at the file if for no other reason to see just how much you can do with some macros and some structured excel formulas. The file goes to yahoo finance and MarketWatch and retrieves both market data (stock prices) and accounting data. I have tried to make effective presentations of financial ratios, multiples, cost of capital and other issues central to financial analysis in this file. I have made many changes to this process over the years and the tool described below is something you can adjust for yorself. I have used it in legal proceedings where I testify on the cost of capital. I have adjusted the tool so it can be used to retrieve data for international companies and adjust for exchange rates.
When I developed the tool, one of the frustrations I had was that it took so long to acquire the data. So, to increase the speed of acquiring data, I have separated the process of gathering data into two parts. The first part is for retrieval of the data and the second part is to analyze the data. This page discusses the process of acquiring the data and putting it together in a sheet that you can then use for analysis. The file attached to the button below can be used to retrieve a comprehensive financial data and then later connected with an analysis file. The process involves working with two files. The first is a file that collects the data and does not have any analysis. Without the file, it is faster to acquire the data. The second set of files contain the analysis. To establish this file,
.
.
.
.
If you have requested the financial library, you can look in Chapter 1 and find examples of how to use the file and find examples of analysis with different companies around the world. The files are in Folder H as illustratd below.
.
.
Step 1: Find Yahoo and Marketwatch Tickers
This page explains how to enter the ticker symbols on the menu sheet to create a new financial analysis database. Finding the appropriate ticker symbols can be the most time consuming part of the process of implementing the Financial Analysis Database. The good news is that Market Watch is much better with companies outside of the U.S. that trade on different exchanges. On this page I demonstrate how to search for the Market Watch and the Yahoo Symbol. I also explain how to add country codes for the Market Watch symbol. If the currency of the financial statements is different from the currency of the stock prices, ratios like the P/E ratio and the Price to Book Ratio will be incorrect in the historic analysis. This page demonstrates how to deal with this issue through reading in exchange rates. As with other pages that describe how to work with the Financial Analysis Database, the objective is to make sure you are completely comfortable with how the database works and how you can modify things by yourself. An updated version of the file that is used to perform the comprehensive analysis is attached to the button below. This example has only two stocks
.
.
Instructions for Downloading Files with Macros
.
Acquiring Tickers for U.S. Companies trading on U.S. Exchanges
For the simple example to start with, I have entered three ticker symbols for U.S. companies as shown in the screenshot below. To get the other tickers, I suggest that you use go to a search engine and then throw the markewatch ticker and the yahoo ticker into the spreadsheet. As with other pages that describe how to work with the Financial Analysis Database, the objective is to make sure you are completely comfortable with how the database works and how you can modify things by yourself.
Step 2: Enter the Tickers in the Read Data Sheet
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. Note that you should put the extra end of the URL into the main sheet.
Step 3: Download Data in the Retrieve Data Book by Pressing Buttons on Menu Sheet
Once 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.
.
.
.
.
.
.
.
.
Don’t forget to read the stock prices.
.
Step 4: Create a Template for the Analysis Book
Step 5: Copy the Names, Tickers and Currencies to the Analysis Book
Step 6: Delete the Ticker and the Break Sheet from the Retrieve Data Book and Select All Sheets
After copying the data, you can delete the sheets and then you can select all of the sheets. One the sheets are selected you can move them to the Analysis Book. I suggest not to save the file at this step.
.
.
.
.
.
Step 7: Move the Sheets into the Analysis Book and You are Ready to Go
.
Use ALT, E, M to move the sheets and then select the analysis book. You should move all of the sheets to the end — after the Break sheet in the Analysis Workbook.
.
.
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.
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.