Thursday 10 July 2014

Excel Portfolio Tutorial: Basic Table Foundation



    Tracking your various investments, registered and non-registered accounts can be fairly difficult and daunting, especially when you have multiple accounts with different companies. I find that by using Microsoft's Excel makes the task much easier and allows for the creation of a clearer picture of ones total investment portfolio. Setting up a spreadsheet (worksheet), or multiple worksheets within Excel and creating the tables can be quite time consuming if you are not familiar with using Excel, along with the financial formula’s to show you what you want to see.

   
    So in this first post I’d like to show you how I have setup my Excel worksheet for tracking my investment portfolio within my brokerage account. I’ll start with:

  1.  Setting up a Table: Columns, rows, labels.
  2.  Basic setup structure.
  3. A few basic formula's shown within Excel.
     

  These will be the basic setup and formulas for this post, and I’ll expand on them in future posts. Later on I will show how I link my portfolio page to my stock watchlist/research page in order to semi ‘automate’ my research page.

    
    To start off, you’ll need a copy of Microsoft Office, I am using the 2010 version; newer and older versions should work just as well for our purposes however I am not 100% sure on that, or whether the tools will be in quite the same places. Now that you have a copy of Microsoft Office, open up Excel and let’s get started! Start with a new, blank workbook and name it to your liking; personally I use ‘Finances’ since I track my income, expenses, portfolio and research all within the same file but under different worksheet names.

  • Open the Insert tab near the top left of the screen, then select Cell 1A to M10 by holding down and dragging your cursor, then click Table. 
  •  Now you will have a nice blank table to work with, so fill in the column labels with the information you will want associated with each one. You can also copy the ones I’v used within this tutorial.

    Now this is where you can start adding in your own information. The company names/Tickers of the stocks or ETFs you own, the number of shares, the average price you bought them for and their current market value. To get the numbers to show up in Dollars, select all the cells within Purchase Price to Total G/L, right click and go Format Cells and select Currency and use two decimal places and say OK. Now you should have something similar to this:


    Now to get the calculations for Book Value is nice and easy. Select cell E2 and in the formula bar type or copy and paste the following =sum(C2*B2) . Excel should then automatically apply that formula to all the rows remaining within that column to give you the following:



    For Market Value you will do the exact same formula but this selecting the cell for Current Price instead of Purchase Price. Within the formula bar, once you've typed in =sum(    you can simply select the cells you want to use instead of typing out the cell letter and number. Just be sure to add the multiplication between selections, which is the asterisks(*) key, found by holding shift+8) and finishing the formula with an end bracket.
  
    To calculate Total Gains or Losses for each investment you will need to insert the following formula the same way you did the previous two, starting to get easier right? This one is a simple subtraction of the Market Value by the Book Value;  =sum(F2-E2) . Then to see how the portfolio of selected stocks are doing we simply need to sum the total of our gains and losses by selecting cell G21, which is outside the table in order to have the above formula's not be applied to this cell. This can be done simply by highlighting the cells within Total G/L, ending at cell G21 and clicking the summation symbol (Σ)found at the top right of the Home page within Excel. Now do the same for Book Value and Market Value to get something that looks like this:


    Now you may be wondering why your Market Value is short by $17.21? That's because since we used the Summation tool by highlighting the cells within the column we were unable to add in the cash value held within our brokerage account. So to add that in simply add +E8, or selecting your cell to the end of the formula, OUTSIDE of the bracket in order to avoid a formula error. To add additional non-stock accounts like i did within my own portfolio for my Scotia TFSA cash simply add another +Cell to the end of the formula, but keep in mind to do the same with your Total Book Value as well.

   For getting the %G/L you will need to do an additional step beyond putting in the formula. Highlight the cells within your table and right click and select 'format cells'. Within the box under categories select Percentage and select how many decimal places you wish to have shown and say OK. For mine I use just up to one decimal place. The formula for your percentage gain or loss is =((Current Price-Purchase Price) / Purchase Price)) , or cells =((D2-C2)/C2)) . You should now have the following:


   Yay! We've now made the beginning foundation for managing our own portfolios. From this point of view, you can simply add all your holdings from different accounts into the same table. If you like for tracking/referencing purposes it may be useful to add a different column for which brokerage/bank or account type the stock is held in(RRSP,TFSA,RESP, non-registered Etc). Which is easily done by selecting the cell to the right of where you want to insert the new column and right clicking then go to Insert within the drop-down menu and 'Table columns to the left'. 

    So hopefully this has helped some of you and I'm looking forward to any feedback.

2 comments:

  1. This is very useful. I want to make something similar to this one for myself.

    ReplyDelete
    Replies
    1. Happy to see that you found useful for you :)

      Delete