Tuesday, December 22, 2009

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.

Friday, December 18, 2009

Industry Overview Workbook

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.

Friday, December 11, 2009

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.

Portfolio Monitor Usage Instructions

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

Tuesday, December 8, 2009

Fantasy Basketball Spreadsheet for ESPN


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