Help importXML google sheets

webscrape · August 26, 2018

I would like to import a table from

https://www.montinvest.com/unit-prices#contact

on google sheets

I have tried :

$B$1 cell contains the above URL

=importxml($B$1,"//tr")

=importxml($B$1,"//td")

Any help would be appreciated

Funce · August 26, 2018

Have you had a look at this for your second argument in importxml?

https://www.w3schools.com/xml/xpath_syntax.asp

It might point you in the right direction

webscrape · August 28, 2018

Yes I have. I tried the wild cards and I can get most of the data but I don't know how I can just get the data in the table only.

webscrape · August 28, 2018

The second arguments have worked previously for other sites, so I don't understand why it didn't work for this particular Web site

Funce · August 29, 2018

What have you tried, and what was the result of them?

webscrape · September 9, 2018

=importxml($B$1,"//tr") No result

=importxml($B$1,"//td") No result

=importxml($B$1,".") ...gets everything not just the table that im after

=importxml($B$1,"*") ...gets everything not just the table that im after

=importxml($B$1,"node()") ...gets everything not just the table that im after

Then i started doing alot of stuff randomly based on what i found on inspection of the data:

NOne of these work:

=importxml($B$1,"/[@class='fund-unit-pricing-table accordion-section-content']")

=ImportXML(A1, "//Div[@class='fund-unit-pricing-table accordion-section-content']")

=ImportXML(A1, "//td[@class='fund-unit-pricing-table accordion-section-content']")

=ImportXML(A1, "//Div/tr/td[@class='fund-unit-pricing-table accordion-section-content']")

=ImportXML(A1, "//div[@id='accordion-5'/@class='fund-unit-pricing-table accordion-section-content']")

=ImportXML(A1, "//a[@class='fund-unit-pricing-table accordion-section-content']")

And a few more .....

dsonesuk · September 9, 2018

Perhaps you need to be more specific as there are multiple tables, tr, and td

For instance first table, first row "//table[0]/tr[0]", what you are targeting is a list of tr, not a specific tr.

Note: I don't know if above is the correct format to do it! that is similar to how you would target specific multiple elements in JavaScript.

webscrape · September 11, 2018

THIS is what i get from the inspection : I AM after the red number below. Can you please tell me what i should have ?

I tried :

=importxml($B$1,"//tr[3]/td[2]")

=importxml($B$1,"//tr[1]/td[2]")

=importxml($B$1,"//table[0]/tr[1]/td[2]")

BELOW IS INSPECTION OF TABLE :

<li>

<div>

<h2 id="tmf" class="field-label active">The Montgomery Fund</h2>

<div class="fund-performance">FUND PERFORMANCE</div>

<div class="column first"><div class="field-content">Returns since inception</div><div class="field-content">97.92%</div></div>

<div class="column"><div class="field-content">ASX 300 Accum. Index</div><div class="field-content">88.72%</div></div>

<div class="column"><div class="field-content">Out/Under Performance </div><div class="field-content">9.20%</div></div>

</div>

<table>

<thead>

<tr>

<th role="columnheader">REDEEM</th>

</tr>

</thead>

<tbody>

<tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tbody>

</table>

<a role="button" href="http://investwith.montinvest.com/tmf-history" class="btn-download">Download Full History</a>

<a role="button" class="btn-funds" href="/tmf">Learn More</a>

</div>

</li>

</div>

<li>

<div>

<h2 id="map" class="field-label">Montgomery Alpha Plus Fund</h2>

<div class="fund-performance">FUND PERFORMANCE</div>

<div class="column first"><div class="field-content">Returns since inception</div><div class="field-content">-9.95%</div></div>

<div class="column"><div class="field-content">Out/Under Performance</div><div class="field-content">-13.31%</div></div>

</div>

<table>

<thead>

<tr>

<th role="columnheader">REDEEM</th>

</tr>

</thead>

