X

  • Portfolio Tracker Excel Document And User Guide

In order to help others manage their portfolios, I have decided to make the excel document that I use to track the positions in my Simulated Portfolio available to everyone for free. I call this document my "Simulated Portfolio Tracker."

In order to simplify the document, I have shrunk my own Portfolio Tracker down to create a "Example" document, and I will be allowing everyone to download it directly by clicking: Portfolio Tracker (Example Document).

I also created a step by step guide for how to use my Portfolio Tracker excel document, and it can be found below. My goal is to help make it as easy as possible for you to integrate data from your own portfolio into my Portfolio Tracker. If, after reading the step by step guide, you still are having some difficulty figuring out how to use the Portfolio Tracker, or integrate your own portfolio into it, please fill out the contact form at the bottom of this page and I will try to help.

Important Information About The Portfolio Tracker:

The following information is necessary to know before any data is inputted into the Portfolio Tracker, because it contains information relevant to understanding how the Portfolio Tracker is visually set up.

  • I use the S&P 500 as a benchmark to compare my portfolio's performance against, but a different benchmark can be chosen if you wish.
  • The Portfolio Tracker excel document is built so that a new worksheet is created every time the owner decides to "track" data from his/her portfolio. For example, I personally update my Portfolio Tracker every weekend, so I add a new worksheet (with the date range as the title) every week. The owner decides the frequency with which the Portfolio Tracker is updated (i.e. you can update it weekly, monthly, etc.). For the sake of convenience, while going through the steps below, I will assume that you are going to do what I did and update the Portfolio Tracker document every week.
  • All of the data in the document came directly from my own Simulated Portfolio, and I have been using the Portfolio Tracker since January 01, 2014. However, when creating the Portfolio Tracker example document, I deleted all of the historical worksheets except for the two most recent worksheets (at the point in time that I created this guide). If you would like to understand more about how my Simulated Portfolio was constructed, please visit the "Portfolio" page of this website.
  • All of the cells that are not headings (shown in bold) and contain data that does not "link" to any other cell in the document is highlighted in yellow and contain blue text. The data in these cells needs to be filled in manually.
  • All of the cells that contain formulas that needs to be updated every week are highlighted in yellow and contain red text.
  • All of the cells that are not headings (show in bold), are not highlighted in yellow, and contain black text have formulas that link to other cells in the document.
  • The main headings for the document are highlighted in orange and contain bold text.
  • Many cells have "comments" (as shown by a red tab in the upper right-hand corner of the cell) that provide information about the data/formula in the cells. MAKE SURE TO READ THE COMMENTS IN THE CELLS WHEN THEY ARE THERE.

STEP BY STEP GUIDE TO INPUTTING DATA FROM YOUR PORTFOLIO INTO THE DOCUMENT

Note: Before reading the Step By Step guide, make sure that you have read the Important Information above.

Step #1: Open excel document and take some time to look at everything that it contains. There are six worksheets in the Portfolio Tracker document ("January 12 - January 16, 2015", "January 19 - January 23, 2015", "Portfolio Beta Calculations", "Share Ratio Calculations", "Graph Of Sector Allocation", and "Graph Of Portfolio vs. S&P 500") and you should begin to become acquainted with all of them.

Step #2: Decide whether or not you want to use historical data from your portfolio to fill in the worksheet pages of the Portfolio Tracker. If you do decide to use historical data, just change the title of the first worksheet("January 12 - January 16, 2015") to whatever you  want your starting period to be, and then proceed to follow the next steps of this guide.

