Portfolio Tracker - Usage Instructions (Google Docs)

Today I imported into Google Docs the Portfolio Tracker workbook that I created previously in Excel. The only thing I had to do prior to importing was to un-hide the columns in my transaction Log worksheet. Without doing this, there was no way that I could figure out of un-hiding them once they were imported. (From what I've tried you need to select the two columns that surround the hidden columns to unhide them. If your hidden columns are to the far right of your worksheet there is nothing to select, and hence you can't un-hide the columns.) Besides that, everything worked fine as I did not use any VBA functions as part of this workbook.

Anyways, I created this portfolio tracking solution for a couple of reasons:

1. The yahoo and google portfolio tools only appear to support equities and mutual funds, and as I write covered calls, I couldn't track the call options that I wrote.

2. I wanted to get the "full picture" for my trades by factoring in call options that I wrote as well as dividends. As a result, the Portfolio tracker that I created includes an "adjusted cost" which is the cost of my position after adding back in proceeds from dividends and call options that I wrote.

I've also included sample data in the tracker so as to provide a useful illustration of how the tool could be used to track transactions across your portfolios. If you're like me, you may have holdings in say an IRA, a 401k, a SEP IRA, and a taxable account. Tools like Mint.com are great for consolidating your account, but if they don't have access to all of your accounts, you can't get the full picture of your holdings.

Note: Next up will be the Portfolio Monitor which will allow you track the P&L of your holdings in real time. For now this workbook just lets you capture and track your transactions.

Instructions on how to use the Portfolio Tracker are provided below:

1. Open the workbook, then go to the Setup worksheet.
2. 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.
3. 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.
4. 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. So, 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.)
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 and other un-quoted holdings, so I use this field to specify the value of those holdings.
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.
5. 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.
6. The above only needs to be done once, except for step 4, which needs to be performed every time you add a new holding to your portfolio.
7. 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 5 – This is the initial investment to fund my account.
Row 6 – Bought 300 shares of Dupont, total cash outlay after transaction costs was 11420.91.
Row 7 – Sold a October 2010 option with a strike price 0f $38 on my 300 shares of Dupont. Hence, the option symbol in the symbol column, and the underlying stock symbol (DD) in the Underlying column.
Row 8 – Bought 200 shares of IGD.
Rows 10-11 – Received dividends for my DD, and 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.

Other usage notes:

1. Use the filters on the Transaction Log worksheet in row 3 to view specific transaction types, securities, or transaction in a specific account.

2. Use the "Notes" column to capture free text about the specific transaction. This may include rationale as to why you made the trade.

3. Un-hide columns T thru Y to see the functions / calculations being used.

4. On the Setup worksheet a Position of 0 in column N, means that you have closed out that position. In this case column P will show you how much you made / lost on the trades relating to that security.

5. If you still hold a position column O will show you the cost basis give purchase / sales of the security, while column P will show you the adjust cost basis that factors in dividends received and premiums from options that were written.

This workbook is available here:

https://docs.google.com/spreadsheet/ccc?key=0Aj8CeiznlFexdG9ITlFUWnpZV2hiUzZ4ZU9uMkJQTVE

And is also available as a template here:

https://docs.google.com/templates?type=spreadsheets&q=portfolio+tracker&sort=hottest&view=public

Look for the one entitled "Portfolio Tracker".

Charting Dividend Stocks

Take a look at the following chart from Yahoo Finance. In it is a relative price comparison between SPY (SP500 Exchange Traded Fund) and CMO (Capstead Mortgage). What does it tell you? What it tells me is that the price of SPY has gone up nearly 25% over the past 2 years, while the price of CMO has gone up about 5%. If one didn't know any better, you would think that SPY was a better investment than CMO. What this, and virtually every other chart you see at Investing sites on the web is missing is the effect of dividends on your investments. Only after you factor in dividends and other corporate actions can you fully understand which of CMO and SPY was the better investment.

To study and demonstrate this I put together an Historical Price Comparison workbook through Google Docs. The following details the steps I went through to accomplish this.

Step 1 - Get Data and Perform calculations

I created a Data worksheet where all of my data access and calculations are to be performend. To get the requisite data, Yahoo Finance has a nice utility for downloading both historical prices for a stock as well as dividend payments details. A sample url for this data is as follows:

http://ichart.finance.yahoo.com/table.csv?s=SPY&d=1&e=18&f=2012&g=d&a=0&b=29&c=1993&ignore=.csv

The following are the parameters and their descriptions for this call:

s- symbol

e - end day

d - end month

f - end year

a - start month

b - start day

c - start year

g - optional parameter that you can use to request just dividends (g=v)

To access this data I leveraged the ImportData function in cell a2 for the first symbol, and in cell m2 for the second symbol. The url's that are leveraged in these function calls are built from the start date defined in the Historical Price Comparison worksheet as well as the two symbols that are defined there.

