Easy guide (I hope) to create a Portfolio Tracker

Disclaimer:

This guide is in "progress" and not "The Official CryptoSheets guide", just an example of what you can do to track your coins using CryptoSheets script. I used the CryptoSheets v1.0, but unless the code change radically, you should be fine using future versions. Diferent people has different preferences and requirements, but wanted to contribute to this project in something, since Im not Developer, crypto expert or Tax advisor...

Before we continue, I wanted to say thanks to saitei/seishi for start this project and all the geeks around the Github and Subreddit for helping converting this script as good as is getting!!

---

1. If you have followed the CryptoSheets Readme you will end up with a spreadsheet and these 2 Sheets; Rates and Wallets

2. Lets now create our new Portfolio tracker

3. First, add the titles. I like to keep record of the following things, but I think following this guide you would be able to track other elements like marketcap, total supply etc...

Coin / Symbol / Exchange / Quantity / Initial Date / Initial price / Current Price / Initial Return / Current Return / Gain Value / Gain Percent / Change 1h / Change 24h / Change 7d

4. Lets start adding the manual part of our portfolio: Add the Currency name (Makse sure you dont add any space or anything, since we will use it later for our VLOOKUP formulas), Exchange/wallet where you keep them, the quantity and the initial date and price you got them:

If you use the wallet sheets, you can use it to get Quantity directly from your Wallets sheet:

Wallets!B4

5. Now, lets start adding the Data from the our autopopulated sheet "Rates". I will start adding the Symbol of the coins using VLOOKUP formula, so if you get new coins, you just add the symbol into the first column and Google Sheets will do the rest

=VLOOKUP(A2,Rates!B1:R16,2,FALSE)

As you can see, we are doing a search of A2 [Bitcoin] into Sheet Rates for the cells from B1 to R16 (you could just set Z99 as end point as long as the text we are looking for is inside that range) and we get the column 2 of this search [BTC]

As you can see, is the column 2 since our search range starts in column B

Now we do the rest of the coins symbols changing [A2] for the Cell number of the coin:

=VLOOKUP(A2,Rates!B1:R16,2,FALSE)

=VLOOKUP(A3,Rates!B1:R16,2,FALSE)

=VLOOKUP(A4,Rates!B1:R16,2,FALSE)

=VLOOKUP(A5,Rates!B1:R16,2,FALSE)

=VLOOKUP(A6,Rates!B1:R16,2,FALSE)

[...]

This should be the result

Double check the formula when you copy paste from previous cells, since I found out that the formula will change the search range in doing so.

6. Lets add now the current prices. We will use the same VLOOKUP formula, but changing the column numer we want as result from 2 to 4:

=VLOOKUP(A2,Rates!B1:R16,4,FALSE)

=VLOOKUP(A3,Rates!B1:R16,4,FALSE)

=VLOOKUP(A4,Rates!B1:R16,4,FALSE)

=VLOOKUP(A5,Rates!B1:R16,4,FALSE)

=VLOOKUP(A6,Rates!B1:R16,4,FALSE)

[...]

7. Since we have the formula really fresh.. lets do the rest of them and populate the columns Change 1h / Change 24h / Change 7d We just need to change again the Column number we get as result of or VLOOKUP formula

=VLOOKUP(A2,Rates!B1:R16,11,FALSE)

=VLOOKUP(A3,Rates!B1:R16,11,FALSE)

=VLOOKUP(A4,Rates!B1:R16,11,FALSE)

=VLOOKUP(A5,Rates!B1:R16,11,FALSE)

=VLOOKUP(A6,Rates!B1:R16,11,FALSE)

[...]

=VLOOKUP(A2,Rates!B1:R16,12,FALSE)

=VLOOKUP(A3,Rates!B1:R16,12,FALSE)

=VLOOKUP(A4,Rates!B1:R16,12,FALSE)

=VLOOKUP(A5,Rates!B1:R16,12,FALSE)

=VLOOKUP(A6,Rates!B1:R16,12,FALSE)

