HYPTUSS Dividend Tab - different currencies

[torata]

Hi guys

As always so many thanks for all your work on HYPTUSS.
b-f.ods is all up and running well.

While I am in setting up mode....
On the dividends tab I use vlookup to give me a rough idea of the actual amount coming in for each share, but have been unable to cope with USD and EUR amounts. They just get treated as if they were GBP and come up with a spurious amount that I ignore.

Is there any (formula) way for me to separate out the currency info contained in column i so that I can make calculations on the fly?
The currency in each cell seems to be formatting element, rather than text, so I can't see a way to pull it out.

thanks

torata

[Itsallaguess]

On the dividends tab I use vlookup to give me a rough idea of the actual amount coming in for each share, but have been unable to cope with USD and EUR amounts. They just get treated as if they were GBP and come up with a spurious amount that I ignore.

Is there any (formula) way for me to separate out the currency info contained in column i so that I can make calculations on the fly?
The currency in each cell seems to be formatting element, rather than text, so I can't see a way to pull it out.

Can you give us a couple of GBP/Euro/USD example shares for us to have a play with torata?

Cheers,

Itsallaguess

[torata]

I'll try a table...

[torata]

Nope, that didn't work, so here are 3 examples from rows 28, 29 and 33 of the dividend tab
Final 2 are my added formulas in columns J (vlookup to get the number of shares) and K (to make the calculation)
Note the currency is included in the data for column i (amount), but as I say, that seems to be a formatting function of LibreOffice.
(I have blanks for columns E (Dec date) and G (record date) because source is Investorease)

Royal Dutch Shell 'B' RDSB Oil & Gas Producers 16-Nov-17 20-Dec-17 USD 0.47 =VLOOKUP(C28,'High Yield Portfolio'.$C$6:$F$41,4,0) =I28*J28
BP BP Oil & Gas Producers 09-Nov-17 21-Dec-17 GBP 0.07621 =VLOOKUP(C29,'High Yield Portfolio'.$C$6:$F$41,4,0) =I29*J29
Vodafone Group VOD Mobile Telecommunications 23-Nov-17 02-Feb-18 EUR 0.0484 =VLOOKUP(C33,'High Yield Portfolio'.$C$6:$F$41,4,0) =I33*J33

torata

[Itsallaguess]

Here are 3 examples from rows 28, 29 and 33 of the dividend tab

Final 2 are my added formulas in columns J (vlookup to get the number of shares) and K (to make the calculation)

Note the currency is included in the data for column i (amount), but as I say, that seems to be a formatting function of LibreOffice.

(I have blanks for columns E (Dec date) and G (record date) because source is Investorease)

Royal Dutch Shell 'B' RDSB Oil & Gas Producers 16-Nov-17 20-Dec-17 USD 0.47 =VLOOKUP(C28,'High Yield Portfolio'.$C$6:$F$41,4,0) =I28*J28

BP BP Oil & Gas Producers 09-Nov-17 21-Dec-17 GBP 0.07621 =VLOOKUP(C29,'High Yield Portfolio'.$C$6:$F$41,4,0) =I29*J29

Vodafone Group VOD Mobile Telecommunications 23-Nov-17 02-Feb-18 EUR 0.0484 =VLOOKUP(C33,'High Yield Portfolio'.$C$6:$F$41,4,0) =I33*J33

I think we can fix this for you, please see -

https://i.imgur.com/S3XIyPG.png

The way I did this was to carry out the following -

1. Copy a blank cell from anywhere in Column R on the Dividends sheet, and PASTE that cell into all the cells in your COLUMN K. This is to over-write your existing cells that may contain formats that might 'get in the way' of the following process.

2. Run your dividend process using the 'Update Dividends' button.

2. Select the full range of used cells in your COLUMN I, and right-click COPY. In my example on the above snapshot, this would be cells I6, I7 and I8, but you want to do this for the range in your own portfolio list.

3. Right-click in CELL K6, and select the option to 'PASTE SPECIAL'

4. On the PASTE SPECIAL window, in the 'Selection' section, un-select everything, and then just select 'FORMATS', and then hit the 'OK' button.

5. In Cell K6, manually type =I6*J6

6. This should show a correct dividend-expectation for the ROW 6 share in the correct currency for that row.

7. Now go down all the ROWS in your spreadsheet, and in the corresponding COLUMN K cell, manually type the above formula for each of the next rows (the next row down will be =I7*J7, then =I8*J8 etc...)

8. It's important to manually type these cell-multiplication formulas, because if we copy and paste anything at this point, we are likely to confuse the previously copied cell formats, which contain the required currency formats for each line of your portfolio.

9. Once you get to the last line of your portfolio using the above manual-typing process, you should now see the correctly multiplied dividend expectation, in the correct currency for each line.

Do you want to give the above a go, and let me know how you get on?

Cheers,

Itsallaguess

[torata]

Thanks Itsallaguess

I don't have time to look at your suggestion immediately, but will get back to you.

torata

[torata]

Hi Itsallaguess

Thanks for the suggestion. Although it's useful to keep the formatting for the result, in fact I was looking to change the Dollar and Euro dividends into pounds (to give me an idea of what was due to come in that month).

And surprisingly, I seem to have been able to do it. I say surprisingly because I assumed the GBP, USD and EUR signifiers were part of the fomatting element of the cell (because the currency texts disappear if you change the formatting from say currency to accounting)

This is how I did it.
I duplicated the dividend column (I found I had to do this manually for each cell, because autofill overwrote all currency texts into GBP)
I used 'data'>>'text to column' with 'space' as the separator. This split the currency signifiers from the dividend amount, putting the amount in the next column.
Then I used IFS function to test if signifier was USD or Euro and multiply by the appropriate currency rate.

So a semi automatic way to know roughly how much is coming in this month and next (I'm ignoring the fact that dividends have declared and fixed exchange rates)

To be honest I'm expcting it to all fall over when I next update the dividend tab in a month or so, because the currency signifiers don't seem to be... I dunno, 'robust' enough. But it's satisfying to crack a problem.

torata