Introduction
This page explains how you can create a financial database with many years of financial data without paying high fees for Bloomberg or something similar. Not only that, I hope that the presentation of the data and the way you can create your own analysis from raw data can help you. The database includes financial statements, stock prices and corresponding valuation ratios and multiples that now come from finance.yahoo.com. 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. 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 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 after the data is retrieved from the internet. I have made many changes to this process over the years and the tool described below is something you can adjust for yourself. 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. An illustration of two of the pages that you can use is shown below.
.
.
Getting Started and Testing
Step 1. – Get the ticker symbols
Fetching Limited History
If you want to get data for a few years and compare this data to the stock price to get a presenation, you can use the library in python called yfinance. If you install python on your computer and then get some libraries (yfinance, pandas, openpyxl) you can download the excel file below. This file can get financial data, stock price data and general data for a long list of companies. The data is put into separate sheets and then I use excel to do make analysis of graphs and other data. By using the INDIRECT function in excel along with XLOOKUP we can scrape a whole bunch of financial data from the internet and create a 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.
To get the analysis, you can click on the blue button below the screens shot and you will get a zipped file with the files that I zipped. This zipped file has the pyton code and the base excel file necessary for the downloading process (the python file named integrated_finance.py and the excel file named Integrated Financial Analaysis are the only files that you really need. You should put the files difectly on your computer (e.g. on the C: Drive) and not on your one drive. You also need to install python as I explain in the stock price download section (I include the video below).
.
.
.
The way the python program works to create the financial analsysis is discussed in the video below.
.
.
.
Prior Method Problems
Life has began to really suck when you try to get data. In the past you could get financial data from market watch wihout much effort — just using a macro with workbooks.open. This is no longer possible. Similarly, you could get stock price data from finance.yahoo which was wonderful because you could evaluate adjusted stock prices that adjusts for dividends. This is also no longer possible. Further, you could hack the FRED database to compare the data with just about any economic data series. This has changed also. After losing access to the data I was seriously depressed. But then I suppose you can use challenges as opportunities. I paid a couple of hundred dollars to access the finance.yahoo data and then revised the database. By paying you can acquire more historic data from financials and go back to something like to the value line reports. So the updated method uses consistent tickers from finance.yahoo (no switching between marketwatch and yahoo). Now the process is more structured and organized as when you re-do something it should improve.
.
.
If you have paid for finance.yahoo (as you can see below that I have by the GOLD tab), then you can google finance.yahoo.com and get the company. On the left you need just two things. Frist is the historic data to get the stock prices. The second is the financials which puts multiple years of financial statement data in a reasonably consistent format. For now, you can download the three annual financial statements. These will be consolidated in subsequent steps.
When I have used the database in the past for case studies on valuation or to demonstrate the history of an industry
The second file below inculdes the analyis. You can create this file by moving the sheets from the file above to after the break sheet. This is explained in the step by step process below. This file includes the presentation and analysis of the data.
.
The screenshot below illustrates putting data in a folder that will be uploaded.
.
Update the stock price indicies
.
.
.
.
.
I used to put the read ticker and the analysis into the same sheet. But the analysis sheet uses a lot of INDIRECT functions and it is faster to separate the read data from the analysis. This causes a couple of extra steps, but I think the time saving is work the extra manual steps. You can see below that the read file may take 1/2 an hour. It would take even longer if the process was combined.
.
- Step 1: Find prior versions of read data and financial analysis (attached to buttons)
- Step 2: Enter Tickers in the read data file
- Step 3: Clear data from the read data file
- Step 4: Run the Read Finanials, Read Stock Prices and Economic Variables (May take 1/2 hour)
- Step 5: Remove Range Names from the Read Data file
- Step 6: Copy Menu Page to the Financial Analysis File (Do not copy last ticker sheet name in column M)
- Step 7: Re-save the Read Data File with only Data — i.e. remove the menu sheet
- Step 8: Go to the financial analysis sheet and clear the data
- Step 9: Select all sheets from the Read Data sheet and then copy them to the end of the Financial Analysis Sheet
- Step 10: Go to the summary graph in the financial analysis sheet.
..
GBP problem (Shell Example)
Unfortunately, the yahoo database for stock prices uses pence instead of pounds when compiling the data. This means that when you compare stock prices to the book value per share or the earnings per share, you will get ratios that are off by 100. To resolve this you can use the copy and paste special with multiply and multiply the stock price data by .01 — this would be the case for example with Shell and BP. Note that for Shell and for BP, even though the stock price is in USD for the ADR (American Deposit Recipts), the financial statements are in GBP as illustrated below. Further note that you can find the currency in the balance sheet and not the income statement of the markewatch website.
.The screenshot below illustrates the yahoo stock price for SHELL.L from yahoo.
.
.
Collecting Tickers
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..
The screenshot below shows two folders that contain all of the files.