0

I'm struggling to get financial data into google sheets. I read some answers here and tried this: =importhtml("https://www.google.com/finance?q="&B2&"&fstype=ii&ei=viASWOnfNsfEeqqUnMgG","table",3)

Yet it always shows this error:

Imported content does not have query with given index.

I went through the tables from 2-10 but nothing worked. It would be great if someone can help me with this. If you only know how to get data from yahoo finance or some other stock website then it would be nice if you'd also say that. I tried yahoo finance but there it always says:

URL could not be fetched

or something like that.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Mxritz Flr
  • 9
  • 3

3 Answers3

1

For google, there is a formula googlefinance. For yahoo, all datas are imported in the code source within a big json you can fetch by this way

  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
  var data = JSON.parse(jsonString)

you can then navigate through data to retrieve the informations you need.

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Where do you have to enter the URL from the website where you want to take data ? – Mxritz Flr May 31 '21 at 17:18
  • 1
    Here is an example : https://docs.google.com/spreadsheets/d/1sTA71PhpxI_QdGKXVAtb0Rc3cmvPLgzvXKXXTmiec7k/copy . The json is extremely complex but contains a lot of informations (>15.000) – Mike Steelson Jun 01 '21 at 00:00
0

See formula GOOGLEFINANCE in Google Sheets. It's not totally live (about 20 minutes delay) but for many purposes is enough. I use it mostly for currency exchange rates but there far more options:

https://support.google.com/docs/answer/3093281?hl=en

Yahoo Finance and most of live sites are protected against webscraping or use javascript (that makes parts generated by javascript impossible for import by formulasa like IMPORTHML, IMPORTXML, IMPORTDATA).

DisappointedByUnaccountableMod
  • 6,656
  • 4
  • 18
  • 22
Krzysztof Dołęgowski
  • 2,583
  • 1
  • 5
  • 21
  • But GOOGLEFINANCE is only for little information. It cant't show you revenue, cash-flow and so on can it? – Mxritz Flr May 31 '21 at 17:14
  • 1
    I agree it's limited. But most of services charge you for more detailed data. That's why they protect their websites against scraping. Yahoo offers you their API service. https://rapidapi.com/blog/how-to-use-the-yahoo-finance-api/ – Krzysztof Dołęgowski Jun 02 '21 at 06:22
0

Regarding cryptocurrency coins you can simply use

=IMPORTDATA("https://cryptoprices.cc/BTC/")

Google Finance only supports the very biggest coins (BTC, ETH), not much more.

Other services usually require fragile web page parsing which is difficult, error prone and not a long term solution.

From looking at some of your comments, you may be looking at something more complex and get a lot more data together. That will probably require access to paid services and some software programming to get it working together.

Google Sheets is not a scraping software, it can pull data from websites and do some limited parsing, but most websites protect against it and or may change over time. Trying to do what you want to do may be the best way to do it.

Pedro
  • 416
  • 1
  • 8
  • 24