here is the M language translation of the same M language code that before was working with the connection https://query1.finance.yahoo.com/v7/finance/download
this maintanes the exact same format. ChatGPT did it for me in a single passage after I prepared a well formatted request, simply unbelivable. This code deals with a day only, the current, since the historical data I load into sql server and combine with the live ones via DAX, but it can easily be adapted to process a period, chatgpt will do for you in no time. Only relevant parameter is Symbol, Cur and CountrySE are currency and stock exchange country but probably not relevant in your code. This is a function that cycles multiple symbols one by one, I load around 20k symbols and it does not work in a multiple-symbol string. Hope this helps.
let
Source = (Symbol as text, Cur as text, CountrySE as text) => let
// Calculate the date differences to generate period1 and period2
DateDiff = Duration.Days(Date.From(DateTime.LocalNow()) - #date(1970, 1, 1)),
DateDiffSecL = DateDiff * 24 * 60 * 60,
DateDiffSecU = (DateDiff + 1) * 24 * 60 * 60,
DateDiff2SecLT = Number.ToText(DateDiffSecL),
DateDiff2SecUT = Number.ToText(DateDiffSecU),
// Get JSON data from Yahoo Finance API
Source = Web.Contents(
"https://query2.finance.yahoo.com/v8/finance/chart/" & Symbol,
[
Query = [
period1 = DateDiff2SecLT,
period2 = DateDiff2SecUT,
interval = "1d"
],
ManualStatusHandling = {404}
]
),
// 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)
#"Changed Type" = Table.TransformColumnTypes(DataTable, {
{"Date", type date},
{"Open", type number},
{"High", type number},
{"Low", type number},
{"Close", type number},
{"Adj Close", type number},
{"Volume", Int64.Type}
}),
// Add Symbol column
#"Added Symbol Column" = Table.AddColumn(#"Changed Type", "Symbol", each Symbol),
// Filter out any erroneous data (e.g., dates or close prices that are null or empty)
#"Filtered Rows" = Table.SelectRows(#"Added Symbol Column", each [Date] <> #date(1900, 1, 1) and [Close] <> null and [Close] <> "")
in
#"Filtered Rows"
in
Source
