This page shows you how to automatically download data from the internet to excel using the WORKBOOKS.OPEN statement. 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. Videos associated with the excel files are included in the table at the end of the page. You can go to the stock price page and find a basic explanation of how to use the WORKBOOKS.OPEN method.

When working in finance or economics you often end up wanting historic and current data on items that vary a lot like oil prices, interest rates, exchange rates and stock prices. These things that vary a lot are often the items that you want to download and update. Databases that go to internet sites and gather current and historic data are presented for: (1) interest rates (including credit spreads); (2) exchange rates; (3) commodity prices (4) economic variables such as GDP per capita and (5) other data such as the cost of solar panels. There are a few websites that are very good for obtaining this data in an automated manner. These websites include:


1. Interest Rates: A few years ago you had to pay a lot for interest rate data from sources like Bloomberg. Now you can get very good historic data from the St. Louis Fed. This includes all sorts of interest rate maturities, swap rates, credit spreads and credit spreads from different countries. There are a couple of examples of downloading interest rates from the St. Louis Fed and the Federal Reserve Economic Database (FRED) below.

2. Exchange Rates: Exchange rate data is important for many reasons. For example, in acquiring stock price data, the data is generally expressed in terms of local prices; you probably want a common currency to compare the stock indicies. Finding historic exchange rates for countries such as the Philippines can be tricky and are not reported by the St. Louis Fed. To get the prices there are some alternatives one of which is http://www.x-rates.com/historical/?from=USD&amount=1&date=

3. Commodity Prices: Commodity prices can be very volatile, have mean reversion. Sources for acquiring data include the world bank and futures markets. The world bank website includes nice monthly data on many variables (except diesel prices, petrochemical prices and other prices that are from refined products). Electricity commodity prices are included on a separate page. The futures data is downloaded from: http://www.cmegroup.com/trading/energy/crude-oil/brent-crude-oil.html. The website for the world bank for the historic monthly data is below:
http://pubdocs.worldbank.org/en/561011486076393416/CMO-Historical-Data-Monthly.xlsx.

Databases and Files associated with automatically downloading data using the WORKBOOKS.OPEN


Selected files that read data automatically from the internet are listed on this page. I have included a short description of how you can use the file just below the file name. Each of the files uses the a macro to read from a website. Some of the files directly read files that already are in a spreadsheet format and other files read data that is simply on a website page. There is a video that describes how the macro is created to read each file. The video descriptions are listed at the bottom of the page. I have also included a step by step explanation at the bottom of the page.

Current Exchange Rate Database and Internet Read


The read current currency file is used as an introduction to use of a macro to read data from the internet using the WORKBOOKS.OPEN statement so that you can record information from a website into an excel file. The example below and the video read data from a commonly used currency website that contains current exchange rates http://www.xe.com/currencyconverter/convert/?From=USD&To=CHF (I think when you go to google and ask for the Euro to Swiss exchange rate it takes you here to this website). I the completed file below, you can select different exchange rates and press a button (Read Currencies in List) to get current updates of the exchange rate. I originally used the list box technique that is further explained on the flexible graph page is used so you can change the number of currencies that you may want in a report. But now in excel 2016, list boxes are dangerous so I have changed the technique to use check boxes with TRUE and FALSE and Match. I use this macro to find current exchange rates to develop invoices or other analyses that require current data on exchange rates.

If you download the file below, all you have to do is click on the currencies you want with the check boxes. Then click the macro box and all of your exchange rates are ready to be copied into any other file that you want.






Interest Rates and Credit Spreads Database

Maybe the best file to get lot of economic and financial date for the U.S. as well as for other countries is data tabulated by the St. Louis Fed. The data collection is called FRED which stands for the Federal Reserve Economic Database. This website has data on credit spreads, all kinds on exchange rates, LIBOR and EURIBOR swap rates for different terms, term structure of interest rates and a whole lot of other data. One of the problems with getting data so that you can put stuff together and automatically update the files is that the old text files have been taken away from the website. But I demonstrate that this does not matter. This is because you can still use the old links from codes that are associated with each database to put the data into text files.

