How I'm planning to track my dividends

I created a google spreadsheet template for tracking dividend income. Thought I’d share it here. This is identical to dividend diplomat’s spreadsheet. Been a big fan of their dividend income journey. My goal is to do the same - grow my dividends with sound /dividend champ companies year-on-year.

Disclaimer: These aren’t my actual holdings. :stuck_out_tongue:

6 Likes

One adjustment I’d probably recommend is aligning the dates to the tax year to help with the tax return (assuming it’s a general account, not an ISA).

2 Likes

Surprising that the dividend yield isn’t on there. Is their strategy to never sell, even when a yield becomes really poor?

Shameless plug time, apologies.

The company I work for just announced this partnership with Microsoft. You can now pull real-time data directly into Excel with native functionality. Given Office 365 is near par with Google Docs I think you should all give it a try :slight_smile:

1 Like

Does it include non-US stocks, etfs and funds? Google Docs hasn’t seemed to be able to get the prices for UK-domiciled mutual funds in the last year…

Yes. I’d estimate you can get 90% of what Freetrade has to offer

Google took out UK mutual funds.It used to be MUTF_GBP:name_of_the_mutual_fund

@adavid Yep, my spreadsheets have been sad since March 2018.

@ytsruh would your thing get price for eg a Vang lifestrategy mutual fund, (say ACDT / B4PQW15 / GB00B4PQW151 )? It’s been a long time since I used Excel but would give it a try. (I’m asking you because I don’t see a way to get the Stock “bit” in the Data menu when I’m using O365.)

Just a random one your vanguard price seems a little out:p don’t you mean £43 instead of £0.43

I tried to add Aviva but didnt seem to find it?

Yes, you are correct. This is a template. You can replicate it if you want and edit it to fit your requirement. These aren’t my real holdings : - )

I can see you’ve manually input the dividend return for Feb and May in CTY.

Is there a technical solution for calculating this return in Google Docs ( i.e. to input the dividend return in the correct column based on the pay date and your holdings)? Or are you doing this manually from company listings / Google / LSE etc.?

21%20PM

Worked ok for me

Unfortunately, I don’t know how to this programmatically. When I receive my dividends i just input the amount manually.

I would then summarise all of it in another sheet that looks like below. This me allows to compare year on year growth in my dividend income. I’d love to get to a point where my dividend income can sustain my lifestyle one day. In the meantime, re-invest everything back.

1 Like

With regards to re-investing dividends back. Some brokers allow DRIP but others do not and it can get really expensive. I usually wait until dividends accumulate to say 1K before re-investing back just to make it worthwile with 9 quid commission.Fortunately with FreeTrade, I don’t need to wait for my dividends to accumulate as I can re-invest it with zero to minimal commission.

You could use something like this, which pulls in the price of Vanguard’s Ftse global all-cap fund:

=value(SUBSTITUTE(query(IMPORTXML("https://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results/v/vanguard-ftse-global-all-cap-index-accumulation","//span[@class='bid price-divide']"),"select* limit 1",0),"p","",1))

Just change the link to whichever fund you’re after.

4 Likes

Example for Lifestrategy 100% in pounds:

=value(SUBSTITUTE(query(IMPORTXML("https://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results/v/vanguard-lifestrategy-100-equity-accumulation","//span[@class='bid price-divide']"),"select* limit 1",0),"p","",1))/100
3 Likes

Oh wow, that works! That is clever and thank you very much.

1 Like

No worries.

When I get around to it: I’ll work out a way of grabbing yield and/or dividend per share from somewhere, so it requires as little manual entry as possible.

You could probably pull in ex-dividend dates, payment dates and so on too.

I’m all ears, if anyone in the hive mind’s got any ideas!

1 Like

3 posts were split to a new topic: Finki