I leverage two additional ImportData calls in cells y2 and aa2 in order to get dividend information for the specified symbols. The results returned are the ex-date of the dividend as well as the dividend amount.

In order to build my comparison chart that includes dividends, I decided to define an initial investment amount (100,000) as the default, and then calculate how many shares that would purchase for me on the start date. On top of that I assumed that all dividends would be reinvested on their ex-div date. (Note exactly right as you would only be able to reinvest the dividends the day after the payment date, but hopefully close enough to prove a point here.)

In order to derive the initial and subsequent shares after reinvesting dividends, I leveraged the following function in column H (copied from cell h3:

IF($A3='Historical Price Comparison'!$H$1,('Historical Price Comparison'!$E$1/$E3),If(Isnumber($I3),($H4+($I3*$H4)/$E3),$H4))

What this formula does is the following:

if the price date equals the starting date, then the numbers of shares is equal to the initial investment value divided by the closing price. It also checks to see if a dividend was paid on that date, and if so it adds the reinvested dividends (in terms of shares purchased) to the number of shares that were held on the previous date.

To determine the div amount in column i I used the vlookup() function to pull in the dividend value from column Z or AB where the dividend date equals the price date.

The market value (column j and v) was calculated simply by multiplying the number of shares by the closing price for the given date.

I also calculated the market value without dividends applied in columns x and l by simplying multiplying the close price by the shares held, but the shares held did not include the effect of reinvested dividends. I did this so as to be able to compare the effect of reinvesting dividends on the market value.

Step 2 - Creating the visuals

The Historical Price Comparison sheet does nothing more than reference the appropriate cells in the Data worksheet, and then in turn graphs the results.

The graph includes four line charts for the two symbols. One line shows the market value with reinvested dividends, the other shows it without. The following is the result. As you can see, with reinvested dividends CMO was a better purchase than SPY.

When using this sheet you need to ensure that your start date is a valid trading date. So, no weekend dates and no holidays. (I probably could have put the validation logic in there, but, figure my time is best spent else where.)

This spreadsheet can be accessed here:

https://docs.google.com/spreadsheet/ccc?key=0Aj8CeiznlFexdEVrLXlQLTdTUF9HOGgwd1UyTnJMM0E

You can also search for it in the template section here:

https://docs.google.com/templates?q=historical+price+comparison&sort=hottest&view=public

Take a look, and let me know what you think.

Company Overview - Google Docs version

Previously I spent some time using the SMF Addin to create a "Company Overview" workbook. This workbook pulls in financial data, time series (price history) data, and news headlines from Yahoo Finance and is available here:

http://www.toteboard.net/ - then there is a link on the right hand side for the Excel version of the Company Overview

Given my new focus working with Google Docs Spreadsheet, I figured I'd see if it was possible, and if so, how difficult it would be to replicate this workbook from Google Docs. The following are the steps that I went through to accomplish this.

Step 1 - Replicate the Data sheet.

As part of the Company Overview workbook, I leverage a "Data" worksheet as a mechanism for pulling in data from Yahoo Finance. In my Excel version, I leverage a function defined in the SMF Addin (RCHGetYahooQuotes)to pull in CSV data from Yahoo Finance. Given a series of paramters, this function makes a call to download.finance.yahoo.com to pull in financial data for a specific company. For example, the following URL is leveraged in this sheet:

http://download.finance.yahoo.com/d/quotes.csv?s=EFA&f=snll1cc1t7va2bapomwj5j6k4k5ers7r1qdyj1t8e7e8e9r6r7r5b4p6p5j4m3m7m8m4m5m6k1b3b2k2c6c8&e=.csv

Take a look here to get a definition of the fields that are provided thru this function:

http://www.gummy-stuff.org/Yahoo-data.htm

The nice part about Google docs is that you can leverage the ImportData function to pull in this data. So, no custom function is required to pull in csv data. To pull this data into the data sheet, I've concatenated the base url, the symbol for the company, and the field list together in cell c4. I then use the concatenated values from cell c4 as the input parameter to the ImportData function in cell h8. I put it there to replicate that same starting position as in my Excel version. The result of my function is a list of data items beginning in cell h8 going thru cell BE8. I then cut and pasted additional calculations into cells BF8 thru BN8. (NOTE: Some of these functions that worked in Excel threw errors in Google Docs as numbers coming from yahoo finance as say "+5.15" were treated as strings, while in Excel they were correctly treated as numbers.) Values like -4.53 were treated correctly as numbers. To address this issue, I had to use the following: value(substitute(M8,"+","")). This simple function replaced the + sign with a null string, and then treated the resulting set as a numeric value. I had to do this for any function that could return a positive number.

The next set of data that I had to move over were the calls to get the historical prices so that I could create a price chart. To get pricing data, the following url is called from Yahoo Finance:

http://ichart.finance.yahoo.com/table.csv?s=EFA&g=d&a=10&b=5&c=2008&ignore=.csv

To simplify this, I just hard coded the start date, but I build the url give the symbol currently being selected (this is done in the Data sheet in cell H36). Again, the ImportData function is used in starting in cell H38 to pull in this data. I then do simple cell referencing in columns O and P to simplify the charting of the data.

The final data set that we need to pull in is a list of headlines for the selected company. In my Excel sheet I leveraged the following url:

http://download.finance.yahoo.com/dnh?s=DUK&n=20

But in the Google Docs version I switched this over to leverage an RSS feed from Yahoo. The url for this is defined in cell B35 of the Data worksheet. Example as follows:

http://feeds.finance.yahoo.com/rss/2.0/headline?s=EFA&region=US&lang=en-US

In this case, the ImportFeed() function was leveraged in cell B38 of the data sheet using the above url as the input parameter.

Step 2 - The Setup worksheet

One of the things I like to do, on top of having a worksheet dedicated to data, is to have a setup worksheet. The intention here is to provide a place where the user can go to "setup" their specific options for the workbook. In my Excel version of this solution there was not a whole lot there, but in the Google docs version I wanted to provide the user with the ability to see the relevant data and labels in the language of their choice. To accomplish this I defined each of the labels that I was going to use in the "Company Overview" worksheet in column B. So, these were the English abbreviated versions that I wanted to display. I then spelled out each of this labels in column C, with the intention of using these as an input to the GoogleTranslate() function. Column D is where I leverage the GoogleTranslate() function. Input to these calls are the labels defined in column C, and then the language as specified by the user in cell C2. The resulting translations were then returned in Column D. I then have simple function in column E that uses my abbreviated labels from Column B if the language is in English, else it uses the translated values from Column D. The value here (as you will see on the Company Overview sheet) is that the user can get the labels in the language of their choice. Now, I have no idea how good the translations are, but if they are close, I am hoping that this will be good enough.

Step 3 - Build the display worksheet

The final step is to build out the display of the data on the Company Overview sheet. Now this differs a bit from my Excel version, but the concept is the same. The difference is that I leverage the Setup sheet to get the translated labels for the display. The actual data is referenced in exactly the same way. (Note: To speed the building of the Google Docs version I tried to cut and paste the functions from my Excel workbook. I had a problem though when trying to cut and paste multiple cells at a time as the cell referencing got messed up. To work around this I cut and pasted a single cell at a time.) For display purposes, I repeated the steps to remove the "+" sign from postive numbers.

For the Headlines section I leveraged the Hyperlink function to allow the user the ability to click through to the story matching the headline.

For charting of the pricing I leveraged the Interactive Time Series gadget. One problem that I found though was that when trying to print the worksheet, this gadget anyways was not printed. As a result, I created a simple time series chart and placed it behind this gadget to support the printing use case.

That's about it.....all told it took maybe 3-4 hours to move this over. Good news is that I probably was able to carry over 90% +/- of the functionality that I had in my Excel version. Then, on top of that I was able to add in the ability to provide a localized solution on the fly. So, if English isn't your first language, via the Setup worksheet you can change the default text language on the fly. (Note: I could probably get closer to 100%, but in the end, it was good enough.)

The end result in Chinese is displayed below:

You can sample this spreadsheet at:

https://docs.google.com/spreadsheet/ccc?key=0Aj8CeiznlFexdGctenNmdlctSGpxNUdZLWk5OUlZV3c

or you can leverage the Company Overview Template here:

https://docs.google.com/templates?q=company+overview&category=2&type=spreadsheets&sort=rating&view=public

Finally given what I experienced, if I could......, I'd make the following suggestions to the Google team to improve the spreadsheet application of Google Docs:

1. Fix the cutting and pasting of formulas from Excel to Google docs.

2. I tried importing my Excel document, but this failed, so I had to do it worksheet by worksheet, and then cell by cell.

3. Provide the ability to protect an entire sheet, except for a specific cell. Ideally the template would be entirely protected, and all the user would have the ability to change would be the symbol they type in to cell c2, and the language they can select in the Setup sheet.

4. Provide for the ability to hide column and row headings. You can hide everything else, so why require that you always see A, B, C, .... and 1, 2, ,3 etc. Without this and with everything else hidden you may not know that this is a spreadsheet template.

5. I had problems when trying to pull in 10 years worth of price history data. The spreadsheet stated that it was "working" but it never seemed to finish, although shutting down and restarting the browser, and coming back to the workbook everything seemed to be saved correctly.

6. It'd be nice if there were a series of UI controls like combo boxes or list boxes that you could use. The data validation function that I used for the language is a close approximation to a list box, but other controls would be extremely useful and would provide for a much better UI.

7. Gadgets should be printable if at all possible.