Stock history formula how to convert GBp to GBP

[vampsthevampyre]

Morning All,

I'm currently trying to convert stock and ETF prices into UK pounds (£), the problem I'm hitting is the two different versions of GB currency. The share price of Aviva Plc and INRG are in GBp (0.01 GBP) and the VUSA and VFEM ETF's is in GBP (1 GBP), the 3M stock in USD are easy to convert.

How do I get a formula to differentiate between the two and give a GBP (£) in the share price column. My google skills have let me down and excel thinks that GBP and GBp are the same thing and I have two ETF's that are different values VUSA (GBP) and INRG (GBp).

Any help will be appreciated

Best Regards

Ian

Stock Question.xlsx
	C	D	E	F	G	H
2	Excel Stock	Exchange	Instrument Type	Purchase Share Price	Currency	Share Price (£)
3	3M COMPANY (XNYS:MMM)	XNYS	Stock	93.76	USD	£118.36
4	AVIVA PLC (XLON:AV.)	XLON	Stock	427.6	GBp	£427.60
5	Vanguard S&P 500 ETF USD D (XLON:VUSA)	XLON	ETF	74.375	GBP	£74.38
6	iShs Glbl Clean Engy ETF USD D (XLON:INRG)	XLON	ETF	646.25	GBp	£646.25
7	Vanguard FTSE EM ETF USD D (XLON:VFEM)	XLON	ETF	43.788	GBP	£43.79
8
9
10		GBP/GBp	Currency	From Currency	Price
11		USD/GBP	GBP	USD	£ 0.79
12		GBP/USD	USD	GBP	$ 1.26
Sheet1

Cell Formulas
Range	Formula
D3:D7	D3	=[@[Excel Stock]].[Exchange abbreviation]
E3:E7	E3	=[@[Excel Stock]].[Instrument type]
F3:F7	F3	=[@[Excel Stock]].Price
G3:G7	G3	=[@[Excel Stock]].Currency
H3:H7	H3	=IF([@Currency]="USD",[@[Purchase Share Price]]*G12,[@[Purchase Share Price]])
E11:E12	E11	=[@[GBP/GBp]].Currency
F11:F12	F11	=[@[GBP/GBp]].[From currency]
G11:G12	G11	=[@[GBP/GBp]].Price

 

[FormR]

Hi, perhaps you could try:

=IF([@Currency]="USD",[@[Purchase Share Price]]*G12,IF(EXACT([@Currency],"GBp"),[@[Purchase Share Price]]*0.01,[@[Purchase Share Price]]))

 

[vampsthevampyre]

Hi, perhaps you could try:

=IF([@Currency]="USD",[@[Purchase Share Price]]*G12,IF(EXACT([@Currency],"GBp"),[@[Purchase Share Price]]*0.01,[@[Purchase Share Price]]))

Thanks for that, I didn't even know the "exact" function existed