[...]

=VLOOKUP(A2,Rates!B1:R16,13,FALSE)

=VLOOKUP(A3,Rates!B1:R16,13,FALSE)

=VLOOKUP(A4,Rates!B1:R16,13,FALSE)

=VLOOKUP(A5,Rates!B1:R16,13,FALSE)

=VLOOKUP(A6,Rates!B1:R16,13,FALSE)

[...]

8. We can add now some colour to this part with Conditional formating

I recommend you to play with the different colours and numbers, since everybody has different taste and idea of what should be Red or just Orange

9. We can now start calculating Gains and percentages:

Initial Return

=D2*F2

=D3*F3

=D4*F4

=D5*F5

[...]

Current Return

=D2*G2

=D3*G3

=D4*G4

=D5*G5

[...]

Gain Value

=I2-H2

=I3-H3

=I4-H4

=I5-H5

[...]

Gain Percent

=(I2/H2)-1

=(I3/H3)-1

=(I4/H4)-1

=(I4/H4)-1

[...]

Format the Percent as Percent number and you can also edit the number to decrease the decimal places and set number as Dollars (or your favourite FIAT)

Looking better right?

Add now some totals:

And voila!!

10. If you are like me, you need to know how current the prices are, so lets add a nice formula to see how old the prices are: (Please, check the CryptoSheets Readme to learn how to setup the different triggers and keep your information updated)

Lets first merger few cells so we can write what we want there:

Now, add the following formula: (Im using just using the iformation about when was last time the price was updated for Bitcoin, but you can use any of them, they should be updated by CoinmarketCap API at a similar rate)

=CONCATENATE("Prices Updated ",round((NOW()-(Rates!O2/86400+date(1970,1,1)))*24*60), " Minutes ago")

The calculation we are doing here is convert the Epoch date we get from CoinmarketCap to a normal Date and the we subtract the current date NOW to leave us just the difference in minutes. The CONCATENATE function, is just there to allow us to add normal Text and our formula

• Since the time from coinmarketcap are set in GMT+0, so you will need to set the spreadsheet settings ("File menu" -> "Spreadsheet settings" to that timezone:

11. If you want to have more control about how often the script is excuted, iKrazy added a refresh buttom as a solution (so Im stealing it.. :P):

Go to Insert -> Drawing

Select a Shape of your liking for our new Refresh buttom

Now, add a Text to it:

Save and close

We have now to add the script. So click in the button and click in the 3 little dots on the right:

Select the option Assign Script and just add getData to it

Now, every time to click in our refresh button, the script will be executed:

12. Using the information from our portfolio, we can create some nice graphics to show the total FIAT you have for each coin or the Portfolio Percent of each coin.

For that, I created a small summary of what I have using the current portfolio, so will be automatically update once I change the main one, only need to add anything if Im getting a new coin.

First, add the titles and lets take the Name/Symbols from the Rates sheet:

Names

=Rates!B2

[...]

Symbol

=Rates!C2

[...]

Add the prices using similar formula we used previously,

=VLOOKUP(A15,Rates!B1:R17,4,FALSE)

=VLOOKUP(A16,Rates!B1:R17,4,FALSE)

[...]

For the Quantity, I use SUMIF function and searching for the symbol as you can see below:

=sumif(B2:B7,B15,D2:D7)

We calculate the Value of each coin with a simply multiplication:

And the Percent dividen the Value of each coin for the Total Value of our portfolio

=F15/I8

=F16/I8

[...]

Remember to set the percent column with correct format

13. To create the Graphics, select the columns you need and go to Insert menu and Chart option:

First, lets create a Pie Chart for the Coin percents

Now, lets just create a simple chart to see the Total value of each coin using columns Name and Value:

After playing around with the Chart options and taking out the gridlines

this is how the portfolio looks like:

Really hope it was useful for any of you!!

What's Next?

• Once we have Historic values, will try to add nice graphics with that information

• Convince Stephen Fry to do a video of himself narrating and following the steps of this guide