-1

I am trying to create a google sheet showing various crypto prices for a few set times (but lets just use BTC-USD for the moment). The sheet would show

BTCUSD     Current Price,   Previous Close,  Close 5 days ago and Close 31 days ago

I have tried the following but running into the problems described which appear to be reserved for crypto.

There are various ways one can get the current price: =GOOGLEFINANCE("BTCUSD") will work - so we are ok for current price
=GOOGLEFINANCE("BTCUSD","change") will not work, however it will work for an equity =GOOGLEFINANCE("AAPL","change") will work

Similarly =index(IMPORTHTML(CONCATENATE("https://finance.yahoo.com/quote/","AAPL"),"table",1),1,2) will return from table 1 row 1, column 2 from the yahoo finance page for Apple (an equity) However =index(IMPORTHTML(CONCATENATE("https://finance.yahoo.com/quote/","BTC-USD"),"table",1),1,2) does not work even though the page and table layout appear to be the same

I also notice that =GOOGLEFINANCE("BTCUSD", "price", DATE(2022,1,1), DATE(2022,8,15), "DAILY") will return the price of bitcoin for the date range, However =GOOGLEFINANCE("BTCUSD", "price", DATE(a1), DATE(a2), "DAILY") will not work even if cell a1 and a2 have a copy and paste of the 2022,1,1 and 2022,8,15 in them.

I suspect the second question relates to the fact that the dates in the formula are not in quotes, however if you reference them from a cell excel may inadvertently put them into a quote causing a problem. This last problem makes it difficult to solve the problem from a different angle ie by referencing cells as the day changes and we refresh the sheet ie we cannot reference a cell which would always be 5 days ago or 31 days ago.

pppery
  • 3,731
  • 22
  • 33
  • 46
user2126062
  • 111
  • 11
  • Perhaps you should tag this with Google sheets rather than Excel and also change the title. – Ron Rosenfeld Aug 14 '22 at 16:56
  • If [this](https://stackoverflow.com/a/73366329/17926478) answered your question, please click the accept button on the left (check icon). By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. [How to accept answer](https://stackoverflow.com/help/accepted-answer) – Lorena Gomez Aug 16 '22 at 17:46

1 Answers1

0

Answer to your first question

With the first formula, =index(IMPORTHTML(CONCATENATE("https://finance.yahoo.com/quote/","AAPL"),"table",1),1,2) it worked for a moment and then stopped working. Then I tested =index(IMPORTHTML(CONCATENATE("https://finance.yahoo.com/quote/","BTC-USD"),"table",1),1,2) and did not work, I even tried =IMPORTHTML("https://finance.yahoo.com/quote/BTC-USD","table") to see if it was importing the table but you get the same error "Resource at url not found".

I did some research and it seems that Yahoo made some changes to their website and this affected some of their web-pages. It's suggested to use another website that is scrape-able by IMPORT functions. This is just an example of what is mentioned about Yahoo Finance and IMPORT functions, there are other communities that are also mentioning issues with doing web scraping to Yahoo Finance.

Answer to your second question

Issue with this formula =GOOGLEFINANCE("BTCUSD", "price", DATE(a1), DATE(a2), "DAILY"), according to documentation:

Inputs to DATE must be numbers - if a string or a reference to a cell containing a string is provided, the #VALUE! error will be returned.

The correct way would be: DATE(2022,1,1) and if you want to refer to a cell you will have to split 2022,1,1 in three different cells and make the reference this way DATE(A2,B2,C2).

Lorena Gomez
  • 1,946
  • 2
  • 4
  • 11