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".

This posting is about the creation of my first data sheet. I'm thinking to keep things organized that I should create a folder for all of these things, so that they're all in one place.

Creating the folder was pretty easy, as all I had to do was to click on Create and specify "Collection". The following screen shot illustrates this:

I created a Data Sheets collection or folder, so that's where I'll be putting all my worksheets that access data from various sources.

Going to start simple, so the first thing that I am going to do is to create a data sheet that provides access to the GoogleFinance functions. I realize this is very easy to do within a sheet, but doing it once and sharing it with my workbooks should save me time in the future.

The syntax for accessing the GoogleFinance functions is quite simple for stocks: =GoogleFinance("symbol"; "attribute");

So the key is you need to know the attributes, and the symbols that you can use. The list of attributes is available here:

https://support.google.com/docs/bin/answer.py?hl=en&answer=155178

There is also a list of attributes available for historical data, and for funds. So for now, my data sheet will be focused on providing access to just equity related attributes.

I cut and pasted each of the attributes into the data worksheet, as well as the description for the item. I have also included a label column for both the attribute and description so as to be able to provide a localized label to the consuming workbook given language as an input.

To do this, I've included a GoogleTranslate function, that takes the text to be translated, from language, and to language as an input. Trying to figure out what should be used as the "to" and "from" parameter, and the help text tells me I need a valid ISO-639 language code. I found a list of them here:

http://en.wikipedia.org/wiki/List_of_ISO_639-1_codes

that appear to work. In practice I'm going to assume that cell d1 accepts a valid language code, so validation will need to be done prior to linking this data sheet to another workbook. I've also hard coded the English labels for some of the attributes as code values like "PE" and "EPS" did not translate to different languages.

To make cell referencing easier in the workbooks I create in the future, I defined names for each of the various cells. This way when accessing this worksheet from another, you can reference the name of the cell rather than the row/column. Point being referencing "price" is much cleaner and more intuitive than referencing "c4". The following screen shot illustrates how this was done. The UI was a bit painful, and will have to evaluate whether this is worth the pain for large data sets, but ...we'll see.

In hindsight, not sure how much if any time this will save me, as it is just as easy to define the function as it is to reference a cell, but oh well. In the long run it may give me a consistent interface with the other Data sheets that I plan to create.

Anyways, 30 minutes or so later, and we're all done. I've saved this worksheet in my Data Sheets collection, and it is available here:

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

I was hoping to share / embed this into the blog, but couldn't figure out how to do it. The documentation I saw didn't jive with the options I was given from within the workbook.

One more time.....Google Docs Spreadsheet

OK, it's been about two years since my last post, so looking to add these things a bit more often now. This time around though, for say the next year or so, I'm going to focus on just using Google Docs Spreadsheet. (Step 1 for Google is to come up with a catchier name.) I've always said that Microsoft Excel is the "great equalizer" in that it allows us semi capable technologists the ability to create tools / applications that come close to rivaling applications created by the super geeks. It also gives others the ability to "tweak" it to their liking. Something that is not possible with online solutions. Question now is whether Google Docs Spreadsheet is ready for the prime time.

Anyways, having switched to Google Chrome nearly two years ago (never once regretting it), figure it is time to cut the cord and see if Google Docs Spreadsheet is any where near Microsoft Excel. I'm guessing no, but key question then will be is it good enough, and if not what needs to change so that it is. Again, what interests me are the gadgets (that can be shared onto iGoogle), on the fly translations, the data retrieval tools, and the online nature of the tool. As in, being able to provide access to my spreadsheets from virtually any device opens up a huge user base. Yes Microsoft has an online version of Excel, but it's pointless if you can't leverage VBA. (I don't think you can, but someone correct me if I am wrong.)

In looking at the spreadsheets I have done in the past, you can break each of them down into 3 parts:

- The visuals / display

- Calculation / business logic

- Data retrieval

As I start my journey I'm thinking that I'll step back and try and do this the right way this time. So, I'm going to focus on a few things out of the gate. Specifically, I'll be defining and creating the visual experience that I want (think CSS in html terms) in terms of a series of front end or visual templates for my workbooks. So, defining color schemes, fonts, screen layouts, etc.

On top of that, and probably more importantly, I'm going to focus on creating a series of "data" worksheets that can be used / accessed to provide access to various data sets.

Finally, I'll determine whether I need to define a separate set of calculation worksheets that manipulate data from the data sheets for display usage by the visual sheets.

In general, experience has taught me.....focus on getting the data, doing any required calculations and only then focus on the visual experience for the user. So, step 1 is to create a series of data sheets that I plan on using. As my interests lie in fantasy sports (especially baseball) and investing, my first plan of attack is to create a series of data sheets that provide access to that content.

Stock Screening in Excel

Just finished creating an Excel based stock screener. The screener leverages content from http://www.finviz.com/, a truly phenomenol site for investing content, and allows you to leverage the power of Excel by enabling you to filter on each column in the screener. Approximately 80 columns of data are available, including real time price and volume data. See the results and impact of your filter changes immediately. The workbook also allows you to calculate Market Cap weighted aggregates at the industry and Sector level off of the same content that you can screen on. As a result, when you're done screening you can compare your results to aggregate values at the sector or industry level.

Looking ahead, a future version will include bubble and or scatter plots on variations of content allowing you to see visually where the outliers are, as well as conditional formatting of "good" and "bad" values, and possibly multi factor rankings across the data items allowing you to "score" the best investment options. Take a look, and let me know what you think:

http://www.toteboard.net/Models/ZipFiles/StockScreener.zip

Note: This is a rather large file, and the aggregate calculations are quite "expensive" in terms of CPU time. So, have a little patience when opening, and be warned about the possibility of locking up Excel for short periods of time. I tried, so I recommend against calculating aggregates for all the sectors and industries in this workbook.

SMF Addin

If you're looking to create spreadsheets that manipulate information about stocks that you can buy or sell, then the SMF addin is the tool for you. It is extremely powerful, and provides access to thousands of individual data items about a company. The addin is free, and there is a yahoo group that has been set up to provide examples and a forum for questions and answers about its use.

The group is at the following location:

http://finance.groups.yahoo.com/group/smf_addin/

I've modified two of my spreadsheets to leverage this tool. One is a portfolio sheet that allows you to monitor your P&L through out the day. The sheet also allows you to look at other publicly defined portfolios. So, industries, sectors, adr's, preferreds, closed end funds, and industry identifiers. It is available at:

http://f1.grp.yahoofs.com/v1/IGz7Siwpdpq0faRp5vvRLTCdBoyMIXJSB9shJbiQkByoUllxhtk5xsNwQ6dHcQo38H6qbVdzQzFO2tHhtfdyX38GiNLsjCCU5u5zFsaLdbM0/Uploads%20by%20forum%20members/PortfolioSheet.201.zip

The other spreadsheet is a company overview sheet that allows you to look at critical investment information about a specific company. It is also integrated with the portfolio sheet defined above in that it allows you to cycle thru the various companies that you own and are tracking thru your portfolio sheet. It is available at:

http://f1.grp.yahoofs.com/v1/IGz7ShET3-C0faRp4mqqrh_5BjTRIJejQd-pEAw1q5jjh6mMW1V1WcVaHySYO0BcsOjsM0fobwgeHbnpVakIy-jtO9J2ffyhsXIS6wgAsYe3/Uploads%20by%20forum%20members/Company%20Overview%20v2.0.xls

If you get a chance take a look at these, and let me know what you think.