Short answer
AFAIK, regarding XPath queries to be used with IMPORTXML there isn't straightforward method as XPath 1.0 support looks that was not fully implemented and the web pages developers could follow the practices to set the structure of their webpages.
Explanation
While the use of tools like Chrome Developer Tools or browser extensions/add-ons could be helpful sometimes these tools doesn't return a XPath query that could be used by IMPORTXML due to differences on how XPath support was implemented by the developers of each tool, by the other hand, web pages could comply or not with the XML rules, so to find the XPath query to be used with IMPORTXML could be necessary to analyze the structure of the source web page and to do several tries.
XPath queries for the use case
The below XPath queries returns 5,208.00
1.
//div[@id="balinterimdiv"]//tr[contains(.,'Total Debt')]/td[2]
2.
(//tr[contains(.,'Total Debt')]/td[2])[1]
Explanation
The referred page includes two views for the Balance Sheet: Quarterly Data and Annual Data. Both of them looks to have the same structure as both includes a table cell (td tag) with the text Total Debt. Fortunately, each view are inside a div tag and each of them have their own id, so in order to get only one, the first step in the XPath query could be to select the right view, then the second step could be to select the right table row (tr tag) and the third step to select the right table cell (td tag).
Another approach is to use the construct (xpath_query)[position() = 1] (see the reference).
References
When URL: http(s)://www.moneycontrol.com/stocks/fno/marketstats/futures/gainers/homebody.php?opttopic=&optinst=allfut&sel_mth=1&sort_order=0
opened in client (browser) following server side rendering of dynamic data are requested by browser:
- send @ jquery-1.12.0.min.js:4
- ajax @ jquery-1.12.0.min.js:4
- updaterss @ jquery.webticker.js:39
- (anonymous) @ jquery.webticker.js:137
- many more...
this is why you can not use IMPORTHTML or IMPORTXML to fetch Dynamic data as in this webpage.
How to check if Error: Imported Content is Empty is because of dynamic data?
use a la carte rendering of web page in question like:
=IMPORTXML("http://www.moneycontrol.com/stocks/fno/marketstats/futures/gainers/homebody.php?opttopic=&optinst=allfut&sel_mth=1&sort_order=0", "/")
- "/" will force to fetch everything from URL, and there is nothing useful in this IMPORT XML/HTML for your need.
Using the Chrome browser's INSPECT function displays all the client requests.
Best Answer
You seem to have forgotten to mention what you would consider tidy, but it should be easier to handle your data (assumed to be starting in A1) with a formula such as:
in B1 and the same again in C1 with an extra
+1immediately before the,0- both copied down to suit.OFFSET
INT
ROW