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!

Advertisements

6 thoughts on “Tracking Spreadsheets

  1. SR, Has this all started because of Google Finance eliminating their portfolio tracking in mid November? I really miss it. I have since switched my portfolio over to Yahoo finance, but do not like it as much. It’s kind of slow and clunky. Too many adds slowing it down I think. Tom

    Liked by 1 person

  2. Yes and Yahoo was a little flaky as well with their changes (and merger). The Two Dividend one broke as well and I was trying to add what I wanted in as automated a fashion as possible. My struggle was with the ex-div date which Two Dividend solved. My next stop was to look at Morningstar’s but I didn’t make it that far. The issues I found are probably not broadly experienced by most investors, however the Telecom sector SPDR was eliminated. Since S&P and MSCI control the sectors, their November 15th announcement that Telecom would be eliminated effective Sep. 28, 2018 and renamed Communications Services was what I was looking for. Components of the new/(renamed) sector will be announced in January and are expected to include cable and internet companies.

    If you’re still looking, I would recommend Two Investing’s updated sheet.

    Like

  3. I can’t take the credit – all the accolades go to Two Investing. If you have a problem with my work-rounds let me know. The only issue I’ve encountered with Two’s work is if Alpha Vantage or IEX are offline parts of the sheet don’t function. I saw this happen on a weekend once. My guess was system upgrades or backups.

    Like

  4. Thanks for the feedback, seekingreturns. I’ll look through your post and try to implement your suggestions.

    I’ve tried to allow as much automation as possible using IEX and Alpha Vantage, but am at the mercy of their service. I find the ability to manually update prices and dividends very helpful in my personal implementation of the portfolio spreadsheet.

    Scott

    Liked by 1 person

  5. Hey Scott and all in all a great sheet. The manual workarounds work fine, only (like you) my preference is greater automation. My suggestion for #8 would be to provide a manual entry field. If IEX data is equal or later use IEX else use the manual entry. #7 is minor but would allow a historical view. The others only give this old guy something to complain about 🙂

    Thanks for the response!

    Like

Comments are closed.