Forum Discussion
Helper IYahoo link not working
stockquotes as text)as table =>
let
EndDate= Number.Round(Duration.TotalSeconds(DateTime.LocalNow()- #datetime(1970,1,1,0,0,0)),0),
// Get JSON data from Yahoo Finance API
Source = Json.Document(Web.Contents(https://query2.finance.yahoo.com, [RelativePath = "/v8/finance/chart/" & stockquotes & "?period1"=1691314869 & "&period2=" & Text.From(EndDate) & "&interval=1d&events=history&includeAdjustedClose=true"])
),
// Parse JSON response
JsonResponse = Json.Document(Source),
// Check for errors in the JSON response
Chart = JsonResponse[chart],
Result = Chart[result]{0},
Meta = Result[meta],
Indicators = Result[indicators],
Quote = Indicators[quote]{0},
Timestamps = Result[timestamp],
AdjClose = Indicators[adjclose]{0}[adjclose],
// Convert timestamps (in seconds) to dates
Dates = List.Transform(Timestamps, each DateTime.From(#datetime(1970, 1, 1, 0, 0, 0)) + #duration(0, 0, 0, _)),
// Combine the data into a table
DataTable = Table.FromColumns({
Dates,
Quote[open],
Quote[high],
Quote[low],
Quote[close],
AdjClose,
Quote[volume]
}, type table [Date=datetime, Open=number, High=number, Low=number, Close=number, #"Adj Close"=number, Volume=Int64.Type]),
// Change data types (if necessary, although columns should already be typed correctly)
#"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"
Expression.Error: We cannot apply operator & to types Number and Text.
Details:
Operator=&
Left=1691314869
Right=&period2=
before it was working
(stockquotes as text)as table =>
let
EndDate= Number.Round(Duration.TotalSeconds(DateTime.LocalNow()- #datetime(1970,1,1,0,0,0)),0),
Source = Csv.Document(Web.Contents(https://query1.finance.yahoo.com/,[RelativePath= "v7/finance/download/"&stockquotes&"?period1=1691314869&period2=" & Text.From(EndDate) & "&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"
Both of those queries are missing double quotes around the URL. It should be
"https://query2.finance.yahoo.com"But the error you posted about is caused because you have a double quote in the wrong place, it should be
"?period1=1691314869" & "&period2="Here's the full query
(stockquotes as text)as table => let EndDate= Number.Round(Duration.TotalSeconds(DateTime.LocalNow()- #datetime(1970,1,1,0,0,0)),0), // Get JSON data from Yahoo Finance API Source = Json.Document(Web.Contents("https://query2.finance.yahoo.com", [RelativePath = "/v8/finance/chart/" & stockquotes & "?period1=1691314869" & "&period2=" & Text.From(EndDate) & "&interval=1d&events=history&includeAdjustedClose=true"]) ), // Parse JSON response JsonResponse = Json.Document(Source), // Check for errors in the JSON response Chart = JsonResponse[chart], Result = Chart[result]{0}, Meta = Result[meta], Indicators = Result[indicators], Quote = Indicators[quote]{0}, Timestamps = Result[timestamp], AdjClose = Indicators[adjclose]{0}[adjclose], // Convert timestamps (in seconds) to dates Dates = List.Transform(Timestamps, each DateTime.From(#datetime(1970, 1, 1, 0, 0, 0)) + #duration(0, 0, 0, _)), // Combine the data into a table DataTable = Table.FromColumns({ Dates, Quote[open], Quote[high], Quote[low], Quote[close], AdjClose, Quote[volume] }, type table [Date=datetime, Open=number, High=number, Low=number, Close=number, #"Adj Close"=number, Volume=Int64.Type]), // Change data types (if necessary, although columns should already be typed correctly) #"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"Regards
Phil
1 Reply
- PhilipTreacy
Super User
Both of those queries are missing double quotes around the URL. It should be
"https://query2.finance.yahoo.com"But the error you posted about is caused because you have a double quote in the wrong place, it should be
"?period1=1691314869" & "&period2="Here's the full query
(stockquotes as text)as table => let EndDate= Number.Round(Duration.TotalSeconds(DateTime.LocalNow()- #datetime(1970,1,1,0,0,0)),0), // Get JSON data from Yahoo Finance API Source = Json.Document(Web.Contents("https://query2.finance.yahoo.com", [RelativePath = "/v8/finance/chart/" & stockquotes & "?period1=1691314869" & "&period2=" & Text.From(EndDate) & "&interval=1d&events=history&includeAdjustedClose=true"]) ), // Parse JSON response JsonResponse = Json.Document(Source), // Check for errors in the JSON response Chart = JsonResponse[chart], Result = Chart[result]{0}, Meta = Result[meta], Indicators = Result[indicators], Quote = Indicators[quote]{0}, Timestamps = Result[timestamp], AdjClose = Indicators[adjclose]{0}[adjclose], // Convert timestamps (in seconds) to dates Dates = List.Transform(Timestamps, each DateTime.From(#datetime(1970, 1, 1, 0, 0, 0)) + #duration(0, 0, 0, _)), // Combine the data into a table DataTable = Table.FromColumns({ Dates, Quote[open], Quote[high], Quote[low], Quote[close], AdjClose, Quote[volume] }, type table [Date=datetime, Open=number, High=number, Low=number, Close=number, #"Adj Close"=number, Volume=Int64.Type]), // Change data types (if necessary, although columns should already be typed correctly) #"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"Regards
Phil