Tracking Spreadsheets

As many of you know, Two Investing has published – and periodically updates – a Google spreadsheet for portfolio tracking.  I started using the original one published by Investment Moats several years ago and later migrated to the Two Investing version.  A new version was recently released due to Google/Yahoo API issues – basically not all data was readily available.  So I spent the last three weeks manually converting my data.  I chose a manual process so I could cleanse data that was no longer relevant primarily due to mergers.

As advertised, I found the new version to be more robust and faster.  But there are a few issues to be aware of.  I did comment on most of these on their site November 30th but figured I’d include my additional findings to provide a heads up to people reading my ramblings.

1. Perhaps you caught it by now but the version I downloaded was missing Telecom as a sector.

This is readily corrected by adding it via the Lists tab.  You also have to update the Summary tab to include it as well.  Update: S&P announced on November 15th the Telecommunications sector will be replaced by a newly formed Communications Services sector on September 28, 2018 with preliminary components announced in January and finalized by August 1st.

2. An error is received (Reference Data) when the company name has an apostrophe (Casey’s/CASY). (I did a manual override).

The override I referred to is ‘IF($D146=””,””,”Caseys General Stores Inc”) which is the name (less apostrophe). Google’s name function includes apostrophes.

3. It appears some ADR issues have data and some don’t. I haven’t figured out the why (maybe the sponsor?) ex. AKO.B does, CMSQY doesn’t.

4. OTC pink sheet data (US pricing/dividends on Canadian issues) not available. ex. HRNNF (corresponds to H.TO)

5. OTCQB market data is lacking. I didn’t check the greys as I own none.

I still haven’t figured it out. Cross (dual) listings work and some ADRs as well.  My work around (for my 7 outliers) is to enter ‘=GOOGLEFINANCE(A100,“price”) in column J on the Reference Data tab (where A100 corresponds to the ticker symbol field of yours) and manually enter the annual dividend in column K.  This sets the override function so the DivPayoutCalc tab and Portfolio tab both update properly.

6) ReferenceData generates ‘error getting data’ (ExDate, PayDate) when the company does not pay a dividend.

Not a functional problem only cleanliness of presentation.

7) (New): Optional use column P – DivCalendar – doesn’t lock to the year as do the other columns.

8) (New – 17 Dec 2017): Column H – ReferenceData – IEX data for ex-div does not reflect current announcements.  Ex: KMB shows the prior 7 Sep 2017 ex rather than the current 7 Dec 2017.

Bottom line I’m pleased with the improvements and improved performance.  It’s been awhile since I’ve had all the fields working and look forward to approaching year end with a minimum of manual adjustments!