Historic currency conversion

[gb24]

Hi, looking to do a exchange rate conversion at a specific date to return a result in the orange cell. The base currency is GBP, but the counter currency is selective from a drop down list of CHF / USD / EUR, the exchange rate date fluctuates dependent upon the payment date entered. Not sure if that's enough information or will work! TIA

 

[etaf]

do you have a table of conversion rates for all currencies in dropdown and the dates on a sheet
you could thne use a lookup of some kind
depends on the table
maybe a grid of dates and currencies with the conversion
and then a sumproduct() to lookup the grid to find the actual rate and conver

 

[gb24]

Thanks! Hoping to avoid having the enter the currency conversions into a different spreadsheet, as once it's left me I won't be in charge of updating it. I was hoping that 'stockhistory' would work but my Excel skills don't make it happen! :D

 

[etaf]

ok, no idea sorry never used stockhistory

 

[gb24]

ok, no idea sorry never used stockhistory

No worries, thank you for your answer. I'll definitely use your suggestion if I can't get this to work

 

[etaf]

how far back would you need to go - just had a play
USED
=STOCKHISTORY("gbp/usd", TODAY()-30, TODAY(),0,2,0,1)
looks like it could be used in agrid somehow - will look further

Book1
	A	B
1	GBPUSD
2	Date	Close
3	3/18/24	$ 1.27
4	3/19/24	$ 1.27
5	3/20/24	$ 1.28
6	3/21/24	$ 1.27
7	3/22/24	$ 1.26
8	3/25/24	$ 1.26
9	3/26/24	$ 1.26
10	3/27/24	$ 1.26
11	3/28/24	$ 1.26
12	3/29/24	$ 1.26
13	4/1/24	$ 1.26
14	4/2/24	$ 1.26
15	4/3/24	$ 1.27
16	4/4/24	$ 1.26
17	4/5/24	$ 1.26
18	4/8/24	$ 1.27
19	4/9/24	$ 1.27
20	4/10/24	$ 1.25
21	4/11/24	$ 1.26
22	4/12/24	$ 1.25
23	4/15/24	$ 1.24
Sheet1

Cell Formulas
Range	Formula
A1:B23	A1	=STOCKHISTORY("gbp/usd", TODAY()-30, TODAY(),0,2,0,1)
Dynamic array formulas.

 

[etaf]

had a play and mocked something up
not fully tested

But an idea - I may change and improve

=STOCKHISTORY(B6&":"&B5, TODAY()-200, TODAY(),0,2,0,1)

the exchange rate is created on the fly - based on the dropdown cells
and on one sheet for simplicity

on dropbox for a few days

Dropbox

www.dropbox.com

so when you change the dropdown , it will update the list and then lookup the rate - i have used lowest - but could use exact lookup as 0) as its producing daily figures

can change the range of dates it looksup

issue will be the historic data will change as its all dynamic

