Dynamic JSON parameter for each row

[gdl74]

Hello,

I have a simple Excel (2016) file with one column containing a stock ticker:
     A
1 AAPL
2 GOOGL
3 MSFT

I have to call a REST API, passing the stock ticker as a parameter:
https://...&symbol=<ticker>

The API returns data as:

{
"Meta Data": {
"1. Information": "Daily Prices (open, high, low, close) and Volumes",
"2. Symbol": "MSFT",
"3. Last Refreshed": "2020-12-07",
"4. Output Size": "Compact",
"5. Time Zone": "US/Eastern"
},
"Time Series (Daily)": {
"2020-12-07": {
"1. open": "214.3700",
"2. high": "215.5400",
"3. low": "212.9900",
"4. close": "214.2900",
"5. volume": "24619997"
},
"2020-12-04": {
"1. open": "214.2200",
"2. high": "215.3800",
"3. low": "213.1800",
"4. close": "214.3600",
"5. volume": "24666039"
},
"2020-12-03": {
"1. open": "214.6100",
"2. high": "216.3757",
"3. low": "213.6500",
"4. close": "214.2400",
"5. volume": "25120922"
},
...

}
}

I want to display the last close price of each stock near its ticker:
     A                 B
1 AAPL      123.7500
2 GOOGL   214.2900
3 MSFT     1817.0300

I'm able to call the REST API via PowerQuery, but I'm stuck on passing the ticker of column A as a parameter.
Any hint?

Thank you

[mahoneypat]

I'm confused.  You can definitely automate this whole process.  You can

1. Get a column of ticker values from an Excel sheet or other source

2. Create a date parameter and/or get a column of dates from an Excel sheet or other source

2. Pass those ticker values and/or date(s) into separate API web calls to get stock data

3. Use the M code approach I showed in last pbix to get the latest refresh and last close value for each ticker/JSON response.

Which part are you stuck on?  It seems like you have all the pieces now to do this.

Regards,

Pat

[gdl74]

Hello,

in my previous answer I was referring to your reply to my private message, which didn't match my requests.

1. Get the ticker from each row in the first column and pass it as a parameter.
I meant something like:
Source = Json.Document(Web.Contents("https://...&symbol=" & <cell_A2>))

I guess this is not possible, so I defined an Excel table and then combined data with fields from the JSON stream for each row.

