Using FRED Data

[Anonymous]

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.

[TylerChessman]

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]

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)

[rverner]

You are awesome!

[cfh]

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)

[wgillingham]

This worked perfect for me. Thanks for your help

[Aron_Moore]

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

[powerbime]

These links are broken

Aron_Moore 

Solution Specialist

 

Re: Using FRED Data

 

‎04-09-2019 01:26 PM

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...

[powerbime]

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...

[Anonymous]

@Aron_Moore 

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

[cfh]

• 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.

[powerbime]

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]

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]

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:

[cfh]

@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.

You can choose some or all.

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.

[powerbime]

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.

What I get is this 

[cfh]

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]

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

[powerbime]

And with the expansion as well

[cfh]

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]

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. 

[cfh]

Hi, @powerbime I can see attribute:value in the list in your screen shot. Do you need to scroll to the right to see the column? I recommend unchecking all but the value and date.