• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scraping Website Table with Power Query

Use developer tool in the browser of your choise. And see what requests are sent to the web service.

In this site, each time you click on one of the buttons. It sends Post request to the service.

You will need to identify what request headers are required and also construct payload for the request.

Ex: Payload for first button.
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","close","currency","pricescale","minmov","fractional","minmove2","change","change_abs","Recommend.All","volume","Value.Traded","market_cap_basic","fundamental_currency_code","price_earnings_ttm","earnings_per_share_basic_ttm","number_of_employees","sector","market"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}

Payload for 2nd button.
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","change|1","change|5","change|15","change|60","change|240","change","change|1W","change|1M","Perf.3M","Perf.6M","Perf.YTD","Perf.Y","beta_1_year","Volatility.D"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
 
Thanks so much for your answer and time.

Sorry for being that noob.

I dont really know how to do. I can only use Power Query like; copy+paste link and Transform Data.

Where to use that codes? Can you suggest a video or something to learn it? Or can you give me an example with one button by attaching and excel file here?
 
Last edited:
Payload needs to be binary form. I don't have time today to go into details...

Base construct:
Code: 
Web.Contents(FullURL, [ Headers = [ Authorization = Signature ],Content=Text.ToBinary("")])

So payload will go inside Text.ToBinary(""). But you'll need to ensure to escape double quote etc.

I'll see if I have time tomorrow. But no guarantees.

Edit: You can also read posts I made in other forums.
Power Query: Reading a text or csv file that is publicly available on the Web? [SOLVED] (excelforum.com)
API Authorization Using HMAC-SHA1 : excel (reddit.com)
(2) Get around authentication at power query (yahoo fantasy) : excel (reddit.com)
 
I did open “View Page Source” and read everyhing in it, and same with developer tool.

I couldnt find anything useful.

I have no other chance instead of wait for your reply.

Thank you.
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    232.4 KB · Views: 10
Last edited:
Here's basic query.

