Google Sheets – Extracting Data from Investing.com

google sheetsimporthtml

I am using google sheet function to extract a table from investing.com, the data I need is on daily basis, where there is a button in the page named "daily". However since the default setting of the page is "hourly", when I use "importhtml" in google sheet the data given is on an hourly basis. The url for both "daily" and "hourly" is exactly the same. How can I extract the "daily" data?

https://www.investing.com/equities/apple-computer-inc-technical

Best Answer

Actually this is an interesting workaround that works - for whatever reason - the mobile version of their site defaults to daily instead of hourly - the table indices are slightly differently ordered.

Original Function

But for example for this table, which on the regular page (the one you listed) would be :

=IMPORTHTML("https://www.investing.com/equities/apple-computer-inc-technical","table",5)

New Function

And for the same table with the mobile site you get daily:

=IMPORTHTML("https://m.investing.com/equities/apple-computer-inc-technical","table",0)

Related Solutions

Google Sheets – How to Auto-Update a Reference to Another Sheet

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.

My formula previously looked something like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

And now it looks like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Update:

This method no longer works since Google no longer permits now() inside importrange(). See comment from Hugh below.

Google-sheets – Daily spend vs projection spend pacing (%)

I would add another column to your projected sheet to calculate the running total for each day. You can paste the following into cell C2 and fill it down.

=If(Actual!B2<>ʺʺ,sum(Actual!B$2:B2)/sum(B2:B$2),ʺʺ)

Once that is done, you can put this formula anywhere you want to see the current rate.

=offset(C1,count(C:C),0)

You can hide column C if you don't want to see it.

Best Answer

Related Solutions

Google Sheets – How to Auto-Update a Reference to Another Sheet

Update:

Google-sheets – Daily spend vs projection spend pacing (%)

Related Topic