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.
Power Point Slides that Explain Technical Details of the Comprehensive Database with Financial Data, Multiples and Cost of Capital Power Point Slides the Explain the Technical Details Associated with the Comprehensive Stock Price FileWhen 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.
Yield Curve Analysis.xlsm
Interest Rates No List Box.xlsm
Read Current Currencies.xlsm
Cost of Capital and Valuation Multiples Databases
You can get a lot of financial data websites like market watch with amazing data for free including historic financial statements and analyst earnings projections. But Market Watch does not allow you to download stock prices so you can compute historic P/E ratios etc. Also market watch reports 2.5 billion as 2.5B and you need to adjust for the B,M and even T. Yahoo finance has a lot of data also, but the financial statement data is not as detailed and again, the historic ratios are not shown. To use the data and evaluate historic multiples and also get historic data in a format ready for forecasts I have created the multiple file which I think is one of the most important files certainly for starting with a forecast. As shown below, in one of the pages you can set-up your financial forecast. In another one of the pages you can review a whole bunch of ratios for a company to get a background for valuation and forecasting.
I originally created the file to analyse cost of capital. I also go crazy about finding alternatives to the CAPM with market to book analysis and use of the P/E ratio. But in analysing corporate finance I am obsessed with things like using the value driver formula (1-g/ROIC)/(WACC-g) and demonstrating its flaws.
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 using alternatives to the CAPM. The file computes cost of capital using different techniques including CAPM, P/E ratios, P/B ratios and EV/EBITDA ratios. It has dynamic goal seeks inside to evaluate cost of capital with stable long-term growth rates and returns.
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.
A-Z Reading Financial Ratios.pptx
Financial Download Insurance.xlsm
Financial Download Dow Stocks.xlsm
Financial Download Selected Stocks.xlsm
Link to My Youtube Channel Where You Can Look At All of the Different Videos that I have Made
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.
Commodity Price Analysis No List Box.xlsm
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
Gas and Oil Futures.xlsm
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.
Country Economic Database.xlsm
Country Economic Database_countries.xlsm
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.
Read PV Insights.xlsm
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).
Read PHP Exchange Rates.xlsm
Get Historic Exchange Rates.xlsm
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.
Housing Data.xlsm
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.
Retrieve Gas and Oil Forecasts Oct 2016.xlsm
Step by Step Explanation of Reading Data from the Internet
Regression Analysis Template.xls
Residential Analysis.zip
Residential Sales Forecast Discussion.pdf
Mongolia.xlsm
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
Financial Download.xlsm
Financial Download 2.xlsm
Financial Download 3.xlsm
Financial Download 4.xlsm
Financial Download 5a.xlsm
nes Download.xlsm
nes Download 1.xlsm
Yaho Industry and Tickers.xlsm
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
screener final.xlsm
This is a comprehensive file that uploads data
Financial Ratio Analysis Large Banks.xlsm
Financial Ratio Analysis General.xlsm