Portfolio Tracker Usage Instructions

Installation and Usage Instructions:

1. Copy the Portfolio Tracker.xls file to the c:\Program Files\Toteboard directory. If that directory does not exist, create it first.
2. Open the workbook, then go to the Setup worksheet.
3. From the Setup worksheet, add your definition of Investment Types if they differ from what is already there. The Investment Types that you define here are then going to be available to you when you define your portfolio, and when you view your holdings by Investment Type in the Portfolio Monitor workbook. You can define up to 20 different types of investments.
4. Once done with Investment types, define your Account List. This will be the list of the accounts that you want to track. For example, 401K, IRA, Personal, UGMA and so on. This account list will then be on your pick list when you define your own portfolio.
5. Starting in cell H3 you can then begin entering the list of symbols that you have holdings in that you want to see carried over to the Portfolio Monitor workbook. What I define in here are only those funds, stocks, or options that I have a position in. Not, things that I used to own but no longer have positions in.
a. The Account column is for the account that the holding is in.
b. Symbol is the trading symbol for the instrument. (We get our pricing from Yahoo, so you need to ensure that the symbology you use is consistent with Yahoo.) This means, for example, that options need to be appended with “.X”.
c. Underlying Symbol is for linking derivatives (options) to the underlying symbol. For example, if I owned options in IBM, I would specify the option symbol in the symbol column, and then IBM in the Underlying Symbol column. This is important if you want to perform “adjusted P&L” calculations.
d. Columns K & L are derived and used for lookups into the Transaction Log worksheet. Don’t touch these.
e. Column M is where you define the Type of investment. This is a lookup from the Investment Type list you defined above. This is used in the Portfolio Monitor workbook within the Holdings worksheet.
f. Columns N, O, and P are calcualated given the transactions that you enter on the Transaction Log worksheet. Do not touch these.
g. Column Q is for defining a “user price”. You would do this for securities or assets that are not understood by Yahoo. For example, I like to monitor my total net worth including the value of my house, so I use this field to specify the value of my house.
h. Column R is for defining the “Previous” price for this asset. Again, for things that are not priced by Yahoo, you can define a previous user price, so that your daily P&L calculations on the Portfolio Monitor workbook are accurate.
6. Transaction codes and their descriptions are provided in cell t3. These codes are used on the transaction log sheet. They can be over ridden in case you plan on uploading a list of transactions from your broker, and if they use different transaction codes.
7. The above only needs to be done once, except for step 5, which needs to be performed every time you add a new holding to your portfolio.
8. Next, open up the Transaction Log worksheet. The columns, and their usage is as follows:
a. TxID is a simple numeric identifier for a specific transaction. Ultimately, this may be used to specify what lot to sell, how ever for now it is reference only.
b. Date – The date of the specific transaction.
c. Shares – The number of shares that were transacted. These need to be positive if you buy shares, and negative if you sell shares or sell shares short. Additionally, this needs to be the number of shares in a contract if you buy or sell options. (So, 1 contract typically equals 100 shares.)
d. Security – The security that you are transacting in. This can be anything, but if you want to leverage the Portfolio Monitor sheet to monitor your holdings, then it should be a symbol that Yahoo Finance recognizes. (Again, refer to step 5 above if you want to see this security in the Portfolio Monitor workbook.)
e. Underlying Security – The underlying security for what you have traded. This is useful primarily for options, whereby the security is derived from the value of the underlying security. So, if you buy or sell an option in IBM, the underlying should be IBM.
f. Cash flow – This is the most important piece of this worksheet. If you get this right, then the P&L and holding calculations will be accurate. So, this is the total cash flow (in or out) for the transaction including transaction costs. So, if you buy 100 shares of DELL for 15 a share, with a transaction cost of $10, then your cash flow should be 1490. (100 * 15) – 10.
g. Price – The actual trade price for this security. (For now this is used primarily for reference purposes.)
h. Commission – How much you paid in commissions for the transactions. Again, this is used primarily for reference purposes, but could be summarized thru a report to figure out how much you have paid your broker over the years.
i. Account – The account that this transaction occurred in. This is linked backed to the account you setup above.
j. Cash Account – This is the cash account where the funds came out of to pay for your purchase, or where the funds went to when you sold your security.
k. Notes – Free text field for capturing notes about the trade. I typically cut and paste in the confirmations that I get in my transaction history from my broker. This field is reference only currently.
l. Portfolio Security, Portfolio/Underlying, Portfolio/Cash – These columns are derived, and should not be touched. They allow the transaction log to lookup and calculate the total holdings / activity in a given security in a given account.


In the Portfolio tracker are sample transactions that have been made that illustrate how you can capture and track your investment activities. The following describes each type of transaction that are provided (Note that I am referring to the row in the spreadsheet, and not the transaction ID):

Row 4 – This is the initial investment to fund my account.
Row 5 – Bought 100 shares of Eli Lilly (LLY) Cost was 37.2795 per share, but after my 9.99 commision, the total cash outlay was -3737.94.
Row 6 – Sold a July 2010 option with a strike price 0f $37 on my 100 shares of LLY. Hence, the option symbol in the symbol column, and the underlying stock symbol (LLY) in the Underlying column.
Row 7 – Bought 200 shares of IGD.
Rows 8 – 13 – Received dividends for my IGD holding. Note the use of Ca$h in the security column. This means that the cash received is going into the cash account. Also, by specifying IGD in the underlying will allow me to calculate an “adjusted cost” for IGD. So, an after dividend price adjusted cost. My broker does not provide that, and for dividend paying stocks I want to see both the real cost, and then the adjusted cost given dividends and/or premiums from option sales.
Row 14 – Bought 500 shares of Etrade.
Row 15 – Sold an option on Etrade
Row 16, 17 – This just reflects a symbol change for the option I bought on Etrade.
Row 17 – This reflects the fact that my Etrade option that I sold short expired.
Row 18 – Bought back my Etrade option to close out my short position. (Hence the use of BTC)
Row 19 – Bought 600 shares of URE
Row 20 – Sold an option on my shares of URE
Row 21 – Sold another option on my Etrade holding.