Just Spreadsheets

Company Overview - Google Docs version

2012-02-13T19:52:00.000-08:00

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.

2012-01-28T09:26:00.000-08:00

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

2012-01-28T08:58:00.001-08:00

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

2009-12-22T11:27:00.000-08:00

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.

Industry Overview Workbook

2009-12-18T09:09:00.000-08:00

Just finished creating an Industry Overview Workbook. This was in partial completion state for quite some time, but with the help of Randy's SMF Addin, I was able to finally finish it off.

Purpose of this spreadsheet is to provide a high level view into the performance of various US based industries. The workbook is made up of 4 worksheets.

The first worksheet is the Industry Monitor worksheet. This allows the user the ability to monitor the performance of various industry specific "indexes" that have been created by Yahoo. There are currently 215 of this indexes and you can monitor a real time price for them. These indexes are generated by rolling up market cap adjusted pricing of the underlying stocks that make up the index. As part of this monitor you can also see a selected set of aggregates for the given industry. A filter is included in this workbook to enable the user to drill down to the industries of interest given their specific criteria.

The second worksheet is an Industry Visualizer worksheet that allows you to see a heat map of the various industries. A simple red (for down) and green (for up) set of conditional formatting is included on this page, which allows you to see at a glance what industries are up/down for the day.

The third worksheet is an Industry Constituent worksheet which allows you to look at the individual stocks that make up the selected industry. You also have access to see sector aggregates, for the sector that the industry belongs to. Again, filters are turned on for this worksheet allowing you to drill down to a specific set of constituents that you are interested in.

The final worksheet is an Industry News worksheet that allows you to access headlines (RSS feed) from Yahoo for the industry of interest.

In totality, this workbook should give you a good picture about what is currently going on within a specific industry. Unfortunately, I could not find data that would allow you to get a historical perspective on whether the industry was doing better or worse than it has done in the past.

This workbook is available at:

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

Feel free to provide your comments about this workbook on the blog, including enhancements or any bugs that you find within the workbook.

Portfolio Tracker Usage Instructions

2009-12-11T18:31:00.000-08:00

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.

Portfolio Monitor Usage Instructions

2009-12-11T18:25:00.000-08:00

Installation Instructions:

1. Copy the Portfolio Monitor.xls file to the c:\Program Files\Toteboard directory. If that directory does not exist, create it first.
2. Note: If you are not using the “SMF Lite” version, then the smf_addin.xla addin must be installed first. Refer to the smf_addin group at: http://finance.groups.yahoo.com/group/smf_addin/ for directions on how to install the addin. (The PortfolioMonitorSMFLite.zip file does not require the SMF Addin.)

User Guide: The Portfolio Monitor should be so easy that you don’t need a user guide, which is why I’m trying to keep it all on 1 page.

Step 1: The first thing you’ll need to do is to determine whether you want to input each individual transaction, or just a summary of your holdings. If you want to input individual transactions then:
a. Go to the Setup worksheet, and click on the User Portfolio button.
b. Click on the “Use Portfolio Tracker” check box at the top of the data form.
c. Download and install the Portfolio Tracker workbook.
If you want to enter and maintain a summary of your holdings within the Portfolio monitor, follow the directions below:
a. Go to the Set up Worksheet.
b. Click the button to define your investment types: Upon clicking the button you should see an area of the worksheet where you can add your definition of Investment Types. 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. You can define up to 20 different types of investments.
c. Once done with Investment types, click the button to 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.
d. Once you define your Account List, click on the button to define your target allocations. You can either use the ones pre-defined within the workbook, or you can generate your own. These target allocations are used by the Holdings worksheet to compare to your actual holdings. The difference is then your variance or “drift” from your target.
e. Next, click on the User Portfolio button where you can begin to define the holdings in your Portfolio. (Note: This workbook uses Yahoo for its quote data, so ensure that you use symbols that Yahoo recognizes.) This shouldn’t be a problem for most common stocks and mutual funds, but for preferred stocks and options, you may need to be careful. For things like cash, real estate, or items that Yahoo does not provide a quote for, you can preface the symbol with an “*”, this will ensure that you will not get a match when the workbook tries to get data from Yahoo. Anyways, from here, you can:
1. Type your holdings directly into the worksheet.
2. Run the “Data Form” for data entry (you must use this if you are going to delete any of your holdings.) To use the form, click on the Data..Form menu option. (You’ll lose the referencing otherwise.)
3. Cut and paste your holdings from another spreadsheet or application. Note: There is validation turned on at the field level, so make sure the data you’re cutting and pasting conforms to the column headings.
f. Next, if you want to monitor a “public portfolio” in addition to your own private one, click on the “Public Portfolio” button where you can define various filters to construct your own. Once you select your filters, click on the “Update” button to build your symbol list. (Have a little patience, as depending on whether you are toggled to see your user defined list or not, it may go out to Yahoo and get all the data for your defined symbol list.)

