thoughts on the 'Dividends' tab

[mrbrightside]

I have used HYPTUSS for many years and simply love it. I am immensely grateful to the authors, especially the fact it works in LibreOffice (on Linux). I really liked the recent changes to add 'Overview' and 'Snapshots' functionality.

Like many others (I suspect), I also use Google Sheets (Excel) for additional detailed analysis and reports. One of those reports is 'Forecast Dividends'.

Currently, I laboriously update this sheet by clicking the 'Update Dividends' button on the HYPTUSS 'Dividends' tab, sorting on payment date and then copying/pasting the relevant cells into my Google sheet.

Then I have to:-

• If currency code = 'GBP' then remove the currency code. This means deleting 4 characters, 4 key strokes. Multiply by N.
• If currency code = 'USD' then lookup the current conversion rate between 'USD' and 'GBP' and enter the resulting number into the sheet.

Would it be better to separate the 'Currency Code' from the 'Amount' column ? As a database chap, this overloading of two separate semantic items in a single field seems slightly anomalous anyway...

Could HYPTUSS be enhanced to read the current holding for XYZ (from the 'High Yield Portfolio' sheet) and calculate the forecast dividend in a new column ?

As a deluxe bonus (subscription only) option, could HYPTUSS be further enhanced to lookup the current 'USD/EUR' -> 'GBP currency conversion rate ?
--
Andy

[Breelander]

...could HYPTUSS be further enhanced to lookup the current 'USD/EUR' -> 'GBP currency conversion rate ?

I manually look them up here: https://www.xe.com/currencycharts/
Not sure if that source is amenable to scraping.....

[spiderbill]

Eventide's Excel script can be set to get the currency rates from Yahoo (I get the US/GB one using GBPUSD=X) so I would have thought it should be perfectly possible.

I've also added extra columns to my copy of HYPTUSS to get the GBP conversion of any shares that report in other currencies so I can project ahead (in yet more extra columns) to what I can expect in the coming year in detail. Whether that's worth including in a future official version is of course up to our blessed project leaders

They'll doubtless be along any minute...

[Itsallaguess]

Could HYPTUSS be enhanced to read the current holding for XYZ (from the 'High Yield Portfolio' sheet) and calculate the forecast dividend in a new column ?

As a deluxe bonus (subscription only) option, could HYPTUSS be further enhanced to lookup the current 'USD/EUR' -> 'GBP currency conversion rate ?

Hi Andy,

Thanks for your suggestions, and I do agree that this is an area that we might be able to look at for our next round of improvements.

Regarding your first point, the process I normally use is to run the 'Update Dividends' process, which presents us with a dividend-sheet-output in the same order as our main portfolio sheet, and then I copy over the 'Shares held' column information from the main sheet to the dividends sheet. I then introduce an additional column on the right of the shares-held data, which then automatically multiplies out both the 'Amount' data and the 'Shares held' data, which gives a total-expected-dividend, but obviously based in the currency of each particular dividend payment at that point.

I then manually sort the whole of the dividend-sheet-data in date-order, from earliest to latest payment-date vertically down the sheet, including the two additional manual columns entered above.

For some time now, and like you, I've thought that this overly-manual process might also be useful to many other users of the tool, and automating it would be extremely helpful. I think kiloran and I have even discussed this possible improvement in the past, so I think we may re-visit the possibility during the next round of updates.

Additionally, as you helpfully suggest, having an auto-scrape of current GBP to USD and EUR currency-rates would also be a hugely useful feature, and would then enable us to automate the above process into an 'expected dividend' column in UK currency, which would possibly be the most useful set of data for the whole process, clearly.

So thanks for raising these points - I think we'll mull them over and consider when we might get to look at this. Given that we've just released a new version with the 'Overview' and 'Snapshots' functionality improvements, then we probably won't be in a rush to touch it for a while unless an existing process fails for some reason, as it's nice to have a break between releases to re-charge our coding-batteries, but I think this is definitely an area that can be improved, and is probably due a coat of looking-at next time round.

Thanks again for your post - I'm glad that you're finding the new functionality useful.

Cheers,

Itsallaguess

[kiloran]

Like many others (I suspect), I also use Google Sheets (Excel) for additional detailed analysis and reports. One of those reports is 'Forecast Dividends'.

Currently, I laboriously update this sheet by clicking the 'Update Dividends' button on the HYPTUSS 'Dividends' tab, sorting on payment date and then copying/pasting the relevant cells into my Google sheet.

Then I have to:-

• If currency code = 'GBP' then remove the currency code. This means deleting 4 characters, 4 key strokes. Multiply by N.
• If currency code = 'USD' then lookup the current conversion rate between 'USD' and 'GBP' and enter the resulting number into the sheet.

Would it be better to separate the 'Currency Code' from the 'Amount' column ? As a database chap, this overloading of two separate semantic items in a single field seems slightly anomalous anyway...
--
Andy

We'll certainly consider your ideas, Andy.

The 3-letter currency codes are just formatting/display items in LibreOffice, so a cell displaying "EUR 1.23" actually contains the value 1.23. Much to my surprise, however, I found that if I copied this then paste-special/values into Google Sheets, it actually pasted "EUR 1.23", rather than the 1.23 I expected.

