Interest Rate Analysis from FRED

This page describes how you can quickly create an interest rate database with code that allows you to automatically update the database. The database retrieves information and presents flexible graphs on credit spreads, the term structure of interest rates, interest rates in different currencies and other items.  You can mix and match the statistics to compare credit spreads with long-term or short-term interest rates. As with other database files, the process uses the WORKBOOKS.OPEN function to retrieve data and download from the internet to excel.

The interest rate database is available for download by pressing the button below. The interest rate database attached to the button may not be current to the latest month. To update the database you can follow the instructions in the section below (you press the Clear Button and after the sheets a cleared out, press the Create Button).

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

A few years ago you had to pay a lot for interest rate data from sources like Bloomberg.  It was very difficult for example to find public data on credit spreads of bonds with different ratings. 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.

Using the Interest Rate Database

The screenshots below demonstrate how you can use the database.  You can go to different sheets of the file and work with the graphs. The first screenshot illustrates presentation of a single data item that is available for download. As is usual for drop-down and boxes, you can select alternative data items. Similarly, you can select different dates.

 

 

The second example is a very common use of the file where you can compare two items. In the screenshot below the example compares credit spreads of BBB bonds and BB bonds over time.  You can select different time periods and different periods.

 

 

The third example in the screenshot below demonstrates how you can add two series to make an analysis. In this case, the credit spread in BBB bonds is added to a 5-year interest rate to give an indicative rate for 5-year swaps.

 

 

Updating the Interest Rate Database

The screenshot below illustrate how you can update the database. You go to the page named “Get Data” and then first press the “CLEAR SHEETS” button as shown below. (It is a good idea to press the F12 key and to re-save the file as all of the detailed sheets will be deleted).  After clearing the sheets, press the “Get All Series” button and the new data will be placed into sheets at the end of the file.  As shown on the screenshot, this should take less than two minutes unless you have a very slow internet connection.

 

Technical Details of Compiling the Database

The database is created by extracting data from individual sheets and putting the data together in a summary sheet.  The compiled database is illustrated in the screenshot below. The process works with by combining the INDIRECT function with the INDEX and MATCH functions.

 

 

The screenshot below demonstrates one of the sheets that is created by reading an individual item from the FRED database.

 

 

Technical Discussion on Creating Databases from Downloaded Data

The process of collecting interest rates uses techniques where you plop the downloaded files into different sheets using the WORKBOOKS.OPEN method.  Then, you use a little VBA code with a FOR NEXT loop and the INDEX function to collect the data and put it into a database summary sheet. To summarise data, you can use the MATCH, INDEX and INDIRECT functions. Some of the techniques to create databases with the workbooks.open method are described in the powerpoint slides below.

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 File

VBA Code for Reading and Organising Data

The first program shows how to use the workbooks.open to get data. You can find this code in the interest rate workbook that is available for download.

.

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
.



.
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

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

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