On-Line Course for Creating Database from FRED

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.

Interest Rate Database that Extracts Data from the FRED Database with Quick Updates and Flexible Graphs

 

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.

https://research.stlouisfed.org/fred2/data/TB1YR.txt

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:
http://pubdocs.worldbank.org/en/561011486076393416/CMO-Historical-Data-Monthly.xlsx.

 

 

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