While we twiddle our thumbs and ponder how to implement your ideas, the following may help you reduce the effort in Google Sheets.....

First, create a sheet to be used for your workings. Your data from HYPTUSS would be pasted into column A

Suppose A1 contains "EUR 1.23"

In B1, type the formula =split(A1," ") .... that's a space between the quotes
This will result in B1 = EUR and C1 = 1.23. You can then copy B1 down the rows

Now, in D1 type the formula =GOOGLEFINANCE("CURRENCY:USDGBP")
and in E1 type the formula =GOOGLEFINANCE("CURRENCY:EURGBP")

That will give you the exchange rates

Finally, in F1, type the formula =if(B1="USD",C1*D1,if(B1="EUR",C1*E1, C1))
F1 will then contain the price in column A converted to GBP, and you can set your main Google sheet to point to this column. All you need to do in future is copy your HYPTUSS data and paste it into Column A

This principle can be extended if you expect currencies beyond GBP, USD and EUR

Hope this helps in the interim

--kiloran

[mrbrightside]

Like many others (I suspect), I also use Google Sheets (Excel) for additional detailed analysis and reports. One of those reports is 'Forecast Dividends'.

Currently, I laboriously update this sheet by clicking the 'Update Dividends' button on the HYPTUSS 'Dividends' tab, sorting on payment date and then copying/pasting the relevant cells into my Google sheet.

Then I have to:-

• If currency code = 'GBP' then remove the currency code. This means deleting 4 characters, 4 key strokes. Multiply by N.
• If currency code = 'USD' then lookup the current conversion rate between 'USD' and 'GBP' and enter the resulting number into the sheet.

Would it be better to separate the 'Currency Code' from the 'Amount' column ? As a database chap, this overloading of two separate semantic items in a single field seems slightly anomalous anyway...
--
Andy

We'll certainly consider your ideas, Andy.

The 3-letter currency codes are just formatting/display items in LibreOffice, so a cell displaying "EUR 1.23" actually contains the value 1.23. Much to my surprise, however, I found that if I copied this then paste-special/values into Google Sheets, it actually pasted "EUR 1.23", rather than the 1.23 I expected.
<snip>

Hi Kiloran

Many thanks for taking the time to reproduce my issue with LO and Google Sheets. I thought it was slightly strange and surprised no-one else reported the same issue (but if it works fine with Excel that may well explain it). Once again, I appear to be an 'edge case'

That detailed workaround is most helpful and much appreciated.

Thanks - Andy

[EssDeeAitch]

Suppose A1 contains "EUR 1.23"

In B1, type the formula =split(A1," ") .... that's a space between the quotes
This will result in B1 = EUR and C1 = 1.23. You can then copy B1 down the rows
--kiloran

Just a point for clarity. The "Split" function referred to is in Google Sheets? As far as I know, there is no "Split" function in Excel (VBA code can be used to create one though) but the same effect can be had by using "Text to Columns"

Is this correct?

[kiloran]

Suppose A1 contains "EUR 1.23"

In B1, type the formula =split(A1," ") .... that's a space between the quotes
This will result in B1 = EUR and C1 = 1.23. You can then copy B1 down the rows
--kiloran

Just a point for clarity. The "Split" function referred to is in Google Sheets? As far as I know, there is no "Split" function in Excel (VBA code can be used to create one though) but the same effect can be had by using "Text to Columns"

Is this correct?

The OP mentioned Google Sheets so that is what I referred to. Text to Columns in Excel should achieve the same result, putting the EUR and 1.23 into separate columns. I'm not aware of a built-in exchange rate function in Excel, though. Not in Excel 2010, for sure.

--kiloran

[EssDeeAitch]

Suppose A1 contains "EUR 1.23"

In B1, type the formula =split(A1," ") .... that's a space between the quotes
This will result in B1 = EUR and C1 = 1.23. You can then copy B1 down the rows
--kiloran

Just a point for clarity. The "Split" function referred to is in Google Sheets? As far as I know, there is no "Split" function in Excel (VBA code can be used to create one though) but the same effect can be had by using "Text to Columns"

Is this correct?

The OP mentioned Google Sheets so that is what I referred to. Text to Columns in Excel should achieve the same result, putting the EUR and 1.23 into separate columns. I'm not aware of a built-in exchange rate function in Excel, though. Not in Excel 2010, for sure.

--kiloran

Interestingly, a brand new exchange rate function has just been released but only on Office 365, the cloud based system. I subscribe to MrExcel for Excel knowledge and he released this video just yesterday. It is thought that this feature will never be released on non-internet versions

https://www.youtube.com/watch?v=rDTx6hM ... ploademail

[genou]

. I'm not aware of a built-in exchange rate function in Excel, though. Not in Excel 2010, for sure.

--kiloran

I'm not aware of one either. But if you use eventide's tlfAddin, you can use e.g

=getdata("gbpusd=X")
=getdata("usdgbp=X")

depending on which way you want the conversion.

[torata]

