Quick and Dirty Portfolio Tracker Part 1 - Stocks

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.

> Click here to jump straight to the main content <

Investing in stocks and cryptocurrencies can be a financially rewarding experience, but with constantly fluctuating market prices, it can also be very stressful!

Some people go the day-trading route, buying and selling with set percent-gain targets multiple times a day, while others, like myself, opt for the more passive “set it and forget it”, AKA couch-potato investor route.

If I’m being fully honest, like many others, I’ve fallen for the hype of various stocks and crypto pumped online, and succumbed more than once to FOMO (Fear of Missing Out), which led to me seeing my money vanish quicker than I can brew a cup of tea! I joke sometimes about buying high, selling low, but I’d actually prefer to see my money grow, not vanish into the abyss!

Most of my FOMO investing was done in 2018, just before crypto markets were about to take a massive dump, but it wasn’t until more recently that I decided to take a more active approach … to passive investing (wait, what?)!

If you’re Canadian and looking to passively invest long-term, consider checking out the Canadian Couch Potato site, as it contains a lot of good info about passive investing, including some simple sample portfolios. If you’re in the U.S. and looking for the same, Scott Burns’ site is probably where you want to head.

Tracking Your Portfolio

There are a number of existing really good desktop and mobile apps out there for tracking your portfolio, all of which I haven’t used, and therefore can’t really comment on. For web-based versions however, Yahoo Finance and Google Finance offer basic portfolio and stock tracking, including up-to-date market data, and may just suit your needs perfectly.

Despite the fantastic options available, and while I do have a bunch of stock, crypto, and currency tickers set up in my Yahoo Finance account, I also like to have my own custom spreadsheets setup to combine my portfolio totals with my overall net worth, budget, and expenses as well.

Creating Your Own Basic Portfolio Tracker

Because I have a variety of spreadsheets set up to track my various finances, I created a relatively simple stock and crypto portfolio tracker as well in a Google sheet, which I can then import into my other financial spreadsheets. In this post I’ll guide you to create a basic template so you create your own easy to use portfolio tracker.

Note: It’s generally a bad idea to store private, sensitive data online or in the cloud. With all of the financial info that I have stored in my spreadsheets, while I do track the number of shares I have along with current market prices, none contain account numbers, passwords, or anything else that could easily identify my private account details.

Getting Started

1. First off, we’ll need to create a new Google spreadsheet. So head over to drive.google.com, and create a new spreadsheet (either directly in My Drive, or in a folder in My Drive, whichever you prefer).

   

2. Optional step: Once the new sheet has loaded, rename the default sheet from Sheet1 to Portfolio Balance by right-clicking the bottom sheet selector tab, and selecting the Rename option.

   

Main Header Setup

Our “header” row, row 1, will house our local currency (to be used in each of the account sections), and also display our portfolio’s total current value, which will be obtained later by summing all of the account values.

3. In cells A1 and C1 respectively, enter the text following text:

   • Local Currency:
   • Portfolio Value:

   

4. In cell B1, you’ll need to enter your currency code.

   For me, that will be CAD, but for you that might be USD, or perhaps GBP or EUR, etc. If you’re not sure what your local currency code is, you should be able to find it in this wikipedia article.

Account Section Setup

For each account we have, we’ll create an account section. Each account section will have its own name (like “Credit & Savings Union Account”, or “Freedom Bank Account”, or “Company Pension Account”, etc) so we can identify it, its current account value, and its percent of the total overall value (based on the sum of all of our accounts).

5. In cells A3 and C3 respectively, enter the text following text:
   

   • Company Pension Value:
   • Percentage of Portfolio:

   Note: I’ve given cell A3 the value “Company Pension Value” as an example only. You can use any name you want, however it would generally be the name of your brokerage or account provider.

6. In cells A4 to I4 respectively, enter the following text to create the header columns for this account:

   • Name
   • Ticker
   • Number of Shares / Units
   • Unit Price

   • =CONCATENATE("Unit Price (",$B$1,")")
     

   • =CONCATENATE("Book Cost (",$B$1,")")
     

   • =CONCATENATE("Market Value (",$B$1,")")
     

   • Unrealized P/L

   Note: the text in cells E4, F4, and G4 above are formulas, so make sure you enter them without spaces before the equal (=) sign

   The text we entered in the above cells are a mix of plain text, and formulas. The reason for the formulas (in cells E4 to G4), is simply to have our base/local currency visible in the headers.

7. The next 2 rows will contain our securities. You may choose to use securities you are actually invested in, or you can just follow along with what I will enter. So for the sake of providing examples, I’ll be using Apple as a test stock, and Vanguard Canada’s equity ETF as a test fund. If you are using your own stocks/funds, just adjust the names, tickers, and number of units you own instead.

   In cells A5 to H5, enter the following text:

   • Apple Inc
   • AAPL
   • 10

   • =IFERROR(GOOGLEFINANCE($B5),0)
     

   • =IFERROR(IF(GOOGLEFINANCE($B5,"currency")=$B$1,$D5,GOOGLEFINANCE(GOOGLEFINANCE($B5,"currency")&$B$1)*$D5),0)
     

   • 800

     Note: This is just an imaginary “Book Cost” I’m using as an example. If you’re using actual data from your portfolio, you would enter your actual book cost here

   • =C5*E5
     

   • =IFERROR((G5-F5)/F5,"")
     

   The formula in cell D5 is what will pull the actual security’s price in the security’s currency using the GOOGLEFINANCE function, and then in cell E5 we’re using the same function to convert from the security’s currency into our own local currency.

   In cell G5, we’re simply multiplying our number of shares times the current price per share (in our local currency) to get the current market value, and then in cell H5 we’re taking the market value vs our book cost to determine our profit or loss percentage.

8. Next, select row 5 by left-clicking on the number “5” in the left-most column in the sheet with your mouse, and then press ⌘ + c on a Mac keyboard, or Ctrl + c on a Windows keyboard, to copy the row:

   

   Then click in cell A6 and paste the row you just copied.

9. I’ll now update cells A6, B6, and C6 with the Vanguard fund. You can do the same, or use any stock or fund you own if you prefer.

   In cells A6 to C6, enter the following text, respectively, overwritting what is there currently:

   • Vanguard All-Equity ETF
   • VEQT
   • 1000

   And then in cell F6, enter the amount 25,000.

   Note: as in step 7 above, if you are using actual data from your portfolio, enter your actual book cost here instead.

10. Next, in row 7, we’ll add get some totals. In cells E7, F7, and G7, enter the following text, respectively:

    • Total:

    • =SUM(F5:F6)
      

    • =SUM(G5:G6)
      

    The formulas here are simply adding the values in the cells above to generate the total amounts to be displayed.

11. Finally, back up in row 3, in cell B3, enter

    =G7
    

    This will basically duplicate the total worth of the account from cell G7 into cell B3. It’s possibly a bit overkill, but I find it makes it easier to see the account totals at a glance.

    We’ll leave cell D3 empty for the time being, and come back to it later in Part 2 of this series.

All Styled-Up And Ready to Go!

I’ve added a bit of styling so it’s a bit more aesthetic and easier to view, but you should now have something resembling the following:

Well, that’s it for this first part of our portfolio tracker.

Continue to Part 2 of this series, where we’ll add a 2nd standard brokerage account, and then proceed to add a crypto currency account to track some crypto!