Ex: For Performans tab
Code: 
let
    payload = "{""columns"":[""name"",""description"",""logoid"",""update_mode"",""type"",""typespecs"",""change|1"",""change|5"",""change|15"",""change|60"",""change|240"",""change"",""change|1W"",""change|1M"",""Perf.3M"",""Perf.6M"",""Perf.YTD"",""Perf.Y"",""beta_1_year"",""Volatility.D""],""filter"":[{""left"":""is_primary"",""operation"":""equal"",""right"":true},{""left"":""typespecs"",""operation"":""has"",""right"":""common""},{""left"":""type"",""operation"":""equal"",""right"":""stock""},{""left"":""name"",""operation"":""nempty""}],""filterOR"":[],""ignore_unknown_fields"":false,""options"":{""active_symbols_only"":true,""lang"":""tr""},""price_conversion"":{},""range"":[0,100],""sort"":{""sortBy"":""name"",""sortOrder"":""asc""},""symbols"":{""query"":{""types"":[]},""tickers"":[]},""markets"":[""turkey""]}",
    Source = Json.Document(Web.Contents("https://scanner.tradingview.com/turkey/scan", [Headers=[Accept="application/json", #"Content-Type"="text/plain;charset=UTF-8"],Content=Text.ToBinary(payload)])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"s", "d"}, {"Column1.s", "Column1.d"}),
    #"Expanded Column1.d" = Table.ExpandListColumn(#"Expanded Column1", "Column1.d")
in
    #"Expanded Column1.d"

You just need to replace payload for each tab. To find tab. Launch developer tool in your browser. Navigate to Fetch/XHR.
Then on the site, click on desired tab. You should see in "Name" column of the developer tool, scan.

That's the request sent to the web service. click on it. Click on Payload. And then view source.
78751

FYI - Page source is not the same as monitoring requests and other network activity related to page/site.
 
I found all payloads of buttons.
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","close","currency","pricescale","minmov","fractional","minmove2","change","change_abs","Recommend.All","volume","Value.Traded","market_cap_basic","fundamental_currency_code","price_earnings_ttm","earnings_per_share_basic_ttm","number_of_employees","sector","market"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","change|1","change|5","change|15","change|60","change|240","change","change|1W","change|1M","Perf.3M","Perf.6M","Perf.YTD","Perf.Y","beta_1_year","Volatility.D"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","close","currency","pricescale","minmov","fractional","minmove2","market_cap_basic","fundamental_currency_code","price_earnings_ttm","price_revenue_ttm","earnings_per_share_basic_ttm","last_annual_eps","enterprise_value_ebitda_ttm","enterprise_value_fq","total_shares_outstanding_fundamental"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","close","currency","pricescale","minmov","fractional","minmove2","dividend_yield_recent","dividends_paid","fundamental_currency_code","dps_common_stock_prim_issue_fy"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","gross_margin","operating_margin","pre_tax_margin","after_tax_margin"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","basic_eps_net_income","fundamental_currency_code","earnings_per_share_basic_ttm","earnings_per_share_diluted_ttm","ebitda","gross_profit_fq","gross_profit","total_revenue","last_annual_eps","last_annual_revenue","net_income"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","current_ratio","debt_to_equity","net_debt","fundamental_currency_code","quick_ratio","total_assets","total_debt","total_current_assets"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","Recommend.Other","ADX","ADX+DI","ADX-DI","ADX-DI[1]","ADX+DI[1]","AO","AO[1]","AO[2]","ATR","CCI20","CCI20[1]","MACD.macd","MACD.signal","Mom","Mom[1]","RSI","RSI[1]","Stoch.K","Stoch.K[1]","Stoch.D","Stoch.D[1]"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}
Code: 
{"columns":["name","description","logoid","update_mode","type","typespecs","Recommend.MA","close","currency","pricescale","minmov","fractional","minmove2","SMA20","SMA50","SMA200","BB.upper","BB.lower"],"filter":[{"left":"is_primary","operation":"equal","right":true},{"left":"typespecs","operation":"has","right":"common"},{"left":"type","operation":"equal","right":"stock"},{"left":"name","operation":"nempty"}],"filterOR":[],"ignore_unknown_fields":false,"options":{"active_symbols_only":true,"lang":"tr"},"price_conversion":{},"range":[0,100],"sort":{"sortBy":"name","sortOrder":"asc"},"symbols":{"query":{"types":[]},"tickers":[]},"markets":["turkey"]}

You just need to replace payload for each tab. To find tab.
Click to expand...

I did. But there was missing quotatıon marks in code. After completing the deficiencies, the result is below.

Maybe there is something wrong with that basic query which you gave me.

You are good to teach but maybe I am screwed up.

Also I have to say, I wrote same question to 5 different excel forum in last 1 week. Only you respond.

It doesn't matter if we succeed or not, I am really grateful to find you. Thank you so much man, God bless you.
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    66.7 KB · Views: 9
Last edited:
Forloveofgod
Moderator note:
Have You read this Forum's rules or any of those other four Forums rules about Cross-posting?

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Maybe there is something wrong with that basic query which you gave me.
Click to expand...

Nah. I only gave example query to pull data. Not complete code to transform etc. As I'm not sure exactly how you want the data to be structured.

Here's one example of transformation.
Code: 
let
    payload = "{""columns"":[""name"",""description"",""logoid"",""update_mode"",""type"",""typespecs"",""change|1"",""change|5"",""change|15"",""change|60"",""change|240"",""change"",""change|1W"",""change|1M"",""Perf.3M"",""Perf.6M"",""Perf.YTD"",""Perf.Y"",""beta_1_year"",""Volatility.D""],""filter"":[{""left"":""is_primary"",""operation"":""equal"",""right"":true},{""left"":""typespecs"",""operation"":""has"",""right"":""common""},{""left"":""type"",""operation"":""equal"",""right"":""stock""},{""left"":""name"",""operation"":""nempty""}],""filterOR"":[],""ignore_unknown_fields"":false,""options"":{""active_symbols_only"":true,""lang"":""tr""},""price_conversion"":{},""range"":[0,100],""sort"":{""sortBy"":""name"",""sortOrder"":""asc""},""symbols"":{""query"":{""types"":[]},""tickers"":[]},""markets"":[""turkey""]}",
    jsonColumns = List.Select(Record.Field(Json.Document(payload),"columns"), each _ <> "typespecs"),
    Source = Json.Document(Web.Contents("https://scanner.tradingview.com/turkey/scan", [Headers=[Accept="application/json", #"Content-Type"="text/plain;charset=UTF-8"],Content=Text.ToBinary(payload)])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"s", "d"}, {"Column1.s", "Column1.d"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each Table.Transpose(Table.FromList(List.Select([Column1.d], each Value.Is(_, type list) = false), Splitter.SplitByNothing(), null, null, ExtraValues.Error),jsonColumns)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.d"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"name", "description", "logoid", "update_mode", "type", "change|1", "change|5", "change|15", "change|60", "change|240", "change", "change|1W", "change|1M", "Perf.3M", "Perf.6M", "Perf.YTD", "Perf.Y", "beta_1_year", "Volatility.D"}, {"name", "description", "logoid", "update_mode", "type", "change|1", "change|5", "change|15", "change|60", "change|240", "change", "change|1W", "change|1M", "Perf.3M", "Perf.6M", "Perf.YTD", "Perf.Y", "beta_1_year", "Volatility.D"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"name", type text}, {"description", type text}, {"logoid", type text}, {"update_mode", type text}, {"type", type text}, {"change|1", type number}, {"change|5", type number}, {"change|15", type number}, {"change|60", type number}, {"change|240", type number}, {"change", type number}, {"change|1W", type number}, {"change|1M", type number}, {"Perf.3M", type number}, {"Perf.6M", type number}, {"Perf.YTD", type number}, {"Perf.Y", type number}, {"beta_1_year", type number}, {"Volatility.D", type number}})
in
    #"Changed Type"

jsonColumns is used to extract "columns" record from Json string and to take out "typespecs" as it is not needed here.

Other than that, it's pretty much standard transformation from list into table. Then to transpose the table.
 
You must log in or register to reply here.
Back
Top