- Excel Version
- 365
- 2021
- 2019
There is an Excel Add-in that makes retrieving Economic Data from the Federal Reserve (simply called FRED Data) easy. However, there are inherent problems it presents. You put the CODE of the data series you want in row 1, and optionally add Data Manipulations (such as retrieving the change from a year ago) and frequency (Daily, Weekly, etc.), and the starting date you want in rows 2-4. You then click the Add-in's Get Data button and two columns of data are retrieved - the date and the value.
Among other problems with this are the options are hard coded so if you used a formula for the date it is overwritten with the hard coded date, and the data is not in a table so that as the data grows, any formulas have to be updated.
There is, however, a FRED API that allows a URL to be used with Power Query to retrieve the data, transform it as needed, and send the results to a Table, Pivot Table, or Pivot Chart. I realized that a template could be set up fairly easily to retrieve the data through Power Query. This solved a multitude of problems. For example, Percent Values are expressed as the whole percent number such as 1 for 1%. As mentioned the cell for the date requested is hard coded even if a formula such as EOMONTH(TODAY(),-13)+1 is changed to 6/1/2022 or whenever the Add-in's Get Data is used. Creating a template that creates the URL for Power Query solves those and other problems.
This starts with generating the URL itself:
Column C only needs to be 46 wide. XL2BB autofits to the widest cell in the column, and the URL cell is meant to be in Column B, but I moved it to C to make everything more visible.
Note that the last item is an API Key. All that's needed is a free account, and then request an API Key.
What isn't shown in the preview is that a name is given to the CELL with the URL, in this case we'll call it urlFRED. That allows you to use From Table Range to pull it into Power Query:
Nothing else is needed. Save it as a Connection Only.
Also not shown are two other Named Ranges called BaseURL which is ="https://api.stlouisfed.org/fred/series/observations", and FAKEKEY which is NOT a valid API Key and is ="api_key=abcdefghijklmnopqrstuvwxyz123456" here. I have no good reason for using BaseURL, but using FAKEKEY allows me to easily change the API Key! Also, note that the API command "api_key=" is included but doesn't really have to be. One thing I learned is that when you use a Named Range in a formula, and then copy that formula to a new Workbook, the Named Range is copied along with it! It's also worth noting that I used hard coded Data Validation Lists and SWITCH statements because I was originally referencing tables, but that made it impractical to easily port to another Workbook.
I still use the FRED Add-in to get the Name of the data, the Source, and Value (like In Billions) manually. If I can figure out the API URLs for those pieces of data, the WEBSERVICE function could retrieve them based on the CODE provided. Maybe someone here with more experience could help?
Now create this Power Query function which I call fxGetFredData:
Now, in the Excel Data tab with the Queries & Connections pane open, double click the fxGetFredData query, and you'll be presented with a drop down list of all the Power Query tables available. Select the urlFRED table and the data will be retrieved and set up for further work. For example, the function sets up the data as a Decimal Number which will work fine for any data retrieved. However, many data sets use Whole Numbers, or as mentioned before a a Percentage expressed as a Decimal Number. You can now take the new Query created by the Function and make any needed modifications like change the name of the Value column to something more useful, divide the Value column by 100 and change it to a Percentage, etc.
I really liked this solution to retrieving the data I wanted! I had a workbook that pulled Key Economic Data as defined here from five sources, and related charts went back five years. The data wasn't provided in a table, I'd have to manually change all five dates to change the start date, and do conversions with formulas and check where the data ended. This provides the ability to use formulas for dates, transform the data in Power Query, and simply use Refresh All to get the latest data.
It's worth noting that the SWITCH function is what limits this to a 2019 solution. I looked at using CHOOSE, but it became very convoluted. Still, would be worth someone providing that solution for those using older versions of Excel. Otherwise I think this could be used with versions as old as 2010 (with the download of Power Query add-in) if that were solved.
One final warning. Generating an incorrect URL will only result in a useless error message. When developing this happened most often when the Frequency was out of scope such as requesting Monthly when only Quarterly is available. This is where the Add-in helps. It will give you a much better idea of where the problem is!
This is by no means very sophisticated, but I've been using the Add-in for over two years, and got my API Key last December, and it took me this long to realize I could use the API instead, and only took about a week to figure it out enough for this. I hope if nothing else this inspires others to think about better solutions to problems. There almost always seems to be one with Excel!
Among other problems with this are the options are hard coded so if you used a formula for the date it is overwritten with the hard coded date, and the data is not in a table so that as the data grows, any formulas have to be updated.
There is, however, a FRED API that allows a URL to be used with Power Query to retrieve the data, transform it as needed, and send the results to a Table, Pivot Table, or Pivot Chart. I realized that a template could be set up fairly easily to retrieve the data through Power Query. This solved a multitude of problems. For example, Percent Values are expressed as the whole percent number such as 1 for 1%. As mentioned the cell for the date requested is hard coded even if a formula such as EOMONTH(TODAY(),-13)+1 is changed to 6/1/2022 or whenever the Add-in's Get Data is used. Creating a template that creates the URL for Power Query solves those and other problems.
This starts with generating the URL itself:
| FredAPI3.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Name: | ||||
| 2 | Source: | ||||
| 3 | Value: | ||||
| 4 | Code: | ||||
| 5 | Manipulation: | cca | Continuously Compunded Annual Rate of Change (cca) | ||
| 6 | Frequency: | d | Daily (d) | ||
| 7 | Start Date: | 01/01/2000 | |||
| 8 | URL: | https://api.stlouisfed.org/fred/series/observations?series_id=&units=cca&frequency=d&observation_start=2000-01-01&api_key=abcdefghijklmnopqrstuvwxyz123456 | |||
Template | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B5 | B5 | =SWITCH(C5,"DEFAULT ()","","Linear (lin)","lin","Change (chg)","chg","Chg from Yr Ago (ch1)","ch1","Percent Chg (pch)","pch","Pct Chg from Yr Ago (pc1)","pc1","Compounded Annual Rate of Change (pca)","pca","Continuously Compunded Rate of Change (cch)","cch","Continuously Compunded Annual Rate of Change (cca)","cca","Natural Log (log)","log") |
| B6 | B6 | =SWITCH(C6,"DEFAULT ()","","Daily (d)","d","Weekly (w)","w","Biweekly (b)","b","Monthly (m)","m","Quarterly (q)","q","Semiannual (sa)","sa","Annual (a)","a") |
| C8 | C8 | =BaseURL& "?series_id="&B4& IF(B5="","","&units="&B5)& IF(B6="","","&frequency="&B6)& IF(B7="","","&observation_start="&TEXT(B7,"yyyy-mm-dd"))& "&"&FAKEKEY |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| C5 | List | DEFAULT (),Linear (lin),Change (chg),Chg from Yr Ago (ch1),Percent Chg (pch),Pct Chg from Yr Ago (pc1),Compounded Annual Rate of Change (pca),Continuously Compunded Rate of Change (cch),Continuously Compunded Annual Rate of Change (cca),Natural Log (log) |
| C6 | List | DEFAULT (), Daily (d), Weekly (w), Biweekly (b), Monthly (m), Quarterly (q), Semiannual (sa), Annual (a) |
Column C only needs to be 46 wide. XL2BB autofits to the widest cell in the column, and the URL cell is meant to be in Column B, but I moved it to C to make everything more visible.
Note that the last item is an API Key. All that's needed is a free account, and then request an API Key.
What isn't shown in the preview is that a name is given to the CELL with the URL, in this case we'll call it urlFRED. That allows you to use From Table Range to pull it into Power Query:
Power Query:
= Excel.CurrentWorkbook(){[Name="urlFRED"]}[Content]Also not shown are two other Named Ranges called BaseURL which is ="https://api.stlouisfed.org/fred/series/observations", and FAKEKEY which is NOT a valid API Key and is ="api_key=abcdefghijklmnopqrstuvwxyz123456" here. I have no good reason for using BaseURL, but using FAKEKEY allows me to easily change the API Key! Also, note that the API command "api_key=" is included but doesn't really have to be. One thing I learned is that when you use a Named Range in a formula, and then copy that formula to a new Workbook, the Named Range is copied along with it! It's also worth noting that I used hard coded Data Validation Lists and SWITCH statements because I was originally referencing tables, but that made it impractical to easily port to another Workbook.
I still use the FRED Add-in to get the Name of the data, the Source, and Value (like In Billions) manually. If I can figure out the API URLs for those pieces of data, the WEBSERVICE function could retrieve them based on the CODE provided. Maybe someone here with more experience could help?
Now create this Power Query function which I call fxGetFredData:
Power Query:
let
Source = (FredURL as table) =>
let
SrcURL = FredURL{0}[Column1],
Source = Xml.Tables(Web.Contents(SrcURL)),
RemovedOtherColumns = Table.SelectColumns(Source,{"observation"}),
ExpandedObservation = Table.ExpandTableColumn(RemovedOtherColumns, "observation", {"Attribute:realtime_start", "Attribute:realtime_end", "Attribute:date", "Attribute:value"}, {"Attribute:realtime_start", "Attribute:realtime_end", "Attribute:date", "Attribute:value"}),
RemovedColumns = Table.RemoveColumns(ExpandedObservation,{"Attribute:realtime_start", "Attribute:realtime_end"}),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Attribute:date", "Date"}, {"Attribute:value", "Value"}}),
ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Date", type date}, {"Value", type number}}),
RemovedErrorRows = Table.RemoveRowsWithErrors(ChangedType, {"Date", "Value"}),
SortedDate = Table.Sort(RemovedErrorRows,{{"Date", Order.Ascending}})
in
SortedDate
in
SourceNow, in the Excel Data tab with the Queries & Connections pane open, double click the fxGetFredData query, and you'll be presented with a drop down list of all the Power Query tables available. Select the urlFRED table and the data will be retrieved and set up for further work. For example, the function sets up the data as a Decimal Number which will work fine for any data retrieved. However, many data sets use Whole Numbers, or as mentioned before a a Percentage expressed as a Decimal Number. You can now take the new Query created by the Function and make any needed modifications like change the name of the Value column to something more useful, divide the Value column by 100 and change it to a Percentage, etc.
I really liked this solution to retrieving the data I wanted! I had a workbook that pulled Key Economic Data as defined here from five sources, and related charts went back five years. The data wasn't provided in a table, I'd have to manually change all five dates to change the start date, and do conversions with formulas and check where the data ended. This provides the ability to use formulas for dates, transform the data in Power Query, and simply use Refresh All to get the latest data.
It's worth noting that the SWITCH function is what limits this to a 2019 solution. I looked at using CHOOSE, but it became very convoluted. Still, would be worth someone providing that solution for those using older versions of Excel. Otherwise I think this could be used with versions as old as 2010 (with the download of Power Query add-in) if that were solved.
One final warning. Generating an incorrect URL will only result in a useless error message. When developing this happened most often when the Frequency was out of scope such as requesting Monthly when only Quarterly is available. This is where the Add-in helps. It will give you a much better idea of where the problem is!
This is by no means very sophisticated, but I've been using the Add-in for over two years, and got my API Key last December, and it took me this long to realize I could use the API instead, and only took about a week to figure it out enough for this. I hope if nothing else this inspires others to think about better solutions to problems. There almost always seems to be one with Excel!
