API to pull data based on list of items

[csmithson]

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

[Greg_Deckler]

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.