Jan 18 2021 10:24 PM
Hi, first post here, is it possible to set a cell to retreive live data such as currency rates?
I would like to be able to keep a track of current conversion on a sheet I'm biulding.
For instance, I would like to display my total balance which is Dollars, in GBP alongside.
Is this possible, and if so, could someone help me do it plase?
Jan 19 2021 09:37 AM
If Stock data type is available for your version of Excel you may pickup current GBP/USD exchange rate by it.
Jan 19 2021 10:55 AM
@Sergei BaklanI was informed the data is available here, by copy a URL, however I am unsure which to copy. Also, once copied URL, then back in XL click DATA and click "From Web" and paste the address, however I can find no "From Web" button, perhaps it doesn't exist in free version?
Currency data is available Here: https://www.x-rates.com/table/?from=GBP&amount=1
It's is quite simple what I am trying to acheive I would have thought.
Here a test sheet I made:
Obviously, I want to convert the GBP to USD using real time data
Jan 19 2021 11:18 AM
The connector is here
First time it will ask authorization, select Anonymous. After that click Transform, Power Query editor will be opened. Rename the query and in UI editor transform the table to keep only US dollar exchange rate. Load resulting table into any suitable place of your workbook. Open query properties and select update with workbook opening
With that formula for the conversion will be like
=F3*GBP2USD[Rate]
If use Stock or Currency data type you'll have same result much easier, just type in any call USD/GBP, click on icon and extract rate field.
It will be updated automatically every 15 minutes as I remember, but not sure.
Both variant are in attached file.
Jan 20 2021 09:57 PM
@Sergei BaklanWoW! Thats great Sercei, I'll give it a try later