Getting all symbols from yahoo - Incremental function in power query

[plumek102]

Hi,

I would like to get all symbols from https://finance.yahoo.com/screener/unsaved/c4869ef0-9f28-4221-8413-213454ddc17f?offset=0&count=100  automatically.  The challange is that it contains more than 14k symbols and maximum number of symbols dispayied on page is 100. 

Each page differs by offset parameter: 0, 100, 200 until it raeachs last symbols

Page difference: 

page 1 https://finance.yahoo.com/screener/unsaved/c4869ef0-9f28-4221-8413-213454ddc17f?count=100&offset=0

page 2 https://finance.yahoo.com/screener/unsaved/c4869ef0-9f28-4221-8413-213454ddc17f?count=100&offset=100

page 3 https://finance.yahoo.com/screener/unsaved/c4869ef0-9f28-4221-8413-213454ddc17f?count=100&offset=200

etc. 

Any idea how to create this function? 

Thanks.

[lbendlin · Accepted solution]

Query:

let
    Source = {0..142},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Replaced Value" = Table.ReplaceValue(#"Converted to Table",each [Column1],each Table("https://finance.yahoo.com/screener/unsaved/c4869ef0-9f28-4221-8413-213454ddc17f?count=100&offset=" & Text.From(100*[Column1])),Replacer.ReplaceValue,{"Column1"}),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Replaced Value", "Column1", {"Symbol", "Name", "Price (Intraday)", "Change", "% Change", "Volume", "Avg Vol (3 month)", "Market Cap", "PE Ratio (TTM)", "52 Week Range"}, {"Symbol", "Name", "Price (Intraday)", "Change", "% Change", "Volume", "Avg Vol (3 month)", "Market Cap", "PE Ratio (TTM)", "52 Week Range"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Symbol", type text}, {"Name", type text}, {"Price (Intraday)", type number}, {"Change", type number}, {"% Change", Percentage.Type}, {"Volume", type text}, {"Avg Vol (3 month)", type text}, {"Market Cap", type text}, {"PE Ratio (TTM)", type text}, {"52 Week Range", type text}})
in
    #"Changed Type"

Table

(url)=>
let
    Source = Web.BrowserContents(url),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10)"}, 
                                                       {"Column2", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9)"}, 
                                                       {"Column3", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8)"}, 
                                                       {"Column4", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8) + TD[colspan=""""]:not([rowspan]):nth-child(4):nth-last-child(7)"}, 
                                                       {"Column5", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8) + TD[colspan=""""]:not([rowspan]):nth-child(4):nth-last-child(7) + TD[colspan=""""]:not([rowspan]):nth-child(5):nth-last-child(6)"}, 
                                                       {"Column6", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8) + TD[colspan=""""]:not([rowspan]):nth-child(4):nth-last-child(7) + TD[colspan=""""]:not([rowspan]):nth-child(5):nth-last-child(6) + TD[colspan=""""]:not([rowspan]):nth-child(6):nth-last-child(5)"}, 
                                                       {"Column7", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8) + TD[colspan=""""]:not([rowspan]):nth-child(4):nth-last-child(7) + TD[colspan=""""]:not([rowspan]):nth-child(5):nth-last-child(6) + TD[colspan=""""]:not([rowspan]):nth-child(6):nth-last-child(5) + TD[colspan=""""]:not([rowspan]):nth-child(7):nth-last-child(4)"}, 
                                                       {"Column8", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8) + TD[colspan=""""]:not([rowspan]):nth-child(4):nth-last-child(7) + TD[colspan=""""]:not([rowspan]):nth-child(5):nth-last-child(6) + TD[colspan=""""]:not([rowspan]):nth-child(6):nth-last-child(5) + TD[colspan=""""]:not([rowspan]):nth-child(7):nth-last-child(4) + TD[colspan=""""]:not([rowspan]):nth-child(8):nth-last-child(3)"}, 
                                                       {"Column9", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8) + TD[colspan=""""]:not([rowspan]):nth-child(4):nth-last-child(7) + TD[colspan=""""]:not([rowspan]):nth-child(5):nth-last-child(6) + TD[colspan=""""]:not([rowspan]):nth-child(6):nth-last-child(5) + TD[colspan=""""]:not([rowspan]):nth-child(7):nth-last-child(4) + TD[colspan=""""]:not([rowspan]):nth-child(8):nth-last-child(3) + TD[colspan=""""]:not([rowspan]):nth-child(9):nth-last-child(2)"}, 
                                                       {"Column10", "TABLE.W\(100\%\) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(10):nth-last-child(1), TABLE.W\(100\%\) > * > TR > TD[colspan=""""]:not([rowspan]):nth-child(1):nth-last-child(10) + TD[colspan=""""]:not([rowspan]):nth-child(2):nth-last-child(9) + TD[colspan=""""]:not([rowspan]):nth-child(3):nth-last-child(8) + TD[colspan=""""]:not([rowspan]):nth-child(4):nth-last-child(7) + TD[colspan=""""]:not([rowspan]):nth-child(5):nth-last-child(6) + TD[colspan=""""]:not([rowspan]):nth-child(6):nth-last-child(5) + TD[colspan=""""]:not([rowspan]):nth-child(7):nth-last-child(4) + TD[colspan=""""]:not([rowspan]):nth-child(8):nth-last-child(3) + TD[colspan=""""]:not([rowspan]):nth-child(9):nth-last-child(2) + TD[colspan=""""]:not([rowspan]):nth-child(10):nth-last-child(1)"}},
                                                        [RowSelector="TABLE.W\(100\%\) > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true])
in
    #"Promoted Headers"

[plumek102]

Hi @lbendlin. Thank you very much for your quick solution. It works. Neverthelss I am wondering if there is any possibilitie to speed up download of it? 

[lbendlin]

Yes, there is. You need to find out how many requests you can run at the same time (surely not 142, but maybe 6 or 10) and then you cut your list into these pieces and run separate queries in parallel.