Step #3: Beginning in Row 6, input data from your own portfolio into the first worksheet in the document ("January 12 - January 16, 2015") by erasing all of the data contained in the cells that are highlighted yellow (with blue text) and inputting data the necessary data for the stocks in your own portfolio. There are 35 different stocks listed in the example Portfolio Tracker document, so just begin inputting data from your own portfolio (beginning in Row 6) into the document and if you have fewer than 35 stocks in your portfolio just input data for as many as you have and delete the data in the remaining rows (i.e. if you have 10 stocks just fill in information for Row 6 - Row 15 and delete Row 16 - Row 40).

  • Note #1: Make sure to actually DELETE the entire row, not just the information in each cell in the row, because if you only clear the contents of the row, and don't delete it entirely, the formulas in some cells may not work properly. After deleting the rows that you do not need, you will notice that some of the formulas in the cells under the headings "Portfolio Breakdown" and "Portfolio Breakdown By Sector" will no longer work (the cells that contain formulas that no longer work will likely show "#REF!"). This happens because the formulas are trying to reference data in cells that no longer exist, or at least the data in the cells is now different. When this happens, just go into each of these cells and delete all the parts of the formulas that read "#REF!" Once this is done, the formulas should work again and numbers/percentages should be shown.
  • Note #2: When inputting data from your portfolio into the first worksheet in the document (entitled "January 12 - January 16, 2015), you should know that the cells in Column P (i.e. starting with cell P6) normally contain formulas, but because it is the first worksheet in the document, and the formulas require historical data, no formulas were used in those cells in the first worksheet in the Portfolio Tracker document. The second worksheet in the document (entitled "January 19 - January 23, 2015") has formulas in the cells in Column P, and those formulas need to be updated every time a new worksheet is created, when you are updating the Portfolio Tracker document (see the section below that describes how to update the Portfolio Tracker document each week for further details). Similarly, the cell next to the subheading entitled "Portfolio Value At End Of Last Week" and the cell next to the subheading entitled "S&P 500's Return For This Week (%)" both would normally contain formulas but. because they require historical data, they were just filled in manually in the example Portfolio Tracker document. Just forget about the formulas when filling in the data for the first worksheet, but be aware of the fact that those cells normally contain formulas.

Step #4: After you have input data from your portfolio into the correct locations in the document, and fixed the formulas in all the cells that read #REF!" (if you deleted any rows), then begin to fill in the required data on the second worksheet in the document (entitled "January 19 - January 23, 2015"). Make sure to take note of the fact that some of the formulas that required historical data, possibly to calculated week-over-week percentage changes, are now present in the second worksheet when they were not in the first worksheet (because no historical data was available). If everything has been done correctly up until this point, you will be ready to continue on to the next step by step guide below.

Final Note: If you do not have any money in commodities or bonds in your portfolio (or in ETFs that specialize in tracking the prices of bonds or specific commodities), then make sure to manually input "0.00%" into the cells to the right of the subheadings entitled "Percent Of Portfolio In Bonds" and "Percent Of Portfolio In Commodities" in the "Portfolio Breakdown" section of each worksheet.

STEP BY STEP GUIDE TO UPDATING THE PORTFOLIO TRACKER EVERY WEEK

Note: This step by step guide can only be following if the step by step guide above has been completed.

Step #1: Create a new worksheet in the excel document, by clicking on the required button on the bottom of the excel document (to the right of the worksheet entitled "Graph Of Portfolio vs. S&P 500"). You can title the page whatever you want to, but I usually just do it with the date range of the trading week that just ended (I update my Portfolio Tracker every weekend).

Step #2: Copy all of the data in the most recent worksheet in the Portfolio Tracker document, by clicking on the button on the top left of the worksheet (above and to the left of cell A1) and copying everything.

Step #3: Paste all of the copied data into the new worksheet and select the paste option that makes sure that the same column widths are used in this new worksheet as those that were used in the worksheet from which the data was copied.

Step #4: It is now important to begin to differentiate between the cells that are highlighted in yellow and contain red text and the cells that are highlighted in yellow and contain blue text. The highlighted cells with red text contain formulas that need to be updated every week, by using data contained in the prior worksheet. Make sure to select the correct data for these cells. Make sure to take note of the fact that the cells in Column P contain formulas that need to be updated every week. Similarly, the cell next to the subheading entitled "Portfolio Value At End Of Last Week" and the cell next to the subheading entitled "S&P 500's Return For This Week (%)" also contain formulas that need to be updated every week.

  • Note: Some of the cells that are highlighted and contain red text have formulas that only need to be updated when a new position is added to the portfolio. All of these cells contain "comments" that should help you figure out what to do in these circumstances.

