Coded a little spreadsheet that:

Coded a little spreadsheet that:

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)

Link: docs.google.com/spreadsheets/d/1l3HkkmmtPUET-F_-iNviAemTWmOh3U2KBewtsSTDmpc/edit?usp=sharing

Other urls found in this thread:

docs.google.com/spreadsheets/d/14hQx_kpUavw-A9MLdamliMQzOt18CNBI2Z8J56c3X2E/edit?usp=sharing
api.coinmarketcap.com/v1/ticker/bitbean/
api.coinmarketcap.com/v1/ticker/?convert=EUR&limit=200
youtu.be/w3sI8WVX-cc
twitter.com/NSFWRedditImage

blockfolio does pretty much the same

fuck you

Yep, that's totally true. Was wondering if I could add any other neat features that may not be available in Blockfolio

Why the hostility senpai?

good job user
thank you for sharing
we're all gonna make it™

Quickly fixed the triggers since that spreadsheet is a copy. Should now update correctly.

That's only for phone users though. The spreadsheet might be useful for those on using the PC.

Very cool. What do I write in a cell to get the percent_change_24h?

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.

May I have this and use it, and if so, how?

=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

Yeah I'm interested in seeing a guide for this.

how do you make it update automatically?
I have my own spreadsheet but I have to update it manually

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

Thanks user! Very good work!

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.

Thank you user, you did a great job.

user, no apply some machine learning algos, and you will be almost there!

nice!

I'm taking a course on Neural Networks online, but I'm not advanced enough to apply it to this yet...

>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.

get some basic theory and determine exact task what you want to achieve/predict or get answer to; And iterate, iterate and again iterate

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 :)

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

What's the A1 for?, I've been looking at the code for 10 minutes and it does nothing.

Can u make it in € too please?
Would really appreciate it ^^

Does Blockfolio actually look at your exchange accounts or is it only for hodlers?

For people who get the BTC value instead of the coin change this line
var url = "api.coinmarketcap.com/v1/ticker/?convert=USD&limit=XXX";
Change the XXX to the maximum coin rank you own, maybe even further.

google sheets has IMPORTXML function that can be used to get most data points. That's how I've set up my tracking sheet

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

write up the guide user.
brogrammers unite.

I put a little guide in the spreadsheet link :)

I added some changes to fit my needs, such as BTC value, and I convert it to EUR.
Check my version if you like:
docs.google.com/spreadsheets/d/14hQx_kpUavw-A9MLdamliMQzOt18CNBI2Z8J56c3X2E/edit?usp=sharing

Very nice. I like that percentage breakdown. Might add that to mine shortly

Hey, for some reason when I make the following command into a new line

=cmc("BITB", "price_usd", A1)

It fetches the price for BTC - not BITB. What gives?

Also thanks a lot for this dank sheet!

Hmmm that's very odd. I'll look into this... the ticker seems to be correct

api.coinmarketcap.com/v1/ticker/bitbean/

I changed
var url = "api.coinmarketcap.com/v1/ticker/?convert=USD&limit=XXX";

XXX to 500.

That was enough to include the bitbean :)

Thanks user!

Is it possible to also see the price in sats (btc) ?

nevermindI just saw the other guy and copied his :)

thanks to you both
>ZFFXs89u

This is because he only fetch the top 70 coins, you have to fetch more to index your BITB.

See how coinmarketcap API works:
api.coinmarketcap.com/v1/ticker/?convert=EUR&limit=200

Oops havn't seen your post sorry.

how do i replace all your dates and have mine put down into the spreadsheet, mine isnt saving the total every 5 minutes

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.

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

youtu.be/w3sI8WVX-cc

...