Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Using FRED Data

Hi,

FRED (Federal Reserve Econcomic Data) is a great website that offers all kinds of economic series data free to the public, and I'm wondering if anyone can recommend a good connector to import these data series into BI.

Thank you in advance.

1 ACCEPTED SOLUTION

API can be tough. Basically, it's like getting from a web source. Check out these instructions: https://blogs.msdn.microsoft.com/charles_sterling/2016/05/25/how-to-call-rest-apis-and-parse-json-wi...

View solution in original post

32 REPLIES 32
TylerChessman
New Member

Hello All - I've built a custom connector for FRED (and the Energy Information Administration – EIA). Would appreciate you trying it out - and providing feedback. Hopefully, this can be a built-in/certified connector one day. https://github.com/tylerchessman/PBI_FRED_EIA;

 

If you like what you see, make sure to vote for the feature at https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=437ab693-4155-ed11-a76e-0003ff4534c2.  For EIA data, please vote at https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=18f4857d-5269-412e-b093-a036b52c3fa1

Anonymous
Not applicable

I had been trying to get more than one economic indicator in power bi at the same time through their API so my friend and I created a python script to collect whatever number of series you want and appends them already making it really easy to slice when in power bi. I hope you guys enjoy this it took me a long time. It works with python 3.77 and make sure you have the modules requests, matplotlib and pandas. Oh and make sure you add your API token(key)

 

Enjoy and don't take credit for it. 

 

#Modules
import urllib.request as urllib2
import urllib, json
import numpy as np
import pandas as pd

#Static Variables
TOKEN = 'YourAPItoken'

#initialzing array for tickers
tickers= []

#open file and create one array of tickers IDs

tickers =['mspnhsus'
]

#temporary variable store values from "for loop"
d = []

#creating list of URL based on tickers list
for ticker in tickers:
tdata = '['
url = str(f'https://api.stlouisfed.org/fred/series/observations?series_id={ticker.strip()}&api_key={TOKEN}&file_...')

#Opening web api and read JSON data
response = urllib2.urlopen(url)

#create JSON object
json_obj = json.load(response)

for i in json_obj['observations']:
d.append({'ticker': ticker, 'date': i['date'], 'value': i['value']})

#create variable "df" using format dataframe
df = pd.DataFrame(data=d)

#print data ready to use for PowerBi
print(df)

You are awesome!

cfh
Frequent Visitor

You can pull FRED data in with a Python script without an API if you have Python installed or you're comfortable installing it. Get Data/Python script then paste in code per this model:

 

import pandas_datareader.data as web
import datetime

# Choose start and end dates for the data series
start = datetime.datetime(2015, 1, 1)
end = datetime.datetime(2019, 10, 9)

# Read the data: US Housing Starts HOUST FRED data set
houst = web.DataReader('HOUST', 'fred', start, end)

# Move the dates, which are row labels, to their own column
houst.index.name = 'date'
houst.reset_index(inplace=True)

This worked perfect for me. Thanks for your help

Aron_Moore
Solution Specialist
Solution Specialist

Looks like they have an API. https://research.stlouisfed.org/docs/api/fred/

 

These links are broken

 

 

Aron_Moore 
 
Solution Specialist
 
Re: Using FRED Data

 

API can be tough. Basically, it's like getting from a web source. Check out these instructions: https://blogs.msdn.microsoft.com/charles_sterling/2016/05/25/how-to-call-rest-apis-and-parse-json-wi...

View solution in original post

Anonymous
Not applicable

@Aron_Moore 

I saw that. I know this is a very newbie question, but how do I use an API with PBI?

cfh
Frequent Visitor

  • In this case, you use Power BI's Web connector (Get Data/Web) and then paste in text per this model:
https://api.stlouisfed.org/fred/series/observations?series_id=GNPCA&api_key=abcdefghijklmnopqrstuvwxyz123456
  • Replace GNPCA with the ID code for the data series you want to retrieve.
  • Replace abcdefghijklmnopqrstuvwxyz123456 with an API key you get from FRED by requesting it in your user account at the API keys link.
  • Then paste the edited string into the Web connector and load the data.
  • Power BI will initially show a list of attributes and descriptive data such as the data series' start and end date. To get to the observations, in query editor, choose transform, then expand.

I was using Python to pull FRED data series, but after discovering the web connector has a refresh advantage, I'm switching.

Hi - when i do this, and after using multiple sources, only the first shows up in the data source tab. After the first one, I do get the observations for the series I enter as a table in Power Query, but I do not see it in the Data source tab. what could i be doing wrong?

powerbime
Frequent Visitor

I tried using the example it does connect and get a reply but  it only returns three columns to query editor :

Series, Attibute:realtime_start, Attribute:realtime_end.

 

It does not return data and it only has the same date for start and end .

There has to be a way to get data and specify dates

cfh
Frequent Visitor

If you'd like to share your code, I'd be happy to help troubleshoot. Were you able to try this step to transform and expand, or do you not have that option?

 

  • Power BI will initially show a list of attributes and descriptive data such as the data series' start and end date. To get to the observations, in query editor, choose transform, then expand.
powerbime
Frequent Visitor

Thank you for your reply.  I used in PowerBI  "Get from Web" and then the example which is at the FRED API :

 

https://api.stlouisfed.org/fred/series/observations?series_id=GNPCA&api_key=abcdefghijklmnopqrstuvwxyz123456

but I substituted my API key. This is what comes back after I click transform:

 
 

 

Capture.PNG

cfh
Frequent Visitor

@powerbime, your api request looks good. This is what I get when I use it. The tan word Table is a signal that you have some data to expand. Click the double expansion arrows in the green square to see what's available.

1.png

You can choose some or all.

2.png

Chances are good you only want date and value, and you probably don't want to keep the original column name as prefix (you can uncheck what you don't want in the prior step), but here's everything in this data set.

3.png

powerbime
Frequent Visitor

Thanks,

I see the expansion arrows and when i choose I get the menu like in your picture but no "Value". See the snips below.  I am using the GNPCA indicator but I do not get the same data as in your example. It's like if it is only giving me the indicators description but no values or historical dates.

 

Capture4.PNG

What I get is this 

 

Capture5.PNG

cfh
Frequent Visitor

In the transform data area, there is be a ribbon option that's labeled something like advanced editor. Can you please open that and take a screenshot of the window to post here to help with further troubleshooting? Of course it's fine to obscure your API key.

powerbime
Frequent Visitor

Here it is and thanks again. I don't know why I am having so much trouble.Capture6.PNG

And with the expansion as well

 

 

Capture7.PNG

cfh
Frequent Visitor

Try this, @powerbime . You can skip the change type step, which I'm guessing was automatic. The removed other columns step gets this down to just the date and the observation. If you want the other columns, delete this step.

 

let
    Source = Xml.Tables(Web.Contents("https://api.stlouisfed.org/fred/series/observations?series_id=GNPCA&api_key=YOURKEYHERE")),
    #"Expanded observation" = Table.ExpandTableColumn(Source, "observation", {"Attribute:date", "Attribute:value"}, {"Attribute:date", "Attribute:value"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded observation",{"Attribute:date", "Attribute:value"})
in
    #"Removed Other Columns"

 

 

powerbime
Frequent Visitor

No luck

Maybe I am getting different data.

the Series you posted does not have the same columns I get. For one I do not get attribute:value, which is why i imagine I so not see the data.2.png 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.