cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
csmithson New Member
New Member

API to pull data based on list of items

Hello all!

 

I'm in need of some help from knowledgable members who have experience using APIs as a data source in Power BI. I am trying to pull financials for companies using IEX's APIs (https://api.iextrading.com/1.0/stock/aapl/financials?period=annual). However, I don't want to pull the data for just one company (aapl in my example URL), but rather an entire list of companies (https://api.iextrading.com/1.0/ref-data/symbols). Is there a way to pass the list of symbols in to the URL so that it pulls the data for each ticker symbol?

 

This is the code for just a single company:

let
Source = Json.Document(Web.Contents("https://api.iextrading.com/1.0/stock/aapl/financials?period=annual")),
financials = Source[financials],
#"Converted to Table" = Table.FromList(financials, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses"}, {"reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"grossProfit", type text}, {"costOfRevenue", type text}, {"operatingRevenue", type text}, {"totalRevenue", type text}, {"operatingIncome", type text}, {"netIncome", type text}, {"researchAndDevelopment", type text}, {"operatingExpense", type text}, {"currentAssets", type text}, {"totalAssets", type text}, {"totalLiabilities", type text}, {"currentCash", type text}, {"currentDebt", type text}, {"totalCash", type text}, {"totalDebt", type text}, {"shareholderEquity", type text}, {"cashChange", type text}, {"cashFlow", type text}, {"operatingGainsLosses", type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"grossProfit", Currency.Type}, {"costOfRevenue", Currency.Type}, {"operatingRevenue", Currency.Type}, {"totalRevenue", Currency.Type}, {"operatingIncome", Currency.Type}, {"netIncome", Currency.Type}, {"researchAndDevelopment", Currency.Type}, {"operatingExpense", Currency.Type}, {"currentAssets", Currency.Type}, {"totalAssets", Currency.Type}, {"totalLiabilities", Currency.Type}, {"currentCash", Currency.Type}, {"currentDebt", Currency.Type}, {"totalCash", Currency.Type}, {"totalDebt", Currency.Type}, {"shareholderEquity", Currency.Type}, {"cashChange", Currency.Type}, {"cashFlow", Currency.Type}, {"operatingGainsLosses", Currency.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"totalRevenue", "Total Revenue"}, {"reportDate", "Date"}})
in
#"Renamed Columns"

 

Here is the code for getting the list of symbols:

let
Source = Json.Document(Web.Contents("https://api.iextrading.com/1.0/ref-data/symbols")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"symbol", "name", "date", "isEnabled", "type", "iexId"}, {"symbol", "name", "date", "isEnabled", "type", "iexId"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([isEnabled] = true))
in
#"Filtered Rows"

 

Thanks in advance for any help!!!

1 REPLY 1
Super User
Super User

Re: API to pull data based on list of items

I recently wrote an article on something very similar. https://community.powerbi.com/t5/Community-Blog/Turning-quot-Web-by-Example-quot-into-Power-Query-Fu...

 

You are going to want to pull your symbols into a table. Then you can calculate the URL using the symbol. Then you can invoke a custom function like below, passing in your URL.

 

let
    fnGetDetail = (url) =>
    let
        Source = Json.Document(Web.Contents(url)),
        financials = Source[financials],
        #"Converted to Table" = Table.FromList(financials, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses"}, {"Column1.reportDate", "Column1.grossProfit", "Column1.costOfRevenue", "Column1.operatingRevenue", "Column1.totalRevenue", "Column1.operatingIncome", "Column1.netIncome", "Column1.researchAndDevelopment", "Column1.operatingExpense", "Column1.currentAssets", "Column1.totalAssets", "Column1.totalLiabilities", "Column1.currentCash", "Column1.currentDebt", "Column1.totalCash", "Column1.totalDebt", "Column1.shareholderEquity", "Column1.cashChange", "Column1.cashFlow", "Column1.operatingGainsLosses"})
    in
        #"Expanded Column1"
in
    fnGetDetail

@ImkeF may have more options and advice. There is also an "Advanced" option to the Web Connector that allows passing in of "parts" which may be of use. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!