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