Sirwin
Sirwin

Building a Portfolio Tracker

By Feder | idee varie ed eventuali | 19 Jan 2021


Most of you will have already installed or have prepared a personal portfolio tracker, so this post will be just me discovering hot water, but for those of you who are wondering how to create a simple but effective portfolio tracker, and who are not very familiar with formulas and excel environment, i'll explain briefly how to create in a simple and fast way a tool to help you monitoring your cripto portfolio using GOOGLE SHEETS

Lets take for example our favorite Ticker : BTC

  • To have on your sheet the current BTC price simply input the formula :
    • =GOOGLEFINANCE("btcusd")    

To start you will need the number of unit you purchased and the sum you spent to buy.  

  • After you have the information above you can use formulas to calculate:
    • Buy value – # of shares times average price per share
    • Current Value – # of shares times current stock price
    • Percent and Dollar amount of change up or down –
      • Percent is current stock price/average price per share-100%
      • Dollar is current value minus buy value
  • However, if you put the totals at the bottom the formulas can get messed up if you accidently filter in your totals within the overall portfolio data. *TIP: put your totals at the top as a portfolio summary… That way you can filter the data below without messing with the totals.

to recap, to get the current stock price:

=GOOGLEFINANCE(“BTCUSD”)

To get today’s price change percentage of a stock:
=GOOGLEFINANCE(“BTCUSD“, “changepct”)/100)

You can also create your own line chart, if you like the visual. You’ll need an additional Google Sheets formula for that called sparkline:


=SPARKLINE(GOOGLEFINANCE(“BUTUSD”,”price”,TODAY()-365,TODAY(),”daily”),{“charttype”,”line”;”linewidth”,1;”color”,”#5f88cc”})

For Ethereum is "ETHUSD" ,  so far i could only find those two cripto working on google sheets, maybe in the future big G will also add few other crypto ticker to it's sheets.

For this reason I decided to simply use my old fashioned excel and import directly for the web a chart from any site (coinbase,investing, ecc...) which will automatically synchronize every X minute I've set .

Is a little more tricky but it works just fine for me.

How do you rate this article?

1



idee varie ed eventuali
idee varie ed eventuali

Ciao a tutti, proviamo a mettere giù qualche idea sul mondo cripto e non solo !

Send a $0.01 microtip in crypto to the author, and earn yourself as you read!

20% to author / 80% to me.
We pay the tips from our rewards pool.