The file that you can download below goes to the St. Louis Fed database (FRED) and gets a bunch of data including credit spreads, interest rates, interest rate swaps, term structure etc. As with other files, the WORBOOKS.OPEN is the basis for the analysis. A second file includes upload of yield curves so you can analyse the term structure of interest rates. The St. Louis Fred has an app that may be helpful, but I think doing this stuff yourself has many advantages.






Cost of Capital and Valuation Multiples Databases


In analysing corporate finance I am obsessed with things like using the value driver formula (1-g/ROIC)/(WACC-g) and demonstrating its flaws. I also go crazy about finding alternatives to the CAPM with market to book analysis and use of the P/E ratio. The files below go to the different financial websites including market watch.com, finance.yahoo.com and the St. Louis Fed to gather data to evaluate the cost of capital and multiples. The file computes cost of capital using different techniques including CAPM, P/E ratios, P/B ratios and EV/EBIT ratios.

Macros are used in the files that allow you to enter ticker symbols and then get a whole lot of data. The process adjusts URLs market watch and yahoo.com according to ticker symbols and then puts financial data into a lot of sheets. The INDIRECT function is then used to gather data together and summarize data in different ways. You can update the data like with other files although depending on your internet speed it may take a bit longer to read all of the stuff when you update your analysis.







Commodity Price History and Forecast Database


I think there are a few sources for financial and economic data that are really good. One of the sources is the world bank data base that records and updates commodity prices called for some reason pink data. On this World Bank web site you can download monthly nominal prices since 1960 for more than 100 commodity price series. The workbook file listed below goes to the pink data part of the world bank website and updates historic commodity price data as well as forecast data published by the world bank. To update the history data you just have to press the macro button. The file allows you to analyse historic data and compare forecast and projected data for a large series of commodity prices. You can update the file every month or couple of months and you can use the file to perform statistical analysis of the various commodity prices. The historic data can be updated very easily by pressing the macro button. To update reading of the forecast you have to look-up the link that sometimes changes. You can also find commodity price data on the FRED database. The second file goes to the FRED database and puts together a whole bunch of the commodity price series.







Reading Forward Natural Gas, Oil, and Electricity Futures Prices from CME

I and some of my clients use futures prices for oil, natural gas and electricity to make forecasts. When you go to the CME website and copy the data into an excel file it can be a pain. One of the big problems is that dates are expressed in different formats and the data is read into files with different spaces. The file below (which is of course transparent like all of the other files) reads data from the CME on futures prices for oil, natural gas, U.S. electricity, gas basis spreads, diesel oil and coal. The data is in different places, there is a bunch of crap above the data and the data has spaces between the futures data. The file below solves these problems. It reads data from the NYMEX website and tabulates futures prices for oil and gas. By pressing on one button you can update your forecasts and analysis very quickly. As with the other files, if you want to use it, you just press the read files button and all is done.

The video below has a low sound volume -- you can use headphones or try to turn the volume up






Reading Economic Data on Different Countries for Comparison

The file below goes to the St. Louis Fed and reads data for different countries. The file includes many variables and countries allowing you to compare GDP per capita and many other variables ranging from population age to banking statistics, exports and inflation rates. The process works by changing URLs from the St. Louis Fed. In this file there are a number of error checks and there is even an error check list because there are so many sheets. As some variables are repeated in the FRED database, there are also provisions to use alternative data sets from the data set. The video below that describes how to get the data compares presentation in excel and presentation in Power BI.





Downloading Trends in the Cost of Solar Panels

There is a nice website called pvinsights that records current prices of polysilicon, polysilicon panels and thin film panels. At first I used this file just to demonstrate how to use the WORKBOOKS.OPEN and to save the data in a separate sheet with the date on the sheet name. After using the file to demonstrate downloading for a few years, I have collected trends in polysilicon prices that are in my opinion interesting. In summary, the file beow goes to the PV insights websites and tabulates silicon prices and panel prices for different historic periods. It uses a MATCH PARTIAL customised function as well as the INDIRECT function.