2. In the expression Source[#"Time Series (Daily)"][#"2020-12-10"][4. close], use LastRefreshed to replace the static part [#"2020-12-10"].
I understand that pointing to the item via First leads to a similar result, but I wanted to know if I can use a parameter so that I can make it work for any date.

3. Return both LastRefreshed and LastClose, in order to fill columns B and C, without any headers or extra metadata information.
The sample you provided does this, but it doesn't take data from the Excel and does not combine the results with the existing list.

Now, I'm not stuck on any part, as I wrote "I managed to get a working file".

I attached my sample files so that you can understand what exactly I was looking for and I was asking whether my code could be optimized.

Thank you

Kind regards

[mahoneypat]

Here is some M code to show how to transform the data once you have the JSON record for each ticker.  I put the JSON files you sent in a folder and used the folder connector, so just look at the #"Added Custom" step forward.  I hope you downloaded the files just for troubleshooting.  This could be made into a function but it is not necessary.  The key for the latest close price is converting the record of date records to a list and then using List.First to get the latest one (and then accessing the close with the field name "[4. close]" to get the value.  Please let me know if any questions.

let
    Source = Folder.Files("C:\Test\IPO"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".json")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Json.Document([Content])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Meta Data", "Time Series (Daily)"}, {"Meta Data", "Time Series (Daily)"}),
    GetLastRefresh = Table.AddColumn(#"Expanded Custom", "Last Refreshed", each [Meta Data][3. Last Refreshed], type date),
    GetLatestClose = Table.AddColumn(GetLastRefresh, "Close", each List.First(Record.ToList([#"Time Series (Daily)"]))[4. close], Currency.Type),
    #"Removed Other Columns1" = Table.SelectColumns(GetLatestClose,{"Name", "Last Refreshed", "Close"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Last Refreshed", type date}, {"Close", Currency.Type}})
in
    #"Changed Type"

Regards,

Pat

[gdl74]

Hello,

I provided the three JSON files just to avoid requesting a new API key, in other words I simply used File.Contents instead of Web.Contents: my aim was to get the tickers from the Excel sheet, I guess there is no way other than defining the range as table.

I understand that pointing to the item via First leads to the same result, but I wanted to know if I can use a parameter so that I can make it work for any date.

I managed to get a working file (https://1drv.ms/u/s!AqhJPWnFwvx3nimEIr5spycJKj5J?e=825Jdi), but I'm pretty sure a few optimizations could be applied.

Thank you

Kind regards

[ImkeF]

Hi @gdl74 ,

you have to turn your query into a function to apply it to multiple tickers. 
I've described the general method here, that also ensures that refreshes will work in the service: Web Scraping 2: Scrape multiple pages at once and refresh in Power BI service – The BIccountant

Your challenge to dynamically retrieve the latest value returns is included in the following function. It creates a record that returns your 2 desired fields from each call:

(myJson) =>
let
    Source = myJson,
    LastClose = Record.ToTable( Source[#"Time Series (Daily)"] ) {0} [Value] [4. close],
    LastRefreshed = Source [Meta Data] [3. Last Refreshed],
    CombineToRecord = [LastRefreshed = LastRefreshed, LastClose = LastClose]
in
    CombineToRecord

If you want to use it as it is, you have to first make the dynamic call following my blogpost. This will return a JSON. Then add another column where you call the above function that takes the JSON as an input parameter.

[mahoneypat]

There is a link to a pbix file in the YouTube video description.  You can then look at the query editor to see the example queries using Giphy API calls (similar to what you are trying to do).  I suggest you pass the ticker into the API (as shown in video), then expand the table, then use additional steps in the query (or right DAX measures to get your desired result).

If you want to send your pbix/xlsx to me in a direct message, I can send back a working pbix file (or Excel, if that is your preference).  You can then change your access token (if applicable).

Regards,

Pat

[Jimmy801]

Hello @gdl74 

add a new column to your table and launching your api there using your column information as parameter.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[gdl74]

Hello,

I would need a bit more help, please see my reply to mahoneypat.

Thank you

Kind regards

[mahoneypat]

Please see if this video helps.  It will show how to pass the values in a column into your web calls.  You should also be able to generate a text strings with today/yesterday date to limit your response to the most recent data.

(1) Power BI - Tales From The Front - REST APIs - YouTube

Regards,

Pat

[gdl74]

Hello,

I've walked through the video, but I didn't find any link to an Excel file, so I'm still looking for a solution.

Basically, I start with:

     A          B         C
1 Ticker  LastRefreshed LastClose
2 AAPL
3 GOOGL
4 MSFT

and I want to achieve this:

     A          B          C
1 Ticker  LastRefreshed LastClose
2 AAPL     2020-12-07    123.7500
3 GOOGL    2020-12-07    214.2900
4 MSFT     2020-12-07   1817.0300

Via the following query:

let
    Ticker = "MSFT",
    Source = Json.Document(Web.Contents("https://...&symbol=" & Ticker)),
    #"MetaData" = Source[Meta Data],
    #"LastRefreshed" = #"MetaData"[3. Last Refreshed],
    #"TimeSeriesDaily" = Source[#"Time Series (Daily)"],
    #"LastDataSet" = #"TimeSeriesDaily"[#"2020-12-07"],
    #"LastClose" = #"LastDataSet"[4. close]
in
    #"LastClose"

I can get a single value, but I would need help on these points:

1. Ticker value should be taken from the Excel file, for each row (A2, A3, A4).
2. #"2020-12-07" should be replaced by #"LastRefreshed" value (the response JSON itself provides the right date, no need to calculate today/yesterday), but I can't find the correct syntax.
3. I need to load LastRefreshed to column B and LastClose to column C, maybe I have to create a Table/List in order to return more than one value, but I'm not sure how this fits into existing data.

Thank you

Kind regards