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.
On this web page I demonstrate how you can select alternative stocks, stock indices (like the S&P 500 or the DAX), economic series, exchange rates, timing and other factors and put these in the database. On the associated web page (this is the link), I explain how you can work with the macros and create this kind of database by yourself. All you really have to do to use this database file for your own analysis 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 economic analysis and how to find tickers and codes used in the database. 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 change the timing from monthly to weekly or daily). You can also adjust the FRED data series to read more detailed data on a weekly or daily basis. I beg you to send me an e-mail at email@example.com if you are trying to use the file and you are having any problems. I think it is an important file on the website and I want to make sure that you can really use it. The two videos below explain how you can use the file and the rest of this webpage walks you through some of the details.
Updates to the Database – Stock Index Since 1927
Whenever I open the database I seem to change it and make revisions. Some of the revisions that I have recently made and that are not discussed in detail below include adding longer-term data for the S&P 500 and creating interest rate indices so that you can see the returns on bonds relative to stocks in different periods. To create the interest rate indices that can be compared to stock prices, I use the interest rate published in FRED and start with and index of 1.0 for the first period. I put this into the column to the right of the interest rate that is read in from the FRED data. The index is computed as the starting value of 1.0 multiplied by the interest rate. In this way you can compare the return on stocks with the return on bonds over different periods. The chart below illustrates the history. It demonstrates that any idea that the risk premium earned on stocks has been anywhere near the 6% returns referred to in the literature is not close to being true.
The long-term S&P 500 is demonstrated on the screenshots below. I don’t know how yahoo.finance made adjustments to find the historic index all the way back to 1927, but I find the history valuable. Now you can compare the stock price declines of 1929, 1987, 2000, 2008 and 2020. I had to make some adjustments to the stock price file and I use monthly data. But when you select monthly data from yahoo.finance, it does give you the most recent price and I have made sure the last price is on the graph. I have put this graph in real terms (I have divided the prices by the GDP deflator). The manner in which you can do this is shown on the second screenshot below.
The screenshots below show you how to get this graph. You should first read the data. To do this, go to the page named “Yahoo Index”. The screenshot demonstrates how you can select different indices and then choose the starting point of the data (just like you would if you went to the yahoo.finance website and did this by hand). Note that the starting date is 1927. This process creates URL’s and then runs a macro that reads the data. To read the data, go to the top of the page and press the button named “Read All”. Note that if you have not already done this, you should press the button named “Clear Data”) to clear out the existing data and create new sheets that appear at the end of the file. After you press the button named “Read All”, you inspect the data and make sure it is read in correctly. After reading the yahoo indices, you can go to the page named “Exchange Rates” and read in the inflation rates and exchange rates so that you can adjust the indices to put them in comparable currency or in real terms. You can also run the macros to read the data in the sheet name “Read Stocks”
After reading in the data, you can go to the sheet named “Multiple Stock Graph” and play around with the graph. I have put a screenshot of some of the things you can do with this sheet in the screenshot below. In the screenshot I present the nominal S&P index with the Real S&P index. The drop down box at the top left allows you to select different stocks or indices or commodity prices as the base value (for example you could compare things to the oil price). In the example, the graph begins in 1989, but it could begin in 1927. This is done with the dropdown box for the years. When you change the date, the IRR is computed assuming you buy the stock at the beginning of that year. The options at the right of the graph illustrate how you can adjust the stock prices by inflation indices or exchange rates. The list at the left allows you to select different stocks, commodity prices, indices or economic series by clicking on the check boxes. In the example shown below, I compare the S&P with the DAX in Germany as well. You can see the start date of the index so that you do not go back before the index starts. To see details of how the process works you can go to the webpage on technical details. To see how to make the graphs with flexible numbers of stock prices, you can go to the page that explains this. When the graphs do not look correct, press the recalculate button.
Updates to the Database – Interest Rates and Bonds
I have also made a revision to compare the value you would generate from bonds with stock indices. I want to do this to test the reasonableness of the silly estimates of the equity market risk premium. I started thinking about this a long time ago when I used to testify about the cost of capital and I would be forced to use the capital asset pricing model. The problem with comparing bonds to stocks is that the yield on bonds is reported by FRED (the federal reserve economic database). So, when reading the interest rates, I add two columns to compute and Index that you would earn from investing in a bond. To create the index, I push the interest rate to the right. Then I divide the index into periodic periods and divide by 100 to express the index as a percent. Finally, I create and index from the interest rate that measures how much return you would make from buying the bond and then re-selling the bond later as you re-invest the interest you are earning.
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: Entering Data in Separate Sheets for Different Stocks and Economic Series
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 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 page name “Read Stocks”.
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.