I have a table with values such as "EUR/USD" or "GBP/EUR", "CAD/USD" etc. in col Asset and the relative amount in col Open Value. I want to be able to scan col Asset and add up values in col Open Value. The inbuilt table filter would not be helpful here becasue the combination of currency pairs is high.
Asset ¦ Buy TimeStamp ¦ Buy ¦ Units ¦ Open Value
EUR/GBP¦ Fri 08 03-13:42 ¦ 0.85895 ¦ 50,000 ¦ 42,947.50
EUR/USD¦ Fri 08 03-14:26 ¦ 1.12311 ¦ 50,000 ¦ 56,155.50
Ideally I would scan the Asset col with a RIGHT() function in order to extract its righmost value, i.e. "GBP", "USD" but is not strictly necessary I can do that in some other col.
I then want to sum all values in Open Value relevant to "GBP", "USD" etc. This is necessary because it makes no sense to add up a total of values in different currencies.
If I could update data by automatically add a currency not previously listed with relative sum such as example below - that would be great
GBP ¦ 2,158.25
USD ¦ 15,657.65
EUR ¦ 1,298.36
CAD ¦ 26,357.41
new currency not listed before ¦ total
Any ideas anyone?
Thanks - Michael
[p.s. this editor is dreadful]