<tbody>

<tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tbody>

</table>

<a role="button" href="http://investwith.montinvest.com/mapf-history" class="btn-download">Download Full History</a>

<a role="button" class="btn-funds" href="/map">Learn More</a>

</div>

</li>

</div>

<li>

<div>

<h2 id="apply-to-invest-montgomery-private-fund" class="field-label">Montgomery [Private] Fund</h2>

<div class="fund-performance">FUND PERFORMANCE</div>

<div class="column first"><div class="field-content">Returns since inception</div><div class="field-content">108.96%</div></div>

<div class="column"><div class="field-content">ASX 200 Industrials Accum. Index</div><div class="field-content">120.82%</div></div>

<div class="column"><div class="field-content">Out/Under Performance </div><div class="field-content">-11.86%</div></div>

</div>

<table>

<thead>

<tr>

<th role="columnheader">REDEEM</th>

</tr>

</thead>

<tbody>

<tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tbody>

</table>

<a role="button" href="http://investwith.montinvest.com/mpf-history" class="btn-download">Download Full History</a>

<a role="button" class="btn-funds" href="/apply-to-invest-montgomery-private-fund">Learn More</a>

</div>

</li>

</div>

<li>

<div>

<h2 id="mgf" class="field-label"> Montgomery Global Fund</h2>

<div class="fund-performance">FUND PERFORMANCE</div>

<div class="column first"><div class="field-content">Returns since inception</div><div class="field-content">51.18%</div></div>

<div class="column"><div class="field-content">MSCI World Net Total Return Index A$</div><div class="field-content">40.32%</div></div>

<div class="column"><div class="field-content">Out/Under Performance </div><div class="field-content">10.86%</div></div>

</div>

<table>

<thead>

<tr>

<th role="columnheader">REDEEM</th>

</tr>

</thead>

<tbody>

<tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tbody>

</table>

<a role="button" href="http://investwith.montinvest.com/mgf-history" class="btn-download">Download Full History</a>

<a role="button" class="btn-funds" href="/mgf">Learn More</a>

</div>

</li>

</div>

<li>

<div>

<h2 id="mga" class="field-label">Montaka Global Access Fund</h2>

<div class="fund-performance">FUND PERFORMANCE</div>

<div class="column first"><div class="field-content">Returns since inception</div><div class="field-content">19.5%</div></div>

<div class="column"><div class="field-content">Montaka Global Fund (2)</div><div class="field-content">37.0%</div></div>

<div class="column"><div class="field-content">Average Net Market Exposure</div><div class="field-content">47%</div></div>

</div>

<table>

<thead>

<tr>

<th role="columnheader">REDEEM</th>

</tr>

</thead>

<tbody>

<tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tbody>

</table>

<a role="button" href="http://investwith.montinvest.com/mga-history" class="btn-download">Download Full History</a>

<a role="button" class="btn-funds" href="/mga">Learn More</a>

</div>

</li>

</div>

dsonesuk · September 11, 2018

The index always start at 0, td[2] will target the 3rd (0, 1, 2 index ref = 1st, 2nd, 3rd elements ) you want td[1]

The td index also restart with 0, when you move to another parent tr element, same with tr index when moving to a another table element

Edited September 11, 2018 by dsonesuk

dsonesuk · September 11, 2018

I should start with something simple and try to access first table //table[0] , see if it works and list all first table content, then move on to the parent tr element of td you wish to target, that should list all td elements within the tr, then list the single td you want.

dsonesuk · September 11, 2018

Just found YouTube video, they don't start the index of a item from 0 as in JavaScript but 1 unless the vid was wrong! anyway! start from single table then transverse table from to just single td item.

webscrape · September 12, 2018

thanks i tried combinations of table/ Tr / Td ...and it didnt work .

Someone mentioned that the table might be a "javascript generated table " in which case importxml function doesn't work .....

Help importXML google sheets

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in