Google spreadsheet for managing balance

[Storm]

Hey guys.

Last night I created a Google Spreadsheet for viewing my current XRP balance, my average buy price and to see my profit/loss.
I wanted to retrieve the live XRP/USD price from Bitstamp, since that is the exchange that I use to buy/sell XRP.

I figured that there might be others out there who would want a similar document, and I therefore decided to publish my spreadsheet (not with my real transactions, but just for giving examples of the use)

Below is a screenshot of the spreadsheet:

 

I struggled a bit to make the data be updated every minute, but managed to work it out with the help of the internet.
Therefore, in order to make this spreadsheet update frequently, a simple script is required.

Installation of script:

1. Go to tools > Scripteditor
2. Create a new script
3. Copy paste the following:

function URLupdate(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();;
  var sn = ss.getSheetByName("Balance"); //enter your personal sheet name here
  var d = new Date();
  var n = d.getMinutes();
  var A = sn.getRange("J3");  //update the cell reference here
  A.setValue("https://coinmarketcap.com/currencies/ripple/#markets#"+n);  //fetch data from the exchanges
  var A = sn.getRange("I3");  //update the cell reference here
  A.setValue(d);
}

4. Remember to edit the sheet name and cell references if you decide to modify the spreadsheet
5. Click on the small clock icon in the script editor, and make a new trigger for every minute. (An authorization is required. This is because you can't use SetValue in functions without authorization. The script will not run without the trigger event)
6. Save the script and close. 

This script is developed to fetch data from the specific XRP exchanges, but the URL can be changed to fetch other data as well.

Use of the spreadsheet

Make sure to make a copy of the spreadsheet, and install the script! Otherwise you will make changes to the master document!

Cell H3 is where the update function is being called.
Cell H4 is where the data is withdrawn from coinmarketcap.com
Cell C18 is where the specific live price is fetched. As default it is USD/XRP from Bitstamp. (Be aware that this might change if the list from coinmarketcap is reorganized!)

The million mark is the required XRP price for you to be a $ millionaire based on your holdings  

Unfortunately the transactions are needed to be inserted manually. Just delete the example transactions, and insert your own. Make sure when you are done that the summations beneath uses all of your  transactions. New rows can easily be created without messing up the exchange data.

This spreadsheet is meant as a template where you can change it to your needs.
I hope you enjoy the spreadsheet!

https://docs.google.com/spreadsheets/d/1QyCINtgb63oCz_Z2JdAVdkEYUQ76wNdRR0zjOd1N3yc/edit?usp=sharing

(This is the first time I'm sharing a google spreadsheet, so let me know if there are any problems with accessing the document)

The spreadsheet is of course open source and free, but donations are welcome 
rGPKbnydoUK6YXncPxND5GeuDEW49TZzk8

[Fenix]

You should not openly post your XRP balance.

[biggn]

He didn't

25 minutes ago, Storm said:

I figured that there might be others out there who would want a similar document, and I therefore decided to publish my spreadsheet (not with my real transactions, but just for giving examples of the use)

 

[Fenix]

Just now, biggn said:

He didn't

 

Oh, my bad. Didn't read entire post. 

[pucksterpete]

1 minute ago, biggn said:

He didn't

he did if that is his ripple address

rGPKbnydoUK6YXncPxND5GeuDEW49TZzk8

[pucksterpete]

39 minutes ago, Storm said:

Installation of script:

me NO good with scripts, but what value would I need to change to get EUR?

[Storm]

8 minutes ago, pucksterpete said:

me NO good with scripts, but what value would I need to change to get EUR?

The currency is not used in the script.
There are two options for formatting to EUR:

Use the function         =GOOGLEFINANCE("Currency:USDEUR")
This will give you the live exchange rate, and you can then multiply this with all your values you want converted.

Or if it is terms of the live price, you should point the cell to the correct reference. For example, the Bitstamp XRP/EUR price is located in K18. 

[Vacek_aka_Luschka]

Since we are in a sharing mood ... here is a tracker for your portfolio, not just XRP.

https://docs.google.com/spreadsheets/d/1RXugeftggSp57cFc0T6M56hed93A0yjQSYVs1jbBqmE/preview

Basic excel skills needed ... but should be VERY easy to grab and re-use, edit, add functionality, etc ...

ONLY column heads in Blue require your input.

 

EDIT: Try this link to grab an actual copy: https://docs.google.com/spreadsheets/d/1RXugeftggSp57cFc0T6M56hed93A0yjQSYVs1jbBqmE/copy

 

Edited October 5, 2017 by Luschka

[Vacek_aka_Luschka]

Fancied up a little ... Feel free to take it and use it however you like. ( link )

 

 

EDIT: Added a "EUR" sheet for those trading in Euros

Edited October 5, 2017 by Luschka