Step #5: After all of the formulas in the cells that are highlighted and contain red text have been updated for the week, begin to update the cells that are highlighted and contain blue text. The highlighted cells with blue text in Column J through Column N, Column Q, and Column W need to be updated every time a new transaction is made during the week for which you are tracking the changes in your portfolio. The highlighted cells with blue text in Column C need to be updated every week using information that is either sourced from your portfolio (for recording your portfolio's current balance) or sourced from the internet (to record the S&P 500's closing values).

STEP BY STEP GUIDE TO UNDERSTANDING The PORTFOLIO BETA CALCULATIONS WORKSHEET

Step #1: Delete all unnecessary columns and all data underneath the columns with the ticker symbols of the stocks in your portfolio (the ticker symbols in your portfolio should have transferred automatically if you filled out the information on the second worksheet for the Portfolio Tracker document).

Step #2: If you want to calculate a 1-year daily beta, which is what I did for my portfolio, go to Yahoo Finance and download the necessary historical data and upload it into this document. THIS VIDEO shows you how to do this.

  • Note: You can choose how many years to use for your beta. I just used 1-year because it was simple and easy.

Step #3:  The cells that are highlighted and contain red text are the ones that you will need to update the formulas for each week if you plan on re-calculating your portfolio's beta each week.

GUIDE TO UNDERSTANDING THE SHARPE RATIO CALCULATIONS WORKSHEET

The Sharpe Ratio Calculations worksheet is a bit more difficult to do, because it requires a lot of historical data for it to work. All you need to do to make it work is to fill in historical portfolio values and the formulas will do the rest.

As you can see from what data I left in the worksheet, I had over a full year's worth of historical portfolio values that I put into the model to calculate my portfolio's sharpe ratio for 2014.

All of the formulas for calculating the sharpe ratio of your portfolio are there, you just need to input the historical portfolio values. Take a look at the formulas in the cells and it should be pretty easy to figure out how the sharpe ratio is calculated.

If you would like to learn what a "Sharpe Ratio" is, and why portfolio managers use it, then check out the definition of the term in my Resource Guide by clicking the link below:

GUIDE TO UNDERSTANDING THE GRAPH OF SECTOR ALLOCATION WORKSHEET

The Graph Of Sector Allocation worksheet is probably the only potentially unnecessary worksheet in the Portfolio Tracker document. I would only recommend that you use it if you are planning on showing information related to your portfolio to someone else (like I do on this website), or if you digest information best when you it is visually presented to you.

The Graph Of Sector Allocation just takes information from the portfolio tracker worksheets and represents that data visually. As you can see, from the fact that the data in the Graph Of Sector Allocation worksheet is highlighted and written in red text, the data in this worksheet needs to be updated and it will not update automatically (i.e. you must manually change the formula in each cell to make sure that it references the most up-to-date information from the portfolio tracker worksheets).

