Home > Enterprise >  Querying helper table within Google Sheets
Querying helper table within Google Sheets

Time:05-10

I want to import central banks interest rates into google sheets. The table I'm using can be found here:

=importhtml("https://www.investing.com/central-banks","table",1)

enter image description here

For each items that I want from this table, I would have a function like (in this example, to fetch 1.00%):

=value(substitute(index(importhtml("https://www.investing.com/central-banks","table",1),2,3),",",""))

If I need this whole table, which consists of 12 rows (excluding header) x 4 columns, I would have 48 such formulas. It seems to me that:

  • this is messy, and
  • making 48 calls to the investing.com URL where one could probably suffice.

So I decided to try a different approach. In another tab of my google sheets, I call the whole table:

enter image description here

In my other tab, I thought to query this table (assume it is in a tab called rates) instead. But I don't know how. Is there a way, and if so, what is the correct way to query say:

  • the first name in the table (Federal Reserve (FED))?
  • the fourth interest rate in the table (-0.75%)?

Any help is greatly appreciated.

CodePudding user response:

To get 1%, try

=index(importhtml("https://www.investing.com/central-banks","table",1),2,3)

index

adapt parameters to fetch

  • the first name in the table (Federal Reserve (FED)) =index(importhtml("https://www.investing.com/central-banks","table",1),2,2)
  • or the fourth interest rate in the table (-0.75%) =index(importhtml("https://www.investing.com/central-banks","table",1),5,3)

To get all % per banks at once

=query(importhtml("https://www.investing.com/central-banks","table",1),"select Col2,Col3",1)

CodePudding user response:

The answer he gave you Mike Steelson is good, but if I were you, I would make it in such a way that I could change the parameters at any time

=query(importhtml("https://www.investing.com/central-banks","table",1);"select Col2 WHERE (Col3='"&$A1&"')",1)

where A1 = 1.00% or any number entered into that cell

  • Related