Component Based Development (in Excel)

One of the things that I am trying to accomplish with this site is to build up a tool kit that others can use to build their own Excel based workbooks.  The reason for this, hopefully is clear, in that each and every user has their own idea about what is useful and what isn’t.  So if you don’t care about the regional and market cap based holdings of an ETF, then don’t include them in the workbook that you use to analyze an ETF.

To me this is one of the primary advantages to using the SMF Add-in and Excel to do your investment analysis, that is the ability to define it exactly as you want it.  Now by using Excel, you may not have all of the bells and whistles (in terms of visuals) that you get from the web, but in the end the ability to customize, consolidate (content from multiple sites), and create your own far outweighs the potential loss of “flashiness”.

To demonstrate how this can be done, I’m going to rebuild the ETF Analyzer workbook using the various components that I have already created.  As a backdrop, I realized after I created the initial version of the ETF Analyzer workbook that I was missing some basic yet critical information, so I will use the rewrite/enhancement of that workbook as my mechanism to illustrate how (and the benefits of)  a workbook can be created from scratch.

Step 1 – Starting with the template

The first step is starting with a template.  This is basically the shell into which your components will be dropped and linked.  As stated else where on this site, the value of using a template is to shorten your development time, and ensure a consistency of experience.  When I refer to a consistent experience I am referring to a consistent visual (in terms of layout and color schemes) and functional experience.

As we are going to be rebuilding the ETF Analyzer workbook, I need to download the ETF Template.  Once downloaded I’m going to open up the workbook,”Enable content”:


and then save it as ETFAnalyzerV1.1 in my Workbooks directory.ETFAnalyzerSaveAs


Now if the SMF Add-in was installed correctly, this should be working already.  So, change the symbol or benchmark should update the limited display on the template.

Step 2 – Adding Display Components

The next step is to start adding display components to the workbook.  Some of these are already provided in the template from the Components worksheet.  These are items that are sourced from Yahoo finance and have a different data access pattern than the other components.  But I also want to include additional content that is available in the YahooFundData workbook.  Specifically, I am going to want to pull in the Profile and Holdings content.  So, download that component workbook and open it.

Now one of the most challenging things is figuring out where everything should go within your workbook.  From my perspective, I want the most important content going from the top to the bottom.  I also want to ensure that like content is grouped together.  So, if you are going to show holdings broken down in different manners (by region, asset class, market capitalization, and so on), those items should all be grouped together.  In this case I want to ensure that the high level profile data about an ETF is provided at the top as that is what was missing from the previous version.  Note:  You need to experiment a bit as not all of the components work with different funds.  For example the “Fees and Expenses” component in the YahooFundData workbook doesn’t support  ETF’s, so I don’t want to include that in my ETF Analyzer workbook.  To test this I put a known ETF symbol (spy) in the Ticker cell of the YahooFundData workbook which was cell a1 of the Holdings worksheet.

I also want to leave the chart on the top right of the workbook, so I am going to drop that in first in order to determine how much space I will have left for the other items.  To accomplish this, go to the Components worksheet, and select the chart and header.  This will be from b18:l40.  Then cut your selection, and drop it into cell N8 of the ETF worksheet.  Now I want to bring over the Fund Overview, and Fund Summary components from the YahooFundData workbook.  Fund Overview is going to go on the top left, so copy that from cells f6:h11 of the Profile worksheet in the YahooFundData workbook.  The paste it into cell b8 of the ETF worksheet.  You should get the following warning:

PasteWarning What this means is that the “Ticker” name is used in both workbooks, so you need to decide which one to use.  Click on “Yes” in this instance.  This will then reference the Ticker cell in the ETF Analyzer workbook.  Don’t worry about the column widths for now as we will have a little work in that area later.

Next I’m going to bring over the “Fund Summary” component, so repeat what you did before, but select from cells h16:h17, and then drop them in cell f8 of the ETF worksheet.  Now clearly a textual description of the fund will not fit in a single cell, so we are going to have to merge some cells together so that the display looks right.  To do this, cell cells f9 :l13, right click, Format Cells, Alignment, then check the Merge Cells check box.  This may still not fit exactly, but we will hold off on fixing this for now until we see what the rest of the workbook looks like.  Do the same thing for the component header where you see “Fund Summary”.

I also want pricing data and headlines in there, so I am going to cut and paste those items from the components worksheet now.  Note:  It is critical to cut and paste these components as they are referencing other cells from the data worksheet, and if you just copy and paste, those references will be broken.  When done it should look something like the following:



You can test that all the links are in place by changing the symbol, which should in turn change your entire display.  Again, hold off on the urge to resize the columns as we have many more components to copy in.

Step 3 – Adding more display components

Next I want to add some of the StockScore components.  To accomplish this, as one would guess, open the StockScores component workbook, and repeat the steps for any of the components that you want as defined in Step 2 above.

Step 4 – Still more display components

As the BarChart components are comparable to the StockScore ones, I’m going to add those next.  Same drill, open the BarChartTechnicalAnalysis workbook, and copy and paste the various components that you are interested in.

To be continued…….



Thoughts on Beta

There are some interesting posts about beta on Investopedia, which made me take a little deeper dive into the subject area.  When I think about what beta is and means, I took the following from the Beta: Gauging Price Fluctuations post:

“Beta measures a stock’s volatility – the degree to which its price fluctuates in relation to the overall market. In other words, it gives a sense of the stock’s market risk compared to the greater market. Beta is used also to compare a stock’s market risk to that of other stocks.”

I then played around with the Beta Calculator, and had the following thoughts / observations:

Know how it is calculated

Take a look at the following 3 charts:




(Click the images to enlarge them) Continue reading Thoughts on Beta

SMF Add-in

One of the most useful things that I have found and used when creating my workbooks is something called the SMF Add-in.  This is a free Excel Add-in that allows you to easily access content from the web.  Within it are functions that let you pull down csv data, text delimited data, as well as data scraped from various web sites.  Additionally, if the data you are interested in is not already “defined” through the add in, you also have the tools to define your own scraping rules. Continue reading SMF Add-in

The Toteboard @

Welcome to The Toteboard!  This site is dedicated to providing tools targeted to individual investors, and those that are interested in personal finance.

My apologies to those that are here looking for a copy of the Daily Racing Form.  I’ve borrowed the toteboard term from the horse racing field, where it is used to provide odds on the horses running a race, as one could compare investing in stocks with betting on horses.  Although in horse racing the payouts are clearer, you have fewer choices, and there is always a winner.

Continue reading The Toteboard @