1. Gets the prices from coinmarketcap API automatically every 5 minutes.
2. Every time the sheet updates (every 5 mins), I save the previous price and date in the spreadsheet
3. It continuously generates a graph of my net cryptocurrency worth over time (RIP eth)
I can write up a little guide on how to do this if there's interest in this... I plan to add some more features in the near future (suggestions welcome)
Yep, that's totally true. Was wondering if I could add any other neat features that may not be available in Blockfolio
Joseph Roberts
Why the hostility senpai?
Alexander Roberts
good job user thank you for sharing we're all gonna make it™
Caleb Hernandez
Quickly fixed the triggers since that spreadsheet is a copy. Should now update correctly.
Sebastian Martinez
That's only for phone users though. The spreadsheet might be useful for those on using the PC.
Jaxson Walker
Very cool. What do I write in a cell to get the percent_change_24h?
Eli Long
That's really cool user!
I've been working on a spreadsheet of my own, that I will share eventually once it's complete (as soon as I learn how to achieve some of the things I want to). I tried to make it pretty user-friendly with a currency selector and everything. All you have to do is enter the symbol/amt of each coin and then it pulls live data from JSON to fill the rest. but unfortunately it doesn't update consistently. I have to brute force delete/undo all the rows to have all the data reload.
I also had a similar Idea for the net worth chart, but couldn't figure out how to log the date/data. Would definitely appreciate a few pointers on that, and how you managed to have everything reload every 5 minutes (don't know if it's different depending on the API). I'd also suggest that you log that data on another sheet and hide it so it doesn't make your spreadsheet monstrous.
James Scott
May I have this and use it, and if so, how?
Owen Gomez
=cmc("BTC", "percent_change_24h", A1)
This would get Bitcoin's 24h percent change in 24 hrs. Need to reference cell A1 for some caching reasons.
Dang, that looks sick asf! In google script I have the following function which appends a row of data to the first empty row in the spreadsheet. This row will contain a timestamp and net worth time. Here is that function.
function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var netWorth = sheet.getRange('Sheet1!E14').getValue();
var date = sheet.getRange('Sheet1!D14').getValue();
sheet.appendRow([date,netWorth]);
}
Sure man! Just add the name of your coin under coin name, how much of it you own in the column, and if you want to get the price of a new coin you simply write =cmc("COINTICKERNAME", price_usd, A1) in one of the cells in the spreadsheet. Then you need to set up how frequently it updates. I'll put the code/instructions for how to do this in the spreadsheet itself. 1 sec
Lucas Hill
Yeah I'm interested in seeing a guide for this.
Matthew Walker
how do you make it update automatically? I have my own spreadsheet but I have to update it manually
Adrian Ramirez
I have the same problems as you but mine looks shittier I am planning on adding a "bought at" to calculate gains though. You should do something like that too
Kayden Evans
Thanks user! Very good work!
Christian Foster
I do, I just shared the overview sheet. I have a second 'trade log' sheet where you can enter individual positions that will calculate your return on investment for either short/long positions. It's still in rough shape though as I'm more of a designer than a coder, and pain stakingly have to solve all the logistics myself. I'll get around to completing it once I'm happy with the overview, which thanks to OP will be sooner than later.
Angel Hernandez
Thank you user, you did a great job.
Andrew Baker
user, no apply some machine learning algos, and you will be almost there!
Camden Walker
nice!
Andrew Adams
I'm taking a course on Neural Networks online, but I'm not advanced enough to apply it to this yet...
Oliver Martin
>1. Gets the prices from coinmarketcap API automatically every 5 minutes.
Can you briefly describe what sort of programming I need to learn to do something similar?
Nice work OP.
Alexander Collins
get some basic theory and determine exact task what you want to achieve/predict or get answer to; And iterate, iterate and again iterate
Brandon Reyes
It's just some Google App Script. I didn't know it until 2 days ago, but I followed some basic tutorials online until I finally got the thing to work. I posted complete instructions in the spreadsheet itself if you'd also like to DL/customize it for yourself :)
Logan Walker
basically zero
you just need to be motivated
the fact that you asked this question instead of just researching it tell me that you probably aren't motivated enough to accomplish it
David Ramirez
What's the A1 for?, I've been looking at the code for 10 minutes and it does nothing.
Ryder Sanders
Can u make it in € too please? Would really appreciate it ^^
Brandon Anderson
Does Blockfolio actually look at your exchange accounts or is it only for hodlers?
google sheets has IMPORTXML function that can be used to get most data points. That's how I've set up my tracking sheet
Nicholas Collins
A1 is set to a random value every time the onEdit function is called. Google's caching is weird and I was having trouble getting the prices to autoupdate without that parameter.
Use Google Finance's function: =GoogleFinance("CURRENCY:EURUSD") to get the exchange rate. Then multiply the USD dollar values by that value. I can do a more in-depth tut a little later
how do i replace all your dates and have mine put down into the spreadsheet, mine isnt saving the total every 5 minutes
Jacob Bailey
make it so it calculates recent rapid increase in volume and price for shitcoins. combine the two in a single qfcnt. make it so that it calculates this fr only five - ten min. That would make finding pump and dumps much easier.
Asher Morris
That's 5 lines in Python (pic related), so if there's actual interest for an app that does this, I may make one and do a tutorial