Comprehensive Stock Price, Economic Variables, Commodity Prices and Exchange Rate Analysis

This page describes how to create a database that compares historic stock prices, commodity prices, stock price indices and economic data. The database can be automatically updated  with new dates and new series (stocks, economic series, commodity prices and stock indices) by pressing a couple of buttons. When working with the file, you can select different stocks, commodity prices, economic series and indices. 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, symbols for stork 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.

Comprehensive Stock Price, Commodity Price, and Economic Series Database with Monthly Prices

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 edwardbodmer@gmail.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.

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.

 

Stock Price Database File with Small Amounts of Ticker Symboles, Stock Indices, Economic Data and Commodity Prices

 

Stock Price Database File that Allows you to Scrape Stock Prices and then Compare them to Indices and Economic Data

 

 

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.

Sometimes you do not just want to look at graphs on the web in finance.yahoo or google finance etc. Instead, you want the source data. Maybe you want to the source data to create your own statistical analysis (e.g. you may want to regress and oil stock against the oil price). Maybe you want to adjust for exchange rates. Maybe you need the stock price to compute EV/EBITDA in historic periods. Maybe you want to create your own graphs or do something else with the data. For all of this, you want the real data an not just a graph that shows “5yr” or “max.”

 

 

This page shows you how to automatically download data from the internet to excel using the WORKBOOKS.OPEN or different alternatives. One alternative is working with WinHttpRequest along with MyRequest.Open “GET”. After using the WORKBOOKS.OPEN function to retrieve data and download from the internet to excel, various techniques are demonstrated that show you how to move the downloaded files into different sheets . This uses VBA with a FOR NEXT loop and the INDEX function. Techniques are presented which demonstrate how to summarise the data using the MATCH, INDEX and INDIRECT functions. The workbook files you can download on this page also show how retrieve stock prices from the internet and then graph the stock prices using the OFFSET function or using the #NA technique.

The major file that includes all of the techniques for downloading, summarising and presenting data is in the file named comprehensive stock price analysis monthly.

The file that you can download below (power point slides) includes technical details on programming issues associated with downloading, extracting and summarizing the data.  This file uses the WEBREQUEST method rather than the WORKBOOKS.OPEN method for extracting the yahoo.finance data.

Power Point Slides the Explain the Technical Details Associated with the Comprehensive Stock Price File

 

Stock Prices and Finance Data: You used to be able to easily get stock price data from finance.yahoo WORKBOOKS.OPEN. But as of May 2017 yahoo has become very mean and stopped allowing the prices to be downloaded easily. The good news is that you can use WinHttpRequest and create crumbs and cookies (explained below). There were other alternatives besides yahoo finance that are have some problems including Google Finance http://www.google.com/finance/historical and the St. Louis Federal Reserve Bank (FRED). The link for the St. Louis Fed is: http://fred.stlouisfed.org/categories. As of November 2017 there is another problem in that Google has limited the ability for you to see source data. Hopefully google will allow you to get data in the future.

General Objective of Scraping Stock Price Data: I assume that you want stock price data to come into excel and not the code that creates the website itself (e.g. the yahoo website). I assume also, that you are like me not some fancy coder and you want to find the way to do this with the minimum effort with as simple of a code as possible. So here are some of my suggestions:

  • Use the workbooks.open whenever you can — this is the simplest method to retrieve data
  • When you are downloading from a website and not a file, then use workbooks.open and try clean up the data by using copy and paste special as values.
  • When you want to download a file and the workbooks.open method does not work, try the WEBREQUEST method. The painful part of this is creating a cookie and a crumb. If you have crumbs in the website you use the initialise program described below
    • Dim WebRequest As WinHttp.WinHttpRequest combined with
    • Set WebRequest = New WinHttp.WinHttpRequest

Philosophy of Putting Work in Excel Rather than VBA Code: I try to do a few things in describing how to scrape stock price data from various websites.

  • First, rather than put a lot of stuff in the VBA code and try to make adjustments in the VBA code itself, you should separate and then re-combine the URL as much as possible in the excel spreadsheet.
  • Second, where possible use the WORKBOOKS.OPEN and work on reading a single stock price in a macro.
  • Once you have the single read worked out and put the results of WORKBOOKS.OPEN back into the base workbook (using move or copy and paste), you can use an INDEX function and a simple loop to repeat the process and read multiple stocks.This is often name read_all in the macros below.
  • Finally, when you are finished reading, use the INDIRECT function to find and summarise the data.

The video below explains various technical concepts underneath using the comprehensive stock price file with the objective that you can modify the file.

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.

 

Comparison of Different Methods for Reading Data

Sometimes the WORKBOOKS.OPEN does not work. This can occur with long URL’s or with URL’s that have crumbs at the end. To resolve this issue you can use the WINHTTP.WINHTTPWEBREQUEST method. To do this you much do the following:

