Disclaimer: The information provided in this blog post is for educational and informational purposes only and should not be considered as financial advice. The content presented here is not intended to be a substitute for professional financial advice, and readers should seek the guidance of a licensed financial advisor before making any financial decisions.

In the previous post, I went over the steps for a basic stock portfolio tracker, using only a single stock and a single fund as part of the portfolio.

Your portfolio may or may not be as simple, and like mine, may be spread out across multiple brokers and/or exchanges due to previous companies you’ve worked for, or possibly just for security.

In this post, we’ll be adding some cryptocurrencies into the mix, which will take a bit more effort as Google sheets doesn’t natively support crypto data.

Adding To The Portfolio

Before Continuing on to the cryptocurrency portion below, I just want to add a bit to our existing sheet to make it a little more reflective of an actual portfolio.

This is where we left off with the last post:

I’m going to now simply copy rows 3 to 7, and then paste them in cell A9, resulting in the following layout:

Next, I’ll simply modify the new account section by replacing the content in the noted cells as follows:

Note: as always, you can copy my steps, or use any securities that you like or already own.

• cell A9: Credit Union Account Value:
• cell A11: Microsoft Corp.
• cell B11: MSFT
• cell C11: 50
• cell F11: 10000
• cell A12: S&P 500 ETF Trust
• cell B12: SPY
• cell C12: 200
• cell F12: 50000

My sheet now looks like this:

Crypto Enters The Chat

Unless you’ve been living under a rock since well, a long time, you’re undoubtedly at least somewhat familiar with crypto currencies.

According to Wikipedia, cryptocurrency was first conceived by David Chaum in 1983. It wasn’t until 2009 however, that the infamous Bitcoin was born and released into the wild.

I myself made my first foray into crypto in 2018, shortly after which my crypto portfolio’s value dipped more than 50%. As it turns out, I missed the “don’t FOMO” memo about investing in general. Oops.

Anyway, enough about that, let’s get into adding crypto tickers to our spreadsheet!

Adding Crypto To Our Portfolio

Unfortunately, Google hasn’t enabled a way to import crypto data directly in Sheets at this point, so we need to use an external data source. We’ll be using the IMPORTDATA function to achieve this, along with data from the cryptoprices.cc API, which will supply us with only the price data.

Note: Per the cryptoprices.cc website, “Data can be 30/60 min late if not more”, so you won’t be getting the most live and up-to-date prices this way.

For simplicity, we’ll stick with two well known cryptos for this example. For the record - I currently have no stake in either of these coins.

1. Highlight and copy rows 9 & 10, or if your sheet looks different than mine, whichever two rows contain the account name and header field names from Credit Union Account I added above, and paste in cell A15 (or two rows down from your last account grid).
2. In the row below, row 17 for me, in cells A17 to H17, enter the following:
   • Bitcoin
   • BTC
   • 0.5

   • =IFERROR(IMPORTDATA("https://cryptoprices.cc/"&B17),"N/A")
     

   • =IFERROR(GOOGLEFINANCE("USD"&$B$1)*D17,"N/A")
     

   • 6000

   • =IFERROR(C17*E17,"N/A")
     

   • =IFERROR((G17-F17)/F17,"")
     

3. Then in the following row, in cells A18 to H18, enter the following:
   • Ethereum
   • ETH
   • 1

   • =IFERROR(IMPORTDATA("https://cryptoprices.cc/"&B18),"N/A")
     

   • =IFERROR(GOOGLEFINANCE("USD"&$B$1)*D18,"N/A")
     

   • 100

   • =IFERROR(C18*E18,"N/A")
     

   • =IFERROR((G18-F18)/F18,"")
     

4. And finally, in the row below that, in cells E19 to G19 respectively, enter the following:
   • Total:

   • =IFERROR(SUM(F17:F18),"N/A")
     

   • =IFERROR(SUM(G17:G18),"N/A")
     

After the above steps, and after applying styling to match the previous account I had set up, my sheet now looks like this:

UPDATE FOR 2023

The GOOGLEFINANCE function in Sheets can now get some crypto price data, but from my brief testing it seems very limited, and is missing a lot of cryptocurrencies.

If you’re only using well known cryptos like Bitcoin and Ethereum, you should be fine with the GOOGLEFINANCE function, and can update the formulas above in column D (cells D17 and D18 for me) to:

=IFERROR(GOOGLEFINANCE(B17&"USD"),"N/A")

and

=IFERROR(GOOGLEFINANCE(B18&"USD"),"N/A")

Finalizing Our Sheet

In Part 1 of this article, we added the text Portfolio Value: to cell C1, but left cell D1 empty. Since we’ve now finished adding the accounts we have to our portfolio, we can now add a simple formula to the noted cell to show our total portfolio value.

The formula we’ll use is a very basic and simple addition formula, but it’s also a manual one, meaning if you need to add more accounts in the future, you’ll need to manually add them to the formula in this cell.

If you used my examples exactly as I laid out in Part 1 and in this post, in cell D1, enter the following:

=B3+B9+B15

If your portfolios differ from mine, you’ll simply need to add the totals in column B in the headers for each account you set up, next to the name of the account (ex: Company Pension Value:).

And now that we’ve got the sum of all our accounts, we can add the formulas to show the percent that each account represents of our total portfolio.

Again, assuming you followed my examples and layout, in cells D3, D9, and D15 respectively, add the following formulas:

• =B3/$D$1
  

• =B9/$D$1
  

• =B15/$D$1
  

After formatting the above noted cells as percentages, our final portfolio sheet layout should now look like this:

If you need to add more securities (stocks, ETFs, funds, bonds, etc) to an account section, simply add a row in the section you need to, and copy over the formulas where applicable.

And of course if you’re adding more accounts, just remember to add the account value to the total portfolio value in cell D1.

And that’s all there is to it!

I hope you enjoyed Parts 1 & 2 of this article and found the information useful, and that you learned a bit more about using Google Sheets to manage your portfolio!