How to create a comparitive currency converter tool in Excel (Mastercard rate vs. Interbank rate)

Question

Summary: Help me create an Excel tool that scrapes currency conversion data from specified sources, rather than Excel's built-in conversion tool.

I have two current accounts geared towards ease of use for travelling, Revolut and Starling. Revolut is a Visa card, and Starling is a Mastercard. They both use slightly different conversion rates as provided by these two websites:

https://www.revolut.com/currency-converter/

https://www.mastercard.us/en-us/personal/get-support/convert-currency.html

To my knowledge, the 'Interbank' conversion rate used by Revolut is supposedly a little better on average than Starling's Mastercard conversion rate. Yay Revolut. However, there are other things to consider...

Revolut has a £200 monthly fee-free withdrawal limit, after which it starts charging 2%.

Starling has no monthly fee-free withdrawal limit.

Yay Starling

Starling has a daily withdrawal limit of £300 (a hard ceiling that can't be raised)

Revolut's daily withdrawal limit is €3,000 currency equivalent

Yay Revolut.

Another layer of difficulty is the flat fee that a lot of ATMs in SE Asia charge for a withdrawal (ranging from nothing to ~£5, or sometimes a percentage of the withdrawal amount)

Below is an example of why I would need to make these comparisons, but it is just extra fluff which probably doesn't impact how you would tackle the import of the data from the two websites.

---

So you may see my problem if I wanted to take out 20,000 Thai Bhat, for example.

Regardless of which card I use, Thai ATMs will charge 220 Bhat straight off the bat, so each bank will really be converting 220 Bhat on top of whatever i want to withdraw.

At the time of writing, Revolut would charge me £446.53 for the basic conversion.

If this was the first withdrawal of the month, then £200 of this would be fee-free, leaving the remaining £246.53 liable to their 2% surcharge, stinging me an extra £4.93.

Overall cost: £451.46

With Starling, the withdrawal amount would exceed the daily limit, so I would need to split the withdrawal over two days.

I withdraw 10,000 Bhat on day one. 220 ATM fee, so Mastercard has to convert 10,220 Bhat. They are currently quoting £227.80.

No % withdrawal fees for Starling, however I have to repeat this process the next day, so another £227.80

Overall cost: £455.60

So Revolut wins this one. But conversion rates change. Of course each conversion rate won't stray too far from the other, however what if was somewhere where the ATMs do not charge a flat amount. Some may charge a percentage cut, some may not even charge at all. I imagine that the favour would swing to Starling in the latter case. Also, the above example assumes I haven't already reached my monthly fee-free £200 withdrawal on my Revolut card. If I had already reached this limit then the favour would swing even more heavily toward Starling.

I would like to create a little Excel tool which I could access on Excel for iOS so I can run these checks when I'm out and about wanting to make a withdrawal. I think I would be able to tackle all of the rest of the tool but the data stream from web sources seems like it would be tricky.

Answer 1

I'm sorry to hear that the web query method didn't work for you. It's possible that the websites you mentioned use a different method to display their data, such as JavaScript or dynamic content, which can make it difficult to scrape data using a web query.

An alternative method to extract data from a website is by using web scraping techniques. Web scraping involves writing code to send a request to a website, retrieve the HTML response, and then parse the HTML to extract the desired data. There are many libraries and tools available for web scraping, such as BeautifulSoup and Selenium for Python.

However, web scraping can be more complex than using a web query and may require some programming knowledge. Additionally, some websites may have measures in place to prevent or limit web scraping, so it's important to check the website's terms of service before attempting to scrape data.

Regards Adeyemi

Answer 2

Thank you for your reply Adeyemi.

Unfortunately I think the only websites that have the specific data I require are the ones I listed since they are slightly different to typical exchange rate results from Google or Yahoo. When I tried that method with those websites, the web query tool could not scrape anything useful from them. One site gave a 'browser not supported' error, and the other had nothing of use in any field I could find by navigating through various 'child' branches in Power Query Editor (see attached image). I may be wrong, but it seems this method accesses the data by analysing the webpage once it has been compiled by the browser (my guess is its not too dissimilar to interpreting a screenshot of the webpage), is there an alternative method that reads the source code of the website itself to extract the relevant data?

I wonder if I would run into problems with both of these webpages with the method you have given anyway because both sites give their data in a dropdown menu selection format, rather than a large table.

Answer 3

Hello Cameron

I’m Adeyemi and I’d be happy to help you with your question.

You can create a currency converter tool in Excel that scrapes currency conversion data from specified sources. One way to do this is by using a web query to import data from an online exchange rate table, such as the one on the Yahoo Finance page. You can then set up a VLOOKUP or INDEX/MATCH formula to get the relevant exchange rates for you.

Here are the steps to set up a web query in Excel:

1. Create a new sheet (recommended).
2. Click Data > From Web.
3. Enter the URL of the page with the data into the URL field.
4. Click the yellow arrow beside the exchange rate table. The arrow should turn into a green checkmark.
5. Click Import.
6. Click Properties.
7. Under Refresh Control, specify how often you want Excel to retrieve new data, or pick Refresh data when opening the file.

Once you have set up the web query, you can use VLOOKUP or INDEX/MATCH formulas to get the relevant exchange rates for your currency conversion tool.

I hope this helps!

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below

Regards Adeyemi