just an idea

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2												GBPUSD
3												Date	Close
4												9/29/23	$ 1.22
5		USD					CHF					10/2/23	$ 1.21
6		GBP					USD					10/3/23	$ 1.21
7							EUR					10/4/23	$ 1.21
8			conversion	stirling								10/5/23	$ 1.22
9	10/1/23	50	$ 1.22	40.993687								10/6/23	$ 1.22
10	2/1/24	100	$ 1.27	78.4806153								10/9/23	$ 1.22
11	3/1/24	1000	$ 1.27	790.513834								10/10/23	$ 1.23
12	4/1/24	55550	$ 1.26	44262.9482								10/11/23	$ 1.23
13												10/12/23	$ 1.22
14												10/13/23	$ 1.21
15												10/16/23	$ 1.22
16												10/17/23	$ 1.22
17												10/18/23	$ 1.21
18												10/19/23	$ 1.21
19												10/20/23	$ 1.22
20												10/23/23	$ 1.22
21												10/24/23	$ 1.22
22												10/25/23	$ 1.21
23												10/26/23	$ 1.21
24												10/27/23	$ 1.21
25												10/30/23	$ 1.22
26												10/31/23	$ 1.22
27												11/1/23	$ 1.22
28												11/2/23	$ 1.22
29												11/3/23	$ 1.24
30												11/6/23	$ 1.23
31												11/7/23	$ 1.23
32												11/8/23	$ 1.23
33												11/9/23	$ 1.22
34												11/10/23	$ 1.22
35												11/13/23	$ 1.23
36												11/14/23	$ 1.25
37												11/15/23	$ 1.24
38												11/16/23	$ 1.24
39												11/17/23	$ 1.25
40												11/20/23	$ 1.25
41												11/21/23	$ 1.25
42												11/22/23	$ 1.25
43												11/23/23	$ 1.25
44												11/24/23	$ 1.26
45												11/27/23	$ 1.26
46												11/28/23	$ 1.27
47												11/29/23	$ 1.27
48												11/30/23	$ 1.26
49												12/1/23	$ 1.27
50												12/4/23	$ 1.26
51												12/5/23	$ 1.26
52												12/6/23	$ 1.26
53												12/7/23	$ 1.26
54												12/8/23	$ 1.25
55												12/11/23	$ 1.26
56												12/12/23	$ 1.26
57												12/13/23	$ 1.26
58												12/14/23	$ 1.28
59												12/15/23	$ 1.27
60												12/18/23	$ 1.26
61												12/19/23	$ 1.27
62												12/20/23	$ 1.26
63												12/21/23	$ 1.27
64												12/22/23	$ 1.27
65												12/25/23	$ 1.27
66												12/26/23	$ 1.27
67												12/27/23	$ 1.28
68												12/28/23	$ 1.27
69												12/29/23	$ 1.27
70												1/1/24	$ 1.27
71												1/2/24	$ 1.26
72												1/3/24	$ 1.27
73												1/4/24	$ 1.27
74												1/5/24	$ 1.27
75												1/8/24	$ 1.27
76												1/9/24	$ 1.27
77												1/10/24	$ 1.27
78												1/11/24	$ 1.28
79												1/12/24	$ 1.28
80												1/15/24	$ 1.27
81												1/16/24	$ 1.26
82												1/17/24	$ 1.27
83												1/18/24	$ 1.27
84												1/19/24	$ 1.27
85												1/22/24	$ 1.27
86												1/23/24	$ 1.27
87												1/24/24	$ 1.27
88												1/25/24	$ 1.27
89												1/26/24	$ 1.27
90												1/29/24	$ 1.27
91												1/30/24	$ 1.27
92												1/31/24	$ 1.27
93												2/1/24	$ 1.27
94												2/2/24	$ 1.26
95												2/5/24	$ 1.25
96												2/6/24	$ 1.26
97												2/7/24	$ 1.26
98												2/8/24	$ 1.26
99												2/9/24	$ 1.26
100												2/12/24	$ 1.26
101												2/13/24	$ 1.26
102												2/14/24	$ 1.26
103												2/15/24	$ 1.26
104												2/16/24	$ 1.26
105												2/19/24	$ 1.26
106												2/20/24	$ 1.26
107												2/21/24	$ 1.26
108												2/22/24	$ 1.27
109												2/23/24	$ 1.27
110												2/26/24	$ 1.27
111												2/27/24	$ 1.27
112												2/28/24	$ 1.27
113												2/29/24	$ 1.26
114												3/1/24	$ 1.27
115												3/4/24	$ 1.27
116												3/5/24	$ 1.27
117												3/6/24	$ 1.27
118												3/7/24	$ 1.28
119												3/8/24	$ 1.29
120												3/11/24	$ 1.28
121												3/12/24	$ 1.28
122												3/13/24	$ 1.28
123												3/14/24	$ 1.28
124												3/15/24	$ 1.27
125												3/18/24	$ 1.27
126												3/19/24	$ 1.27
127												3/20/24	$ 1.28
128												3/21/24	$ 1.27
129												3/22/24	$ 1.26
130												3/25/24	$ 1.26
131												3/26/24	$ 1.26
132												3/27/24	$ 1.26
133												3/28/24	$ 1.26
134												3/29/24	$ 1.26
135												4/1/24	$ 1.26
136												4/2/24	$ 1.26
137												4/3/24	$ 1.27
138												4/4/24	$ 1.26
139												4/5/24	$ 1.26
140												4/8/24	$ 1.27
141												4/9/24	$ 1.27
142												4/10/24	$ 1.25
143												4/11/24	$ 1.26
144												4/12/24	$ 1.25
145												4/15/24	$ 1.24
146
Sheet2

Cell Formulas
Range	Formula
L2:M145	L2	=STOCKHISTORY(B6&":"&B5, TODAY()-200, TODAY(),0,2,0,1)
C9:C12	C9	=INDEX($M:$M,MATCH(A9,$L:$L,1))
D9:D12	D9	=B9/C9
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B5	List	=$G$5:$G$7