Hello Guys, I have an update, I wrote some hours ago but it does not appear to show up...
One bad news and a semigood one.
As you can see from the Yahoo answer yesterday, they were asking for more details if available which I assumed was to help them with the investigation. So I did send them some more information, in particular the 2 ways the issue was showing up, meaning with a M query via Power BI, and via Power Shell.
1)
Source = Web.Contents(
"https://query1.finance.yahoo.com/v7/finance/download",
[
RelativePath=Symbol,
Query=
[
period1= DateDiff2SecLT, //"1711497601"
period2= DateDiff2SecUT, //"1711584001"
interval="1d"
],
]
)
2)
# ► Create $session Object
$session = New-Object Microsoft.PowerShell.Commands.WebRequestSession
# ► Call 3rd Url ( Get Yahoo's Data )
try {
$URL = $("https://query1.finance.yahoo.com/v7/finance/download/" + $Stock + "?period1=" + $dateDiff2SecLT + "&period2=" + $dateDiff2SecUT + "&interval=1d")
$ResponseText = Invoke-WebRequest -UseBasicParsing -Uri $URL -WebSession $session
$ResponseText = $ResponseText.ToString()
}
Then I got this meaningless answer:
Hi Marco,
Thank you for writing us back.
I apologize for the inconvenience. It appears that the program you're using is not supported by Yahoo Finance because of this we can't provide additional assistance with your concern.
If you have some suggestions and feedback on ways to improve our product we would love to hear from you. Send us your feedback on the Yahoo Finance Feedback Forum. This feedback helps us develop the future versions of our app.
If you have additional questions or concerns, please feel free to contact us again.
Best,
Lucas
Yahoo Customer Care
I asked clarification back again since this was counterminding their previous response but got just an automated reply back.
Then I carried on with the new string that was provided here with "chart", https://query2.finance.yahoo.com/v8/finance/chart/,
which works but of course with completely different format and data returned compared to the one we were discussing.
Nevertheless the subset of data of the previous one is contained in this latter.
I managed to rewrite power shell to accomodate the result, I am not done yet, as this format I have assumes you pass just 1d as interval, some more arrangements are needed to manage multiple days in the same call, here ChatGPT can be your best friend, but I will post the final one later on.
Also, the date retrived for the set of data, in timestamp format, is much more confusing than the straightforward date retreived by the other connection, but it is manageable after you get the logic.
Data are also not as clean as with the previous connection, some are missing here and there, for instance as I write META symbol is missing for the 9/10 (yesterday)
Here the power shell script if it can be of help, I still have to adapt the M language which I have within a quite complex model. So far the period boundaries are harcoded but you see above the variables that you can adapt as needed.
----------------------------------------------------------------------------------
param(
[string] $Stock,
[string] $Cur,
[string] $CountrySE
)
$server = "DATAKING"
$database = "Gekko"
$table = "ndsPrices"
$username =
$password =
$currentDate = Get-Date
$dateDiff = [math]::floor($currentDate.Subtract([datetime]::Parse("1970-01-01")).TotalDays)
$dateDiffSecL = (($dateDiff - 1) * 24 * 60 * 60)
$dateDiffSecU = ($dateDiff * 24 * 60 * 60)
# Lower and upper boundaries for the period in the connection to yahoo
$dateDiff2SecLT = $dateDiffSecL.ToString()
$dateDiff2SecUT = $dateDiffSecU.ToString()
# ► Create $session Object
$session = New-Object Microsoft.PowerShell.Commands.WebRequestSession
# ► Call 3rd Url ( Get Yahoo's Data )
$ResponseText = Invoke-WebRequest -UseBasicParsing -Uri $URL -WebSession $session
$ResponseText = $ResponseText.ToString()
$tempFile = [System.IO.Path]::GetTempFileName()
$responseText | Set-Content -Path $tempFile -Encoding UTF8 -Force
# Load JSON data
$json = Get-Content -Raw -Path $tempFile | ConvertFrom-Json
# Extract relevant data from "indicators" section
$unixEpoch = [datetime]"1970-01-01 00:00:00"
$date = $unixEpoch.AddSeconds($json.chart.result[0].timestamp[0])
$formattedDate = $date.ToString("MM/dd/yyyy")
$high = $json.chart.result[0].indicators.quote[0].high[0]
$low = $json.chart.result[0].indicators.quote[0].low[0]
$volume = $json.chart.result[0].indicators.quote[0].volume[0]
$open = $json.chart.result[0].indicators.quote[0].open[0]
$close = $json.chart.result[0].indicators.quote[0].close[0]
$adjclose = $json.chart.result[0].indicators.adjclose[0].adjclose[0]
#$timestamp = [datetime]::FromFileTimeUTC($json.chart.result[0].timestamp[0] * 10000000 + 116444736000000000)
# Create SQL connection string
$connectionString = "Data Source=$server;Initial Catalog=$database;User ID=$username;Password=$password;"
# Insert data into SQL table
$query = @"
INSERT INTO $table (Symbol, Cur, CountrySE, Date, DayOpen, DayHigh, DayLow, DayClose, DayAdjClose, DayVolume)
VALUES ('$Stock', '$Cur', '$CountrySE', '$formattedDate' , $open, $high, $low, $close, $adjclose, $volume)
"@
# Execute SQL query
Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -Query $query
# Create a SQL connection object
$connectionString = "Data Source=$server;Initial Catalog=$database;User ID=$username;Password=$password;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
# Open the SQL connection
$connection.Open()
# Create a command object
$command = $connection.CreateCommand()
$command.CommandText = "insert into [ndspricesfull] select [Symbol]
,[Cur]
,[CountrySE]
,[Date]
,cast([DayOpen] as numeric(38, 10))
,cast([DayHigh] as numeric(38, 10))
,cast([DayLow] as numeric(38, 10))
,cast([DayClose] as numeric(38, 10))
,cast([DayAdjClose] as numeric(38, 10))
,cast([DayVolume] as bigint)
FROM [dbo].[ndsprices] where CountrySE <> 'UK'
union
select [Symbol]
,[Cur]
,[CountrySE]
,[Date]
,cast([DayOpen] as numeric(38, 10)) / 100
,cast([DayHigh] as numeric(38, 10)) / 100
,cast([DayLow] as numeric(38, 10)) / 100
,cast([DayClose] as numeric(38, 10)) / 100
,cast([DayAdjClose] as numeric(38, 10)) / 100
,cast([DayVolume] as bigint)
FROM [dbo].[ndsprices] where CountrySE = 'UK'
truncate table [ndsprices]
"
# you can get rid of try and catch if not wanted
try{
$command.ExecuteNonQuery()
}
catch {
$errorMessage = $_.Exception.Message
$errorDetails = $_.Exception
# Create a SQL connection object
#$connectionString = "Data Source=$server;Initial Catalog=$database;User ID=$username;Password=$password;"
#$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
# Open the SQL connection
#$connection.Open()
$cmdText= "INSERT INTO logPricesErr (symbol, err, errDetail) values ('" + $Stock + "', '" + $errorMessage + "', '" + $errorDetails + "')"
# Create a command object
$command = $connection.CreateCommand()
# Prepare the SQL command
$command.CommandText = $cmdText
$command.Connection = $connection
$command.ExecuteNonQuery()
$command.CommandText = "truncate table [ndsprices]"
$command.ExecuteNonQuery()
$connection.Close()
throw $_
}
$connection.Close()
------------------------------------------------------------------------------
just get rid of the personal (mine) logic that you do not need and you have the script, please fill up the variables above as it suits you.
Here a cyclical bat file for the call:
set powershellScript=C:\.....\YOURFILENAME.ps1
sqlcmd -S %server% -U %login% -P %password% -d %database% -Q "set nocount on SELECT Symbol, Cur, CountrySE FROM cnfSymbolToLoadDB" -W -h-1 -s "," | FOR /F "tokens=1,2,3 delims=," %%D IN ('MORE') DO (
powershell.exe -ExecutionPolicy Bypass -File %powershellScript% "%%D" "%%E" "%%F"
)
Finally, I do not see why they closed the other connection, which was lighter as with less data retrieved, and left this open (and all the others), if the objective was to put it under a payment wall, also because I explicitely asked them how to pay to get the data, and they did not mind.
Either it was unintentional or I do not know... I still hope it goes back to work.
