Pulling Historical Stock Market data -- Function does not work

[chrisB13]

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

[ohyicong1995]

Watch this tutorial to import yahoo finance data with python (5 mins only!)

https://www.youtube.com/watch?v=7p1F3Kno548&feature=youtu.be

[chrisB13 · Accepted solution]

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-msft]

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. 

Best Regards,

Lin