Basic Currency Exchange lookup spreadsheet, looking to automate

[mrbenn]

At work I have to update the attached spreadsheet daily, filling in the day's rate for GBP -> EUR and GBP -> USD and the vice versa values (update via a formula)
Is there some easy way to automate this and also backdate over the past few weeks? I have been on furlough from work because of the Covid-19 pandemic. I have pasted in the output from XL2BB below

Thanks in advance

Stephen

	£ to € Euro	€ Euro to £	£ to $ Dollar	$ Dollar to £
09/03/2020	1.1537	0.8668	1.3149	0.7605
10/03/2020	1.1472	0.8717	1.3062	0.7656
11/03/2020	1.1427	0.8751	1.2930	0.7734
12/03/2020	1.1342	0.8817	1.2747	0.7845
13/03/2020	1.1275	0.8869	1.2580	0.7949
16/03/2020	1.1275	0.8869	1.2323	0.8115
17/03/2020	1.0973	0.9113	1.2211	0.8189
18/03/2020	1.0954	0.9129	1.2026	0.8315
19/03/2020	1.0621	0.9415	1.1532	0.8672
20/03/2020	1.0967	0.9118	1.1820	0.8460
23/03/2020	1.0855	0.9212	1.1584	0.8633
24/03/2020	1.0793	0.9265	1.1667	0.8571
25/03/2020	1.0941	0.9140	1.1820	0.8460
26/03/2020	1.0902	0.9173	1.1914	0.8393

 

[Rijnsent]

Hi Stephen,
what's your current source/formula for that data and/or where would you look for historical rates? If it is taken from some online source you could definitely automate it with VBA.
Cheers,
Koen

 

[mrbenn]

I do it manually from XE Currency Tables , 1 for GBP to EUR and 1 from USD to GBP and then just run a simple 1/C6 to get the corresponding rate

Stephen

 

[Rijnsent]

Hi Stephen,
XE has an API service, but that will set you back $800 a year. So your next best option is to build something scraping their site. I've had a look and they made their site rather hard to scrape. My best guess would be to try e.g. this solution: VBA Currency Conversion Function (Historical Date)
Cheers,
Koen

 

[mrbenn]

Forgive me but how do I run this after putting it into the workbook through the macro editor?