Step 2: Click on the Portfolio Monitor worksheet, and view the results. You will get totals for columns N, O, and P, so if you want to see aggregate data like holdings or P&L for your portfolio, select those column headings in one of these 3 fields. The data that is displayed under the columns can be changed by clicking on the individual column headers. This should then display a drop down arrow that you can click on, which will then allow you to select another piece of data. Selecting the “Auto Update” checkbox will trigger automatic updates from Yahoo into your spreadsheet. These are currently triggered to be run every 10 minutes, and when anything in your symbol list changes. As in, adding a security to your portfolio, or defining a new Public Portfolio. Click on the “User Defined Portfolio” checkbox to toggle between your own portfolio and your Public Portfolio. Click on the down arrow in the “Links” list box to set the hyperlink value within the “Description” column. This will then allow you to link to the corresponding destination using the appropriate context of the security you click on. In other words, if you click on “International Business Machines” for Quotes (Yahoo) it will pull up IBM in a Yahoo quotes page. This is a very, very powerful feature.

Step 3: Click on the Portfolio Visualizer tab to get a visual display of your portfolio or selected Public Portfolio. Change the visualized item by clicking on the down arrow next to the “Item” list box. Change the lower and upper boundary to modify the display. Items above the upper boundary are green, items below the lower boundary are red, items in between the boundaries are white. Min, Max, median and average values are provided to help you construct your boundaries.

Step 4: For your user defined portfolio, click on the Holdings worksheet. This worksheet will then provide you with a “holdings” pie chart next to a “target allocation” pie chart. These charts will then change given what you select to plot in the “Allocation by” list box.

That’s it. Remember, resist the temptation to change your holdings on the Portfolio Monitor sheet, and do it on the Setup worksheet.

NOTE: With the introduction of the Portfolio Tracker workbook you can now monitor 3 different types of portfolios. First off, the publicly defined ones that you can define with various criteria (Country, Sector, Security type, etc.) Secondly, the user defined portfolio or watchlist within the Setup sheet., when you uncheck the “Use Portfolio Tracker” checkbox. Thirdly the user defined portfolio that is c

Fantasy Basketball Spreadsheet for ESPN

2009-12-08T19:11:00.001-08:00

I've just gotten into fantasy basketball again. Last I played was back in the late 90's, but I've found that it is much more interesting and challenging than Fantasy Baseball. I've been using ESPN for this, and am playing the Rotisserie style. This is where they score each of the 10 teams based on how they are doing in 8 different categories. As part of these, and to keep things competitive they provide a limit on how many games can be played by the players on your team. (820 total games.) Anyways, one of the things that bothered me was that I couldn't tell how I was doing relative to the other players. So, as you can see in the screenshot to the left, the guy in first has played more games than everyone else, and is way over the "pace" for the league. This means that once he hits his 820 game limit, he no longer accumulates points. So my question was, do I need to take drastic steps to change my team, or will I eventually catch him once he hits the 820 game limit.

Although I could estimate it, I wanted to quantify how I was doing, in terms of averages per game vs. the leader and all others in the league. To accomplish this, I created a spreadsheet that pulls in all the relevant information from my league, and then thru a series of calculations I can see my averages and projections vs. the others in my league. This is a little rough right now, but it is available on my web site completely free at:

http://www.toteboard.net/Models/ZipFiles/FantasyBasketballESPN.xls

As of now, this just supports 10 team rotisserie leagues registered with ESPN. Depending on interest, this could be enhanced, to support a h2h league, or leagues from other providers (CBS, or Yahoo for example.)

The following will tell you how to use it, and what you can do with it.

Use Cell C8 to specify your league number. You will be able to see this on the address line. For example, one of the leagues I'm in is number 130475.

Use Cell C8 to specify the team number. By selecting a team, the spreadsheet will highlight the relevant rows of the team in green. The team you select is also used in the heat map area that show you the categories that you need to improve on to move up in the standings.

