Forum Discussion
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 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!
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"
8 Replies
Your only data source is an Excel file. Do you call the API in the excel file?
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"Right, didn't see that. Your API call has a fixed period range.
