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"
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)?

lbendlin's avatar
lbendlin
Super User
4 years ago

Can I assume that Period1 and Period2 are expressed in seconds since 1970-01-01 ?

  • findingsolution's avatar
    findingsolution
    Helper I
    4 years ago

    I assume so, the earliest data is from Jan 1 2018 so it's probably in seconds.

    • lbendlin's avatar
      lbendlin
      Super User
      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"
      • findingsolution's avatar
        findingsolution
        Helper I
        4 years ago

        works a charm, thanks so much 🙂