PowerBi connection failed to Yahoo finance

[Schch]

Hi All,

Does anyone have idea what the issue to connecting Yahoo finance historical data.  It failed recently.  I have it all time success in before.

Dow Jones Industrial Average (^DJI) stock historical prices and data – Yahoo Finance

https://au.finance.yahoo.com/quote/%5EDJI/history/

Any clue would be appreciated

Stephen

[pbi_2004]

try this instead

let
    today = Text.BeforeDelimiter(
        Text.From(
            Duration.TotalSeconds(DateTime.LocalNow() - #datetime(1970,1,1,0,0,0))
        ),
        "."
    ),
    Source = Json.Document(
        Web.Contents("https://query2.finance.yahoo.com",
            [
                RelativePath="v8/finance/chart/%5EDJI",
                Query=[
                    period1="0",  // Start from the earliest available date
                    period2=today,
                    interval="1d",
                    events="history",
                    includeAdjustedClose="true"
                ]
            ]
        )
    ),
    Results = Source[chart][result]{0},
    Timestamps = Results[timestamp],
    Quotes = Results[indicators][quote]{0},
    AdjClose = Results[indicators][adjclose]{0}[adjclose],
    ConvertToTable = Table.FromColumns({
        Timestamps,
        Quotes[open],
        Quotes[high],
        Quotes[low],
        Quotes[close],
        AdjClose,
        Quotes[volume]
    }, {"Timestamp", "Open", "High", "Low", "Close", "Adj Close", "Volume"}),
    ConvertUnixTimestamp = Table.TransformColumns(ConvertToTable, {
        {"Timestamp", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_), type datetime}
    }),
    FormatDates = Table.TransformColumnTypes(ConvertUnixTimestamp, {{"Timestamp", type date}}),
    SortedTable = Table.Sort(FormatDates, {{"Timestamp", Order.Descending}}),
    FormattedTable = Table.TransformColumnTypes(SortedTable, {
        {"Open", type number},
        {"High", type number},
        {"Low", type number},
        {"Close", type number},
        {"Adj Close", type number},
        {"Volume", Int64.Type}
    })
in
    FormattedTable

[Anonymous]

Hi @Schch 

What error are you getting when you connect? You mentioned that it was connecting fine before, did you do a recent update to Power BI?

Best Regards,
Community Support Team _ Ailsa Tao

[Schch]

Hi Ailsa,

thanks for your reply.   

Hoping the following information can help.

Stephen

-----------

I did not make any upgrade to PowerBi lately before I noticed this failure in recent days.

the current version is :

I noticed the problem when I attempted to refresh the data in PowerBi desktop file.

I chased back to Power Query and the data source setting.  It is because I expereinced similar problem several months ago.  At that time, I used the data source from finance.yahoo.com.  One day the above error came when I refreshed the data,  With different sorts of try by error,  I found the Australia site works, so I changed the source to au.finance.yahoo.com.  I thought it relates to the Web page layout variance in relation to Ad banner.  

Just so happened 2 days ago, the similar problem starts to appear in the yahoo AU site as I noticed.

In order to simply the fault finding routine, I try the connection from scratch file.  

The error message I got is :

Details: "Web.Contents failed to get contents from 'https://au.finance.yahoo.com/quote/%5EHSI/history/' (503): Service Unavailable",

Another thing I discovered today may help.  I placed the same task in Excel to Getdata from the same Web source.  https://au.finance.yahoo.com/quote/%5EHSI/history/   Surprisingly, It works.

I got what I normally expected.

[Schch]

Hi  pbi 2004,

Can I know where should I paste the script, in a new query of Power Query editor?  

If so, how can I extract a table from the source script.

thanks

Stephen

[pbi_2004]

Go Advance editor for that query

If not working you can try this, also work - edit with your pair  %5EDJI

let

    url = "https://au.finance.yahoo.com/quote/AUDEUR%3DX/history/",
    headers = [
        #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.71 Safari/537.36",
        #"Accept" = "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
        #"Accept-Language" = "en-US,en;q=0.9",
        #"Cache-Control" = "no-cache",
        #"Pragma" = "no-cache"
    ],
    Source = Web.Contents(url, [Headers=headers]),
    SourceText = Text.FromBinary(Source),
    
    #"Extracted Table" = Html.Table(SourceText, {
        {"Date", "td:nth-child(1)"},
        {"Open", "td:nth-child(2)"},
        {"High", "td:nth-child(3)"},
        {"Low", "td:nth-child(4)"},
        {"Close", "td:nth-child(5)"},
        {"Adj Close", "td:nth-child(6)"},
        {"Volume", "td:nth-child(7)"}
    }, [RowSelector="table[class*='yf-ewueuo'] > tbody > tr"]),
    
    // Custom function to parse the date and adjust to Australian time
    ParseAndAdjustDate = (dateText as text) =>
        let
            parts = Text.Split(dateText, " "),
            day = Number.FromText(parts{0}),
            monthMap = #table({"Month", "Num"}, {
                {"January", 1}, {"Jan", 1}, {"February", 2}, {"Feb", 2}, {"March", 3}, {"Mar", 3},
                {"April", 4}, {"Apr", 4}, {"May", 5}, {"June", 6}, {"Jun", 6}, {"July", 7}, {"Jul", 7},
                {"August", 8}, {"Aug", 8}, {"September", 9}, {"Sept", 9}, {"Sep", 9}, {"October", 10}, {"Oct", 10},
                {"November", 11}, {"Nov", 11}, {"December", 12}, {"Dec", 12}
            }),
            month = Table.SelectRows(monthMap, each Text.StartsWith([Month], Text.Proper(parts{1}))){0}[Num],
            year = Number.FromText(parts{2}),
            usDate = #date(year, month, day),
            australiaDate = Date.AddDays(usDate, 1)  // Add one day to adjust to Australian time
        in
            australiaDate,
    
    // Apply the custom date parsing and adjusting function
    #"Parsed and Adjusted Dates" = Table.TransformColumns(#"Extracted Table", {
        {"Date", each if _ = null then null else ParseAndAdjustDate(_), type date}
    }),
    
    // Transform other columns to appropriate types
    #"Changed Type" = Table.TransformColumnTypes(#"Parsed and Adjusted Dates",{
        {"Open", type number},
        {"High", type number},
        {"Low", type number},
        {"Close", type number},
        {"Adj Close", type number},
        {"Volume", Int64.Type}
    }),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Volume", null}})
in
    #"Replaced Errors"

[Schch]

thanks pbi2004.

it works

[BritPowerBI]

@pbi_2004 good solution - got it to work for the one stock - how would you go about changing the JSON code to be dynamic so that you could pull in different ticker numbers?