Hi, I have been able to create a stock portfolio watching your videos on Import html/xml functions. I am grateful to you. Can I make my portfolio auto refresh ? Could you please leave me a suggestion.
how can i import table which is visible only when certain tabs are pressed on site: for eg. on this url : in.tradingview.com/markets/stocks-india/sectorandindustry-sector/ i want to import table visible under the "performance" tab rather than "overview"
@Gohit Bhat I am also having same problem. Did you find any solution? In my case, data table is splited into 2 pages. merolagani.com/StockQuote.aspx I am able to get data from page 1 but not able to get data from page 2 from that url.
Interesting way to do web scrapes! Just grabbing the desired column solved your problem. Going to try that. Some questions on your methods. How did you get your "Inspect" web pages to come up? I am using Chrome and I see a totally different Inspection window. Yours looks much more helpful. Do you have a more basic video on how to inspect and look at the structure of a web page? My Inspect is just not too helpful. Also, I am trying to web scrape some financial info, and when I use IMPORTHTML or IMPORTXML, it is really slow and puts up a "Loading..." message for a while (some times Seconds or Minutes, sometimes it does not finish loading). This also happens when doing IMPORTRANGE calls from other spreadsheets (but I am not sure if these 2 problems are related or not). There is a definite performance problem with these IMPORT commands. Is this common? Could it be that the IMPORTHTML command is trying to download some tables that are very big? My performance issue even occurs even downloading a 3 row-2 column table from the web. Any ideas or thoughts that I could try? Thanks.
Great video my friend. At the end of the video you had mentioned you can do this with a product grid. Is there a video on this? Or any help on that would be amazing
Thanks for the help. How can you now get the url from the items in the list? lets say I want to have a list of all the communities and the link to the pages about those communities?
Are we able to edit the table once its imported to google sheet? For example I would only want one column from the table. Also will it keep updating live everytime the table is updated throughout the day?
05:57 - WTF????? Why this is work with word "list"??? Where in the page code is this word????? I totaly not understeand why is this work. In page code is "ul" not "list". 09:08 - Hey this "//table/tr" is impossible to work! Correct is "//table//tr"
5:57 google sheets is programmed to accept "list" so any random person can use it. 9:08 /tr does work because the root node and the current selected node are the same.
Good tutorial.!! I have a question.. I would like to know how import data to google sheet in cells that are already filled by other data. ?? I'm trying to automate my stocks imported financial data (income statement, balance sheet, cash flow statement) but I'm only able to import one by one because google sheet cells are already filled by the first stock data I imported.!
I've tried this on one of our tools that is not publicly available (meaning you have to log in). And Its giving me an error. I dont know if there is another step so it would automatically extract the data. | Idont know if this would help but table class in "inspect" is "widefat". Hope you can accommodate my inquiry
Do all websites allow this? On inspecting the website I want to get info from I can see the table I want, but when inputting the formal into Google sheets it can't be found? Wondering if some websites block this functionality?
No, the page must be loaded by the server, not by javascript for this to work. As javascript loaded websites are slowly taking over the web, this will progressively work on less & less sites.
Hi - I'm trying to import a table from a website however after inspecting the code, it looks like the table is populated with angular content (ng_content), and therefore the importXML function is not working as the table I'm trying to import from (I'm assuming) looks empty to the computer. Is there a way I can import the angular content data?
Hi, you've probably got an answer for this by now but just in case you haven't - I experimented with using '//div' instead of '//table' and it worked :) Drilling down further into the div I used '//div/p[1]' for the first line of data in the div and then found I could use '//div/p[1]/a[1]' to get the first piece of data in the first link in the first line. I hope you find this useful.
I used importHTML with "Table",8 and it works, but I want to learn how to use the IMPORTXML in the same way - so do you do IMPORTXML and use the path as "//table[8]" ? I doesn't work
Thanks a lot for this video, unfortunaly, I search the way to recover data from my gmail... I receive email on html format with table, and I dont manage to use this fonction with this because I have not an URL to give at IMPORTHTML... How can I do it ??
When I use importHTML it frequently won't update. If I go in and change the table from 1 to 3 and back again, it finally loads. What is the best way to function to load?
one more thing , my web contain agent data .i checked the source the name of agents are in //a/href . in tried this in function as //a/@href but its showing wrong data. any help woould be appreciated.
Excuse me, I have a question, When I apply =importxml(".....", "//table") All the data compare in the only cell that I selected, how is it possible? How can I solve this problem? Thank you
Hi there. I watched the video and tried it myself but unfortunately it doesn't work. I think I'm making a mistake or the link I have is wrong. I received a link from my dealer where I can get the XML file. These are then only displayed as text in the browser. I would like to import them into Excel or Sheets for analysis. But unfortunately it doesn't work. Maybe you could help me? Best regards
Im trying to import a list from a website with checkmarks When I copy it copes into 3 columns: Column 1 - number Column 2 - blank (because of checkmark) Column 3 - name of the item. How can I import only number into column 1 and name of item into column 2?
Learn Google Spreadsheets okay thanks! Random Question: If I create a script project file in one spreadsheet, how can I import that project file into another spreadsheets?
I tried to grab data from NSE India website and finds not working with the function =importhtml(“www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm”,”table”,1). Can you suggest possible corrections..
HI great video... but I have a problem with this formula =IMPORTXML("en.wikipedia.org/wiki/Community_areas_in_Chicago";"//table/tr")... my google sheet works with semicolon instead of comma... So what is the problem? Can you help me? The error that I notice is "the imported subject is empty"... Thanks
do =IMPORTHTML("en.wikipedia.org/wiki/Community_areas_in_Chicago";"table";1) instead of =IMPORTHTML("en.wikipedia.org/wiki/Community_areas_in_Chicago","table",1)
I'd like to extract just the Forward Dividend from this site =IMPORTHTML("finance.yahoo.com/quote/XOM?p=XOM&.tsrc=fin-srch","table",2) In this example, the answer is "3.48" How can I find the path to extract just the dollar amount instead of the whole table?
I have few questions. 1. How to count the words in the page 2. I use more than 8 import function it's take more time to load. Kindly give few tips to load faster. 3. I use this method for more than 10 url's In my single page I have more than one h1. So use this method it's come to next row. How I get everything in one cell.
Hi, Thank for your explanation! It works! GREAT ☺😀 But i need more help. What do I have to do when in a web page there is table with a 'information details' button, which actually is an hyperlink to another page? Does it exist any formula or solution that can help me? May I write you in private, please? Thanks for your precious support! Micol
So, you can click on the 'information details' button through your web browser to open the page, then copy the url, and use it on formula, but you also need inspect that the content of the page is html or XML, if it XML then use 'importxml' formula. I don't know exactly but this may help you.
How do i import specific price from a website to google sheets (Ex: "www.amazon.in/gp/product/B06W55K9N6/ref=ox_sc_act_title_4?ie=UTF8&psc=1&smid=A14CZOWI0VEHLG" price tag is 5899 and i want it to display only the price in spread sheet)
How do you work with DIV ID / CLASS on a page with so many sub elements? For example - if you go to this website (BLOCK LOCATION) it will show a full USA Results on left as well as on a map. www.heatstoprefractorymortar.com/dealers/ I want to see if I can pull that list which is in div[@id='wpsl-stores'] If I use that - =importxml(A1,"//div[@id='wpsl-stores']") the result is NULL (Blank)
This info is great. I need to get some baskeball team stats imported for some reason the 7th table on this website "www.basketball-reference.com/leagues/NBA_2020.html#opponent-stats-per_game::none" does not come up. Can anyone help me correct this formula "=IMPORTHTML("www.basketball-reference.com/leagues/NBA_2020.html#opponent-stats-per_game::none","table",7)"? Thanks for the content
Great video, thanks.:) CAn i import the contents from UA-cam: "ua-cam.com/channels/8p19gUXJYTsUPEpusHgteQ.htmlvideos?view=0&sort=da&flow=grid" to sheets? I tried importhtml with 'list' element, but entire list is not coming up. Please help, Thanks.
@@ExcelGoogleSheets Yeah, I understood. Thanks very much for replying. I've copied the contents of your UA-cam Channel (Cmd+A) and arranged to get the list. This is the sheet with list of your channel videos: docs.google.com/spreadsheets/d/16uw9viUNSVLX4MWP_JOW9Dum3rsUollwDh16cZAFReQ/edit?usp=sharing
I had been trying to figure out how to do a similar thing to getting the 'second column' data, and this gave me the exact answer I was looking for!
Thanks ever so much for this. It encourages me to play with the functions, which, for me, is the best way of learning stuff like this!!
Thank you for the information, you helped with a web scraping assessment I needed for a job role. Merci beaucoup!
Hi, I have been able to create a stock portfolio watching your videos on Import html/xml functions. I am grateful to you. Can I make my portfolio auto refresh ? Could you please leave me a suggestion.
hi
I will likely download my sheet from Drive to work on it in Excel. This was an excellent tutorial.
how can i import table which is visible only when certain tabs are pressed on site:
for eg. on this url : in.tradingview.com/markets/stocks-india/sectorandindustry-sector/
i want to import table visible under the "performance" tab rather than "overview"
@Gohit Bhat I am also having same problem. Did you find any solution? In my case, data table is splited into 2 pages. merolagani.com/StockQuote.aspx I am able to get data from page 1 but not able to get data from page 2 from that url.
@@binodmaharjan9981 couldn't find solution. it runs javascript to fetch data on web.
Interesting way to do web scrapes! Just grabbing the desired column solved your problem. Going to try that.
Some questions on your methods. How did you get your "Inspect" web pages to come up? I am using Chrome and I see a totally different Inspection window. Yours looks much more helpful. Do you have a more basic video on how to inspect and look at the structure of a web page? My Inspect is just not too helpful.
Also, I am trying to web scrape some financial info, and when I use IMPORTHTML or IMPORTXML, it is really slow and puts up a "Loading..." message for a while (some times Seconds or Minutes, sometimes it does not finish loading). This also happens when doing IMPORTRANGE calls from other spreadsheets (but I am not sure if these 2 problems are related or not).
There is a definite performance problem with these IMPORT commands. Is this common? Could it be that the IMPORTHTML command is trying to download some tables that are very big? My performance issue even occurs even downloading a 3 row-2 column table from the web. Any ideas or thoughts that I could try? Thanks.
How to import data from sites with login and pass??? need help on this
Do tell me also
will this function make an autmation for the sheet?
Great video my friend. At the end of the video you had mentioned you can do this with a product grid. Is there a video on this? Or any help on that would be amazing
Thanks for the help. How can you now get the url from the items in the list? lets say I want to have a list of all the communities and the link to the pages about those communities?
This should do it
=IMPORTXML("en.wikipedia.org/wiki/Community_areas_in_Chicago","//table/tr/td[3]/ul/li/a/@href")
With this kind of import, does it update automatically if the table was updated on the website? if not is there a way to do that?
I´m wondering the same thing.
@mikell Hey! Thanks for the info; I'm not really good at making scripts. Could I reach out to you or find some way to get help? Thanks again.
how about divs? is it possible?
Are we able to edit the table once its imported to google sheet? For example I would only want one column from the table. Also will it keep updating live everytime the table is updated throughout the day?
05:57 - WTF????? Why this is work with word "list"???
Where in the page code is this word?????
I totaly not understeand why is this work.
In page code is "ul" not "list".
09:08 - Hey this "//table/tr" is impossible to work!
Correct is "//table//tr"
5:57 google sheets is programmed to accept "list" so any random person can use it.
9:08 /tr does work because the root node and the current selected node are the same.
What browser are you using? Thanks
Google Spreadsheet is so powerful! Thank you. :-)
Is it possible to use the importdata function to bring an account's contact data?
Good tutorial.!! I have a question.. I would like to know how import data to google sheet in cells that are already filled by other data. ??
I'm trying to automate my stocks imported financial data (income statement, balance sheet, cash flow statement) but I'm only able to import one by one because google sheet cells are already filled by the first stock data I imported.!
How Can I RESOLVE This Issue.??
Thanks
sir plz make video on how i extract live data continue from sites in google sheet plz sir make video on it i m waiting for this concept,
I've tried this on one of our tools that is not publicly available (meaning you have to log in). And Its giving me an error. I dont know if there is another step so it would automatically extract the data. | Idont know if this would help but table class in "inspect" is "widefat". Hope you can accommodate my inquiry
You can't use it for sites not publicly available.
@@ExcelGoogleSheets Thanks for the fast response. :)
Do all websites allow this? On inspecting the website I want to get info from I can see the table I want, but when inputting the formal into Google sheets it can't be found? Wondering if some websites block this functionality?
No, the page must be loaded by the server, not by javascript for this to work. As javascript loaded websites are slowly taking over the web, this will progressively work on less & less sites.
if data on website changins its changing in real-time in sheet?
Apparently so, but only when you open the sheet.
Apply sheet refresh rate
Wow your the best
Hi - I'm trying to import a table from a website however after inspecting the code, it looks like the table is populated with angular content (ng_content), and therefore the importXML function is not working as the table I'm trying to import from (I'm assuming) looks empty to the computer. Is there a way I can import the angular content data?
Would Table[1] bring in only the first Table's text?
Yes.
What if the Inspect - just has as class?
Hi, you've probably got an answer for this by now but just in case you haven't -
I experimented with using '//div' instead of '//table' and it worked :) Drilling down further into the div I used '//div/p[1]' for the first line of data in the div and then found I could use '//div/p[1]/a[1]' to get the first piece of data in the first link in the first line.
I hope you find this useful.
@@LanceJarvis1959 I actually didn't! I believe I did okay around with that ... I'll try again. If I fall I sent you the URL
Thank You!
@@RobertLongM Not wishing you a failure or anything, but please do. :)
I used importHTML with "Table",8 and it works, but I want to learn how to use the IMPORTXML in the same way - so do you do IMPORTXML and use the path as "//table[8]" ?
I doesn't work
I want get data from local market price list and make graph
how would i do this for lets say getting the dividend yield for a stock from yahoo finance page
What do you do if the table has a unique name instead of a number?
How to change the url inside the fonction from a list of keywords
Thanku , Pls can u tell me how to reverse .
I want to import from Google spread sheet one row at a time and display in HTML page
Thanks a lot for this video, unfortunaly, I search the way to recover data from my gmail... I receive email on html format with table, and I dont manage to use this fonction with this because I have not an URL to give at IMPORTHTML... How can I do it ??
When I use importHTML it frequently won't update. If I go in and change the table from 1 to 3 and back again, it finally loads. What is the best way to function to load?
one more thing , my web contain agent data .i checked the source the name of agents are in //a/href . in tried this in function as //a/@href but its showing wrong data. any help woould be appreciated.
Thanks a lot brother..... May God bless you much
What is best method to import data from JavaScript rendered pages
Check out on my other channel ua-cam.com/play/PLRmEk9smitaXljDN2CjwZ96o4Aj87iuC5.html
@@ExcelGoogleSheets Thankyou
Is it dynamic? Will values change automatically along with the page?
yes.
Excuse me, I have a question,
When I apply =importxml(".....", "//table")
All the data compare in the only cell that I selected, how is it possible? How can I solve this problem? Thank you
Thank you very much - For people in Europe - if it doesn't work then use ";" instead of "," as seperator :-)
Hi there. I watched the video and tried it myself but unfortunately it doesn't work. I think I'm making a mistake or the link I have is wrong. I received a link from my dealer where I can get the XML file. These are then only displayed as text in the browser. I would like to import them into Excel or Sheets for analysis. But unfortunately it doesn't work. Maybe you could help me? Best regards
how to extract data which is depend on selection on site
Superb, my friend. Thank you.
thanks my friend,
Greetings from Chile
Hi tried this & succeed but importXML only pulling 1st page my web contains approx 100 .please advise how to pull all pages with on functions
How to import a graph data??
Hello from Argentina, i need UPDATE CELL OR PAGE every 5 minutes, who is apps script i would be use for this?? thanks for your attention
Im trying to import a list from a website with checkmarks When I copy it copes into 3 columns:
Column 1 - number
Column 2 - blank (because of checkmark)
Column 3 - name of the item.
How can I import only number into column 1 and name of item into column 2?
=QUERY(IMPORTFORMULA(link,args),"SELECT Col1, Col3",0)
I’m very curious, what keyboard and mouse are you using? It’s always so satisfying to hear them click 😂
"resource at url contents exceeded maximum size" how to cover up this problem?
How to import price from Newegg?
Wow - Great work
Odd that HTML Tables / Divs can get parsed with XML import? I always assumed XPath is for XML code
It is, but they both have similar structure so you can parse HTML with XML parsing tools.
Can you import tables from Google Docs?
I want to update a table in a Google Doc and the data automatically be updated in a Google Sheet
You'll need to write a script for that.
Learn Google Spreadsheets okay thanks!
Random Question: If I create a script project file in one spreadsheet, how can I import that project file into another spreadsheets?
I tried to grab data from NSE India website and finds not working with the function =importhtml(“www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm”,”table”,1). Can you suggest possible corrections..
That was very useful, thank you for the help!
"ERROR" when I copy your formula in my own google sheets. Can you help me please ?
An updated formula in the sample spreadsheet would be helpful
easy and great...
HI great video... but I have a problem with this formula =IMPORTXML("en.wikipedia.org/wiki/Community_areas_in_Chicago";"//table/tr")... my google sheet works with semicolon instead of comma... So what is the problem? Can you help me? The error that I notice is "the imported subject is empty"... Thanks
Hi, I think its only work on Wiki, I try other sites but it does not work.
Any way to use other sites or directory sites?
Thanks
It will work on sites where HTML is fully loaded on initial page load. Any page that is built by adding content with JavaScript won't work.
How does one import data behind a credentialed barrier?
No easy way.
I am tried but unsuccessful. The web page is not comming like yours.
Very nice learning video. Sir, How to import .xls file from weblink directly to google sheets?
I don't think you can do it directly. You'll need to use some sort of service in the middle.
Hi sir,,, but I can't import data from Yahoo finance to Google sheet
To get dynamically generated data use this ua-cam.com/play/PLRmEk9smitaXljDN2CjwZ96o4Aj87iuC5.html
what if the website is a ".php"
i tried lot of times every time i got errors help me
=importxml("xxx";"//table//tr//td[4]") --> use double /
do =IMPORTHTML("en.wikipedia.org/wiki/Community_areas_in_Chicago";"table";1) instead of =IMPORTHTML("en.wikipedia.org/wiki/Community_areas_in_Chicago","table",1)
@@Mikarevival One thing to take in count, in some countries as mine,, the "," should be replace with ";", other case doesn't work
I'd like to extract just the Forward Dividend from this site =IMPORTHTML("finance.yahoo.com/quote/XOM?p=XOM&.tsrc=fin-srch","table",2) In this example, the answer is "3.48" How can I find the path to extract just the dollar amount instead of the whole table?
Wowwww!!!!!, Love ir
I managed to import my data from Wikipedia but I can't delete or change text.
Copy/paste values only.
I have few questions.
1. How to count the words in the page
2. I use more than 8 import function it's take more time to load. Kindly give few tips to load faster.
3. I use this method for more than 10 url's In my single page I have more than one h1. So use this method it's come to next row. How I get everything in one cell.
Hi,
Thank for your explanation! It works! GREAT ☺😀
But i need more help. What do I have to do when in a web page there is table with a 'information details' button, which actually is an hyperlink to another page?
Does it exist any formula or solution that can help me?
May I write you in private, please?
Thanks for your precious support!
Micol
So, you can click on the 'information details' button through your web browser to open the page, then copy the url, and use it on formula, but you also need inspect that the content of the page is html or XML, if it XML then use 'importxml' formula. I don't know exactly but this may help you.
How to importXML from Truecaller on Google shit
Thank you!
Superb, thanks
How do i import specific price from a website to google sheets (Ex: "www.amazon.in/gp/product/B06W55K9N6/ref=ox_sc_act_title_4?ie=UTF8&psc=1&smid=A14CZOWI0VEHLG"
price tag is 5899
and i want it to display only the price in spread sheet)
How do you work with DIV ID / CLASS on a page with so many sub elements? For example - if you go to this website (BLOCK LOCATION) it will show a full USA Results on left as well as on a map.
www.heatstoprefractorymortar.com/dealers/
I want to see if I can pull that list which is in div[@id='wpsl-stores']
If I use that - =importxml(A1,"//div[@id='wpsl-stores']")
the result is NULL (Blank)
Yeah Still Dont Work....
Just tried this. Keep in mind you have to use brackets now, so it's like this:
=IMPORTHTML("en.wikipedia.org/wiki/Beat_It"; "table"; 1)
This info is great. I need to get some baskeball team stats imported for some reason the 7th table on this website "www.basketball-reference.com/leagues/NBA_2020.html#opponent-stats-per_game::none" does not come up. Can anyone help me correct this formula "=IMPORTHTML("www.basketball-reference.com/leagues/NBA_2020.html#opponent-stats-per_game::none","table",7)"? Thanks for the content
how can i reduce refresh time from 1 hr to 15 min?
Man you would try more difficult site.. like ecommerce
You may want to watch this one instead ua-cam.com/video/pwZ44kAeiOo/v-deo.html
🙏🏻
Doesn't work:-)
Great video, thanks.:)
CAn i import the contents from UA-cam: "ua-cam.com/channels/8p19gUXJYTsUPEpusHgteQ.htmlvideos?view=0&sort=da&flow=grid" to sheets? I tried importhtml with 'list' element, but entire list is not coming up.
Please help, Thanks.
UA-cam now loads a lot of its HTML using JavaScript, so these functions are not likely to work very well.
@@ExcelGoogleSheets Yeah, I understood. Thanks very much for replying. I've copied the contents of your UA-cam Channel (Cmd+A) and arranged to get the list.
This is the sheet with list of your channel videos:
docs.google.com/spreadsheets/d/16uw9viUNSVLX4MWP_JOW9Dum3rsUollwDh16cZAFReQ/edit?usp=sharing