After you have updated all of the cells in the Graph Of Sector Allocation worksheet that require it, you will notice that the graph does not automatically update to reflect the new data (this is quite annoying and it is a problem that I am working to fix - it might be a problem with Excel). In order to have the graph update to reflect the new data, follow the simple steps described below:

  1. Right click on the chart and click the option that reads "Select Data." A new window entitled "Select Data Source" will then pop up.
  2. In the upper right-hand corner of the window that popped up (to the right of where it reads "Chart data range" and gives the excel formula for the chart), click the small box. See below for a picture of the window and the spot that you should click:Portfolio Tracker (Example Document) How To Use Picture #1
  3. After clicking the box shown in the picture above, highlight the cells under the heading "Weighting" (i.e. cell E5 through E16 in the example document), but do NOT highlight the cells under the heading "Sector" yet. After these cells have been highlighted, click on the same small box again. When this has been done, you should notice in the background that the data in the chart has updated, but the correct names in the legend are now showing up yet (they should have turned into numbers from 1 through 12).
  4. Now click on the small box in the window again (the one used in step #3) and this time highlight all the cells under the heading "Weighting" and under the heading "Sector" (i.e. highlight cells D5 through D16 and Cells E5 through E16). After all of the correct cells have been highlighted, the formula in the small window should read " ='Graph Of Sector Allocation'!$D$5:$E$16 "
  5. Once step #4 has been completed correctly, the chart should have updated to reflect the new data, and the correct label names should be shown in the "legend" to the right of the chart.
  6. At this point you will have noticed that the labels on the actual chart itself have not changed. For example, the label for cash on the chart will still read "Cash (24.55%)" even when 24.55% of your portfolio may not be in cash. These labels will need to be updated manually if you wish them to be displayed, however, you can delete them if you wish by simply left clicking on one of the labels on the chart (which should select all of the labels on the chart automatically) and then clicking "delete" or "backspace" on your keyboard.

GUIDE TO UNDERSTANDING THE GRAPH OF PORTFOLIO VS. S&P 500 WORKSHEET

The Graph Of Portfolio vs. S&P 500 worksheet is very similar to the Sharpe Ratio Calculations worksheet, in that they both require a lot of historical data related to the values of your portfolio. The worksheet is quite self-explanatory: it simply charts your portfolio's performance and the performance of the S&P 500.

As you can see in the worksheet, the date ranges are in the left-hand column, your portfolio's return for the designated week are in the middle column, and the S&P 500's return for the designated week (historical values of the S&P 500 can be sourced from Yahoo Finance or a similar data provider) are in the right-hand column.

To begin creating the graph, manually input all of the historical date ranges that you are intending to have charted into Column B, under the heading "Date Ranges (Trading Weeks)". You can create the chart using any date range that you want, just make sure that you have historical data for your portfolio and the S&P 500 that aligns with the date ranges.

Once you are done manually adding in all of the date ranges, you can now begin adding historical data for your portfolio and the S&P 500 by linking each cell directly to the necessary cell in the portfolio tracker worksheets that pertains to the designated date ranges (the cells you will need to link to are on the bottom left-hand side of the portfolio tracker worksheets under the heading "Portfolio Performance Snapshot").

After you have done this, follow the simple step by step process below:

  1. Right click on the chart and click the "Select Data" option. A new window should appear that will be entitled "Select Data Source."
  2. Click the "Edit" button (see the picture below): Portfolio Tracker (Example Document) How To Use Picture #2
  3. Click the small box to the right of the formula under the heading "Series values:"
  4. Select all of the historical total returns (from the beginning date to the end of the designated date range) for your portfolio. This data should be in Column C. After you have selected the data, click the small box to the right of the formula again to confirm your selection and be taken back to the previous dialog window. Then click "OK." You should now be back in the main dialog window, and your portfolio's historical total returns (from the beginning date to the end of the designated date range) should have been selected.
  5. Click on the words that say "The S&P 500's Performance", then click the "Edit" button.
  6. Click the small box to the right of the formula under the heading "Series values:"
  7. Select all of the historical total returns (from the beginning date to the end of the designated date range) for the S&P 500, which you should have already added to your portfolio tracker worksheets under the "Portfolio Performance Snapshot" heading. This data should be in Column D. After you have selected the data, click the small box to the right of the formula again to confirm your selection and be taken back to the previous dialog window. Then click "OK." You should now be back in the main dialog window, and the S&P 500's historical total returns (from the beginning date to the end of the designated date range) should have been selected.
  8. Now click the "Edit" button on the right-hand side of the main dialog window, which will be under the heading entitled "Horizontal (Category) Axis Labels."
  9. Click the small box to the right of the formula under the heading "Axis label range:"
  10. Select all of the historical time periods, which you should have already manually entered into Column B. After you have selected the data, click the small box to the right of the formula again to confirm your selection and be taken back to the previous dialog window. Then click "OK." You should now be back in the main dialog window.
  11. Click the "OK" button on the bottom right-hand side of the main dialog window to confirm all of your data selections.
  12. The chart should now appear with your portfolio's historical total return (from the beginning date to the end of the designated date range) being shown in blue and the S&P 500's historical total return (from the beginning date to the end of the designated date range) being shown in red.

If you have any questions about anything, please fill out the form below and I will try my best to help you. 

Your Name (required)

Your Email (required)

Subject

Your Message

CAPTCHA

captcha

Submit

Customize

Customize

Header Style Horizontal Vertical