Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Hello,
I would need a bit more help, please see my reply to mahoneypat.
Thank you
Kind regards
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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:
Thank you
Kind regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.