Downloading Day by Day Exchange Rates

I cannot find the exchange rate between the Philippines and the U.S. on the FRED website that has reasonably current data. I need this data for my analysis of electricity prices that is explained in the download electricity price page. The file below uses another website that reports day by day exchange rates to compute averages per month, week or year. This file and the associated video is comprehensive. The steps to put this together are the following: (1) find the website; (2) Copy the URL; (3) Make a list of dates for which you want to record the data; (4) assign sheet names to the list of data where the sheet name is just the name of the date; (5) Split-up and re-connect the URL to make a flexible URL; (6) make a simple macro with WORKBOOKS.OPEN; (7) adjust the macro to move the newly created sheet from WORKBOOKS.OPEN back to your sheet; (8) Make a code number and use the INDEX function to be able to read every single URL in the list; (9) Make another macro with a simple little loop that changes the code number and re-reads every URL; (10) Once you have put all of the days in different sheets, use the INDIRECT function along with INDEX and MATCH to put retrieve and summarise the data.

I have made a video the works through these 10 steps and it is not all that long (about half an hour).




Downloading and Comparing Real Estate Prices

The file below shows you how to go to the Case-Shiller housing price indexes and update for the latest data. It is helpful for studying housing markets.




Reading Forecasts of Natural Gas, Oil, and Electricity Prices from EIA, World Bank and CME Futures

The above file goes to various sources and collects alternative project prices for oil and gas - primarily from the EIA website. I think that anybody who claims to be able to forecast things like oil prices is a fraud. But in many applications you have to make forecasts. In these cases it is probably best to refer to the forecasts of some other "expert" such as the EIA or the World Bank. The file below collects data on different forecasts and puts them together. You can then waste less time looking around for the forecasts and adjusting them for inflation and other factors. As with other files, you can press a button and all of the forecast data is updated.







Step by Step Explanation of Reading Data from the Internet










Videos that Explain how to automatically download data using the WORKBOOKS.OPEN






Subject

Excel Exercise File

Video

Chapter Reference

Page Reference











Reading Basic Data from the Yahoo.Finance.com

Basic Read from Yahoo

https://www.youtube.com/watch?v=0u9lBxfpD6A





Reading Text Files from the Internet and placing files in new sheets

Upload Exercise 2: Reading Text Files

https://www.youtube.com/watch?v=vLXOCZTuVZ0

Chapter 16

193

Reading Stocks from Yahoo - Reading One URL with Workbooks.open

Financial Ratio Download 1

https://www.youtube.com/watch?v=VLmzmcw_s

Chapter 16

201

Using Match, Index and Indirect to Summarise Downloaded Data

Housing Data

https://www.youtube.com/watch?v=PgLSc2AvU-Y

Chapter 16

193

Reading Data on Gas Prices

Upload Exercise 1: Gas Price Upload

https://www.youtube.com/watch?v=ZfO9K0STL8M

Chapter 16

193

Reading and Extracting Data from St. Louis Fed 1 with multiple urls

Financial Ratio Download 1

https://www.youtube.com/watch?v=yxJuhYqQEdM

Chapter 16

193

Upload Multiple Web Pages to Excel

Upload Exercise 2: Hydro Upload

https://www.youtube.com/watch?v=mDGR1Pdmp20

Chapter 16

193

Reading Data on Gas Prices

Upload Exercise 1: Gas Price Upload

https://www.youtube.com/watch?v=ZfO9K0STL8M

Chapter 16

193

Electricity price read all macros

US Electricity Prices

https://www.youtube.com/watch?v=uNKQxKOlxog

Chapter 16

193

Electricity price extraction

US Electricity Prices

https://www.youtube.com/watch?v=cmw4fm1lQxw

Chapter 16

193

Electricity price reading

US Electricity Prices

https://www.youtube.com/watch?v=G2Rbh2F6luc

Chapter 16

193

Retrieving Currencies and Importing to Excel File

Get Currencies



Chapter 16

193

