Jump to content
webscrape

Help importXML google sheets

Recommended Posts

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

Share this post


Link to post
Share on other sites

=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 .....

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 :

<div id="block-views-fund-unit-pricing-block-1" class="block block-views clearfix">
<div class="content">
 
 
 
 
<div class="required-fields accordion-holders add-style">
<div class="field fund-unit-pricing-section accordion-section">
<li>
<div>
<h2 id="tmf" class="field-label active">The Montgomery Fund</h2>
<div class="fund-performance">FUND PERFORMANCE</div>
<div class="holder">
<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>
</div>
 
 
<div id="accordion-5" class="fund-unit-pricing-table accordion-section-content">
<table>
<thead>
<tr>
<th role="columnheader">DATE</th>
<th role="columnheader">BUY</th>
<th role="columnheader">REDEEM</th>
<th role="columnheader" class="last-child">MID</th>
</tr>
</thead>
<tbody>
<tr>
<td class="">10/09/18</td>
<td class="">1.3877</td>
<td class="">1.3808</td>
<td class="last-child">1.3842</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">07/09/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.3917</td>
<td class="" style="overflow: hidden; display: table-cell;">1.3847</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.3882</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">06/09/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.3928</td>
<td class="" style="overflow: hidden; display: table-cell;">1.3858</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.3893</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">05/09/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4067</td>
<td class="" style="overflow: hidden; display: table-cell;">1.3997</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.4032</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">04/09/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4124</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4053</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.4089</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">03/09/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4085</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4015</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.4050</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">31/08/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4105</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4035</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.4070</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">30/08/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4118</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4047</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.4083</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">29/08/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4104</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4034</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.4069</td>
</tr>
<tr class="collapsable">
<td class="" style="overflow: hidden; display: table-cell;">28/08/18</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4079</td>
<td class="" style="overflow: hidden; display: table-cell;">1.4009</td>
<td class="last-child" style="overflow: hidden; display: table-cell;">1.4044</td>
</tr>
</tbody>
</table>
<div class="info-buttons" style="overflow: hidden; display: block;">
<div class="buttons">
<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>
</div>
</div>
</li>
</div>
<div class="field fund-unit-pricing-section accordion-section">
<li>
<div>
<h2 id="map" class="field-label">Montgomery Alpha Plus Fund</h2>
<div class="fund-performance">FUND PERFORMANCE</div>
<div class="holder">
<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">RBA Cash Rate</div><div class="field-content">3.36%</div></div>
<div class="column"><div class="field-content">Out/Under Performance</div><div class="field-content">-13.31%</div></div>
</div>
</div>
 
 
<div id="accordion-121" class="fund-unit-pricing-table accordion-section-content">
<table>
<thead>
<tr>
<th role="columnheader">DATE</th>
<th role="columnheader">BUY</th>
<th role="columnheader">REDEEM</th>
<th role="columnheader" class="last-child">MID</th>
</tr>
</thead>
<tbody>
<tr>
<td class="">10/09/18</td>
<td class="">0.8408</td>
<td class="">0.8366</td>
<td class="last-child">0.8387</td>
</tr>
<tr class="collapsable">
<td class="">07/09/18</td>
<td class="">0.8390</td>
<td class="">0.8348</td>
<td class="last-child">0.8369</td>
</tr>
<tr class="collapsable">
<td class="">06/09/18</td>
<td class="">0.8380</td>
<td class="">0.8338</td>
<td class="last-child">0.8359</td>
</tr>
<tr class="collapsable">
<td class="">05/09/18</td>
<td class="">0.8399</td>
<td class="">0.8357</td>
<td class="last-child">0.8378</td>
</tr>
<tr class="collapsable">
<td class="">04/09/18</td>
<td class="">0.8406</td>
<td class="">0.8364</td>
<td class="last-child">0.8385</td>
</tr>
<tr class="collapsable">
<td class="">03/09/18</td>
<td class="">0.8450</td>
<td class="">0.8408</td>
<td class="last-child">0.8429</td>
</tr>
<tr class="collapsable">
<td class="">31/08/18</td>
<td class="">0.8394</td>
<td class="">0.8352</td>
<td class="last-child">0.8373</td>
</tr>
<tr class="collapsable">
<td class="">30/08/18</td>
<td class="">0.8305</td>
<td class="">0.8263</td>
<td class="last-child">0.8284</td>
</tr>
<tr class="collapsable">
<td class="">29/08/18</td>
<td class="">0.8215</td>
<td class="">0.8174</td>
<td class="last-child">0.8194</td>
</tr>
<tr class="collapsable">
<td class="">28/08/18</td>
<td class="">0.8313</td>
<td class="">0.8272</td>
<td class="last-child">0.8293</td>
</tr>
</tbody>
</table>
<div class="info-buttons">
<div class="buttons">
<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>
</div>
</div>
</li>
</div>
<div class="field fund-unit-pricing-section accordion-section">
<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="holder">
<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>
</div>
 
 
<div id="accordion-6" class="fund-unit-pricing-table accordion-section-content">
<table>
<thead>
<tr>
<th role="columnheader">DATE</th>
<th role="columnheader">BUY</th>
<th role="columnheader">REDEEM</th>
<th role="columnheader" class="last-child">MID</th>
</tr>
</thead>
<tbody>
<tr>
<td class="">06/09/18</td>
<td class="">1.1942</td>
<td class="">1.1883</td>
<td class="last-child">1.1913</td>
</tr>
<tr class="collapsable">
<td class="">05/09/18</td>
<td class="">1.1942</td>
<td class="">1.1883</td>
<td class="last-child">1.1913</td>
</tr>
<tr class="collapsable">
<td class="">04/09/18</td>
<td class="">1.1942</td>
<td class="">1.1883</td>
<td class="last-child">1.1913</td>
</tr>
<tr class="collapsable">
<td class="">03/09/18</td>
<td class="">1.1942</td>
<td class="">1.1883</td>
<td class="last-child">1.1913</td>
</tr>
<tr class="collapsable">
<td class="">31/08/18</td>
<td class="">1.1942</td>
<td class="">1.1883</td>
<td class="last-child">1.1913</td>
</tr>
<tr class="collapsable">
<td class="">30/08/18</td>
<td class="">1.2091</td>
<td class="">1.2030</td>
<td class="last-child">1.2060</td>
</tr>
<tr class="collapsable">
<td class="">29/08/18</td>
<td class="">1.2091</td>
<td class="">1.2030</td>
<td class="last-child">1.2060</td>
</tr>
<tr class="collapsable">
<td class="">28/08/18</td>
<td class="">1.2091</td>
<td class="">1.2030</td>
<td class="last-child">1.2060</td>
</tr>
<tr class="collapsable">
<td class="">27/08/18</td>
<td class="">1.2091</td>
<td class="">1.2030</td>
<td class="last-child">1.2060</td>
</tr>
<tr class="collapsable">
<td class="">24/08/18</td>
<td class="">1.2091</td>
<td class="">1.2030</td>
<td class="last-child">1.2060</td>
</tr>
</tbody>
</table>
<div class="info-buttons">
<div class="buttons">
<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>
</div>
</div>
</li>
</div>
<div class="field fund-unit-pricing-section accordion-section">
<li>
<div>
<h2 id="mgf" class="field-label"> Montgomery Global Fund</h2>
<div class="fund-performance">FUND PERFORMANCE</div>
<div class="holder">
<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>
</div>
 
 
<div id="accordion-73" class="fund-unit-pricing-table accordion-section-content">
<table>
<thead>
<tr>
<th role="columnheader">DATE</th>
<th role="columnheader">BUY</th>
<th role="columnheader">REDEEM</th>
<th role="columnheader" class="last-child">MID</th>
</tr>
</thead>
<tbody>
<tr>
<td class="">07/09/18</td>
<td class="">1.2639</td>
<td class="">1.2576</td>
<td class="last-child">1.2608</td>
</tr>
<tr class="collapsable">
<td class="">06/09/18</td>
<td class="">1.2560</td>
<td class="">1.2497</td>
<td class="last-child">1.2529</td>
</tr>
<tr class="collapsable">
<td class="">05/09/18</td>
<td class="">1.2690</td>
<td class="">1.2627</td>
<td class="last-child">1.2659</td>
</tr>
<tr class="collapsable">
<td class="">04/09/18</td>
<td class="">1.2867</td>
<td class="">1.2803</td>
<td class="last-child">1.2835</td>
</tr>
<tr class="collapsable">
<td class="">03/09/18</td>
<td class="">1.2854</td>
<td class="">1.2790</td>
<td class="last-child">1.2822</td>
</tr>
<tr class="collapsable">
<td class="">31/08/18</td>
<td class="">1.2857</td>
<td class="">1.2792</td>
<td class="last-child">1.2825</td>
</tr>
<tr class="collapsable">
<td class="">30/08/18</td>
<td class="">1.2784</td>
<td class="">1.2720</td>
<td class="last-child">1.2752</td>
</tr>
<tr class="collapsable">
<td class="">29/08/18</td>
<td class="">1.2791</td>
<td class="">1.2727</td>
<td class="last-child">1.2759</td>
</tr>
<tr class="collapsable">
<td class="">28/08/18</td>
<td class="">1.2672</td>
<td class="">1.2609</td>
<td class="last-child">1.2640</td>
</tr>
<tr class="collapsable">
<td class="">27/08/18</td>
<td class="">1.2664</td>
<td class="">1.2600</td>
<td class="last-child">1.2632</td>
</tr>
</tbody>
</table>
<div class="info-buttons">
<div class="buttons">
<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>
</div>
</div>
</li>
</div>
<div class="field fund-unit-pricing-section accordion-section">
<li>
<div>
<h2 id="mga" class="field-label">Montaka Global Access Fund</h2>
<div class="fund-performance">FUND PERFORMANCE</div>
<div class="holder">
<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>
</div>
 
 
<div id="accordion-85" class="fund-unit-pricing-table accordion-section-content">
<table>
<thead>
<tr>
<th role="columnheader">DATE</th>
<th role="columnheader">BUY</th>
<th role="columnheader">REDEEM</th>
<th role="columnheader" class="last-child">MID</th>
</tr>
</thead>
<tbody>
<tr>
<td class="">01/08/18</td>
<td class="">1.0943</td>
<td class="">1.0943</td>
<td class="last-child">1.0943</td>
</tr>
<tr class="collapsable">
<td class="">01/07/18</td>
<td class="">1.0926</td>
<td class="">1.0926</td>
<td class="last-child">1.0926</td>
</tr>
<tr class="collapsable">
<td class="">30/06/18</td>
<td class="">1.0926</td>
<td class="">1.0926</td>
<td class="last-child">1.0926</td>
</tr>
<tr class="collapsable">
<td class="">01/06/18</td>
<td class="">1.1748</td>
<td class="">1.1748</td>
<td class="last-child">1.1748</td>
</tr>
<tr class="collapsable">
<td class="">01/05/18</td>
<td class="">1.1172</td>
<td class="">1.1172</td>
<td class="last-child">1.1172</td>
</tr>
<tr class="collapsable">
<td class="">01/04/18</td>
<td class="">1.0925</td>
<td class="">1.0925</td>
<td class="last-child">1.0925</td>
</tr>
<tr class="collapsable">
<td class="">01/03/18</td>
<td class="">1.0841</td>
<td class="">1.0841</td>
<td class="last-child">1.0841</td>
</tr>
<tr class="collapsable">
<td class="">01/02/18</td>
<td class="">1.0551</td>
<td class="">1.0551</td>
<td class="last-child">1.0551</td>
</tr>
<tr class="collapsable">
<td class="">01/01/18</td>
<td class="">1.0629</td>
<td class="">1.0629</td>
<td class="last-child">1.0629</td>
</tr>
<tr class="collapsable">
<td class="">01/12/17</td>
<td class="">1.0896</td>
<td class="">1.0896</td>
<td class="last-child">1.0896</td>
</tr>
</tbody>
</table>
<div class="info-buttons">
<div class="buttons">
<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>
</div>
</div>
</li>
</div>
</div>
 
 
 
 
 
</div>
</div>

Share this post


Link to post
Share on other sites

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 by dsonesuk

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 .....

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×