1. Add a dimension to the VBA that defines a variable that will be used to get the data
2. Use the SET command to set the variable defined above as a WINHTTP.WINHTTPWEBREQUEST
3. Use the GET commend to get the data
4. Retrieve the data into a long text.
5. Use a dimension variable and define it to a RANGE to make the process convert the string to a text.

The VBA code for doing this is illustrated below. Underneath the diagram is a short excel file that illustrates different methods to work with this.

 

WEBREQUEST.JPG

When you use the WinHttpRequest, you need to open VBA and select an add-in from the reference menu. If you don’t do this the method will not work as demonstrated below.

 

Reference.JPG

To enable the add-in you should first go to the references menu after opening a macro (press the ALT, F8 key to get the VBA menu and open create a new file or use and existing file). After you are in the VBA page, go to tools and references. Then select the WinHTTP services as shown below.

 

 

reference selection.JPG

Acquiring, Comparing and Analyzing Stock Price Data

Downloading and Comparing Stock Prices and Computing Betas and Volatility

I have a whole lot of files and analysis associated with reading and performing statistical analysis on stock prices. Indeed, on the google drive I have an entire separate folder for reading and analysing stock prices. The best file is probably a file named COMPREHENSIVE STOCK PRICE ANALYSIS. This file can be used on a monthly or daily or weekly basis. This COMPREHENSIVE STOCK PRICE ANALYSIS file available for download below the videos go to google finance.yahoo.com and read data on different sets of stock prices. I think the best source for reading stock prices was finance.yahoo.com. But now yahoo has removed the possibility of reading the data in a simple manner with WORKBOOKS.OPEN. Somebody from a company from signal.com showed how to use WinHttpRequest and the first file below uses this method. I have also made various files that can read stock prices from google finance rather than from yahoo.

This COMPREHENSIVE STOCK ANALYSIS file is intended to enable you to compare prices of stocks with other stocks, with various different indices, and with commodity prices. Examples of statistical analysis are included and you can make comparisons between stocks in different countries using a common exchange rate. The file that contains the VBA code and the equations to do this analysis are in a file named “comprehensive stock analysis.” There are two ways you can use this file (like many others). You can try to really use it for stock analysis (or you can use it for case studies). But I don’t think many of you will do this. Alternatively you can use this as a collection of different ways to gather data and then make presentations. I have tried to put all of the different methods ranging from WORKBOOKS.OPEN to WinHttp.WinHttpRequest to putting together stock prices from multiple pages of a web page. Sources of data and different methods of download are demonstrated for the Federal Reserve Economic Data Base: http://fred.stlouisfed.org/categories, Yahoo.Finance and GoogleFinance.

I have made two videos that describe this file and also a rough power point explanation. The file includes innovations (for me at least) on how to make the process efficient through only re-calculating ranges. It also allows you to use different indices as a way to evaluate beta. The file can be used to evaluate commodity prices, exchange rates, inflation rates and interest rates. Two videos describing how to use the file and then how to work on technical macro aspects of the file are shown below.

Other files demonstrate how to make statistical analysis with stock prices, make scatter plots, normal plots and various other kinds of analysis. In the files below this paragraph you can enter different ticker symbols as with the COMPREHENSIVE file above. With the downloaded data you can graph stock price history and do different kinds of statistical analysis including computing autocorrelation adjustments. Some of the files were built before Google limited access to data and will not work until Google hopefully allows you to get the data again. Other files use finance.yahoo to read prices for different stocks. Finance.yahoo.com has adjusted stock prices that are somewhat better to work with Google because of the re-investment of dividends and adjustments for stock splits. In the file named “Read Google from List” you can go to google and read indicies such as the S&P 500 or FTSE and/or stock prices that do not have an option to download to a spreadsheet. This technique may be useful when there are many pages of data that you want to stick into a file.

`

Downloading and Comparing Stock Indicies in Different Countries

The file below uses the FRED (Federal Reserve Economic Database) to put together a lot of stock indicies including stock indicies from different countries. You used to be able to get many of the indicies downloaded by going to finance.yahoo.com, but sadly this has been eliminated by yahoo. The indicies much be adjusted for local currencies. This allows trends, volatility and beta to computed on a comparable basis. It also uses a list box with multiple entries to reads data for different countries. The file includes many variables and countries allowing you to compare GDP per capita and other variables with things like population age. The file uses a list box function with a macro so you can compare different items.

Link to My Youtube Channel Where You Can Look At All of the Different Videos that I have Made

Downloading and Comparing Stock Indicies in Different Countries

The file below uses the FRED (Federal Reserve Economic Database) to put together a lot of stock indicies including stock indicies from different countries. You used to be able to get many of the indicies downloaded by going to finance.yahoo.com, but sadly this has been eliminated by yahoo. The indicies much be adjusted for local currencies. This allows trends, volatility and beta to computed on a comparable basis. It also uses a list box with multiple entries to reads data for different countries. The file includes many variables and countries allowing you to compare GDP per capita and other variables with things like population age. The file uses a list box function with a macro so you can compare different items.