Downloading Data on Saudi Stock Bubble

Saudi Stocks

https://www.youtube.com/watch?v=C_Sz6-iO-OQ

Chapter 2

40

Downloading Data on House Prices and Demand 1

Housing Data 1

https://www.youtube.com/watch?v=U-EJ2D1JVpM

Chapter 16

193

Clearing Sheets to Enable Updating of Data

Housing Data 1

https://www.youtube.com/watch?v=z-e1zefhZoc

Chapter 16

193

Adjusting Nominal Data for Inflation

Housing Data

https://www.youtube.com/watch?v=L2TM2VG72MU

Chapter 16

193

Read Yield Curve as Basis for Forward Interest Rate Analysis

Yield Curve

https://www.youtube.com/watch?v=PwRPLB_ungE





Read Stock Prices from Google - Part 3; Reading Indicies

Read Indicies

https://www.youtube.com/watch?v=5ivVds9a9Wc

Chapter 16

201

Read Stock Prices from Google - Part 1

Read Saudi Stocks

https://www.youtube.com/watch?v=2ZZqy0hczPw

Chapter 16

193

Read Stock Prices from Google - Part 2

Read Saudi Stocks

https://www.youtube.com/watch?v=v8n_Jv9WyHQ

Chapter 16

201

Read Stock Prices from Google - Part 4; Match/Index/Indirect

Read Saudi Stocks

https://www.youtube.com/watch?v=W_pg_nFLoaM

Chapter 16

201

Read Stock Prices from Google - Part 5; Graphs with #N/A

Read Saudi Stocks

https://www.youtube.com/watch?v=C_Sz6-iO-OQ

Chapter 16

201

Summarizing Annual Stock Prices - Stock Summary by Date

Financial Ratio Download 2

https://www.youtube.com/watch?v=IqQLg7y61pg

Chapter 16

201

Read Industry Data and Put in New Sheets from FRED

Read Airfreight Industry Data

https://www.youtube.com/watch?v=9-ZEicsj0iw





Update of Read Stocks

Read Oil Stocks

https://www.youtube.com/watch?v=-OgyfFub_do





Reading Financial Data

Raw Financial Data.xls







Simple Classification Exercise

Simple Classification.xls







Complex Classification Exercise

Raw Financial Data.xls







Financial Ratio Download 1 - Finding Tickers

Financial Ratio Download 1







Financial Ratio Download 2 - Reading One URL with Workbooks.open

Financial Ratio Download 1

https://www.youtube.com/watch?v=VLmzmcw_s

Chapter 16

193

Financial Ratio Download 3 - Stock Summary by Date

Financial Ratio Download 2

https://www.youtube.com/watch?v=IqQLg7y61pg

Chapter 16

193

Financial Ratio Download 4 - Moving Sheets

Financial Ratio Download 3



Chapter 16

193

Financial Ratio Download 5 - Reading Multiple Companies

Financial Ratio Download 4

https://www.youtube.com/watch?v=_byg3ojjUgo

Chapter 16

193

Financial Ratio Download 6 - Extracting Data from Sheets

Financial Ratio Download 5

https://www.youtube.com/watch?v=D2mClmUwkro

Chapter 16

193

Financial Ratio Download 7 - Selecting Companies with TRUE/FALSE

Financial Ratio Download 5

https://www.youtube.com/watch?v=Nri1In4rDw0

Chapter 16

193

Financial Ratio Download 8 - Reading Companies from Market Watch









Shows how to read in a set of companies and then analyse the data

Yieldco Download and Cost of Capital

https://www.youtube.com/watch?v=hGMFl6czx6A





……………………………………………………………………………………………………………………
…..
………………………………………………………………………
…..
………………………………………………………………………………………………….
…..
………………………………………
…..
……………………………………………………

Files Associated with Video Set on Downloading Financial Data




The above files are for the set of videos that walk through how to create macros that first read files, then copy data into a master file and then use different techniques to summarise the data an put it all together.


This file includes an apporach to upload data and then summarise data






This is a comprehensive file that uploads data