This page describes how to use and create a database that compares historic stock prices, commodity prices, stock price indices and economic data. The stock price database is unique in the way it can be used to evaluate a combination of different economic and financial series (compare stocks to median income; adjust for inflation etc.) It can be automatically updated with new dates and new series (individual stocks, economic series, commodity prices and stock indices) by pressing a couple of buttons that retrieve data from various websites. It works pretty fast too. When working with the file, you can select different stocks, commodity prices, economic series and indices and present them in different ways (as indices, adjusted for exchange rates, different time periods, as percent changes). The growth rate or IRR for each series as well as beta and volatility is also presented and you can make all sorts of adjustments for inflation and exchange rates. This page explains how you can download the database, how to use the database in economic analysis and how to create the database.
All you really have to do to use this file is to enter stock tickers (generally from yahoo), symbols for stock indices, commodity price symbols from FRED, codes for economic series from FRED and then press a couple of buttons. This page explains how to use the file in analysis; how to find tickers and codes used in the database; and, technical details of how to read in data from the internet. The specific process for creating this file is explained in a couple of videos and in the power point slides below. But first, you can download the file by clicking on the button below.
This file retrieves data on a monthly basis from Yahoo finance. You can easily modify the download process to extract data on a weekly or daily basis. You can also adjust the FRED data series to read more detailed data. I beg you to send me an e-mail at email@example.com if you are having any problems with this file. It is an important file on the website and I want to make sure that you can really use it.
Updates to the Database
Whenever I open the database I seem to change it. Revisions that I have made and that are not discussed below include creating rate indices so that you can see the returns on bonds relative to stocks in different periods.
Using the Comprehensive Stock Price Data in Simple Manner
When you open the file above, it may seem intimating, it is to me. (I admit that I am sometimes afraid or my own files). So, I have tried to include a simple step by step explanation of how to use the stock, commodity and economic database file in this section. If you want to try this simple case, you can download another file that is blank and has options to download only a couple of shares. You can also download a separate a second simple file that has this example completed. So, in this section I proceed from a blank file and illustrate how to use the approach. I have attempted to explain this in a step by step manner.
You can download the simple files by clicking on the buttons below. The first file is completely blank and can be used to work through the exercises below. The second file has a completed version of the exercise.
Step 1: Begin Entering Data
To begin the process of stock analysis, you need to enter the ticker symbol or FRED symbol (FRED stands for Federal Reserve Economic Database). There are a number a separate pages where you can enter different ticker symbols or FRED codes. After you enter the ticker symbol and re-calculate the spreadsheet and the company name, then the new item appears on the menu.
In the screenshot below, the manner in which you enter data for stock indices that are compiled on finance.yahoo.com. You enter the ticker in column D in the blue area. In column E you enter the code that will used in the menu and check boxes. Finally in column F, the description of index is input. After you select indices, you press the Read All button. Each index is put into a different sheets. If you clear the sheets, you can start a run and it does not take long to read all of the data.
Once the data is put into a sheet (see the sections below), you can select the base series. In the example that is used I put in the DAX and the S&P 500 as two indices. When you use the drop down box you can select the basis for comparison. This is used for Beta statistics and creating a time series. This could be GDP or Oil prices.
Note that the graph above has not been repaired for removing the #N/A on unused the series. Note also that the IRR, the volatility and the Beta are presented. Click on this sentence to go to the page that describes how to get rid of the #N/A.
Step 2: FRED Data for Other Stock Indices
The next step demonstrates how to acquire data from the FRED database which includes other stock price indices. The process of entering data is illustrated in the screen shot below. There is a list of many of the stock indices in column K. Next to the name of the index, the FRED Code is shown. The code includes brackets which have to be removed. In the screenshot below I have selected NASDAQ from the list and copied the code into F16.
After pressing the button titled “Read Federal Reserve Economic Database”, the NASDAQ is put in a new sheet. The sheet has the name of the FRED code as shown in the screenshot below. When the data is read into the file, it looks like a mess at the top (because of the text to columns code in the macro). Note also that some of the values are not entered.
You can clean this sheet up by pressing SHIFT, CNTL, F. The clean-up can be done for any of the items are read from the FRED database. The screenshot below illustrates the sheet format after it has been cleaned up.
The screenshot below illustrates a comparison of the S&P 500, the NASDAQ and the DAX where the data sources are from different. The start dates are shown in column AA which allows you to change the start dates.
Step 3: Download Inflation, Exchange Rates and Economic Series
The sheet named exchange rates allows you to download series from the FRED database. The first six series from the this page are used for deflating or adjusting the data for inflation. Out of these first six series the first two are designed for inflation. For these series the following formula is used:
Deflated Data = Raw Data/Inflation Index
Some stocks and indices are not expressed in USD by finance.yahoo. For example, the DAX index is expressed in Euro. To compare series, you may want to convert series to USD. For the exchange rates, the USD/Other Currency is Used. If the exchange rate is multiplied by the raw data, the raw data is converted to USD. This is illustrated below:
Data in USD = Raw Data in Other Currency x USD/Other Currency
The screenshot below illustrates how to enter various inflation, exchange rate and economic series into the file. A long list of economic series from FRED is listed in column Q with the FRED code listed in column O.
After pressing the button at the top of the page, the data is read into a new sheet. An example of how the data is read into a new sheet is shown below. After reading in the data, the indices are used to deflate the data if the option is chosen.
The process of adjusting data is illustrated in two screenshots below. In the first screenshot the DAX, S&P 500, and DAX are shown without adjustment.
In the second screenshot, the DAX is adjusted for the Exchange Rate. In this case, the index of the USD/Euro exchange rate is computed. This index is multiplied by the DAX value. The DAX growth rate and index value is then computed.
In the screenshot below, the S&P 500 is deflated by the price index. This is done by using the spinner box in K14. Note the lower IRR which reflects the real increase in wealth from holding stocks.
The screenshot below illustrates how the economic time series can be used. The initial screenshot of this section included the GDP. The graph demonstrates the dramatic dispersion between growth in stocks and growth in income.
Step 4: Download Inflation, Exchange Rates and Economic Series
After entering the economic series, you can move to the commodity price page. You could use the whole file as a method for comparing commodity prices. The screenshot below demonstrates entering codes for the commodity prices. As with economic series, this data comes from the FRED database. In the screenshot below the list of commodity prices in column J.
After pressing the button to read the commodity price data, the prices are put into new sheets. After you enter the data, there is a new item in the menu on the graph page which is named the Multiple Stock Price Graph. In the screenshot below, the False button is pressed and then the Oil Price is clicked.
Step 5: Downloading Individual Stocks
The final step addresses how to include individual stocks in the analysis. You can go to the page Yahoo Stocks and enter the ticker symbol from finance.yahoo.com. You should also enter the name of the company. Once you enter the data the entire list is shown in the Read Stocks page. As you enter more companies, the list becomes longer.
After pressing the button on the yahoo stocks page, the data for each stock is put into different pages. One of the stocks read is GE. The sad graph of GE is shown below. Because of dividends, the return on GE is still above the overall market for the time series selected. If you change the time period you can evaluate if the beta is stable.
A second analysis that includes Google is shown in the screenshot below. The google versus GE and the market demonstrates the dramatic growth in Google shares. You can find where to start the graph in column in AA where you can see that the first period for Google was August of 2004. Even with the very high growth in Google shares, the IRR is 21%.
The graph in the screenshot below includes Amazon and Apple. Note that the increase in Amazon is dramatic. The question about whether this growth is justified is addressed in the financial database. Note that the stock price indices hardly show up.
The graph below illustrates how you can make an analysis with different time periods. The graph demonstrates the decline after the crash of 2000 when the stocks fell dramatically. The screenshot also shows the differences in volatility and beta.
Demonstration of the Stock Price, Commodity Price and Economic Series Database
The graphs below demonstrate how you can use the comprehensive database. The first screen shot demonstrates how you can compare different stocks for different time periods (you just click on the check boxes and select the starting year). The graph shows the the index values of the stocks starting from the first selected date. The volatility of the stock and the IRR of the stock from the initial date is also presented. In the graph below the rate of return on the stock market since 2010 is 11.75% and the volatility is 11.84%. Other stocks had higher IRR’s and higher volatility.
The second graph shows how the German DAX relative to the US S&P. The DAX is converted to USD and the index is created as with the graph above. The graph shows that the DAX has had a higher growth rate than the U.S. market and also has higher volatility.
The video below walks you through issues with using the comprehensive stock price file and illustrates how to compare stocks to commodity prices and economic series. You can see how to select different stocks by clicking on the check boxes; how to select different starting and ending points; how to interpret IRR’s, volatility and beta that are shown for the stocks and how to adjust for changes in inflation and exchange rates.
Creating a New Database – Finding Ticker Symbols
One of the most painful things about making one of these files that allows you to evaluate the returns, multiples, growth rates, cost of capital and other items is simply getting the ticker symbols correct. To address this I have done two things. The first is collecting ticker symbols for different industries that you may be interested in. For utility companies, stocks in the DOW (other than financials), life insurance companies, yieldcos, selected international electricity companies, telecom companies, utility companies, large banks, oil majors and other companies I have put together lists. You can get these companies in the zipped file below.
Step 1: Install the VBA Add-in for WebRequest
When you open the file, an instruction panel appears. This panel shows that you should have a check box pressed for “Microsoft WinHTTP Services, Version 5.1.”
To find this check box you need to get into the VBA editor and go to the Tools Menu. It sounds a little harry, but you only have to do it one single time. To get to this menu you could go to the View menu in excel. Then press the Macros button. Then either select and existing macro or create a new macro. You then get the VBA menu as shown below.
The first screenshot shows how to get to the VBA menu from the View menu. I have circled where you press the macros button. Then, once the macro box appears, select the create button or the edit button. After that, the VBA menu will pop up.
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.