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 graphss 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 retreive 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.




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 crums 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.


Basic Example with P/E Ratio Comparison of Yahoo, Google and Market Watch


Let’s start with a simple case where you want to make a comparison of financial statistics for a bunch of companies. (You should understand what drives things like the P/E ratio and the EV/EBITDA ratio before you get into comparative analysis).

  • The first step is to go to yahoo.finance.com or google.com or marketwatch.com and find the URL.This is illustrated in the picture below. All you do is copy the URL and paste it into your excel sheet.

Googlefinance URL.JPG

  • The second step is to separate the URL so you can read other stocks. You need to find the URL that works and then make a new URL. The INDEX function is perfect for this as you can make a list of stocks and then pick one.

Index to Read Stocks.JPG


  • The third step is to separate the URL into components that do not have the stock and then to re-combine it with a simple & sign. The stock symbol can come from the INDEX function and the URL should have a range name.


re-combine.JPG



  • The fourth step is to create a macro with workbooks.open as shown below. When you operate this macro it puts the data in another file.

workbooks.open.JPG
  • The fifth step is to create a macro that copies and pastes special as values to back to the original sheet. You can also re-name the sheet. Then you should put stuff in the macro that makes the process generic as shown below. Make sure you do not close the file because you will have to adjust the macro as shown below. (Don’t forget the rule – when you make a macro, save the workbook before running it.) You can find this macro in the spreadsheet below.

Adjusted VBA.JPG
VBA in read before adjustment.JPG





























  • The sixth step is and easy step which is to read all of the stocks into separate sheets rather than make one sheet. This is simple because of the index function. All you do is make a little loop that goes around for the same number as items in the index function. Then you make a FOR NEXT loop where you assign the code number used in the INDEX function to the counter in the loop. Then you call the program that reads the individual stock. This is illustrated in the VBA code below.

  • The final step is using INDIRECT, MATCH and INDEX to find the data in the various sheets. To do this you need the sheet names so you can look for all of the data. Then you have to put the sheet names together which can be a little painful with “’” when there is a space in a sheet name. The excerpts below illustrate how to use the MATCH and then the INDEX and then show the summary report.

Match Part 1.JPGIndex and Indirect.JPGIndirect.JPG


The final output is illustrated below -- node the difference in the P/E ratios from the different sources. The excel file associated with all of this is included below the picture. This file has all of the VBA code.


Final Summary.JPG





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 Analysing 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 indicies, 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.