Do you want to manage your cryptocurrency portfolio with real-time updates? In this tutorial, we'll learn how to track live cryptocurrency prices and automatically update your portfolio in Google Sheets.
Introduction:
I'm Yogesh Shinde, and welcome to my channel where we learn Google Sheets. If you're new, subscribe and hit the bell icon for upcoming tutorials.
Getting Started:
- The tutorial starts with a prefilled header and formatted columns.
- Dates of transactions are added in column A, and currencies in column B.
- Supported cryptocurrencies include Bitcoin, Ethereum, Litecoin, and Bitcoin Cash.
- Column C lists currency tickers (BTC, ETH, LTC, BCH).
- Column D contains purchase prices for each currency.
Portfolio Setup:
- Transactions from exchanges are listed in columns E to I.
- Column J sums up all coins across exchanges.
Real-Time Price Updates:
- Column K fetches live cryptocurrency prices using the
GOOGLEFINANCEfunction. - For example, to get Bitcoin's price in USD:
=GOOGLEFINANCE("CURRENCY:BTCUSD")
Portfolio Valuation:
- Column L calculates the cost of cryptocurrencies purchased (coins * purchase price).
- Column M computes the current value of holdings (coins * live price).
- Column N calculates the percentage change in value.
- Column O shows the USD change in value.
Visualizing Data:
- Conditional formatting highlights profit (green) and loss (red) in columns O, L, and M.
- Charts are created to visualize portfolio composition and profit/loss trends.
Conclusion:
By following this tutorial, you can create a dynamic cryptocurrency portfolio dashboard in Google Sheets. Don't forget to subscribe for more tutorials and share this video with others who might find it helpful.
Explore more Google Sheets tips and tricks on my website yogeshshinde.co and download our ebook "101 Google Sheets Tips, Tricks, and Hacks".