XPath query for Google importXML function

Question

I'm trying to write an xpath query to import some content from a webpage in google spreadsheets using importXML function. I need to capture % of Buy under COMMUNITY SENTIMENTS on the below webpage: https://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI

This % was showing 73% at time of posting this message, but may change later. (So I need to import 73% in my Google sheets).

Relevant HTML code of this page has below script:

</script>
        <ul class="buy_sellper">
            <li><span result="73" class="bullet_clr buy buy_results"></span>73% BUY</a></li>
            <li><span result="20" class="bullet_clr sell sell_results"></span>20% SELL</a></li>
            <li><span result="7" class="bullet_clr hold hold_results"></span>7% HOLD</a></li>
        </ul>
                    </div>
                </div>
                <div class="chart_fr ">

            <div class="txt_pernbd">73%</div>
            <div class="cht_mt25">of moneycontrol users recommend <span class=green_txt>buying</span> SBI</div>
                </div>
            <!-- buy, sell, hold starts -->
<div class="buy-sell-hold">
    <p>What's your call on SBI today?</p>
    <p>

Using Chrome, I used "inspect element" function and then "copy xpath" which gave me the following....

//*[@id="MshareElement"]

But this is not getting any results when I use in Google sheets with importxml function. I have zero knowledge of programming and I am trying to learn web scraping techniques using this function.

Please help.

Dear @Player0, Thank you very much for your response. I spent so many hours in understanding the syntax of importxml, and still failed to get the solution. And I never thought that answer would lie between multiple functions. I really appreciate your support. — RP-2020
Hi @player0, Can you please suggest how to import '500112' and 'SBIN' in Google spreadsheet from [link] (moneycontrol.com/india/stockpricequote/banks-public-sector/…). Also, please suggest how can I learn writing such xpath queries. — RP-2020

player0 player0 · Accepted Answer · 2020-05-06T21:35:19

try:

=REGEXEXTRACT(QUERY(IMPORTDATA(
 "https://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI"), 
 "where lower(Col1) contains 'txt_pernbd'"), ">(.+?)<")

=REGEXEXTRACT(QUERY(IMPORTDATA(
 "https://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI"), 
 "where lower(Col1) contains 'bullet_clr sell sell_results'"), "/span>(.+?)</a")

=REGEXEXTRACT(QUERY(IMPORTDATA(
 "https://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI"), 
 "where lower(Col1) contains 'investor views'"), ">(.+?)<")

XPath query for Google importXML function

1 Answers