Would it be better to separate the 'Currency Code' from the 'Amount' column ? As a database chap, this overloading of two separate semantic items in a single field seems slightly anomalous anyway...
Andy

We'll certainly consider your ideas, Andy.

The 3-letter currency codes are just formatting/display items in LibreOffice, so a cell displaying "EUR 1.23" actually contains the value 1.23. Much to my surprise, however, I found that if I copied this then paste-special/values into Google Sheets, it actually pasted "EUR 1.23", rather than the 1.23 I expected.
...

I'd appreciate this feature also. As you say, Kiloran, the currency codes are formatting/display items, so I've not been able to separate out the currency in LibreOffice using just functions.

torata

[Itsallaguess]

Would it be better to separate the 'Currency Code' from the 'Amount' column ? As a database chap, this overloading of two separate semantic items in a single field seems slightly anomalous anyway...
Andy

We'll certainly consider your ideas, Andy.

The 3-letter currency codes are just formatting/display items in LibreOffice, so a cell displaying "EUR 1.23" actually contains the value 1.23. Much to my surprise, however, I found that if I copied this then paste-special/values into Google Sheets, it actually pasted "EUR 1.23", rather than the 1.23 I expected.
...

I'd appreciate this feature also. As you say, Kiloran, the currency codes are formatting/display items, so I've not been able to separate out the currency in LibreOffice using just functions.

Does the 'Paste as unformatted text' process not get you somewhere near, at least as a workaround?

If you've got the following in Cell I6 (following the execution of the 'Update Dividends' process) -

USD 0.1025

and then if you right-click and COPY that cell, and then right-click PASTE-SPECIAL / UNFORMATTED TEXT into Cell J6, that should place this TEXT field in Cell J6 -

USD 0.1025

as that's then a TEXT field you can use a LEFT function in Cell K6 to pull out the CURRENCY part of that field, so in Cell K6, type -

=LEFT(J6,FIND(" ",J6,1))

this would then leave the currency text-field showing as this in Cell K6 -

USD

Obviously the above is showing the process for a single dividend-line, but if you COPY/PASTE-SPECIAL/UNFORMATTED TEXT the whole dividend-data-column, and then dragged down the appropriate function formula above, then it should grab all the specific currencies that you need?

Cheers,

Itsallaguess

[kiloran]

Would it be better to separate the 'Currency Code' from the 'Amount' column ? As a database chap, this overloading of two separate semantic items in a single field seems slightly anomalous anyway...
Andy

We'll certainly consider your ideas, Andy.

The 3-letter currency codes are just formatting/display items in LibreOffice, so a cell displaying "EUR 1.23" actually contains the value 1.23. Much to my surprise, however, I found that if I copied this then paste-special/values into Google Sheets, it actually pasted "EUR 1.23", rather than the 1.23 I expected.
...

I'd appreciate this feature also. As you say, Kiloran, the currency codes are formatting/display items, so I've not been able to separate out the currency in LibreOffice using just functions.

torata

As is typical with spreadsheets, there are many ways to skin this cat.
One simple way in LibreOffice is to select column I in the Dividends sheets, then DATA/TEXT-TO-COLUMNS then select SEPARATED BY SPACE. That will leave the currency code in column I and put the amount in column J

If you don't want to tamper with column I since it is part of the inherent data, just copy column I to a spare column to the right and perform text to columns on that

--kiloran

[torata]

Thanks for the quick responses, Kiloran and IAAG

It works (initially), although it seems quite 'fragile' when using '='sign to replicate the cells (in order to keep text>column away from downloaded data).

I had to make sure that the cells being used for those operations had all formatting stripped, and each cell was done individually, otherwise the USD or EUR would change into GBP or vice versa.

However, I don't think the replicating cells pick up any changes to currency after an update of data i.e. if stocks move up or down to different position. It seems therefore that the currency is fixed to the replicating cell, not to the replicated data.

I'd prefer not to manually copy and paste (isn't that the point of auto-updating spreadsheets?) and it's only a few stock anyway that are not GBP dividends, but I would still like to echo the suggestion that the currency is pulled out into a separate column. It's a "nice to have".

torata

[Itsallaguess]

I'd prefer not to manually copy and paste - isn't that the point of auto-updating spreadsheets? and it's only a few stock anyway that are not GBP dividends, but I would still like to echo the suggestion that the currency is pulled out into a separate column. It's a "nice to have".

The manual copy and paste was only suggested as a workaround in the current absence of an automated solution....

Cheers,

Itsallaguess

[midgesgalore]

I create an adjusted column of dividends and simply divide my $ denoted dividends by a variable dlrx which I manually update now and again. Manually because the dividends can be fixed on exchange rates on specific days before a dividend is paid out, so not the same conversion rate for all dividends anyway.
My euro dividends I divide by another named variable eurx, updated on occasion.
This is roughly correct.

I also use conditional formatting to highlight dividend payments in green, red and blue for last month, this month and next month respectively so I can sum and log the current month for record keeping and the others let me see what has been and what is coming up.

A little bit manual for the summing up but otherwise automatic and "good enough for government work".

midgesgalore