Forum Discussion
Pulling Historical Stock Market data -- Function does not work
Hey Everyone,
I am hoping to get a 2nd pair of eyes on this and see what I might be missing. I am building a dashboard for historical stock prices and working on building out some technical indicators. I originally went the path of having a friend build a connector to the API and then build out a SQL database (Painful process, buggy so far and I also dont know how to program).
I am now attempting to skip over all this because I think it may be easier to directly call the web APIs into Power BI.
I have created a table for ticker examples (AAPL, MSFT, NFLX) as examples (Won't let me add screenshot)
ID
AAPL
MSFT
NFLX
[This is all the table has]
I have created a function getTickers(id):
= (id as text) => let
Source = Json.Document(Web.Contents("https://api.tiingo.com/tiingo/daily/"& id &"/prices?startDate=2012-1-1&endDate=2016-1-1&token=[Covered up for security]")),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"
When I go to invoke the custom function I receive a :
An error occurred in the ‘getTickers(id)’ query. Expression.Error: We cannot convert a value of type List to type Record.
Details:
Value=List
Type=Type
It is interesting enough because other examples have worked. Any ideas?
Thanks in advance
Resolved on my own
The key was to:1. Pull in the API data for any ticker symbol first
2. Create a Parameter for any symbol
3. Create a ticker list (all tickers I intend on gathering data for -- theres 300 of them) in Excel, load the Excel sheet as a separate table.
4. Create a function called "GetSymbolHistoric" (or any name you want)
5. Invoke function on table from Excel (Ticker List)
Now, as I add tickers I just need to add them to the Excel sheet and refresh report and everything is completed.
Next part of the project:
Incremental Refresh -- Storing the historical data, then only updating the most recent data on the report to save performance and time.
3 Replies
Resolved on my own
The key was to:1. Pull in the API data for any ticker symbol first
2. Create a Parameter for any symbol
3. Create a ticker list (all tickers I intend on gathering data for -- theres 300 of them) in Excel, load the Excel sheet as a separate table.
4. Create a function called "GetSymbolHistoric" (or any name you want)
5. Invoke function on table from Excel (Ticker List)
Now, as I add tickers I just need to add them to the Excel sheet and refresh report and everything is completed.
Next part of the project:
Incremental Refresh -- Storing the historical data, then only updating the most recent data on the report to save performance and time.
v-lili6-msftCommunity Support
hi, chrisB13
It's pleasant that your problem has been solved,
please close the topic and mark the relevant post as an answer.
Thank you. :smileyvery-happy:
Best Regards,
Lin
Watch this tutorial to import yahoo finance data with python (5 mins only!)
https://www.youtube.com/watch?v=7p1F3Kno548&feature=youtu.be
