Trying to gather history for multiple days
Closed this issue · 17 comments
=GECKOHIST("ethereum","USD","price", H5:H7,false)
H5:H7 is equal to 01-01-2021, 01-02-2021, 01-03-2021
I keep getting an error message when I try to run this formula. I'm trying to set it up where it will post 30 day historical but was using those three dates for example
Hello,
Unfortunately, you can't call multiple dates using the GECKOHIST formula,
Coingecko only allows one date at a time
For example:
=GECKOHIST("ethereum","USD","price","01-01-2021" ,false)
If you reference to a date, you need to make sure the date is in string format and not datevalue, otherwise Coingecko will not recognize it.
There is an example in the following template sheet, tab "Portfolio Example", right of the table:
https://docs.google.com/spreadsheets/d/1-QNPo7-gq3vLMoxrCWg_DwTA5dfD9b-Lv-tRVTqo1RE/edit?usp=sharing
I might create a real history function in the future from my own data on servers so that you can call multiple dates at the same time. We will see if I get time and ressources.
I've changed tge Geckohist function so that one can request arrays of data.
New code here:
https://raw.githubusercontent.com/Eloise1988/COINGECKO/master/CoinGeckoV2.gs
Ex:
=GECKOHIST("ethereum","usd","price",datevalue("12-31-2020"),datevalue("08-31-2020"))
depanding on the timezone of your sheet either datevalue('mm-dd-yyyy') or datevalue('dd-mm-yyyy')
I'm trying to get historical data based on one day
=GECKOHIST("BTC","usd","price","12-12-2020")
I'm getting this message:
"TypeError: Cannot read property 'toString' of undefined (line 825)."
@tommybivens14 Sorry I made a mistake in the formula I gave you in terms of date. Instead of
Ex:
=GECKOHIST("ethereum","usd","price",datevalue("12-31-2020"),datevalue("08-31-2020"))
It should be August 2021 not 2020, otherwise start date is greater than end date:
=GECKOHIST("ethereum","usd","price",datevalue("12-31-2020"),datevalue("08-31-2021"))
Also if you get #ERROR 429, just reload the cell until it gives you the values. 429 means that too many people are requesting data from Coingecko at the same time using Google Sheets. We are all using the same set of IP provided by Google, COINGECKO limits by IP address per second.
@JKZuk A couple of days ago I changed the GECKOHIST function so that you can require arrays of data instead of just one like shown on GIF below:
If you still wish to request the formula
=GECKOHIST("BTC","usd","price","12-12-2020")
I just rewrote the old formula into a new one:
=GECKOHISTBYDAY("BTC","usd","price","12-12-2020")
You need to download the new code here:
https://raw.githubusercontent.com/Eloise1988/COINGECKO/master/CoinGeckoV2.gs
=GECKOHISTBYDAY("BTC","usd","price","12-12-2020")
This works very well. Thank you.
There is another small issue. When I try to add the date from a cell it says "Loading..." and then disappears and no value is visible.
@JKZuk You need to make sure that the cell format is in text and not in date otherwise the "12-12-2020" will be taken as timestamp value 1607727600 which will not work. Hope that helps.
Thanks for adding this feature. My test shows it pulls hourly data. see attached. not sure if anything has changed.
Also noticed that the sheet has version 2.2.3 where as the latest here is 2.1.1.
@efurban
It's embedded like that in their api unfortunately.
Get historical market data include price, market cap, and 24h volume (granularity auto)
Minutely data will be used for duration within 1 day, Hourly data will be used for duration between 1 day and 90 days, Daily data will be used for duration above 90 days.
Got it! Changed the dates and all is good. thank you !