Power Query - Extract from web

[Pavan.Sada.PS]

Hello Fellas,

Plz have a look at the attached jpeg file where i am willing to extract a table from the website "https://www.nseindia.com/live_market/dynaContent/live_analysis/top_gainers_losers.htm"
I may need only first 4 columns from the table. expected to see 40 records in a query output (20 from gainer & 20 from looser)

 

[Chihiro]

? This site provides only top 10 gainer/loser. If you need more, contact them for their API.

Without API, you can scrape Top10 gainers only for this site with PowerQuery. As you won't be able to navigate within site.

You may be able to scrape cookie first and pass that info via query and scrape json response. But that's quite a bit more involved.

See link for some details on the process.
http://www.excelandpowerbi.com/?p=58

With API, you can set up various query requests directly to server and obtain info.

 

[Chihiro]

Had bit of time so tested. Looks like you don't need cookie string as long as it's stored in your local file. However, if it expires, you may need to implement the process outlined in the link provided.

See attachment for how json data is queried and transformed.

 

[Pavan.Sada.PS]

Had bit of time so tested. Looks like you don't need cookie string as long as it's stored in your local file. However, if it expires, you may need to implement the process outlined in the link provided.

See attachment for how json data is queried and transformed.

Hello Chihiro,

This works for me.. Not sure on how to get securities greater than 20 field. tried looking for Json thing but couldnt get clue

 

[Chihiro]

Using browser of your choice, launch Developer tool or equivalent.

Navigate to site and click on security 20 field. Navigate to Network pane and go to XHR section.

Find the request sent and locate Request URL. That's what you need to get that info.

 

[Pavan.Sada.PS]

Using browser of your choice, launch Developer tool or equivalent.

Navigate to site and click on security 20 field. Navigate to Network pane and go to XHR section.

Find the request sent and locate Request URL. That's what you need to get that info.

View attachment 52481

Awsome Got it.... Thanks much for your help

 

[Pavan.Sada.PS]

Your inputs have helped me a lot. One last help, could you help me extract the data from the attached website. plz have a look at the image on from which table i need to extract the data.

"https://in.investing.com/indices/s-p-cnx-500-components"

 

[Chihiro]

It can't easily be scraped without piloting browser.

If you inspect source code, you will notice that all table fields are populated java script.

One way to do it is use legacy Query from Web tool. Go to Options -> Data and check From Web (Legacy).



Then you can use in built browser to query entire web page.

 

[Pavan.Sada.PS]

It can't easily be scraped without piloting browser.

If you inspect source code, you will notice that all table fields are populated java script.

One way to do it is use legacy Query from Web tool. Go to Options -> Data and check From Web (Legacy).

View attachment 52814

Then you can use in built browser to query entire web page.
View attachment 52815

Chihiro,

Could you help me with the file. i tried this but getting the bugs on the jscript error

 

[Chihiro]

So what kind of help do you need? Did you just clicking OK and continue with script?