If you are investing in stocks and crypto, do you actually have a simple way to track how your overall investment portfolio is performing?
Most likely you will have some stocks in one investment brokerage and then crypto held in different wallets and crypto brokers. This makes is difficult to get a quick snapshot of how much you are up or down on any particular day.
One solution is to build a simple sheet on Google Sheets that will track everything in one place.
Feel free to download the template I have created for your own use. You will need to copy and paste it into your own new google sheet. It will give you a head start on building your own. Also, follow the tips below to get up to speed on using the Google Finance formulae.
How to Build Your Own Stocks and Crypto Tracker on Google Sheets
You don’t have to be a whizz at excel to be able to build a portfolio tracker, anyone can do it.
Here are some useful Google Finance formulae that can be used to stocks:
Company Name =GOOGLEFINANCE(B2,”name”)
Share Price =GOOGLEFINANCE(B2,”price”)
Market Cap =GOOGLEFINANCE(B2,”marketcap”)
Daily Price Change =GOOGLEFINANCE(B2,”changepct”)
Price to Earnings Ratio =GOOGLEFINANCE(B2,”PE”)
Still a bit confused? Then check out my video below that takes you through how to start setting up your sheet.
Google have a data base where you can pull financial information from and this database also includes some crypto data but only covers big projects such as Bitcoin, Ethereum etc.
Here is an example of the Google Finance formula:
If you want to add in crypto holdings of smaller Altcoins then you will need to use a different method that pulls data directly from the Coinmarketcap.com website.
Follow these steps:
Step 1. Go to the CoinMarketCap website and click on the crypto you want to pull price information from. For example if you want to pull the price information for Bitcoin, then you need to go to the following page
Copy the url and past it into your google sheets document as such.
For this example past it into cell B2 ( See below image)
Step 2. Copy and past the following formula into cell C2
Now at this point, the price should automatically pull into your google sheets document, however this price will not update in real time. It will just remain at the same price, a little more work is needed.
Step 3. One method we could use is to create a macro. A macro is essentially a script that records a couple of actions you make on a google sheet doc, like copying, deleting, inserting lines, and saves that sequence of actions so you can automatically rerun the same set of actions again at the click of a button.
To record a macro, you simply go to extensions/macros/record macro.
Here is a video tutorial I created on how to add in a crypto section to your portfolio tracker (apologies for the poor quality I made this when I was new to YouTube).
I am still working on getting an operational formula for pulling the market cap data from the coinmarketcap website. If there are any updates on this you will find them here.
Disclaimer: This blog post is for informational and educational purposes only and should not be construed as financial advice.