tjh290633 wrote:It strikes me that you are going a long way round to achieve your objectives.....
TJH
Hi TJH, see my comments on your comments in italics.
The biggest problem with managing dividends I find is the fact that the dates and amounts aren’t known precisely till they are announced, but I still want to have a schedule for the year on the 1st Jan – so hence the method of a best guess list at the start which is then updated as time passes with inputs from various sources.
The problem that I see with your method is that the linkage to the number of shares held may be incorrect, if shares are sold XD or if shares are added after the XD date.
When a share goes XD I take the row for that dividend and overwrite it with a copy and paste special values so the values cant change thereafter. So if for example I buy more shares or sell some or all of them. Of course selling after the XD date does not stop the dividend payment. And if I buy or sell XD then I want the new number of shares to be present starting with the next dividend. If I buy a completely new holding I create new dividend table entries for that after the buy.
I use conditional formatting to highlight today's date and for dividends unchanged, increased, decreased, passed or awaiting currency declarations.
I use conditional formatting to highlight dividends that have gone XD or the payment due date is met. For dividends requiring a currency conversion I record the rate in a comments field when it is published.
I store an abridged copy of RNS announcements with the essential data about each payment. I also keep a spreadsheet for each financial year as a record of dividends received. This just entails copying the data for each payment from the main spreadsheet.
I don’t keep RNS data and I just add to my dividend table every year adding next years dividends to the bottom of the list. This acts as my record of past dividends. I use this to calculate the dividend element of total return for each holding, spanning multiple years where necessary.
Additionally I have a chronological record of events for each share held, from first purchase to eventual disposal.
I have a Cash table which records every transaction on any of our accounts – be it Buy, Sell, Dividend, Fees, Deposits or Withdrawals everything that affects the balance. The primary purpose of this is to reconcile the Cash balance with my broker accounts. However for Buys, Sells, Dividends etc. I also record the stock ticker, purchase/sell/dividend amounts, number of shares etc. in the Cash table in additional columns I can filter this by ticker or date/time range or whatever to see a record of all activity on that investment.
But I guess its each to their own when it comes to spreadsheet solutions !.
ATB
Pref