Yahoo link not working

[praveenjujare]

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"

[PhilipTreacy · Accepted solution]

@praveenjujare 

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