0

I am trying to use importXML or importHTML function to retrieve a stock options chain, specifically using this function to get a table. So far, this is what I have:

=importhtml("https://bigcharts.marketwatch.com/quickchart/options.asp?symb=TSLA","table",3)

The problem I'm getting is that I can't retrieve "hidden" tables. For example, if you go on the website: https://bigcharts.marketwatch.com/quickchart/options.asp?symb=TSLA

If you scroll down on this page the hidden tables would only be revealed if you click on ,"show april 2022", "show may 2022", etc. I am trying to retrieve all of this information.

The end result is that I would like to create a table that looks like this: https://www.barchart.com/stocks/quotes/tsla/put-call-ratios

And a table that looks like this: https://www.barchart.com/stocks/quotes/TSLA/options?moneyness=10&view=stacked&expiration=2022-04-14-m

As a result, there are two things that I am trying to create, the above tables shown on barchart.

I have tried to use importHTML or importXML on barchart, but it looks like it's not allowed. If there is a way to directly retrieve the information from barchart, that would be a much better solution rather than having to import all of the data separately using a different website.

Please note, that I do have a beginner knowledge, so a step-by-step solution on what to do would be very helpful. Thank you

doglover123
  • 103
  • 1
  • 1
  • 6
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your goal from `The end result is that I would like to create a table that looks like this:`. Can I ask you about the detail of your goal? – Tanaike Mar 21 '22 at 00:52
  • Hi there @doglover123! Besides the question that Tanaike made, could you please also show where the *hidden data* is? Also please be aware that the applied function captures an static HTML webpage, so if the *hidden data* requires dynamic user interaction the function wouldn't be appropriate. – Jacques-Guzel Heron Mar 21 '22 at 09:07
  • @Tanaike if you click the url I sent, I just want to copy that entire page and be able to put it on a googlesheet – doglover123 Mar 22 '22 at 04:10
  • Thank you for replying. I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I would be grateful if you can forgive my poor English skill. – Tanaike Mar 22 '22 at 04:59
  • Thank you for your response @doglover123. I was able to copy the entire page into a Sheet just by using your formula `=importhtml("https://bigcharts.marketwatch.com/quickchart/options.asp?symb=TSLA","table",3)`. Do you face a different behaviour? Please edit your question and show examples of what you see and what you want to see instead. – Jacques-Guzel Heron Mar 22 '22 at 09:07
  • Hi @Jacques-GuzelHeron this is what I want to recreate on google sheet: https://www.barchart.com/stocks/quotes/tsla/put-call-ratios and https://www.barchart.com/stocks/quotes/TSLA/options?moneyness=10&view=stacked&expiration=2022-04-14-m those two exact same pages, replicated onto googlesheets. – doglover123 Mar 22 '22 at 23:51
  • I can recreate those webpages using your formula. Can you do the same? If not please share what you see instead. Otherwise please explain what is the difference between your end goal and `=importhtml("https://bigcharts.marketwatch.com/quickchart/options.asp?symb=TSLA","table",3)`. – Jacques-Guzel Heron Mar 23 '22 at 09:12
  • @Jacques-GuzelHeron if you go on the website https://bigcharts.marketwatch.com/quickchart/options.asp?symb=TSLA you must scroll all the way down and click "april 2022 options" to reveal a hidden table. I would like to be able to get that information. Hope that makes things more clear – doglover123 Mar 23 '22 at 13:23
  • 1
    Thank you for your clarification. As stated above, `IMPORTHTML` needs a static HTML webpage. If you can't feed that formula a static HTML, then it isn't appropriate for your project. – Jacques-Guzel Heron Mar 25 '22 at 08:08

0 Answers0