How To Track Live Cryptocurrency Prices Data and Create Your Real-Time Portfolio in Google Sheets

Do you want to track and manage your cryptocurrencies
portfolio on a single dashboard? In this tutorial,
we are going to learn how to get real-time cryptocurrency prices and update your portfolio
automatically within Google sheets. My name is Yogesh Shinde And I welcome you to my channel where we learn
Google Sheets. If you are new to this channel,
please subscribe to my channel and press the bell icon
so you will not miss my upcoming Google sheets videos. So let’s get started
To speed up the video I have prefilled the header data,
which is the first row, I have also formatted all columns,
like percentage column with percentage, number columns with numbers, and
currency columns with currencies In column A,
we have dates on which we bought or traded the cryptocurrency So let’s consider we bought all these cryptocurrencies
today, I will select the range and press ctrl plus
semicolon to enter today’s date In column B, I will add currencies Currently, Google sheets support only four
cryptocurrencies, those are Bitcoin,

Ethereum,
… Litecoin,
… and
Bitcoin Cash … In Column C,
Currency tickers Those are
BTC, …
ETH, …
LTC …
And BCH
… Column D,
Price at which we bought the currency So let consider we bought
BTC at eleven thousand USD, …
ETH at four hundred and fifty USD, …
LTC at fifty USD, …
And BCH at two hundred USD
…. From column E to I, We have exchanges where we bought these currencies. So let’s consider, We bought 0.05 BTC on ZEBPAY
… 0.75 ETC on UNOCOIN
… 1 LTC on LOCALBITCOINS
… 1 BCH on BLOCKCHAIN
.. And 0.07 BTC on BINANCE
.. In My Coin column,
which is column J, We are going to sum coins from all exchanges. In cell J2, I will add is equals to, SUM,
Open bracket and select the range which E2 to I2, Let’s drag it down. Now comes the main part, in column K we are
going to add the live price of cryptocurrencies In K2, I will add is equals to, GOOGLEFINANCE,
… Open bracket,
… Quotation mark,
… Type – CURRENCY,
… And then Colon
… quotation mark
… And then ampersand (ampəsand),
… Then select the currency you want to convert, in our case it’s C2 so let’s select C2, Then ampersand (ampəsand), then quotation mark and then currency in which
we want to convert to, In our case,
it’s USD, so I am going to type USD And then quotation marks and then close bracket.

Press enter Now we can see we have a real-time price of
bitcoin in USD. Please don’t forget to read the disclaimer. Let’s check the current price on Google
… We can see it matches with our data Now let’s drag it down In column L,
we are going to get the cost of cryptocurrencies we bought,
that is the number of coins we have, multiplied by the price at which we bought
cryptocurrencies. In L2, I will add is equals to
D2, asterisk,
J2 And press enter Let’s drag it down We can see the cost of all the cryptocurrencies Let’s get a total of all the cryptocurrencies, In cell, L33, I will add is equals to SUM,
open bracket, Select the range,
Close bracket and press enter Now we can see the total cost of all cryptocurrencies, In column M which is the current value, we
are going to multiply the current price with my coins In cell M2, I will add is equals to,
K2, Asterisk,
J2 And press enter We can see the current value,
Let’s drag it down In cell M33, let’s get our total value by simply dragging
the formula from L33 to M33 In column N which is percentage change, we
are going to determine how much it has changed in terms of a percentage In cell N2,
Add is equals to, Open bracket,
M2 minus
L2, Close bracket,
Divided by, L2,
And press enter Let’s drag it down To get total percentage change copy and paste
the same formula in N33 In column O which is USD change, we are going
to determine how much it has changed in terms of USD In O2, I will add is equals to,
M2, Hyphen,
L2 And press enter Let’s drag it down —– To get profit and loss Simply copy and paste the formula in O33
—– In column P, we are going to detect how much
percentage we have invested in a particular cryptocurrency.

In P2, I will add, is equals to L2, which is cost
Divided by L33, which is the total cost If you don’t get the percentage change the
range format to the percentage —- Let me format the totals to make it more visually
appealing Select all data, increase the font size, change
the fill color and give colored borders —- To improve our data visualization and get
to know profit and loss, let’s apply conditional formatting to highlight values. Select the range from O2 to O31,
Click on the format, ..
Then conditional formatting, ..
Colour scale, ..
Format rules from red to white to green, ..
Set midpoint to number to zero, ..
it will show all positive values in green and negative values in red
.. Apply the same conditional formatting to other
columns. Let me quickly do that. Now let’s make a chart of our portfolio, Select column B and P by holding ctrl And click on insert chart, And change chart type to pie Let me adjust it below the totals To visualize profit and loss,
select column B, L, and M by holding ctrl and click on insert chart Change chart type to the stacked bar chart Let me adjust it below the totals You can change bar colors by simply clicking
on the bars, I am going to change the current value to
Green and cost to red to denote profit and loss Here is what you are going to get, a simple
dashboard where you can see your cryptocurrency portfolio.

If you want to track your stocks too, then
don’t forget to check our previous video on how to track stocks in Google sheets by
clicking the I icon above. If you like today’s video, please press
the like button, if you think this will help your friends and family share the video on
WhatsApp, and if you have any queries or questions please comment below. This is Yogesh Shinde singing off..

You May Also Like