Forum Discussion

findingsolution's avatar
findingsolution
Helper I
4 years ago
Solved

Stock Query Pull

Hello,   A while ago I followed this youtube tutorial to use the Yahoo API to create a stock pull query.    My issue is that it won't update with the latest data - still stuck at 2/8/21, when I c...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Something like this

    let
StockQuote="AMZN",
P1=Duration.Days((Date.From(DateTime.LocalNow())-#date(1970,1,1)-#duration(7,0,0,0)))*86400,
P2=Duration.Days(Date.From(DateTime.LocalNow())-#date(1970,1,1))*86400,
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&StockQuote&"?period1="&Text.From(P1)&"&period2="&Text.From(P2)&"&interval=1d&events=history&includeAdjustedClose=true"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
#"Changed Type"
lbendlin's avatar
lbendlin
Super User
4 years ago

Your only data source is an Excel file.  Do you call the API in the excel file?

  • findingsolution's avatar
    findingsolution
    Helper I
    4 years ago

    no, I have a query that pulls the api:

     

    (StockQuote as text) as table =>

    let
    Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&StockQuote&"?period1=1514764800&period2=1627862400&interval=1d&events=history&includeAdjustedClose=true"),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
    in
    #"Changed Type"

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      Right, didn't see that.  Your API call has a fixed period range.

      • findingsolution's avatar
        findingsolution
        Helper I
        4 years ago

        How do I change it so that it will automatically call the latest data (ie today or yesterday)?