Vizzing my Hobby

One of my favourite hobbies is analyzing my investments.  For me, it is not about the money, although that is a nice boost. It is more about the sheer enjoyment of looking at the numbers and finding a quality investment that I can trust for the long term.

I have been doing this for a while.  I am the ‘investor’ for my family.  I make all the major decisions and do all the homework.  I also maintain the budget and calculate the delta to retirement. In this realm, I find most people are very comfortable with their spreadsheets.  A lot of videos feature Excel sheets that extrapolate forecasts. In the past, I have definitely relied on these as the backbone of my analysis but recently when I was discussing with a group of ladies, my daughter said, ‘there has got to be an easier way.’

This is obviously true and I have always touted Tableau as an easier and faster solution.

The first hurdle is obviously getting the data. I have used a few API to import different financial data. I like the API approach in that the data is always up to the minute and accurate.  I use a feed like this if I want to track a stock’s price or something that changes on a frequent basis. 

In contrast, when I am evaluating the stocks I hold in my portfolio, I use the quarterly and annual figures released by the companies as required by the SEC. These documents are generally released in pdf format which does not translate well into a data source for Tableau. The financials are available from a number of different sources though.  Morningstar has a subscription service that not only provides the financials but analysis of the moat, fair value, etc. I do not subscribe to this directly but my broker has the information which allows me to access it and it is downloadable in CSV format. This is obviously very helpful but anyone who has had to struggle with spreadsheets understands that these are meant to be stand-alone products that are human-readable. This means for my purposes, they have extra rows and are not in a machine-readable format for Tableau.

Enter Tableau Prep. Since the format provided by my broker is consistent between companies I am now pretty quick at running the sheets through a Prep flow.  It is not consistent enough to be able to actually copy the step to reuse although it might be close for most stocks as the financial year-end is usually Dec 31 - or Oct 31 for Canadian Banks.

This straightforward Tableau Prep looks clean and simple but it is a little deceiving…. There is a lot of cleaning!

I start by downloading from my broker the 4 CSV sheets I need (Income Statement, Balance Sheet, Cash Flow and Ratios). I can then bring them in as a single data source by calling a wild card union. (the CSVs all end in the stock ticker, currency and file extension).

The clean step merges the metric column because they are named differently on each of the 4 sheets, renames some columns as the date does not come across in the top header, and deletes a bunch of filler lines. Then, I need to pivot the date so it is in one column instead of 4 separate date columns as you would typically see in Excel.

Next, I convert the date column to a date type and the metrics column to a decimal number. Some of the values are blank in the sheet so I have to add a Zero Null calculation so they all appear as 0.

I delete the old metric field and the ‘Files Paths’ field which identifies which sheet the data came from originally. Now I can pivot one final time (rows to columns) to get a column for each metric in my new data source.

This pivot ensures I have multiple fields like ‘profit/loss’, ‘total revenue’, and ‘quick ratio’ instead of having to separate them out with a slow calculation in Tableau.  Something like 

IF [Metric] = ‘quick ratio’ THEN [Value] END

My Prep flow is now ready for a hyper Output so I can bring it into Tableau desktop or Tableau Online.

Currently, I use these metrics as a quick evaluation of whether the value meets a goal and I can show this over time in a highlight table.  

Normally, my preference would be to use something other than a highlight table but I am looking at up to 20 metrics at once and just want to know a Yay or Nay if it met the threshold which I colour code. The upgrade of this analysis to Tableau means I can quickly calculate this all at once for each year and see if the company is trending in the right direction again represented by the colour of the highlight table. Thanks to the ratio sheet in the data source, lots of these metrics are precalculated for me although I sometimes check them from scratch just to ensure my numbers jive. I copy the calculations I need from previous analysis of other companies and use the measures in the current stock’s data.

Believe it or not, this entire process only now takes me about 30 minutes which is a big time-saver over the several hours it used to take me to do in a spreadsheet to get 1 year of data when this now yields a 5-year trend on all the metrics.

OK, now that this is done, I am off to read the analysts reports.