This page walks through how to create a excel database that scrapes data from Federal Reserve Economic Database (FRED) that is published by the St. Louis Fed using the VBA code that WORKBOOKS.OPEN. The FRED database contains a massive amount of data on economic and financial variables and you can combine the data effectively into a master database. You may want to present the data in different ways and automatically update your presentations using the data and control different ways to use and present. You can quickly scrape data and then compare, adjust, analyse and graph data in a flexible manner by using the workbooks.open method. This page explains the steps of finding the FRED codes; using the Workbooks.open and text to columns to read a single series; reading multiple series using the INDEX function; summarising data using the INDIRECT function.
Introduction: Types of Data in Fred
In the old days, you could read data into text files in the FRED database. The text files were available via a link and could easily be read into excel with text to columns tools. While the text files are no longer there, you can still read the data. The only problem is that it is a bit more difficult to find the URL names. Essentially, you must add the name .TXT the URL’s. Luckily, I retained files with the URL’s which have the following form. Note that this URL is different from the other URL’s that you may try to copy. For example, the components /fred2/data/ are not in the URL’s to get the data directly to excel. To find different series, you just have to get the codes and put the .txt at the end.
To find the codes, just go to FRED and look up things like interest rates, commodity prices or economic series. Then you can find the codes by looking at the URL associated with a graph. Once you have the code, you can get the data. In some cases you can try to modify the codes as with country by country data. In other cases, you can just create a list of URL’s with different codes as illustrated below. I created this list by looking up at each URL. You can then use the INDEX function with a code number to read each of the URL’s and put them into a different sheet. You can find this interest rate file on the associated web page http://edbodmer.com/interest-rate-analysis-from-fred/
Using the Workbooks.open and Text to Columns to Read a Single Series
In this section I explain how to read a single series. The steps in doing this are first to use the WORKBOOKS.OPEN statement in VBA. In the code below, the Then you can go to the file that is read in and record a macro when you are using the text to columns tool in excel. After that, you can do some work in moving the file back to the master database and then naming the sheet. It can be a pin to go back to the project and then close the file. These steps are illustrated in the code below. The statement Workbooks.Open (Range(“econ_url”)) reads the kind of URL discussed above the .TXT. After the text file is read into excel, you can separate the variables into different columns. This is shown
Sub GetData() base_book = ActiveWorkbook.Name base_sheet = ActiveSheet.Name num_sheets = Sheets.Count On Error GoTo exit1: Workbooks.Open (Range("econ_url")) temp_book = ActiveWorkbook.Name temp_sheet = ActiveSheet.Name ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1)), TrailingMinusNumbers:=True Sheets(temp_sheet).Copy after:=Workbooks(base_book).Sheets(num_sheets) If Range("quick_read") = False Then min_date = WorksheetFunction.Min(Range("A:A")) ' get min date data_row_start = WorksheetFunction.Match(min_date, Range("A:A"), 0) ' fin row number For Row = 1 To data_row_start - 1 For col = 2 To 20 Cells(Row, 1) = Cells(Row, 1) & " " & Cells(Row, col) Cells(Row, col) = "" Next col Next Row End If On Error GoTo problem_reading ActiveSheet.Name = Range("series_name") GoTo skip2: problem_reading: MsgBox "problem with sheet name " & Range("series_name") On Error GoTo 0 skip2: Workbooks(temp_book).Close exit1: End Sub
Reading Multiple Series using the INDEX Function
Once you have read in a single series and make sure that it works, you can read in multiple series. You just have to co-ordinate the INDEX function in excel with a basic loop in VBA. The key is to change the index code number (defined with a range name), with the counter in the loop in the VBA code. This illustrates why the INDEX function is so useful, as it is corresponds to a loop where you can do multiple things. This routine takes the names that are associated with each URL as shown in the first screenshot above and uses those name for the sheet name. Note that you have to go back to the sheet with the URL list and the INDEX function each time you go around. The key is to use the FOR NEXT loop to change the index code.
Sub get_all_data() calc_status = Application.Calculation Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Application.ScreenUpdating = False base_sheet = ActiveSheet.Name Range("start_time") = Time For i = 1 To Range("total_to_read") Range("code") = i ' Assign the index code number in excel to the loop counter Sheets(base_sheet).Select ActiveSheet.Calculate On Error Resume Next Application.StatusBar = " Downloading " & Range("series_name") & " Series " & i & " Out of " & Range("total_to_read") GetData ' Run the file that gets the data for a single series Range("all_data").Cells(i, 1).Select series_name = Range("series_name") ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "'" & series_name & "'!A1", TextToDisplay:=sheet_name Next i Application.Calculation = calc_status Application.StatusBar = False Range("end_time") = Time Sheets(1).Select End Sub
Summarising Data using the INDIRECT Function
After reading the data into separate sheets you have a file with a whole bunch of different sheets. With these sheets, you can then try and put the data into a comprehensive database. The key to doing this is the use the INDIRECT function with either LOOKUP or MATCH and INDEX. The big trick is to use the sheet names which are defined in the place where you have put in your URL list. Once you have the sheet names, you can write down your LOOKUP function by going to the sheet. After doing this, you have to replace a lot of things with “‘” and the & symbol. Using the INDIRECT function and using the LOOKUP function is illustrated in the two screenshots below.
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:
Clearing Sheets with a Backwards FOR Loop and a BREAK Sheet
An important part of this method is to be able to delete sheets and then re-read the sheets. The whole process should not take much time. To do this, you can create a BREAK sheet or use some other name. Then you can delete all of the sheets after the break sheet. The key to doing this is to create a FOR NEXT loop that starts at the end and then goes backwards using the STEP -1 after the FOR NEXT. You then go from the end backwards and stop when you have hit the BREAK sheet.
Sub clear_sheets() calc_status = Application.Calculation Application.Calculation = xlCalculationManual Application.DisplayAlerts = False For i = Sheets.Count To 1 Step -1 If Sheets(i).Name = "BREAK" Then Application.Calculation = calc_status Exit Sub End If Sheets(i).Delete Next i Application.Calculation = calc_status End Sub