Getting all symbols from yahoo - Incremental function in power query

plumek102 — Wed, 13 Mar 2024 20:35:24 GMT

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.

Re: Getting all symbols from yahoo - Incremental function in power query

lbendlin — Wed, 13 Mar 2024 23:11:51 GMT

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"

Re: Getting all symbols from yahoo - Incremental function in power query

plumek102 — Thu, 14 Mar 2024 18:45:16 GMT

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?

Re: Getting all symbols from yahoo - Incremental function in power query

lbendlin — Thu, 14 Mar 2024 23:43:18 GMT

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.

topic Getting all symbols from yahoo - Incremental function in power query in Power Query

Getting all symbols from yahoo - Incremental function in power query

Re: Getting all symbols from yahoo - Incremental function in power query

Re: Getting all symbols from yahoo - Incremental function in power query

Re: Getting all symbols from yahoo - Incremental function in power query