Cells B12:L23 are used to illustrate the current standings of your league. So, who has how many points for each of the 8 categories in the league.

Cells M14:N23 are used to illustrate the average PR (player rank over the last 15 games) for the players on your team. This is used to tell you who is hot, and who is not. You can also see the average % ownership value for your team. This tells you what % of teams in all the ESPN leagues own a specific player. This gives you an indication of the talent level on each of the teams.

Cells P8:W9 are used to define and control the heat maps that are displayed below them. Use cells P8:W8 to define the ranges that are used for the "Season Statistics" heat map. For example, specify the value of 20 in R8 if you want to display the teams that are ahead of you by less than 20 3 pointers. The per game values in row 9 are used to control the display of the Per Game Statistics heat map. Again, if you want to see the teams who are averaging less than .2 rebounds per game more than you, put .2 in cell s9. (Details on the heat maps below.)

Cells P12:W35 are used to display heat maps for each team and category. The season statistics heat map shows you your current standings relative to everyone else. The colors are as follows:

Red - Teams that are behind you (the team specified in cell c9) in the specific category. Red is intended to indicate "beware".

Green - Teams that are ahead of you by less than the values specified in rows 8 or 9 as defined above. Green is intended to indicate short term moves that you could make to improve your standings.

Yellow - Teams that are ahead of you by more than the values specified. Yellow is intended to indicate where you are behind by more, which in turn means it will probably take a long time to move up in the standings.

The "Per Game Statistics" heat map shows your current standings but with a per game average. This is probably the more important of the two heat maps. In the leagues I am in, the two leaders are doing well in the standings, but they are primarily doing well because they have played more games than everyone else. So, I use this area to know if my per game averages are better than his, which in turn means that I will eventually catch him when I make up the game differential. For example, I may be down 10 blocked shots in the standings but he's played 20 more games than me. But if I average .6 blocks per game, and he is only average .5 blocks per game, I know I will catch him without making any major moves.

Cells B24:L35 are used to illustrate what the standings would be if they used per game averages. Again, this is a more telling result than the overall standings. In one of my leagues someone is up by over 15 total points, but the per game average standings illustrate that the guy in 4th will eventually take him once he makes up the games.

Cells B36:X335 are used to illustrate weekly standings. These standings can be either standard or per game averages depending on how you check the box in cell d36. The value here is to see your trend over time. So, you may have made some major moves in week 2, so it will be important to see if those moves are paying off, and if you are improving over time. You can also see how others are doing, as well. So, someone may have started off well, but are cooling off due to some key injuries. To simplify the creation of this sheet, I included an area for each week, of the year. Recommend you hide the row if the week has not been played yet, or you can hide the rows of weeks that you don't care about any more.

Cells B336:X361 are used to drill down into a specific category. So, Blocks, or Steals depending on what you select in the dropdown list in cell d336. This will display either the per game averages, as selected previously, or the overall totals by team and by week for the item that you selected. These weekly values are charted, and will allow you to see a trend for your or the other teams in a specific category. So, are your blocks per game improving, and if so, what is the impact on your FT%.

Again, the workbook is a little rough, and just the part of what I want to do overall. If you have enhancements requests or changes or you find bugs with the workbook, please comment on this blog, and I'll do what I can to address the issues.

SMF Addin

2009-11-11T18:41:00.001-08:00

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.

Google Docs

2009-11-04T15:33:00.000-08:00

I've been using google docs to create some online spreadsheets lately in the hope that they'll be an online alternative to Excel Spreadsheets. Frustration reigned......simple tasks like cutting and pasting, automatic updates of cell references after inserting or deleting columns or rows were problematic. All those things you take for granted in Excel, just aren't there yet. There are some good things though, and for simple spreadsheets having them immediately available to all online is a really nice feature. They also have some interesting "gadgets" which you can use to graphically display your data. The interactive chart, text cloud, and the world map were three things that I used. Problem with all of them though, is that they don't provide access to enough properties for the gadgets. With the world map, I was able to create a "heat map" that illustrated the % price change for foreign exchange rates between two user defined dates. Problem was I wanted my world map to show red if the rates went down, and green if they went up. Only option I had was white for bad, orange for good. I was able to get something up and running in a pretty short time frame though, so take a look if you like:

http://www.toteboard.net/Models/WorldForexOverview.html