CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
sgdata
sgdata

Reputation: 2763

Re-use (through query statements) a data array from an import formula in Google Sheets

I am trying to retrieve a list of values of from distinct but identical schema tables. However, I'm trying to reduce the number of ImportHTML call-outs that sheets has to make for best performance. Some issues I'm running into:

Using "Scrape data from Wikipedia to a Google sheet" as an example:

In a new sheet where A1 = =ImportHtml("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 4)

  1. Is it possible to use the data without expanding the result?

    For instance when I have data in nearby columns:

    Example constrained ImportHTML

1.b) Is it possible to query that A1 result even when it is in a #REF! error?

  1. What is the best practice to perform a single import table and (without expansion) query that array data multiple times?

    Example query pointing at source only

I'm aware that if I allowed for expansion, I would be able to query like:

=query(A1:F100,"select F where A = 'India'")

Upvotes: 0

Views: 100

Answers (2)

sgdata
sgdata

Reputation: 2763

@nabais's answer had gotten the juices flowing and I was actually able to retrieve the table format as a single string and then use find functions to retrieve the data I needed.

Example:

=CONCATENATE(IMPORTHTML(CONCATENATE("https://finviz.com/quote.ashx?t=",Watchlist!$A4),"table",9))

Which produces a string like:

Index*DJIA S&P500*P/E*35.92*EPS (ttm)*3.27*Insider Own*0.07%*Shs Outstand*17.06B*Perf Week*-1.61%*Market Cap*1994.16B*Forward P/E*27.26*EPS next Y*4.31*Insider Trans*-6.88%*Shs Float*16.99B*Perf Month*1.37%*Income*57.41B*PEG*2.84*EPS next Q*1.39*Inst Own*61.70%*Short Float*0.47%*Perf Quarter*-6.77%*Sales*274.52B*P/S*7.26*EPS this Y*10.20%*Inst Trans*-*Short Ratio*0.52*Perf Half Y*48.19%*Book/sh*3.83*P/B*30.64*EPS next Y*8.99%*ROA*17.60%*Target Price*123.11*Perf Year*79.14%*Cash/sh*5.35*P/C*21.93*EPS next 5Y*12.64%*ROE*75.20%*52W Range*53.15 - 137.98*Perf YTD*59.84%*Dividend*0.82*P/FCF*33.64*EPS past 5Y*7.30%*ROI*31.70%*52W High*-14.96%*Beta*1.33*Dividend %*0.70%*Quick Ratio*1.30*Sales past 5Y*3.30%*Gross Margin*38.20%*52W Low*120.76%*ATR*3.15*Employees*147000*Current Ratio*1.40*Sales Q/Q*1.00%*Oper. Margin*24.10%*RSI (14)*52.96*Volatility*1.72% 2.66%*Optionable*Yes*Debt/Eq*1.73*EPS Q/Q*-3.00%*Profit Margin*20.90%*Rel Volume*0.47*Prev Close*118.64*Shortable*Yes*LT Debt/Eq*1.52*Earnings*Oct 29 AMC*Payout*24.10%*Avg Volume*154.64M*Price*117.34*Recom*2.10*SMA20*1.13%*SMA50*1.75%*SMA200*25.71%*Volume*72,188,812*Change*-1.10%*

And can then be parsed for a particular data value using something like:

=IFERROR(VALUE(substitute(LEFT(MID(MID(<stringcell>,FIND("Rel Volume",<stringcell>),21),FIND("*",MID(<stringcell>,FIND("Rel Volume",<stringcell>),21))+1,LEN(MID(<stringcell>,FIND("Rel Volume",<stringcell>),21))),FIND("*",MID(MID(<stringcell>,FIND("Rel Volume",<stringcell>),21),FIND("*",MID(<stringcell>,FIND("Rel Volume",<stringcell>),21))+1,LEN(MID(<stringcell>,FIND("Rel Volume",<stringcell>),21))))-1),"%",""))/100,"Not Found")

Upvotes: 0

nabais
nabais

Reputation: 2037

I tried to do some tests to understand how google sheets fetched the information.

What I have done was:

  • created a request bin to see when the requests were being made,
  • imported the HTML with as many different ways I could:
    • importhtml(urlInCell)
    • importhtml(urlInFormula)
    • query({importhtml(urlInCell), importhtml(urlInFormula)})

The result was that google sheets only made the request once and cached the imported value for all those requests, and I tried closing and reopening the sheet, refreshing and even hard-refreshing, and the request was still only made once. As seen below:

enter image description here

Then, I tried to query the results by calling the way you do above, and it didn't work as well.

The only solution for what you wanted exactly to make would be to query the data directly from the importhtml with:

=query(ImportHtml("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 4),"select Col6 where Col1='India'")

enter image description here

suggestion

My suggestion here would be to fetch the data into a sheet on its own and then query the entire sheet in another place:

  • Sheet Info - =ImportHtml("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 4)
  • Target Sheet - =query(Info!A:Z, "select F where A = 'India'")

tests

Here is the image with the tests I did and their results enter image description here

Hope it helps, if you need further information let me know in the comments

Upvotes: 2

Related Questions