This page shows you how to automatically download data from the internet to excel using the WORKBOOKS.OPEN or 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 using a 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 on this page also show how download stock prices from the internet and then graph the stock prices using the OFFSET function.


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 they have been very mean and stopped allowing the prices to be downloaded easily. Now you can use WinHttpRequest and create crums and cookies (explained below). There are other alternatives 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.

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. 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 is my very suggestion:
  • Use the workbooks.open whenever you can -- this is the simplest method
  • 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 value
  • When you want to download a file and the workbooks.open method does not open, use: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 the data.
  • First, rather than put a lot of stuff in the VBA code and try to make adjustments there in the VBA code, you should separate and then re-combine the URL.
  • Second, where possible use the WORKBOOKS.OPEN and work on a single read.
  • Once you have the single read worked out and put the results of WORKBOOKS.OPEN back into the base sheet, you can use an INDEX function and a simple loop to read multiple stocks.
  • Finally, when you are finished reading, use the INDIRECT function to find 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 files listed 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.

In the file below you can enter different ticker symbols and then graph stock price history and do statistical analysis. The above file goes to google finance to read stock some stock indices and also to finance.yahoo to read prices for different stocks. Finance.yahoo.com has adjusted stock prices that are somewhat better to work with. I 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.

`









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.











Comprehensive Stock Price Analysis


Introduction: This 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. 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.

The file is large and I suggest that you set the calculation to manual. In the first page you can select stock prices, stock indicies, commodity prices and exchange rates from different sources. To manage this, you can re-calculate only the sheet when you select data by attaching a macro to the various check-boxes.


Calculation.JPG




This section describes how to download and analyse the stock prices using various different methods depending on the type of stock, index, commodity price. First, Downloading from a Website Rather than a File
http://fred.stlouisfed.org/categories

If you want to put data from a website into a spreads


This section of the website describes various ways to acquire data from the web and then put it into
  1. you use the code:
Dim MyRequest As New WinHttpRequest
MyRequest.Open "GET", _
"http://www.google.com"

You will get not get the real data.

This section of the website describes various ways to acquire data from the web and then put it into excel files. I get a bit irritated when people tell me that Bloomberg can do all of that. It is so much

Scraping Websites to Acquire Stock Prices