How to create a Google docs in order to track the real-time crypto trending

Introduction

The aim of this guide is to help either the newbies or the experts to keep track of the continuous change of the market without using a subscription service or the access to specific websites against payment of a fee.

This guide will help you make:

• A list of real-time prices

• A complete list of transactions (optional)

• A complete recap of your situation within the crypto (profit, losses...)

Prerequisite

• Google Account

Step-by-Step process

Go to Google Docs Spreadsheets and create a new file clicking the icon "+"

Give the file a revealing name so that later you can remember the contents (e.g."Crypto Balance"), in order to do this click in the upper left where there is the area concerning the title and fit in the name you chose (in my case "Crypto Balance").

Complete transactions list (optional)

If you want to have a transaction history regardless of the exchange I suggest you to begin from a similar scheme:

Here the instructions for the fields:

• Date = Fill in the date of the transaction, I suggest you the format %YYYY-%MM-%DD %HH:%mm (e.g. 2018-04-03 22:50)

• Action = Short description of the transaction done, e.g. DEP SEPA in order to indicate a sepa transfer

• Earned crypto = What is the earned value from the transaction

• Description = A complete description of the transaction e.g. Sepa Deposit towards Kraken

• FIAT deposited = In case of investment in euros I suggest you to fill in this field for the following steps

• Spent Value = In case of spending a crypto you have to fill in this field, e.g. -5000 BTC

• Result = Profit and/or loss as regarding crypto, e.g. +20000 ETH

• Exchange = Where the transaction happened

• Wallet Src / Wallet Dst = In case of crypto transfer you need to indicate the name of the wallet, e.g. in source Kraken and in dest Ledger

You can add any kind of field, feel free to modify it as much as you like.

Prices and profit/losses in real-time

In order to know in real-time the prices of a token or a coin we will rely on the API of https://coinmarketcap.com/ (well-known website regarding crypto).

If you already done a transactions list I suggest you to create a new file clicking on the icon "+" in the lower left

At this point it is necessary to add a function in google scripts in order to have the real-time updating.

Open the menu "Tools" and go to the item Script Editor

It will open a parallel tab with a similar content:

Delete completely the content and fill in as follows:

Price in EUR

function ccprice(name) {
    var url = "https://api.coinmarketcap.com/v1/ticker/" + name + "/?convert=EUR";
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText();
    var data = JSON.parse(json);
    var price = parseFloat(data[0]["price_eur"]) 
    return price
}

Price in USD

function ccprice(name) {
    var url = "https://api.coinmarketcap.com/v1/ticker/" + name + "/";
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText();
    var data = JSON.parse(json);
    var price = parseFloat(data[0]["price_usd"]) 
    return price
}

(Modified version of the script https://github.com/rathergood/Crypto-Currency-Price/)

Now click on save on the top left

Fill in the name ccprice

The final result should be something like this:

Perfect, now you can close those tabs and go back to our file.

Create a scheme similar as it follows (if you want USD use usd name):

In the first filed fill in the name of the coin or the token, e.g. Bitcoin.

In the second field we will recall the function that we created few minutes ago in this way:

=ccprice("<NAME OF COIN/TOKEN>")

e.g.

=ccprice("Bitcoin")

In order to fill in it correctly click on the filed and then on the top left in the space preceded by the writing fx

Now you have to write exactly like we said before, e.g.

Push the enter key and you will have the real-time price.

Let's fill in the field "Hold num", you have to insert the number of the coin or the token that you have got in numeric value, e.g. 10000000

Finally fill in the field "Total in euro" (or usd) as it follows in order to calculate exactly what is the current price of your coin in comparison with euros

=B2*C2

The result should be something like it follows:

Repeat the transaction for every coin/token that you have.
Beware of the name of the coin, remember that coins or tokens that contain spaces have to be replaces with "-", e.g. Bitcoin Cash become bitcoin-cash.

Recap profit/ losses

If you did all the previous steps for the next one you will just have to apply a little bit of maths in order to have a general recap of what it is happening.

I suggest you a scheme like it follows:

Now let's fill in the first field "FIAT deposited" and here there are two possible scenarios:

Scenario No 1: You have followed the step "Completed transactions list" and you have the field "FIAT deposited"

In the field write =SUM( go back to the first file and select ALL the lines relating to the field "FIAT deposited" (remember to update the function in case you add something to the list), the result should be something similar to this:

=SUM('Transaction list'!E2:E5)

Now push the enter key and in the first field you will have the total amount of euros

Scenario No 2: You DON'T have followed the step "Completed transactions list"

So fill in manually the total amount of euros in the field

In both cases you should be confronted with a similar situation:

Now let's see the field "Current Total in FIAT", select it and write =SUM( and select ALL the lines regarding the field "Total in EUR" that we created before:

Push the enter key:

Now let's fill in also the "Total Profit" field

Obviously the total of profit will consist of the current total less the total introduced, at this point we can fit in the formula

=<No cell total profit>-<No cell total introduced>

In my case for example is¨:

=G2-F2

Let's now calculate the percentage of profit

This will consist of the current total DIVIDED the total introduced

=<No cell total profit>/<No cell total introduced>

In my case for example is:

=G2/F2

Now let's give this the finishing touches in order to make it easier to read

In the field "Total in euros", "Total introduced euros", "Current total" e "Total profit" do this:

• Select the column

• Open the menu Format-> Number and select "Currency"

While in the field "Percentage profit" do like it follows:

• Select the column

• Open the menu Format -> Number and select "Percentage"

The result should be something like it follows:

I hope this guide helped you :)
See you to the next one,