perfect brilliant. couple questions; 1. a couple days have passed the data has changed, how are the prices updated? on a quick [F5] (up to 8 times per 24 hours?) 2. is there a way to make the data enter the sheet virtically rather than horizontally? / or is the best way to get around this to link the data onto another sheet? (And hide the messy one?)
Also my Guy if you do respond to this comment please: when i update the running, all the prices move all over the place, bitcoin replacing etheriums price for example. what is happening?
@@automatictruth6488 Hey! -Data is updated whenever you hit the "Run" button, so yes up to 8x in 24 hours -I always have my raw data sheet from API Connector than pipe it over to another sheet (and yes hide the messy one if you want ;), see next point: -Yeh, that does happen cause by default it sorts by market_cap, which constantly changes. You can fix this by appending &order=id_desc on the end of your API URL. This is also why the 2-sheet trick is so critical, cause you can use VLOOKUP() to get around this in cases where you can't sort the returned data. Let me know if all that helps :)
Awesome video! This is really going to help me track everything. Is there a way to have the information run vertically, rather than horizontally? Cheers!
Sure! You'd still have to port the data in horizontally, but you could flip it into a new sheet using the =TRANSPOSE(Infoon3!A1:D2) function for example :)
Hi, thanks for the tutorial. I try to create a macro for updating the crypto prices to make it faster, but I keep getting an error saying: ''ReferenceError: sidebarListRequests is not defined'' Any idea how to fix it or how to make a working macro?
@@SpirosZochios Hey Spiros! It looks like our developer mentioned 2 weeks ago that it's not possible to use a Macro on a Sheets add-on, search 'macro' on this page: mixedanalytics.com/knowledge-base/api-connector-scheduling/ Have you gotten it to work with other add-ons? Now I'm curious, maybe we can try to fix it to allow for that.. Also for the last question, we don't support the mobile version at the moment.
thanks for for vid when attempting to update prices, I noticed that I need to delete/clear previously populated cells, otherwise I get an error message when I press "run". Is there a way around this?
Hey Parker! Interesting, I don't get that bug on my end. I only get that if I'm using the =ARRAYFORMULA() and then trying to pull in on top of that or if I did happen to have some data in that already. If you can't resolve it, you can post some more detailed feedback on our Reddit here (much easier to debug if we can see screenshots, Loom, etc.): www.reddit.com/r/api_connector/
Very good but I have one problem with it. When refreshing the data on my API connector it changes the cell order where the coin and relative prices are and all links have to manually be amended again. Why is that? How can I keep the order fixed?
Hi Ricardo! Yes, this does happen sometimes with certain APIs (they may reorder based on the current price, ascending, descending, etc.) To get around this, you can make a new sheet and use VLOOKUP to fix your desired cryptos in the order you would like. Here's an article to get you started: support.google.com/docs/answer/3093318?hl=en Let me know if that works for you!
Hi Lazar! The easiest way to refresh your data is via the pro-version "scheduling" feature outlined here: mixedanalytics.com/knowledge-base/api-connector-scheduling/ However, if you are not ready to pay for our service (or you want faster but more unstable refreshes), you can hack your way to a 2-minute refresh with cell-based refreshing that we outlined here: mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/#num5 Let me know if that helps! :)
Thanks. Unfortunately the order of the data keeps changing with reloads so you can't directly reference it in a portfolio to calculate profit or loss. I only collected current prices so the names and prices were in rows, a row of names, and row of prices on a separate sheet called APIs. Therefore I had to use HLOOKUP which looks like this example for Bitcoin =HLOOKUP("bitcoin » usd",APIs!A1:R2,2,false). Google is buggy when it comes to copy/paste of the function, so much so that I'm thinking of going back to Excel.
Yep, that's exactly the logic I use to get around it as well, except that I use VLOOKUP with the currencies I am interested in following (I'm assuming you are using the /markets endpoint). Another option you have is to check out the "order" query parameter. By default it's set to market cap descending, but you could change it to id_asc or id_desc to lock the index. If you have any other questions feel free to check out our r/api_connector for more community-provided solutions :)
How might you get historical data? Like how with stocks you can use PRICE to get the price on a certain date at close. Thanks for the video, very helpful.
Hey John, If you want to do that, my guess would be to try this endpoint: www.coingecko.com/api/documentations/v3#/coins/get_coins__id__history, just pass in the id as a query parameter along with the date, and I assume it will return the closing price. Let me know if that helps!
Hmm, good question. I don't think so but you could try pulling data from the endpoint /coins/{id}/market_chart and specifing the days=1, granularity should be by the hour or minute in this case.
Awesome video- Followed your instructions and it works. I have two questions. Q1 how do i get the info from the 3rd sheet (info on 3) so it can show in the second sheet next to where I am putting my coin name? Q2 I then want to input my coin quantity that I hold and have it calculate the total for each coin. Is there a video which answers both these questions?
Hey Jamie! Q1: if you want to do this dynamically, it may be a bit tricky since the data comes labelled like this: dogecoin » twd [you can't do a VLOOKUP directly]. My suggestion would be to first strip the excess information from that cell with this function: =LEFT(A1,FIND(" ",A1)-1) [this grabs the first word, which in our case would be the crypto symbol], then you could perform VLOOKUP to match the symbol/data in InfoOn3 with those in your ids list. Q2: Currently we don't have a dashboard that allows your own custom input, but that's a fantastic idea! I'll put that on the list of ideas :) For now, you can check this one out we did [simple tracker, doesn't have input for your holdings] but I'm sure you could easily modify it for your use case. Check it out here: mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/#num5 Let me know if that helps, thanks! Doug
I keep getting the following status: Completed with errors - Server responded with an error (404) Any idea what could be causing this? I'm following the steps in the video. I tried the Radiohead/itunes quick start API to check and THAT worked, can't get any output from coingecko though.
Hey Matthew, A 404 is pretty generic and usually indicates to me there is a typo somewhere. If you are comfortable with it, you can post the URL you are trying to access here and I can take a look.
is it possible to somehow sort them? Whenever i update the prices, the token get mixed in their row. Means i cannot reference to this cell as in this second its the price of btc and next its eth.
You can use Sheets functions like VLOOKUP to check for coin names rather than pointing to a fixed position in the sheet. If you check this article, there's a template already set up for this: mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/
Say for example, you wanted to build some kind of crypto portfolio in Google Sheets for example, or you could even build a simple trading bot right within Sheets! Really the possibilities are endless, it's all about being able to access the data.
Hey I would love to know how to pull the data into rows (vertically) instead of columns (horizontally) how do you change that format in output options?
Hey Amethyst! On the free account you will have to manually re-run. Otherwise we offer scheduling with the paid version :) Check here for pricing dets: mixedanalytics.com/api-connector-pricing/
I don't think there is a specific endpoint you can call for that, but you could just run the request once, then run again in a separate sheet on a daily/weekly basis to check for new additions in coingecko.
Hi HS, I think to accomplish this, start by listing all the cryptos you have (so you only get prices for the stuff you have). Then you would have your raw data from CoinGecko running to one sheet, followed by QUERY() to another sheet. Or maybe you can wire up your date purchased+ volume to this one: www.coingecko.com/api/documentations/v3#/coins/get_coins__id__history You would only need to run it once to get the data into Sheets (if you don't have it yet), followed by comparing to the current prices: "www.coingecko.com/api/documentations/v3#/coins/get_coins__id_" So I imagine roughly 1 inputs sheet (your coins), 2 import sheets for data (above end points), followed by a 3rd sheet that compares what you have in a dashboard. Others that may help: www.coingecko.com/api/documentations/v3#/coins/get_coins__id__market_chart Hope that helps! Let us know you if you have any other questions!
Isn't there a way to just put a formula into the cell to get live price? I want to track like 20 tokens. I don't need 6000 cryptos. I don't want to be a computer programmer...
Is there a way to do it without paying for API connector? I have around 80 crypto and I need it to update the price (actually the % change for 24 hours) once a day for each 80 of them. I guess I'd need to pay for that since it is more then 8 calls a day, but there's got to be the way to do it free without the connector.
Hmm, I think that you can't easily. the endpoint /coins/{id}/history will give you a snapshot at a specific date but not the min max values of all time.
Thank you so much for the tutorial! Do you know where I can find out, how to get the prices get API into two columns instead of into two rows? I am trying to sort the data alphabetically, and it seems it would be much easier that way. I cannot figure out how to sort rows alphabetically, only columns!
Also, everytime the API refreshes, it changes what cells the data appears in. so if I use these cells for reference, it jumbles everything after it refreshes the API.
If you wanna get the prices by column, I think you could use the =TRANSPOSE() function or a pivot table. As for the API jumping around, try to use &order=id_asc on the end of your URL, otherwise you will need to use =VLOOKUP() to pipe your data over to another sheet. Let me know if that helps! :)
Hello, nice tutorial the most simple and comprehesive! i have a question when I run the command to get the prices every time the order of coins is changing. Do you have a solution about this?
Hi Antonis, you can use Sheets functions like VLOOKUP to check for coin names rather than pointing to a fixed position in the sheet. If you check this article, there's a template already set up for this: mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/
Not by default. You would need to manually run the request or you can get scheduling with our pro version, you can read more here: mixedanalytics.com/knowledge-base/api-connector-scheduling/
Great tutorial! Thanks for sharing knowledge with our community. Assume I have a long list of "ids" with 30 different cryptocurrencies. How can I refer to an id-list in the API URL path? In your tutorial, you use three different coins which you have to type in "&bitcoin,dogecoin,rabbitcoin" in the API URL path. Is there a shortcut for a longer list of "ids" (coin) that avoids typing in all ids separately? Thanks! Keep up your fantastic work.
Hey y'all! So yes you can use the +++sheet!A1+++ syntax unique to our tool to reference cells instead. You can read up on it here: mixedanalytics.com/knowledge-base/api-connector-create-api-request-based-on-cell/ Let me know if that helps! Thanks :)
Hi Avel, So every time you press the "Run" button, API Connector will update the data from CoinGecko. If you want to automate this, you can check out scheduling, one of our pro features: mixedanalytics.com/knowledge-base/api-connector-scheduling/
Thanks for the information! But how do you only retrieve like the price of BTC or dogecoin? So that the only thing that you get is the price without any other information whatsoever?
If you use the /coins/{id} endpoint, you can specify which coins you would like returned. Read more here: www.coingecko.com/api/documentations/v3#/coins/get_coins__id_ Let me know if that helps! :)
@@mixedanalytics Yes, so I let Bitcoin and Ethereum return, but I also get a cel above them with "bitcoin >> euro" and "ethereum >> euro which I both don't want when I let the API run, also I can't find a way to get the information placed underneath eachother in cells that I've selected, idk if thats even possible hahah. What I did is just add another page for just the prices and then copied them into my crypto portfolio which was then automatically updated when I ran the API. But please let me know if u do have got answers on my questionss
@@siebevermijs3756 You nailed it! The best practice is to have a "raw-data" sheet that pulls in the data from API Connector (sorry, can't disable headers 😝, the "bitcoin >> euro" and "ethereum >> euro" cells you are referring to in this case) and then port that over to another sheet using either VLOOKUP or QUERY. Currently, we don't support selection-based output. API Connector just dumps everything to a single sheet. That would be a cool feature though, thanks for the suggestion! Let me know if that helps answer your questions :)
@@mixedanalytics Yes it definitely does! thankyou, I've just started spread sheet since this week and I really enjoy the coding stuff. I'll keep trying to improve myself! +1 sub!
Hey Juan! Make sure you have the right ID, and try to use this endpoint: www.coingecko.com/api/documentations/v3#/coins/get_coins__id Let me know if that helps!
I'm not sure why you even make the IDs sheet,then, you can skip that step,cause just the query & comma separated ids works. I want to include other data too, so I just keep adding ampersands & the code,right? I see they have exchanges, finance platforms & finance products, I wonder if that includes defi platforms & interest rates,I don't see that in the code on each of those categories,I guess I gotta just do a query & find out,unless anyone knows for sure & other free API's from other sites that would have this data.
That's right, you don't NEED the ids sheet, but I find it's good practice as it prevents you from confusing them (as a reference sheet if you see what i mean). There are TONS of free APIs out for crypto. I have more videos in the works about them, or you can check a few of the other ones I've already posted on this channel. Check out some of these other ones: medium.com/coinmonks/best-crypto-apis-for-developers-5efe3a597a9f Try this one for DeFi: docs.defipulse.com/
@@mixedanalytics Cool,will do,yea,I wanna have one that will track the various defi interest rates & keep track of what I'm making versus what I may be paying back on Crypto loans, a P&L spreadsheet basically & it can monitor my investments & business.
Hello, Yep it is free! If you think API Connector is behaving strangely you can post a Loom/screenshot over in our Reddit to get some more advanced technical help here: www.reddit.com/r/api_connector/
Hi Pierre, Sure, why not try it? API Connector can handle a bunch of APIs, read more here: mixedanalytics.com/knowledge-base/frequently-asked-questions/#num7 Hope that helps!
Hi, great videos from you to understand how it works, but maybe there is a chance to get video how to create API to get a price for each and every coin on KuCoin exchange please.
How about the security issues this may cause? Think about the bank/ crypto accounts you are accessing through the browser and leaving a footprint. You might get vulnerable with this running. Just saying
not usable really. When you pull refresh to get newest coin prices, it gets prices in a random sequence. So you have made yourself a nice looking table with your totals, you have synced cells with prices, everything is nice. Next day you want to refresh prices and you get a new sequence of coin prices and your table looks like trash and you have to sync everything from zero.
Hello! I'd suggest in that case to either use the sheet you pull data into as a raw sheet, then pipe it over to another sheet using VLOOKUP(), or if you are calling /coins/markets endpoint, please try adding the `&order=id_desc` query parameter to the end of your request
When you install this add-on, you automatically enter a 14-day free trial that activates all premium features. After 14 days, you will be switched to the free plan How do you automate it since it has it in the 14day trial?
Just wanted to say thank you, I understand the API doc much better now.
You're very welcome :)
Taiwanese here and thank you for making a video!
Our pleasure!
Thanks for the awesome vid, great work. Definitely the easiest way to locate data for the most obscure coins.
Glad you enjoyed!
i cant believe i struggled though this so much...
but your walkthrough is really good thank you
Glad I could help!
hello what is the difference between this method and the abscript method and also the import functions?
is data synchronization done in real time?
Awesome video and great explanations! Thank you so much!
You're very welcome!
hello what is the difference between this module and appscript?
many thanks for simple introduction!
Glad it was helpful!
What would be the "formula" be to get all of the crypto names and prices in one go? Is this information updated in Google Sheets as prices change
perfect brilliant.
couple questions;
1. a couple days have passed the data has changed, how are the prices updated? on a quick [F5] (up to 8 times per 24 hours?)
2. is there a way to make the data enter the sheet virtically rather than horizontally? / or is the best way to get around this to link the data onto another sheet? (And hide the messy one?)
Also my Guy if you do respond to this comment please:
when i update the running, all the prices move all over the place, bitcoin replacing etheriums price for example. what is happening?
@@automatictruth6488 Hey!
-Data is updated whenever you hit the "Run" button, so yes up to 8x in 24 hours
-I always have my raw data sheet from API Connector than pipe it over to another sheet (and yes hide the messy one if you want ;), see next point:
-Yeh, that does happen cause by default it sorts by market_cap, which constantly changes. You can fix this by appending &order=id_desc on the end of your API URL. This is also why the 2-sheet trick is so critical, cause you can use VLOOKUP() to get around this in cases where you can't sort the returned data.
Let me know if all that helps :)
Thank you my friend! Great job!
Cheers! :)
how do we get API data for "watchlist" within Coingecko?
For example Bitcoin: "On 1,2Million watchlist"?
Amazing video! So helpful
So glad!
Awesome video! This is really going to help me track everything. Is there a way to have the information run vertically, rather than horizontally? Cheers!
Sure! You'd still have to port the data in horizontally, but you could flip it into a new sheet using the =TRANSPOSE(Infoon3!A1:D2) function for example :)
Hi, thanks for the tutorial.
I try to create a macro for updating the crypto prices to make it faster, but I keep getting an error saying: ''ReferenceError: sidebarListRequests is not defined''
Any idea how to fix it or how to make a working macro?
Also, is there a way to update the prices from the mobile app 'Sheets'?
@@SpirosZochios Hey Spiros!
It looks like our developer mentioned 2 weeks ago that it's not possible to use a Macro on a Sheets add-on, search 'macro' on this page: mixedanalytics.com/knowledge-base/api-connector-scheduling/
Have you gotten it to work with other add-ons? Now I'm curious, maybe we can try to fix it to allow for that..
Also for the last question, we don't support the mobile version at the moment.
Great videos. Really helpful and much appreciated. Keep up the awesome work!
Glad you like them! Thanks for the support Kuda :)
Thanks for this! Is there anyway to have the data auto refresh without manually running the API function?
Yes! We offer scheduling in the pro version of the add-on: mixedanalytics.com/knowledge-base/api-connector-scheduling/
thanks for for vid
when attempting to update prices, I noticed that I need to delete/clear previously populated cells, otherwise I get an error message when I press "run". Is there a way around this?
Hey Parker!
Interesting, I don't get that bug on my end. I only get that if I'm using the =ARRAYFORMULA() and then trying to pull in on top of that or if I did happen to have some data in that already. If you can't resolve it, you can post some more detailed feedback on our Reddit here (much easier to debug if we can see screenshots, Loom, etc.): www.reddit.com/r/api_connector/
Great instructional video. Thank.
Glad it was helpful!
Very good but I have one problem with it. When refreshing the data on my API connector it changes the cell order where the coin and relative prices are and all links have to manually be amended again. Why is that? How can I keep the order fixed?
Hi Ricardo!
Yes, this does happen sometimes with certain APIs (they may reorder based on the current price, ascending, descending, etc.) To get around this, you can make a new sheet and use VLOOKUP to fix your desired cryptos in the order you would like. Here's an article to get you started:
support.google.com/docs/answer/3093318?hl=en
Let me know if that works for you!
@@mixedanalytics thanks for the tip. I went through it. Finally found a way to fix the problem 👍
so useful, cheers for the help man!
Glad it helped!
Can I get 1min, 5min,15min and 1 hour price value with API Connector from the CoinGecko or some crypto exchange?
Hi Lazar! The easiest way to refresh your data is via the pro-version "scheduling" feature outlined here: mixedanalytics.com/knowledge-base/api-connector-scheduling/
However, if you are not ready to pay for our service (or you want faster but more unstable refreshes), you can hack your way to a 2-minute refresh with cell-based refreshing that we outlined here: mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/#num5
Let me know if that helps! :)
Thanks. Unfortunately the order of the data keeps changing with reloads so you can't directly reference it in a portfolio to calculate profit or loss. I only collected current prices so the names and prices were in rows, a row of names, and row of prices on a separate sheet called APIs. Therefore I had to use HLOOKUP which looks like this example for Bitcoin =HLOOKUP("bitcoin » usd",APIs!A1:R2,2,false). Google is buggy when it comes to copy/paste of the function, so much so that I'm thinking of going back to Excel.
Yep, that's exactly the logic I use to get around it as well, except that I use VLOOKUP with the currencies I am interested in following (I'm assuming you are using the /markets endpoint). Another option you have is to check out the "order" query parameter. By default it's set to market cap descending, but you could change it to id_asc or id_desc to lock the index.
If you have any other questions feel free to check out our r/api_connector for more community-provided solutions :)
@@mixedanalytics Thanks! I was using simple/price which didn't have the asc/desc choices.
Very Much appreciated thank you That's great work ;) !!!
You're welcome!
How might you get historical data? Like how with stocks you can use PRICE to get the price on a certain date at close. Thanks for the video, very helpful.
Hey John,
If you want to do that, my guess would be to try this endpoint: www.coingecko.com/api/documentations/v3#/coins/get_coins__id__history, just pass in the id as a query parameter along with the date, and I assume it will return the closing price. Let me know if that helps!
Video was awesome and it left me wondering if there's any way to import those simplified plot graphs they have on their main page? Thanks a lot!
Hmm, good question. I don't think so but you could try pulling data from the endpoint /coins/{id}/market_chart and specifing the days=1, granularity should be by the hour or minute in this case.
Thanks for a great video. How do you replace a space when retrieving a data for a specific coin like "Tomb Shares" in the request?
You're welcome!
You mean the space in "Tomb Shares"? Not sure I understand your question.
@@mixedanalytics I figured it out. Had to write “tomb-shares”
Awesome video- Followed your instructions and it works. I have two questions. Q1 how do i get the info from the 3rd sheet (info on 3) so it can show in the second sheet next to where I am putting my coin name? Q2 I then want to input my coin quantity that I hold and have it calculate the total for each coin. Is there a video which answers both these questions?
Hey Jamie!
Q1: if you want to do this dynamically, it may be a bit tricky since the data comes labelled like this: dogecoin » twd [you can't do a VLOOKUP directly]. My suggestion would be to first strip the excess information from that cell with this function: =LEFT(A1,FIND(" ",A1)-1) [this grabs the first word, which in our case would be the crypto symbol], then you could perform VLOOKUP to match the symbol/data in InfoOn3 with those in your ids list.
Q2: Currently we don't have a dashboard that allows your own custom input, but that's a fantastic idea! I'll put that on the list of ideas :) For now, you can check this one out we did [simple tracker, doesn't have input for your holdings] but I'm sure you could easily modify it for your use case. Check it out here: mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/#num5
Let me know if that helps, thanks!
Doug
I keep getting the following status:
Completed with errors
- Server responded with an error (404)
Any idea what could be causing this? I'm following the steps in the video. I tried the Radiohead/itunes quick start API to check and THAT worked, can't get any output from coingecko though.
Hey Matthew,
A 404 is pretty generic and usually indicates to me there is a typo somewhere. If you are comfortable with it, you can post the URL you are trying to access here and I can take a look.
This is AWESOME - thank you!
Glad you liked it!
so this API connector thingy has started to allow only 8 refreshes per day. Is there anyway to override this without having to get a membership?
Nope, sorry not possible. What are you trying to do? Do you have to pull more than 8/day?
is it possible to somehow sort them? Whenever i update the prices, the token get mixed in their row. Means i cannot reference to this cell as in this second its the price of btc and next its eth.
You can use Sheets functions like VLOOKUP to check for coin names rather than pointing to a fixed position in the sheet. If you check this article, there's a template already set up for this: mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/
Thanks for the video by the way..
You're welcome
What's a use case for this to be on a spreadsheet, when you can see the info right on the site though
Say for example, you wanted to build some kind of crypto portfolio in Google Sheets for example, or you could even build a simple trading bot right within Sheets! Really the possibilities are endless, it's all about being able to access the data.
Why i dont see any other buttons like *Start* , only Help ?
Hey! You can post a screen shot over on our Reddit, maybe our support can take a look: www.reddit.com/r/api_connector/
Hey I would love to know how to pull the data into rows (vertically) instead of columns (horizontally) how do you change that format in output options?
Actually, for this use case you could just use the =TRANSPOSE() function in Sheets and just have it spit out to a new sheet.
Is coin gecko still available?? I cannot find it in search on Mac app store.
Hmm, I'm not sure why you are looking in the App store... try the website coingecko.com
Awesome! Thank you so much!
Thank you Bruno :)
How to transfer my portfolio data, preferably to another free portfolio tracker?
I don't think you can access your portfolio from CoinGecko via API, from what I can tell, since all the endpoints are open.
Thanks for the video. Are the current coin values supposed to be updated real-time in my spreadsheet as they change in coingecko too?
Hi Angel!
They will not update automatically, you will need to manually re-run the sheet every time you want to refresh.
@@mixedanalytics How do you do that on google sheets?
@@luismariademourasimoesmend5347 Update the prices? Just press the "run" button in API Connector and it will refresh the request.
Does it auto update or do I need to manually reinsert the data every so often?
Hey Amethyst!
On the free account you will have to manually re-run. Otherwise we offer scheduling with the paid version :) Check here for pricing dets:
mixedanalytics.com/api-connector-pricing/
to bad nothing google sheets want money for an api now. is there an alternative?
Sorry you mean for our service or something else?
How can i get list of latest coin that imported to coingecko ??
I don't think there is a specific endpoint you can call for that, but you could just run the request once, then run again in a separate sheet on a daily/weekly basis to check for new additions in coingecko.
Hi, thx for an amazing explanation! Is it possible for you to make an entire profile tracker that updates PNL.
Hi HS,
I think to accomplish this, start by listing all the cryptos you have (so you only get prices for the stuff you have). Then you would have your raw data from CoinGecko running to one sheet, followed by QUERY() to another sheet.
Or maybe you can wire up your date purchased+ volume to this one: www.coingecko.com/api/documentations/v3#/coins/get_coins__id__history
You would only need to run it once to get the data into Sheets (if you don't have it yet), followed by comparing to the current prices:
"www.coingecko.com/api/documentations/v3#/coins/get_coins__id_"
So I imagine roughly 1 inputs sheet (your coins), 2 import sheets for data (above end points), followed by a 3rd sheet that compares what you have in a dashboard.
Others that may help:
www.coingecko.com/api/documentations/v3#/coins/get_coins__id__market_chart
Hope that helps! Let us know you if you have any other questions!
@@mixedanalytics Yes, that would be great, I am a big fan! To see daily and weekly gains would be awesome. What do you think about Crypto Sheets?
Isn't there a way to just put a formula into the cell to get live price? I want to track like 20 tokens. I don't need 6000 cryptos. I don't want to be a computer programmer...
Not possible with our tool. I guess there are other tools available for this but I don't know any off the top of my head. Good luck!
Is there a way to do it without paying for API connector? I have around 80 crypto and I need it to update the price (actually the % change for 24 hours) once a day for each 80 of them. I guess I'd need to pay for that since it is more then 8 calls a day, but there's got to be the way to do it free without the connector.
There probably is, you can check out App Script to get started. Good luck!
Can i get historical max and min values of a crypto token from coingecko API?
Hmm, I think that you can't easily. the endpoint /coins/{id}/history will give you a snapshot at a specific date but not the min max values of all time.
@@mixedanalytics Hmm, That's what I thought. Ty for the answer!
Thank you so much for the tutorial! Do you know where I can find out, how to get the prices get API into two columns instead of into two rows? I am trying to sort the data alphabetically, and it seems it would be much easier that way. I cannot figure out how to sort rows alphabetically, only columns!
Also, everytime the API refreshes, it changes what cells the data appears in. so if I use these cells for reference, it jumbles everything after it refreshes the API.
If you wanna get the prices by column, I think you could use the =TRANSPOSE() function or a pivot table.
As for the API jumping around, try to use &order=id_asc on the end of your URL, otherwise you will need to use =VLOOKUP() to pipe your data over to another sheet.
Let me know if that helps! :)
For binance exchange future, work also?
Sure, it works for any API endpoint.
Hello, nice tutorial the most simple and comprehesive! i have a question when I run the command to get the prices every time the order of coins is changing. Do you have a solution about this?
Hi Antonis, you can use Sheets functions like VLOOKUP to check for coin names rather than pointing to a fixed position in the sheet. If you check this article, there's a template already set up for this: mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/
@@mixedanalytics Nice!!
does the price of the coins get updates everyday?
Not by default. You would need to manually run the request or you can get scheduling with our pro version, you can read more here: mixedanalytics.com/knowledge-base/api-connector-scheduling/
hello!
How can I do to obtain all the serie of prices (for example all the last year prices)?
Thanks!!
Hi Franco, try this one! www.coingecko.com/api/documentations/v3#/coins/get_coins__id__history
@@mixedanalytics thanks! I will
Will the price gets updated each time the price change?
No, it won't update automatically. To change it you'll need to manually refresh or set up scheduling.
This is awesome video! How did you do pop up menu for type bitcoin search ?
Ah, you mean Ctrl+F at 5:46? I'm not sure if that's what you were talking about but let me know :)
Awesome mate thanks
Any time!
some coins values can't be extracted.
Which ones in particular? I can take a look and try to find a workaround.
Great tutorial! Thanks for sharing knowledge with our community. Assume I have a long list of "ids" with 30 different cryptocurrencies. How can I refer to an id-list in the API URL path? In your tutorial, you use three different coins which you have to type in "&bitcoin,dogecoin,rabbitcoin" in the API URL path. Is there a shortcut for a longer list of "ids" (coin) that avoids typing in all ids separately? Thanks! Keep up your fantastic work.
exactly my question too, I want to basically run a watchlist.
Hey y'all!
So yes you can use the +++sheet!A1+++ syntax unique to our tool to reference cells instead. You can read up on it here: mixedanalytics.com/knowledge-base/api-connector-create-api-request-based-on-cell/ Let me know if that helps! Thanks :)
Does the data updates on real time?
Hi Avel,
So every time you press the "Run" button, API Connector will update the data from CoinGecko. If you want to automate this, you can check out scheduling, one of our pro features: mixedanalytics.com/knowledge-base/api-connector-scheduling/
Thanks man
No problem
Thanks for the information! But how do you only retrieve like the price of BTC or dogecoin? So that the only thing that you get is the price without any other information whatsoever?
If you use the /coins/{id} endpoint, you can specify which coins you would like returned. Read more here:
www.coingecko.com/api/documentations/v3#/coins/get_coins__id_
Let me know if that helps! :)
@@mixedanalytics Yes, so I let Bitcoin and Ethereum return, but I also get a cel above them with "bitcoin >> euro" and "ethereum >> euro which I both don't want when I let the API run, also I can't find a way to get the information placed underneath eachother in cells that I've selected, idk if thats even possible hahah. What I did is just add another page for just the prices and then copied them into my crypto portfolio which was then automatically updated when I ran the API. But please let me know if u do have got answers on my questionss
@@siebevermijs3756 You nailed it! The best practice is to have a "raw-data" sheet that pulls in the data from API Connector (sorry, can't disable headers 😝, the "bitcoin >> euro" and "ethereum >> euro" cells you are referring to in this case) and then port that over to another sheet using either VLOOKUP or QUERY.
Currently, we don't support selection-based output. API Connector just dumps everything to a single sheet. That would be a cool feature though, thanks for the suggestion!
Let me know if that helps answer your questions :)
@@mixedanalytics Yes it definitely does! thankyou, I've just started spread sheet since this week and I really enjoy the coding stuff. I'll keep trying to improve myself! +1 sub!
I try to do this with pancakeswap you can help me, how i can get last price of a token
Hey Juan!
Make sure you have the right ID, and try to use this endpoint: www.coingecko.com/api/documentations/v3#/coins/get_coins__id
Let me know if that helps!
Great vid ! Can we get a similar one for Libre Office ?
Oh, you mean like connecting an API to Libre Office?
I'm not sure why you even make the IDs sheet,then, you can skip that step,cause just the query & comma separated ids works. I want to include other data too, so I just keep adding ampersands & the code,right? I see they have exchanges, finance platforms & finance products, I wonder if that includes defi platforms & interest rates,I don't see that in the code on each of those categories,I guess I gotta just do a query & find out,unless anyone knows for sure & other free API's from other sites that would have this data.
That's right, you don't NEED the ids sheet, but I find it's good practice as it prevents you from confusing them (as a reference sheet if you see what i mean).
There are TONS of free APIs out for crypto. I have more videos in the works about them, or you can check a few of the other ones I've already posted on this channel. Check out some of these other ones: medium.com/coinmonks/best-crypto-apis-for-developers-5efe3a597a9f
Try this one for DeFi:
docs.defipulse.com/
@@mixedanalytics Cool,will do,yea,I wanna have one that will track the various defi interest rates & keep track of what I'm making versus what I may be paying back on Crypto loans, a P&L spreadsheet basically & it can monitor my investments & business.
Thank you Man.
You're welcome!
Admin isn't allowing the download?
Sorry, not allowing the download of what now?
is API connector free , it doesn't look like i can "create "
Hello,
Yep it is free!
If you think API Connector is behaving strangely you can post a Loom/screenshot over in our Reddit to get some more advanced technical help here:
www.reddit.com/r/api_connector/
Hey, is it possible to connect this tool to Messari API ?
Hi Pierre,
Sure, why not try it? API Connector can handle a bunch of APIs, read more here: mixedanalytics.com/knowledge-base/frequently-asked-questions/#num7
Hope that helps!
@@mixedanalytics I will have a look thank you !
thank you!
You're welcome!
How frequently does the sheet get upgraded using the tool?
You can run it manually to fetch data immediately or set up scheduling up to 1x per hour.
@@mixedanalytics Hi! How do you set it up to fetch immediately?
@@PeterClaven Hi Kevin! Every time you press "Run" in the sidebar, the data is fetched live at that moment.
Very useful app guys, hope you're still holding ur doge
🐕📈🌘
Hi, great videos from you to understand how it works, but maybe there is a chance to get video how to create API to get a price for each and every coin on KuCoin exchange please.
Great suggestion! I'll mark that down.
@@mixedanalytics thanks, it will be another one great video i suppose :)
great video !
Thanks for watching!
How about the security issues this may cause? Think about the bank/ crypto accounts you are accessing through the browser and leaving a footprint. You might get vulnerable with this running. Just saying
Hi Yerko!
That's a good point. You can read about our privacy policy here mixedanalytics.com/privacy-policy/
Not working anymore
not usable really. When you pull refresh to get newest coin prices, it gets prices in a random sequence. So you have made yourself a nice looking table with your totals, you have synced cells with prices, everything is nice. Next day you want to refresh prices and you get a new sequence of coin prices and your table looks like trash and you have to sync everything from zero.
Hello! I'd suggest in that case to either use the sheet you pull data into as a raw sheet, then pipe it over to another sheet using VLOOKUP(), or if you are calling /coins/markets endpoint, please try adding the `&order=id_desc` query parameter to the end of your request
cant get it for free now sadddddddddddd
dont work -.-
:(
API has been moved to the bottom By the way
👍👍👍
Oh "you aren't into this investing thing?" I bet you are BTC maximalist ; 0
TO THE MOON! 🤫
taiwan no.1 lol
;)
Video is pretty outdated.
Used to work. Lost 20 minutes of life. Don’t even try.
:( What broke? Maybe I can go take a look and see what needs to be updated
Does it change automatically or do I need to press "run" again to sync it with the current price in coingecko?
Hi! Correct, you will need to press run again to synchronize with the current prices. However, with our pro account, you can automate this process.
@@mixedanalytics How much is the Pro and you got any promo code ,)
When you install this add-on, you automatically enter a 14-day free trial that activates all premium features. After 14 days, you will be switched to the free plan
How do you automate it since it has it in the 14day trial?
@@I.Thor27 Haha yep, 14 free days! Check out this article here: mixedanalytics.com/knowledge-base/api-connector-scheduling/