locked
Power query - web query HTTP error handling RRS feed

  • Question

  • Hi

    I am doing some web queries to yahoo finance to grab stock information using power query in Excel.

    I have managed to set up the query so that it goes through a lick of stock tickers and then fetches the data for each row/ticker.

    However, occasionally one of the calls using a random ticker will result in an error and the whole refresh of the table stops.

    So I have been looking at ways to retry those tickers when they encounter an error.

    I have found this piece of code (https://docs.microsoft.com/en-us/power-query/waitretry#valuewaitfor) which I think could do the trick, but I cannot figure out how to incorporate it into my existing code.

    retry code:

    let
        waitForResult = Value.WaitFor(
            (iteration) =>
                let
                    result = Web.Contents(url, [ManualStatusHandling = {500}]), 
                    buffered = Binary.Buffer(result),
                    status = Value.Metadata(result)[Response.Status],
                    actualResult = if status = 500 then null else buffered
                in
                    actualResult,
            (iteration) => #duration(0, 0, 0, Number.Power(2, iteration)),
            5)
    in
        waitForResult,

    And this is my query function code:

    (Id as text) as table =>
    let
        Source = Web.Page(Web.Contents("http://tools.morningstar.dk/dk/stockreport/default.aspx?Site=dk&id=" & Id & "&LanguageId=en-Gb&SecurityToken=" & Id & "]3]0]E0WWE" & "$$ALL")),
        Data1 = Source{1}[Data],
        #"Replaced Value" = Table.ReplaceValue(Data1,".",",",Replacer.ReplaceText,{"Last Close"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"52 Week Range"}),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".",",",Replacer.ReplaceText,{"P/E"}),
        #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",".",",",Replacer.ReplaceText,{"Yield %"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value3",{{"Last Close", type number}, {"P/E", type number}, {"Yield %", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Yield", each [#"Yield %"]/100),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Yield %"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ISIN", "Yield", "P/E", "Last Close", "52 Week Range"})
    in
        #"Reordered Columns"

    So I would change "500" to "404" and insert the url part, but can't figure out how to merge it with my existing code.

    Also, I am unsure of the significance of this: the code runs fine in the power query editor window, but when I "close and load" (to table) that is when th error can occur, or when I refresh the Excel table.

    And why is it necessary to both fetch and load all the data into the power query editor window and then fetch it and load it again when loding to table in Excel?

    Why doesn't it just transfer the already refresed data from power query window to table in Excel...?

    Any hints would be appreciated.

    I am very new to power query...




    • Edited by Velsyvels Friday, December 28, 2018 2:03 PM added
    Friday, December 28, 2018 12:10 PM

Answers

  • Something like this should work:

    (Id as text) as table =>
    let
     fnDelayedCall = (url) =>
     let
      waitForResult = Value.WaitFor(
       (iteration) =>
        let
         result = Web.Contents(url, [ManualStatusHandling = {500}]), 
         buffered = Binary.Buffer(result),
         status = Value.Metadata(result)[Response.Status],
         actualResult = if status = 500 then null else buffered
        in
         actualResult,
       (iteration) => #duration(0, 0, 0, Number.Power(2, iteration)),
       5)
     in
      waitForResult,
     DelayedCall = fnDelayedCall("http://tools.morningstar.dk/dk/stockreport/default.aspx?Site=dk&id=" & Id & "&LanguageId=en-Gb&SecurityToken=" & Id & "]3]0]E0WWE" & "$$ALL")
        Source = Web.Page(DelayedCall),
        Data1 = Source{1}[Data],
        #"Replaced Value" = Table.ReplaceValue(Data1,".",",",Replacer.ReplaceText,{"Last Close"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"52 Week Range"}),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".",",",Replacer.ReplaceText,{"P/E"}),
        #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",".",",",Replacer.ReplaceText,{"Yield %"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value3",{{"Last Close", type number}, {"P/E", type number}, {"Yield %", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Yield", each [#"Yield %"]/100),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Yield %"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ISIN", "Yield", "P/E", "Last Close", "52 Week Range"})
    in
        #"Reordered Columns"
    



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, January 2, 2019 9:33 AM

All replies

  • Anyone?
    Monday, December 31, 2018 12:54 PM
  • Something like this should work:

    (Id as text) as table =>
    let
     fnDelayedCall = (url) =>
     let
      waitForResult = Value.WaitFor(
       (iteration) =>
        let
         result = Web.Contents(url, [ManualStatusHandling = {500}]), 
         buffered = Binary.Buffer(result),
         status = Value.Metadata(result)[Response.Status],
         actualResult = if status = 500 then null else buffered
        in
         actualResult,
       (iteration) => #duration(0, 0, 0, Number.Power(2, iteration)),
       5)
     in
      waitForResult,
     DelayedCall = fnDelayedCall("http://tools.morningstar.dk/dk/stockreport/default.aspx?Site=dk&id=" & Id & "&LanguageId=en-Gb&SecurityToken=" & Id & "]3]0]E0WWE" & "$$ALL")
        Source = Web.Page(DelayedCall),
        Data1 = Source{1}[Data],
        #"Replaced Value" = Table.ReplaceValue(Data1,".",",",Replacer.ReplaceText,{"Last Close"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"52 Week Range"}),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".",",",Replacer.ReplaceText,{"P/E"}),
        #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",".",",",Replacer.ReplaceText,{"Yield %"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value3",{{"Last Close", type number}, {"P/E", type number}, {"Yield %", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Yield", each [#"Yield %"]/100),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Yield %"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ISIN", "Yield", "P/E", "Last Close", "52 Week Range"})
    in
        #"Reordered Columns"
    



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, January 2, 2019 9:33 AM