Stock Query Pull

[findingsolution]

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 created the initial query. Every time I refresh, it remains stuck with last data from August. How do I get the formula to pull today's data from yahoo, instead of data from august?

Here is the query from the advanced editor:

let
Source = Excel.Workbook(File.Contents("file.xlsx"), null, true),
Stocks_Sheet = Source{[Item="Stocks",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Stocks_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Stock List", type text}, {"Purchase Price", type number}, {"Amount of Shares", Int64.Type}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Quote Pull", each #"Quote Pull"([Stock List])),
#"Expanded Quote Pull" = Table.ExpandTableColumn(#"Invoked Custom Function", "Quote Pull", {"Date", "Open", "High", "Low", "Close", "Adj Close", "Volume"}, {"Date", "Open", "High", "Low", "Close", "Adj Close", "Volume"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Quote Pull",{{"Date", type date}, {"Open", Currency.Type}, {"High", Currency.Type}, {"Low", Currency.Type}, {"Close", Currency.Type}, {"Adj Close", Currency.Type}, {"Volume", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Descending}})
in
#"Sorted Rows"

Thank you!

[lbendlin]

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

[findingsolution]

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]

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

[findingsolution]

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

[lbendlin]

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

[findingsolution]

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

[lbendlin · Accepted solution]

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]

works a charm, thanks so much 🙂